Simple SQL DDL statement fails with error: SqlState: 07003
lazarus / 16-Databases

----------------------------------------------------------------------
Buckwheat · 2012-09-17 23:26

Hello guys,

I am trying to execute a simple SQL DDL statement against MonetDB
server using MonetDB ODBC driver and TODBCConnection.ExecuteDirect:

Code: [Select]Connection.ExecuteDirect('create table a (col1 int);');
I am always getting EODBCException with the message:

Could not execute statement: ODBC error details: LastReturnCode:
SQL_ERROR; Record 1: SqlState: 07003; NativeError: 0;
Message: [MonetDB][ODBC Driver 11.11.7]EXEC: no prepared statement
with id: 0

On the other hand, simple select statement:

Code: [Select]Connection.ExecuteDirect('select 1;');
works fine.

The database is not read-only. I can connect to it using
OpenOffice.org Base and MonetDB ODBC driver, issue the same create
table statement and it works fine. A while ago I have written a
program that executes DDL statements against Microsoft SQL Server 2008
using SQL Server ODBC driver and TODBCConnection.ExecuteDirect and it
works fine too.

Can you help me to understand the problem here? Thank you very much!

----------------------------------------------------------------------
BigChimp · 2012-09-18 08:26

Sorry, no experience with monetdb, but you could enable ODBC tracing
and see what gets sent to the db in both the OpenOffice and Lazarus
cases.
If there's any differences, that could be useful information for a bug
report.

Please also post operating system/version, Lazarus/FPC version,
perhaps a small sample program for others that use MonetDB as well.

There have been some changes in the SQLDB query parser that tries to
detect if something returns rows (e.g. the query needs to be opened)
or doesn't (e.g. the query just needs to be executed), but I don't
know if that parser is ever run when calling .ExecuteDirect (I
wouldn't think so but haven't looked at the code).

----------------------------------------------------------------------
ludob · 2012-09-18 11:34

Do you have a dataset opened when calling
TODBCConnection.ExecuteDirect ?
According to http://article.gmane.org/gmane.comp.db.monetdb.user/4840
MonetDB is special in that it cancels all prepared statements when
executing a DDL. So all dataset have to be closed and re-opened.
Actually I'm not 100% sure that a simple close/open will cause the
query to be re-prepared. An explicit Unprepare will be needed in that
case.

----------------------------------------------------------------------
Buckwheat · 2012-09-18 20:16

Hi BigChimp!

Thank you very much for your reply!

Below are all details you mentioned:

Operating system and hardware:

OS Name:                   Microsoft Windows 7 Home Premium
OS Version:                6.1.7601 Service Pack 1 Build 7601
OS Manufacturer:           Microsoft Corporation
OS Configuration:          Standalone Workstation
OS Build Type:             Multiprocessor Free
Product ID:                00359-OEM-8992687-00057
Original Install Date:     15/09/2010, 2:05:09 AM
System Boot Time:          15/09/2012, 9:53:53 AM
System Manufacturer:       TOSHIBA
System Model:              Satellite L670
System Type:               x64-based PC
Processor(s):              1 Processor(s) Installed.
                           [01]: Intel64 Family 6 Model 37 Stepping 2
GenuineIntel ~2266 Mhz
BIOS Version:              TOSHIBA 1.40, 17/06/2010
Windows Directory:         C:\windows
System Directory:          C:\windows\system32
Boot Device:               \Device\HarddiskVolume1
System Locale:             en-ca;English (Canada)
Input Locale:              en-us;English (United States)
Time Zone:                 (UTC-05:00) Eastern Time (US & Canada)
Total Physical Memory:     3,891 MB
Available Physical Memory: 1,379 MB
Virtual Memory: Max Size:  11,669 MB
Virtual Memory: Available: 8,052 MB
Virtual Memory: In Use:    3,617 MB
Page File Location(s):     C:\pagefile.sys
Domain:                    WORKGROUP

Lazarus/FPC:

Version #:1.0
Date: 2012-08-28
FPC Version: 2.6.0
SVN Revision: 38415
i386-win32-win32/win64

Sample program:

Code: [Select]program project1;

{$mode objfpc}{$H+}

uses {$IFDEF UNIX} {$IFDEF UseCThreads}
  cthreads, {$ENDIF} {$ENDIF}
  Classes { you can add units after this },
  SysUtils,
  odbcconn,
  sqldb;

var
  Connection: TODBCConnection;
  Transaction: TSQLTransaction = nil;
begin
  Connection := TODBCConnection.Create(nil);
  try
    with Connection do
    begin
      Driver := 'MonetDB ODBC Driver';
      UserName := 'monetdb';
      Password := 'monetdb';
      Params.Add('Host=localhost');
      Params.Add('Port=50000');
      Open;
    end;
    Transaction := TSQLTransaction.Create(nil);
    try
      Connection.Transaction := Transaction;
      Transaction.DataBase := Connection;
      Transaction.StartTransaction;
      try
        Connection.ExecuteDirect('create table a (col1 int);');
        Transaction.Commit;
      except
        Transaction.Rollback;
      end;
    finally
      FreeAndNil(Transaction);
    end;
  finally
    FreeAndNil(Connection);
  end;
