Board index » delphi » Low-Level ODBC-Calls with Delphi

Low-Level ODBC-Calls with Delphi

Hy,

I want to write an ISAPI-Service with ODBC-Database support and don't
want to have all the overhead from BDE or third-party ODBC-Drivers.
So i tried to access an odbc-datasource by low-level-API-Calls. Reading
data through an select-statement works perfectly fine, but if i try to
send an insert-,update,- or create-statement i don't get any changes in
my database. A call to SQLError returns a SQL_SUCCESS-Value.

Here is some of my code

{################################################}
program odbctest;

uses odbcapi,sysutils;

var rc: RETCODE;
    ahenv: Henv;
    ahdbc: HDBC;
    ahstmt: HSTMT;
    szData: array [0..255] of char;
    cbData,cbValue: sdWord;
    szSQLSTATE: array [0..5] of char;
    fname: array [0..50] of char;
begin
     { Alloc Connection }
     SQLAllocEnv(ahenv);
     SQLAllocConnect(ahenv,ahdbc);
     SQLCOnnect(ahdbc,'odbctest',SQL_NTS,nil,0,nil,0);
     SQLAllocStmt(ahdbc,ahstmt);

{#### Select-Statements work perfectly fine ####}
     SQLExecDirect(ahstmt,'select nr,name from odbctest',SQL_NTS);
     SQLBindCol(ahstmt,2, SQL_C_CHAR, @fname, 50, cbValue);
     rc := SQLFetch(ahstmt);
     while rc = SQL_SUCCESS do begin
           writeln(strpas(@fname));
           rc := SQLFetch(ahstmt);
     end;

{#### Insert-,Update- & Create-Statements doesn't cause an error, but
don't do any changes in database ####}
     SQLExecDirect(ahstmt,'insert into odbctest(nr,name)
values(4,"Test4")',SQL_NTS);

     { Free Connections }      
     SQLFreeStmt(ahstmt,SQL_DROP);
     SQLDisconnect(ahdbc);
     SQLFreeConnect(ahdbc);
     SQLFreeEnv(ahenv);

     readln;
end.

{###############################}

thanks for helping

Bye Andy

 

Re:Low-Level ODBC-Calls with Delphi


Quote
Andreas Spang wrote:

> Hy,

> I want to write an ISAPI-Service with ODBC-Database support and don't
> want to have all the overhead from BDE or third-party ODBC-Drivers.
> So i tried to access an odbc-datasource by low-level-API-Calls. Reading
> data through an select-statement works perfectly fine, but if i try to
> send an insert-,update,- or create-statement i don't get any changes in
> my database. A call to SQLError returns a SQL_SUCCESS-Value.

> Here is some of my code

> {################################################}
> program odbctest;

> uses odbcapi,sysutils;

> var rc: RETCODE;
>     ahenv: Henv;
>     ahdbc: HDBC;
>     ahstmt: HSTMT;
>     szData: array [0..255] of char;
>     cbData,cbValue: sdWord;
>     szSQLSTATE: array [0..5] of char;
>     fname: array [0..50] of char;
> begin
>      { Alloc Connection }
>      SQLAllocEnv(ahenv);
>      SQLAllocConnect(ahenv,ahdbc);
>      SQLCOnnect(ahdbc,'odbctest',SQL_NTS,nil,0,nil,0);
>      SQLAllocStmt(ahdbc,ahstmt);

> {#### Select-Statements work perfectly fine ####}
>      SQLExecDirect(ahstmt,'select nr,name from odbctest',SQL_NTS);
>      SQLBindCol(ahstmt,2, SQL_C_CHAR, @fname, 50, cbValue);
>      rc := SQLFetch(ahstmt);
>      while rc = SQL_SUCCESS do begin
>            writeln(strpas(@fname));
>            rc := SQLFetch(ahstmt);
>      end;

> {#### Insert-,Update- & Create-Statements doesn't cause an error, but
> don't do any changes in database ####}
>      SQLExecDirect(ahstmt,'insert into odbctest(nr,name)
> values(4,"Test4")',SQL_NTS);

>      { Free Connections }
>      SQLFreeStmt(ahstmt,SQL_DROP);
>      SQLDisconnect(ahdbc);
>      SQLFreeConnect(ahdbc);
>      SQLFreeEnv(ahenv);

>      readln;
> end.

> {###############################}

> thanks for helping

> Bye Andy

I suspect that the data is correctly posted to your database, but they
are never commited. Try adding the function SQLTransact.

if (SQLExecDirect(ahstmt, 'insert.....',SQL_NTS) <> SQL_SUCCESS) then
      .. do some error handling..
   else
      if (SQLTransact(ahenv,ahdbc,SQL_COMMIT) <> SQL_SUCCESS then
         .. do some error handling..
      else
          .. OK ..

You have to add the constants (SQL_COMMIT   = 0) and (SQL_ROLLBACK = 1)

One more thing that you might want to change in your code. After the
last connection is disconnected, the connection and enviorment handles
are freed automaticly. The full information on the subject is shown
below.

PSS ID Number: Q101521
Article last modified on 11-10-1994

1.00

WINDOWS

SUMMARY
=======

A driver gets unloaded by the ODBC Driver Manager when an application
calls the ODBC SQLDisconnect() function by passing the handle to the
database connection made by that driver. This article discusses how
the driver is unloaded.

MORE INFORMATION
================

All ODBC calls by an application are intercepted and routed by the
ODBC Driver Manager (DM). When an application calls the
SQLDisconnect() function on a particular connection, the DM calls the
driver's SQLDisconnect() function.

When the driver returns a status of SQL_SUCCESS or
SQL_SUCCESS_WITH_INFO on this call, the DM then calls the driver's
SQLFreeConnect() and SQLFreeEnv() in quick succession. Also, since the
driver has made a successful disconnect on the call to
SQLDisconnect(), the DM calls the FreeLibrary() function in the
Windows API to reduce the reference count of the driver DLL by one.
When the reference count of the Driver DLL gets to zero (0) after
repeated calls to SQLDisconnect() by the application for all
connections made by that driver, Windows will unload the driver DLL.

Driver developers may note minor implications arising out of the
above. Note that every time an application calls SQLDisconnect(), the
DM, besides calling the driver's SQLDisconnect(), also calls the
Driver's SQLFreeConnect() and SQLFreeEnv() immediately. Thus
SQLFreeEnv() is called on every SQLDisconnect(). When the application
calls SQLFreeConnect() and SQLFreeEnv(), the DM does its own cleanup
and does not make the same calls to the Driver because these calls
would have been made at the time of SQLDisconnect().

If an ODBC application terminates without calling SQLDisconnect on all
open connections, Windows will unload the Driver Manager if its
reference count is reduced to zero. However, the Driver Manager cannot
unload the driver DLL because the WEP of the Driver Manager (or any
Windows DLL) cannot call the FreeLibrary function.

Good luck !!

Martin Korsrud          mailto:m...@nera.no

Re:Low-Level ODBC-Calls with Delphi


Hi Andreas,

Quote
You wrote:
> I want to write an ISAPI-Service with ODBC-Database support and don't
> want to have all the overhead from BDE or third-party ODBC-Drivers.
> So i tried to access an odbc-datasource by low-level-API-Calls. Reading
> data through an select-statement works perfectly fine, but if i try to
> send an insert-,update,- or create-statement i don't get any changes in
> my database. A call to SQLError returns a SQL_SUCCESS-Value.
> <snip...>

Step 1: Download ODBCExpress
Step 2: Open the ISAPI sample project
Step 3: Write your own based on (2). <g>

See http://www.odbcexpress.com for more details!

Regards,

Rob
--
Rob McGillivray
Software Development Manager
DataSoft - "Voted Top Development House in South Africa - 1994"
ODBCExpress: the top ODBC data access tool for Delphi,
http://www.odbcexpress.com

Other Threads