SQLite ExecuteDirect

[rayanAyar]

I assume that method
TZConnection.ExecuteDirect(const SQL: string)
should send SQL to DB-library "as is". Without "preparing statements". Without any parsing and modifications. Am I right?

This behavior is in TZPostgreSQLConnection.ExecuteImmediat:

Code: Select all

procedure TZPostgreSQLConnection.ExecuteImmediat(const SQL: RawByteString;
  LoggingCategory: TZLoggingCategory);
...
  QueryHandle := FPlainDriver.PQexec(Fconn, Pointer(SQL));
...

For example, this will works as expected:

Code: Select all

ZConnectionPostgreSQL.ExecuteDirect(
  'INSERT INTO TestTable (Id, Val) VALUES (1, ''AAA'');' + LineEnding +
  'INSERT INTO TestTable (Id, Val) VALUES (2, ''BBB'');' + LineEnding +
  'INSERT INTO TestTable (Id, Val) VALUES (3, ''CCC'');' + LineEnding);

All three SQL statements will be executed, because SQL string will be send to libpq "as is".

But this will not work for SQLite. Only the first INSERT will be executed. Because currently method TZSQLiteConnection.ExecuteImmediat trying to prepare statement. This method use sqlite3_prepare_v2.
In earlier versions of ZeosLib this method used sqlite3_exec. I suggest it must looks like:

Code: Select all

procedure TZSQLiteConnection.ExecuteImmediat(const SQL: RawByteString;
  LoggingCategory: TZLoggingCategory);
var
  Status: Integer;
  LogSQL: String;
  ErrorMessage: PAnsiChar;
begin
  {$IFDEF UNICODE}
  LogSQL := ZRawToUnicode(SQL, zCP_UTF8);
  {$ELSE}
  LogSQL := SQL;
  {$ENDIF}
  if Pointer(SQL) = nil then
    Exit;
  try
    Status := FPlainDriver.sqlite3_exec(FHandle, Pointer(SQL), nil, nil, ErrorMessage);
    if (Status <> SQLITE_OK) and (Status <> SQLITE_DONE) then
      HandleErrorOrWarning(LoggingCategory, Status, LogSQL, IImmediatelyReleasable(FWeakImmediatRelPtr));
  finally
    if DriverManager.HasLoggingListener then
      DriverManager.LogMessage(LoggingCategory, URL.Protocol, LogSQL);
  end;
end;

P.S.
ExecuteImmediat - is this a typo? Or this is not English? In English must be "Immediate".

[marsupilami]

We changed to the use of a prepared statement because sqlite3_exec doesn't give detailed error information. Zeos 8 introduced special handling for file I/O errors and other things. Take a look at the HandleErrorOrWarning procedure in the SQLite driver.

We are currently discussing about reverting back to sqlite3_exec and doing special error handling there but that change is not done yet. As a workaround you might want to think about changing your code into three calls, one for each SQL statement.

ExecuteImmediat - is this a typo? Or this is not English? In English must be "Immediate".

This is a typo. Most Zeos developers don't speak English as their first language and so mistakes simply happen sometimes.