end.

By some reason I was not able to capture ODBC trace for OpenOffice.org
Base, even I did use ODBC User DSN, so I used Database Browser
(http://www.dbsoftlab.com/database-editors/database-
browser/download.html) instead. It (Database Browser) worked fine with
MonetDB ODBC driver: create table command executed successfully. I
have attached two ODBC trace logs: one for the sample Lazarus program
above and one for the Database Browser. Please search for "create
table" in these files. Please let me know what you think.

----------------------------------------------------------------------
Buckwheat · 2012-09-18 20:38

Hi ludob!

Thank you very much for your reply!

I have tried TSQLQuery.ExecSQL: the result was the same as with
TODBCConnection.ExecuteDirect - [MonetDB][ODBC Driver 11.11.7]EXEC: no
prepared statement with id: 0 (0). I have also tried TSQLQuery.Prepare
-> TSQLQuery.ExecSQL -> TSQLQuery.UnPrepare: same result -
[MonetDB][ODBC Driver 11.11.7]EXEC: no prepared statement with id: 0
(0) .

My first idea was that MonetDB ODBC driver has a bug. To verify that,
I wanted to check whether other applications can execute "create
table" against MonetDB ODBC driver. I tried two applications: Database
Browser and OpenOffice.org Base. They both execute "create table"
against MonetDB ODBC driver successfully. By some reason I was not
able to capture ODBC trace log for OpenOffice.org Base.

----------------------------------------------------------------------
BigChimp · 2012-09-19 09:01

Thanks Buckwheat, that was quite comprehensive.

Quote from: Buckwheat on September 18, 2012, 10:16:55 pmBy some reason
I was not able to capture ODBC trace for OpenOffice.org Base, even I
did use ODBC User DSN, so I used Database Browser
(http://www.dbsoftlab.com/database-editors/database-
browser/download.html) instead. It (Database Browser) worked fine with
MonetDB ODBC driver: create table command executed successfully. I
have attached two ODBC trace logs: one for the sample Lazarus program
above and one for the Database Browser. Please search for "create
table" in these files. Please let me know what you think.

Not an ODBC expert - hoping the real experts stand up  , but:
- FPC use SQLExecute; db browser uses SQLExecuteDirectW
- FPC preparation seems to go ok but the execute probably doesn't
reference the right statement?
Code: [Select]project1        3008-3378 ENTER SQLPrepare
                HSTMT               0x00184AE8
                UCHAR *             0x015BCD90 [      26] "create
table a (col1 int);"
                SDWORD                    26

project1        3008-3378       EXIT  SQLPrepare  with return code 0
(SQL_SUCCESS)
                HSTMT               0x00184AE8
                UCHAR *             0x015BCD90 [      26] "create
table a (col1 int);"
                SDWORD                    26

project1        3008-3378       ENTER SQLExecute
                HSTMT               0x00184AE8

project1        3008-3378       EXIT  SQLExecute  with return code -1
(SQL_ERROR)
                HSTMT               0x00184AE8

                DIAG [07003] [MonetDB][ODBC Driver 11.11.7]EXEC: no
prepared statement with id: 0 (0)

project1        3008-3378       ENTER SQLGetDiagRecW
                SQLSMALLINT                  3 <SQL_HANDLE_STMT>
                SQLHANDLE           0x00184AE8
                SQLSMALLINT                  1
                SQLWCHAR *          0x0149FC78
                SQLINTEGER *        0x0149FD6C
                SQLWCHAR *          0x00000000
                SQLSMALLINT                  0
                SQLSMALLINT *       0x0149FD68

----------------------------------------------------------------------
ludob · 2012-09-19 09:11

The odbc logs are useful. Seems MonetDB doesn't like preparing DDL
commands. The DataBase browser shows a prepare of the Create query but
then abandons and does a SQLExecDirect. In the existing sqldb
implementation prepare is always used. Here is a workaround you can
try (I don't have MonetDB installed, untested code):

Code: [Select]uses ...,odbcsqldyn;
...
function
ExecDLL(ODBCConnection:TODBCConnection;sQuery:string):boolean;
var STMTHandle: SQLHANDLE;
begin
result:=false;
if SQLAllocHandle(SQL_HANDLE_STMT, ODBCConnection.Handle,
STMTHandle)=SQL_SUCCESS then
  begin
  result:=SQLExecDirect(STMTHandle,pchar(sQuery),length(sQuery))=SQL_S
UCCESS;
  SQLFreeStmt(STMTHandle,SQL_CLOSE);
  end;
end;
Then replace Code: [Select]Connection.ExecuteDirect('create table a
(col1 int);'); with Code: [Select]ExecDLL(Connection,'create table
xyz');Error checking is minimal. The function only returns false when
not succeeding. You might want to look at ODBCCheckResult in odbconn
to get the error code in case something goes wrong.

----------------------------------------------------------------------
Buckwheat · 2012-09-19 16:51

Hi ludob, it works! Fantastic! Thank you so much!

I will think how to get access to ODBCCheckResult (it is in the
implementation section of the unit odbcconn and is not visible to the
external code).