ODBC error in Ubuntu server 24.04 + 11.4.8-MariaDB + ma-3.2.7 ODBC +
Lazarus 4.2
lazarus / 16-Databases

----------------------------------------------------------------------
jqr · 2025-10-29 22:09

Hi, I am a Delphi user who wants to compile some Delphi applications
in Lazarus 4.2, 

I made a simple application to test with DBGrid but I got an error,
and then I simplified in order to isolate from DBGrid but I still get
the error.

Button1 shows the error.
Button3 shows the same error (slightly different code).

Button2 is the only way I can insert a record by using
direct sql statements

This is the error message:

SQLQuery.ApplyUpdates I get the following error :

     Could not prepare statement. ODBC error details: LastReturnCode:
SQL_ERROR;
     Record 1: SqlState: 42000; NativeError: 1064;
     Message: [ma-3.2.7]
     [11.4.8-MariaDB-ubuntu2404]
     You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server versión
     for the right syntax to use near '"id","text") values(?,?)' at
line 1      

This is the code:

unit unit3;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, odbcconn, SQLDB, DB, Forms, Controls, Graphics,
Dialogs,
  StdCtrls, DBGrids;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Button4: TButton;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    ODBCConnection1: TODBCConnection;
    SQLQuery1: TSQLQuery;
    SQLQuery1id: TLongintField;
    SQLQuery1text: TStringField;
    SQLTransaction1: TSQLTransaction;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
  private

  public

  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
begin   // Button1 gives error when executing : SQLQuery1.ApplyUpdates
  SQLQuery1.SQL.Text := 'SELECT * FROM testing';

  SQLTransaction1.Active := True;
  SQLQuery1.open;
  SQLTransaction1.CommitRetaining;

  SQLQuery1.Append;
  SQLQuery1Id.Value := 3;
  SQLQuery1Text.Value := '3';
  SQLQuery1.Post;


  //SQLQuery1.appendRecord([3,'3']);

  SQLTransaction1.Active := True;
  SQLQuery1.ApplyUpdates;

  {
   WHEN EXECUTING THE LAST STATEMENT I GET THE FOLLOWING ERROR :

   Could not prepare statement. ODBC error details: LastReturnCode:
SQL_ERROR;
   Record 1: SqlState: 42000; NativeError: 1064;
   Message: [ma-3.2.7]
   [11.4.8-MariaDB-ubuntu2404]
   You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server versión
   for the right syntax to use near '"id","text") values(?,?)' at line
1
  }

  SQLTransaction1.CommitRetaining;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin  // Button2 works fine.
  SQLQuery1.SQL.Text := 'insert into testing (id, text) values
(2,"2")';
  SQLTransaction1.Active := True;
  SQLQuery1.ExecSQL;
  SQLTransaction1.CommitRetaining;
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
    SQLQuery1.SQL.Text := 'SELECT * FROM testing';

    SQLTransaction1.Active := True;
    SQLQuery1.open;
    SQLTransaction1.CommitRetaining;


    SQLQuery1.appendRecord([3,'3']);


    SQLTransaction1.Active := True;
    SQLQuery1.ApplyUpdates;

    {
     Could not prepare statement. ODBC error details: LastReturnCode:
SQL_ERROR;
     Record 1: SqlState: 42000; NativeError: 1064;
     Message: [ma-3.2.7]
     [11.4.8-MariaDB-ubuntu2404]
     You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server versión
     for the right syntax to use near '"id","text") values(?,?)' at
line 1
    }

    SQLTransaction1.CommitRetaining;
end;

procedure TForm1.Button4Click(Sender: TObject);
begin
  SQLQuery1.open;
end;

end.

----------------------------------------------------------------------
Zvoni · 2025-10-30 07:36

At a guess: Your InsertSQL-Property of the TSQLQuery is faulty.
I see double-quotes in your Error-Message.
IIRC, MySQL/Maria uses backticks for escaping Fieldnames

Try (same in Button3)
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button1Click(Sender: TObject);begin   // Button1 gives error
when executing : SQLQuery1.ApplyUpdates  SQLQuery1.SQL.Text := 'SELECT
ID, text FROM testing';  SQLQuery1.InsertSQL.Text:='INSERT INTO
testing(`ID`, `text`) VALUES(?,?)';  SQLTransaction1.Active := True; 
SQLQuery1.open;  //SQLTransaction1.CommitRetaining;  //--> You don't
need a Commit for SELECTS   SQLQuery1.Append;  SQLQuery1Id.Value :=
3;  SQLQuery1Text.Value := '3';  SQLQuery1.Post;   
//SQLQuery1.appendRecord([3,'3']);   SQLTransaction1.Active := True; 
SQLQuery1.ApplyUpdates;    SQLTransaction1.CommitRetaining;end;
FWIW: I'd never trust a machine to "guess" the Insert/Update/Delete
statements from your SELECT-statement.
Use Workbench, HeidiSQL (or whatever) to write your SQL-Statements,
test them, and then assign them to the Properties.
Never had a Problem once with that approach

