(test-odbc) crashes newLISP

[HPW]

Just want to give ODBC.lsp from modules a try.

I start newLISP-TK.exe (8.9.12) and load the module from the source-dir.

When I call (test-odbc) it crashes immediatly.

(Tested on german WIN XP PRO (All service packs and patches)

odbc32.dll is in 'c:windowssystem32'.



Any idea?

[Lutz]

Is it crashing in the ODBC:connect call? Did you make a 'data source' with the odbc applet in the Windows control panel?



Lutz

[HPW]

Is it crashing in the ODBC:connect call?

Yes, it seems to exit here:



(if (not (ODBC:connect "test-db" "" "")) (exit))



and the it crashes newLISP-TK.exe



When I comment the line out I get:

Code Select Expand

newLISP v.8.9.12 on Win32 MinGW.

> (test-odbc)
connected ...
could not allocate statement handle
could not allocate statement handle
could not allocate statement handle
inserted 3 records
could not allocate statement handle
performed a query
0 columns in result set
fetching rows ...

not enough memory
called from user defined function ODBC:bind-columns
called from user defined function ODBC:fetch-row
called from user defined function test-odbc
>


Did you make a 'data source' with the odbc applet in the Windows control panel?

I have 3 data-sources: DBF, XLS and MDB

[Lutz]

I don't have a Windows machine to check this out, until the coming weekend (travelling at the moment).



Lutz

[Lutz]

Works fine for me on XP-Home edition, all service packs patches, tested with newLISP 8.9.0 and 8.9.14 (due later today/this weekend).



You have to configure an Access database: 'test-db' with table 'fruits' and text field 'name' width 20 and field 'qty' as type integer. Then make the 'user data source' connection with the ODBC control applet in Administrative Tools. This is the output you should get:

Code Select Expand

(test-odbc)
connected ...
inserted 3 records
performed a query
2 columns in result set
fetching rows ...
("apples" "11")
("oranges" "22")
("bananas" "33")

rows deleted: 3

closing database
true


Lutz

[HPW]

Thanks, I will test when I have access to a PC with access on it.



When I understand it right, then do you not use newLISP to create the ODBC connection to the MDB-file, the ODBC must be actice and present.

Is there a way to do this also from newLISP?

[Lutz]

ODBC is a kind of translation layer between a database and the application accessing it. Not only on Windows but also on UNIX or Mac OS X. On any platform offering ODBC you will always find some piece of software doing the registration. newLISP then connects to the ODBC layer via the 'ODBC:connect' call.



The idea behind ODBC is, that you only have one SQL based API for a variety of different datasources.



Lutz

[HPW]

That is also my understanding of ODBC, but my question was:



Is there a method of opening a MDB without going to the  ODBC control apple and choose there the MDB. When the ODBC driver is present and I have a MDB anywhere in the file system, can I simple connect to it via newLISP code with a given path-name?



Can you contain the sample MDB in the WIN-distribution to allow users a quick and simple test on their system?

[Lutz]

There is no way to go directly to the MDB, unless you have a driver/library to deal with the Access file format, which is a proprietaty unpublished MS file format. But more important there is a SQL layer in ODBC, which is the same for many applications and file formats.



There is a sample test-db here: http://newlisp.org/downloads/Other/">http://newlisp.org/downloads/Other/



Lutz

[HPW]

Thanks for the demo data-base.

I put it into the newlisp-dir and choose it in the ODBC-applet:



http://www.hpwsoft.de/anmeldung/html1/newLISP/ODBC_err.png">



But in newLISP after load module ODBC.lsp:

Code Select Expand

newLISP v.8.9.14 on Win32 MinGW.

> (ODBC:connect "test-db" "" "")
Could not connect
nil
>

[HPW]

Oops, just get it.



I have to rename the 'Datenquellenname' from 'Microsoft Access Datenbank' to 'test-db'.



Then it works.



Anyway, for a real app, I can not force a normal user to get there into the applet and configure a database.

It should be easier to get it by code.

[HPW]

Surf the internet for ODBC config.

This german delphi code shows how to call the API for it:

Code Select Expand


const
  ODBC_ADD_DSN            = 1; //Add data source
  ODBC_CONFIG_DSN         = 2; //Configure (edit) data source
  ODBC_REMOVE_DSN         = 3; //Remove data source
  ODBC_ADD_SYS_DSN        = 4; //add a system DSN
  ODBC_CONFIG_SYS_DSN     = 5; //Configure a system DSN
  ODBC_REMOVE_SYS_DSN     = 6; //Remove a system DSN
  ODBC_REMOVE_DEFAULT_DSN = 7; //Remove the default DSN

function SQLConfigDataSource(hwndParent: HWND;
  fRequest: WORD;
  lpszDriver: LPCSTR;
  lpszAttributes: LPCSTR): BOOL; stdcall; external 'ODBCCP32.DLL';

procedure TForm1.Button1Click(Sender: TObject);
begin
  if not SQLConfigDataSource(0,ODBC_ADD_SYS_DSN,
   'MySQL ODBC 3.51 Driver', PChar('DSN=Meine Verbindung'#0'Description=Ein Text'#0'Server=192.168.0.1'#0'Database=TestDB'#0'User=Wortmann'#0'Password=geheim'#0) ) then
    ShowMessage('Fehler');
end;

{
Parameter von SQLConfigDataSource

1. Windowhandle halt.
2. ODBC... -> Könnt ihr aus den Konstanten entnehmen
3. Der Treibername. Den könnt ihr aus der ODBC-Konfiguration von Windows unter dem Reiter "Treiber" entnehmen.
   Er muß vollständig so geschieben werden, wie er dort unter Name aufgeführt ist (mit allen Leerzeichen und Klammern, usw).
   Ist ein Parameter falsch wird er ebenfalls nicht berücksichtigt (Es kommt aber auch kein Fehler, die Funktion meldet trotzdem ein True zurück).
   Die Parameter sind von Treiber zu Treiber unterschiedlich. Einfach mal einen Link manuell erstellen, und dann ein bischen probieren bis es klappt. Das Beispiel ist ein MySQL-Treiber.
4. Ein PChar in dem alle Parameter, die notwendig sind um den Link zu erstellen, jeweils durch #0 separiert aufgelistet werden. Nicht vorhandene Parameter werden nicht berücksicht.
}


So it might be possible to do it also from newLISP.

;-)



Watching this DLL with a dependency walker shows a lot of other interesting commands!



For example:

Code Select Expand

function SQLGetInstalledDrivers( lpszBuf: LPSTR; cbBufMax: WORD; var pcbBufOut:
WORD): BOOL; stdcall; external 'ODBCCP32.DLL';

procedure TForm1.FormCreate(Sender: TObject);
 var
  Buffer : string;
  Size   : Word;
  i: Integer;
begin
  SQLGetInstalledDrivers(nil, 0, Size);
  SetLength(Buffer, Size);
  SQLGetInstalledDrivers(PChar(Buffer), Size, Size);
  for i := 1 to Size - 1 do if
   Buffer[i] = #0 then Buffer[i] := #10;
   ComboBox1.Items.Text := Buffer;
end;


[Lutz]

For the near and mid future there are other priorities in newLISP, but perhaps somebody else, wants to add this to the current odbc.lsp module? Several years ago adamss3 would do fixex/modifications to odbc.lsp, but I don't know if he is still around.



Lutz