(test-odbc) crashes newLISP

HPW · Post by **HPW** » Tue Sep 26, 2006 6:48 am

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:\windows\system32'.

Any idea?

Lutz · Post by **Lutz** » Tue Sep 26, 2006 5:41 pm

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 · Post by **HPW** » Tue Sep 26, 2006 5:59 pm

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 all

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 · Post by **Lutz** » Tue Sep 26, 2006 6:23 pm

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

Lutz

Lutz · Post by **Lutz** » Sat Sep 30, 2006 5:56 pm

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 all

(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 · Post by **HPW** » Sat Sep 30, 2006 10:36 pm

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 · Post by **Lutz** » Sun Oct 01, 2006 2:33 am

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 · Post by **HPW** » Sun Oct 01, 2006 6:35 am

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 · Post by **Lutz** » Sun Oct 01, 2006 1:51 pm

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/

Lutz

HPW · Post by **HPW** » Sun Oct 01, 2006 3:54 pm

Thanks for the demo data-base.
I put it into the newlisp-dir and choose it in the ODBC-applet:

But in newLISP after load module ODBC.lsp:

Code: Select all

newLISP v.8.9.14 on Win32 MinGW.

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

HPW · Post by **HPW** » Sun Oct 01, 2006 3:59 pm

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 · Post by **HPW** » Sun Oct 01, 2006 4:42 pm

Surf the internet for ODBC config.
This german delphi code shows how to call the API for it:

Code: Select all

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 all

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 · Post by **Lutz** » Sun Oct 01, 2006 6:16 pm

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