----------------------------------------------------------------------
Thaddy · 2025-10-30 07:37

You can also tryCode: Pascal  [Select][+][-]window.onload =
function(){var x1 = document.getElementById("main_content_section");
if (x1) { var x = document.getElementsByClassName("geshi");for (var i
= 0; i SQLQuery1Text.Value := '"3"';MariaDb's engine can possibly not
handle single quotes, only double quotes for string types. The above
will pas the value as "3".
There are more engines that have the same format, so I expect this to
work.

----------------------------------------------------------------------
Zvoni · 2025-10-30 07:40

Quote from: Thaddy on October 30, 2025, 08:37:41 amYou can also
tryCode: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1Text.Value := '"3"';MariaDb's engine can not handle single
quotes, only double quotes for string types. The above will pas the
value as "3".
The double-quotes are around the Fieldnames, not the Values.
MySQL/Maria uses backticks for escaping Fieldnames.
His Error-Message shows, that the auto-generated InsertSQL uses
Parameters ("(?,?)"), so the Parameter-assignment should take care of
String-Escaping

EDIT: Found something.
https://mariadb.com/docs/server/server-management/variables-and-
modes/sql-mode
Check your SQL_MODE-Variable.
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
@@SQL_MODE, @@GLOBAL.SQL_MODE;You can set it to be closer to ANSI-SQL
(which would allow the use of Double-Quote to escape Fieldnames).

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SET
sql_mode = 'ANSI';SET GLOBAL sql_mode = 'ANSI';

----------------------------------------------------------------------
Thaddy · 2025-10-30 07:45

But this is definitely wrong for a stringfield value:Code: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLQuery1Text.Value := '3';That really needs to be as I explained.
'"3"' It needs to be correct in the Query?, not Pascal, but SQL.
That happens in more engines. I may be wrong for MariaDB.

----------------------------------------------------------------------
Zvoni · 2025-10-30 07:48

Quote from: Thaddy on October 30, 2025, 08:45:01 amBut this is
definitely wrong for a stringfield value:Code: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLQuery1Text.Value := '3';That really needs to be as I explained.
'"3"' It needs to be correct in the Query?, not Pascal, but SQL.
Agreed. Since it's assigned directly to Value of a TStringField (Why
is he even using that?)

My guess is that his MariaDB-Server runs in "standard"-config.
See my Link above to Server-Management/Variables.

Doesn't change the fact, that he should explicitely assign the
InsertSQL et al himself

----------------------------------------------------------------------
LacaK · 2025-10-30 11:04

TODBCConnection uses default FieldNameQuoteChars (which are according
to the ANSI SQL standard double quotes) to enclose field names in
constructed Insert/Update/DeleteSQL statements.

As it was already mentioned there can be used SQL statement which sets
MySQL/MariaDB in mode more close to SQL ANSI standard. For another
example:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SET SESSION
sql_mode='STRICT_ALL_TABLES,PAD_CHAR_TO_FULL_LENGTH,ANSI_QUOTES' 
or put it in my.ini (https://mariadb.com/docs/server/server-
management/install-and-upgrade-mariadb/configuring-
mariadb/configuring-mariadb-with-option-files) as sql-mode=

It was for me always mystery why some database systems does not follow
SQL standard, when we have here standard for decades ...

----------------------------------------------------------------------
jqr · 2025-10-30 17:14

Hi you all

 I tried this   SQLQuery1.InsertSQL.Text:='INSERT INTO testing(`ID`,
`text`) VALUES(?,?)';
but  I got this: EDatabaseError : SQLQuery1 : Field not found : ""

And then I tried this :   SQLQuery1.InsertSQL.Text:='INSERT INTO
testing("ID", "text") VALUES(?,?)';
and obtained the same error.

For the ideas about the quotes or double quotes I tried without this
statement : //  SQLQuery1Text.Value := '3';
and it gave same error.

I could not find any place with the syntax for InsetSQL, UpdateSQL and
DeleteSQL, that is one of the reasons I posted here. 
Any of you guys know where is the syntax for those statments in
lazarus ?
Do I need something more to have DBGrid full functionality like in
Delphi ?

----------------------------------------------------------------------
Thaddy · 2025-10-30 17:28

The syntax for Queries is simply SQL syntax, not Pascal syntax.
So you always need to be sure that strings are properly handled.
I already showed you how. The only change you need.

----------------------------------------------------------------------
jqr · 2025-10-30 18:20

This is working:
  SQLQuery1.SQL.Text := 'insert into testing (id, text) values
(2,"2")';
  SQLTransaction1.Active := True;
  SQLQuery1.ExecSQL;

 This is giving error:
  SQLQuery1.InsertSQL.Text:='INSERT INTO testing(id, text)
VALUES(?,?)';

It seems to me that I am not using the right syntax

Anyway, I would love to know where is the documentation for InsertSQL,
UpdateSQL, DeleteSQL and perhaps RefreshSQL

----------------------------------------------------------------------
atroesch · 2025-10-30 18:35

Code: [Select]SQLQuery1.SQL.Text := 'insert into testing (id,text)
values(:THE_ID,:THE_TEXT)';
SQLQuery1.Params.ParamByName('THE_ID').asInteger:=2;
SQLQuery1.Params.ParamByName('THE_TEXT').asString:='2';
SQLQuery1.ExecSQL;
SQLTransaction1.Commit;

and i think text is a reserved word?

https://dev.mysql.com/doc/refman/8.4/en/keywords.html#keywords-8-4-
detailed-T

----------------------------------------------------------------------
jqr · 2025-10-30 19:14

Quote from: LacaK on October 30, 2025, 12:04:55 pmTODBCConnection uses
default FieldNameQuoteChars (which are according to the ANSI SQL
standard double quotes) to enclose field names in constructed
Insert/Update/DeleteSQL statements.

As it was already mentioned there can be used SQL statement which sets
MySQL/MariaDB in mode more close to SQL ANSI standard. For another
example:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SET SESSION
sql_mode='STRICT_ALL_TABLES,PAD_CHAR_TO_FULL_LENGTH,ANSI_QUOTES' 
or put it in my.ini (https://mariadb.com/docs/server/server-
management/install-and-upgrade-mariadb/configuring-
mariadb/configuring-mariadb-with-option-files) as sql-mode=

It was for me always mystery why some database systems does not follow
SQL standard, when we have here standard for decades ...

I also added this to the config and still got the same error :

----------------------------------------------------------------------
jqr · 2025-10-30 19:19

Quote from: atroesch on October 30, 2025, 07:35:28 pmCode:
[Select]SQLQuery1.SQL.Text := 'insert into testing (id,text)
values(:THE_ID,:THE_TEXT)';
SQLQuery1.Params.ParamByName('THE_ID').asInteger:=2;
SQLQuery1.Params.ParamByName('THE_TEXT').asString:='2';
SQLQuery1.ExecSQL;
SQLTransaction1.Commit;

and i think text is a reserved word?

https://dev.mysql.com/doc/refman/8.4/en/keywords.html#keywords-8-4-
detailed-T

i do not know how to use this with the DBGrid, I want the user to
directly insert or update records by using the dbGrid,  I changed the
text column to another name, thanks for the observation, I am still
getting the exact same error.

----------------------------------------------------------------------
Zvoni · 2025-10-31 06:56

Quote from: jqr on October 30, 2025, 06:14:48 pmI could not find any
place with the syntax for InsetSQL, UpdateSQL and DeleteSQL, that is
one of the reasons I posted here. 
Any of you guys know where is the syntax for those statments in
lazarus ?
Do I need something more to have DBGrid full functionality like in
Delphi ?

Seriously???

See attachment
and see DOCUMENTATION --> https://www.freepascal.org/docs-
html/fcl/sqldb/tsqlquery.insertsql.html

To me, it looks more like, that TS has no clue whatsoever about SQL
nevermind the involved RDBMS

----------------------------------------------------------------------
LacaK · 2025-10-31 08:16

Quote from: jqr on October 30, 2025, 08:14:12 pmQuote from: LacaK on
October 30, 2025, 12:04:55 pmTODBCConnection uses default
FieldNameQuoteChars (which are according to the ANSI SQL standard
double quotes) to enclose field names in constructed
Insert/Update/DeleteSQL statements.

As it was already mentioned there can be used SQL statement which sets
MySQL/MariaDB in mode more close to SQL ANSI standard. For another
example:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SET SESSION
sql_mode='STRICT_ALL_TABLES,PAD_CHAR_TO_FULL_LENGTH,ANSI_QUOTES' 
or put it in my.ini (https://mariadb.com/docs/server/server-
management/install-and-upgrade-mariadb/configuring-
mariadb/configuring-mariadb-with-option-files) as sql-mode=

It was for me always mystery why some database systems does not follow
SQL standard, when we have here standard for decades ...

I also added this to the config and still got the same error :

This should not to be added in config. This should be executed as SQL
statement just after you connect to MySQL/MariaDB server.
After connection use:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
AConnection.ExecuteDirect('SET SESSION sql_mode=''STRICT_ALL_TABLES,PA
D_CHAR_TO_FULL_LENGTH,ANSI_QUOTES''');assuming that AConnection is our
instance of TODBCConnection (or TSQLConnection) class.

OR (if you are on Linux)
locate for file my.cnf and put in file line in [mysqld] section:
sql-mode=PAD_CHAR_TO_FULL_LENGTH,ANSI_QUOTES

----------------------------------------------------------------------
Zvoni · 2025-10-31 09:44

Quote from: jqr on October 30, 2025, 08:19:22 pmi do not know how to
use this with the DBGrid, I want the user to directly insert or update
records by using the dbGrid,  I changed the text column to another
name, thanks for the observation, I am still getting the exact same
error.

QuoteUse Workbench, HeidiSQL (or whatever) to write your SQL-
Statements, test them, and then assign them to the Properties.
Never had a Problem once with that approach
.....and i'm out of here....

----------------------------------------------------------------------
Thaddy · 2025-10-31 11:58

If he still not understands that he can copy the query from heidi and
put it into single quotes I am out too.
And treat SQL strings contained in it as double quotes.

----------------------------------------------------------------------
Zvoni · 2025-10-31 13:56

I have to amend my reply earlier.
I forgot a crucial Detail:

Change this
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.InsertSQL.Text:='INSERT INTO testing(`ID`, `text`)
VALUES(?,?)';
To:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.InsertSQL.Text:='INSERT INTO testing(ID, text)
VALUES(:ID,:text)';
See here:
https://wiki.freepascal.org/Working_With_TSQLQuery#InsertSQL,_UpdateSQ
L_and_DeleteSQL_-_Basic_use_of_parameters
QuoteParameter names must be exactly the same as the field names used
in the dataset. The field names in the dataset may be different from
the column names in the table, depending on the used select statement
(see above).Meaning: If you don't use Aliases for the Columns, it MUST
be the Columnname prepended with a Colon ":",
otherwise it MUST be the Alias

----------------------------------------------------------------------
jqr · 2025-10-31 17:45

Quote from: Zvoni on October 31, 2025, 02:56:21 pmI have to amend my
reply earlier.
I forgot a crucial Detail:

Change this
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.InsertSQL.Text:='INSERT INTO testing(`ID`, `text`)
VALUES(?,?)';
To:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.InsertSQL.Text:='INSERT INTO testing(ID, text)
VALUES(:ID,:text)';
See here:
https://wiki.freepascal.org/Working_With_TSQLQuery#InsertSQL,_UpdateSQ
L_and_DeleteSQL_-_Basic_use_of_parameters
QuoteParameter names must be exactly the same as the field names used
in the dataset. The field names in the dataset may be different from
the column names in the table, depending on the used select statement
(see above).Meaning: If you don't use Aliases for the Columns, it MUST
be the Columnname prepended with a Colon ":",
otherwise it MUST be the Alias


This is it !!! I knew it was something in the syntax. 

SQLQuery1.InsertSQL.Text:='INSERT INTO testing(id, mytext)
VALUES(:id,:mytext)'; // This one works, the one with ? ? did not work
in my case.

The link to the documentation is what I was looking for, it will solve
all my syntax questions regarding the subject.

I appreciate your consideration.

Have the weekend that you deserve pal !

----------------------------------------------------------------------
jqr · 2025-10-31 17:49

Quote from: LacaK on October 31, 2025, 09:16:44 am
OR (if you are on Linux)
locate for file my.cnf and put in file line in [mysqld] section:
sql-mode=PAD_CHAR_TO_FULL_LENGTH,ANSI_QUOTES

Thanks for your response, I use linux, I included the directive inside
one of my cnf files, I think this is really important to avoid
compatibility issues.

----------------------------------------------------------------------
jqr · 2025-10-31 17:59

Finally, thank you all for your thoughtful feedback regarding the
subject.

By the way, zeoslib is an open source library that handles this really
well, I highly recommend it, includes libaries for delphi and
lazarus. 

Here is the link: https://sourceforge.net/projects/zeoslib/