Error in SQL execution - SOLVED
lazarus / 16-Databases

----------------------------------------------------------------------
Marcelo Confortino · 2016-04-19 10:04

Godd morning everybody.

I'm in the task of creating and application for data management. The
data is now stored in tables within Word documents. I converted the
tables to text, columns separated with '#'. The relevant part of the
code to insert into mysql tables is:

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           
readln(tfIn, s);            expte.DelimitedText := s;           
fecha.DelimitedText := expte[2];            sqle := 'insert into
exptes (nroexp, feching, cdeexp, datos, iniciador, ';            sqle
:= sqle + 'referencia, origen) values (';            sqle := sqle +
expte[0] + ', ';            sqle := sqle + QuotedStr('2016-' +
fecha[1] + '-' + fecha[0]) + ', ';            sqle := sqle +
QuotedStr(expte[3]) + ', ';            sqle := sqle +
QuotedStr(expte[4]) + ', ';            sqle := sqle +
QuotedStr(expte[5]) + ', ';            sqle := sqle +
QuotedStr(expte[6]) + ', ';            sqle := sqle +
QuotedStr(expte[1]) + ');';            writeln(tfOut, sqle); // this
is for debugging            Query.SQL.Text := sqle;           
Query.ExecSQL;       
in a loop thar reads the text file line by line.

This produces, for example, this sql:

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 insert into
exptes (nroexp, feching, cdeexp, datos, iniciador, referencia, origen)
values (397, '2016-04-11', '', 'Def. Acta 32, Tomo I Año 2016 COMODORO
CENTRO', 'LEONORI, Valentín', 's/RECTIFICACION DEL ACTA DE DEFUNCION',
'COMODORO CENTRO');
The problem is, when I execute the program to migrate the text to the
database, it stops on the very first line read with:

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 An
unhandled exception occurred at $000000000046B9AF:ESQLDatabaseError:
TMySQL55Connection : Error executing query: You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near '��001, '2016-01-27', '',
'DEFUNCION FUERA DE TERMINO ', 'Francisco Joaquín ' at line 1
 $000000000046B9AF  $000000000046D088 
but if I feed the generated sql in the mysql client, it executes fine.
I can't find the error, except for the odd characters before 001,
'2016-01-27', which are not in the text file. I am using Lazarus
1.6/FPC 3.0, Ubuntu 14.04 LTS. Any guidance will be appreciated.
Thanks in advance.

----------------------------------------------------------------------
rvk · 2016-04-19 10:42

Try to put a showmessage with expte[0] and/or sqle just before the
ExecSQL.

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
writeln(tfOut, sqle); // this is for
debugginShowmessage(expte[0]);Showmessage(sqle);Query.SQL.Text :=
sqle;Query.ExecSQL;
My guess is that your expte[0] contains some invalid characters. When
converting to text, what encoding did you use? UTF-8?

Maybe the file is saved with the BOM-signature in the text-file (for
UTF-8). In that case you shouldn't just read it with readln() without
stripping that signature.

----------------------------------------------------------------------
Marcelo Confortino · 2016-04-19 11:01

Thank you for your answer. This is what you asked

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 expte[0]:
001sqle: insert into exptes (nroexp, feching, cdeexp, datos,
iniciador, referencia, origen) values (001, '2016-01-27', '',
'DEFUNCION FUERA DE TERMINO ', 'Francisco Joaquín ALVAREZ ALMONACID',
'DEFUNCION FUERA DE TERMINO', 'ESQUEL CENTRO NOTA Nº 021/2016 –
DRCE');An unhandled exception occurred at
$000000000046BA2F:ESQLDatabaseError: TMySQL55Connection : Error
executing query: You have an error in your SQL syntax; check the
manual that corresponds to your MariaDB server version for the right
syntax to use near '��001, '2016-01-27', '', 'DEFUNCION FUERA DE
TERMINO ', 'Francisco Joaquín ' at line 1  $000000000046BA2F
 $000000000046D108 
As for the file, it's just a flat text file, with nothing but the data
in it.

----------------------------------------------------------------------
rvk · 2016-04-19 11:06

You can't see the BOM-signature if you open it with a UTF-8 aware
editor (like notepad).

I'n not sure if Showmessage strips unreadable characters...

Could you try looking at your input-file with a hex-editor and see if
it starts with anything else but 001?
(Or do a "type yourinputfile.txt | more" in a cmd.exe prompt)

As an alternative you could read the complete file into a TStringList
(which interprets the BOM correctly) and loop through the lines and
assign each to your extra TStringList with the delimiter-character.

----------------------------------------------------------------------
rvk · 2016-04-19 11:16

Correction. I think using TStringList also reads the BOM in the first
line.

What do you get if you insert the following line:
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
Showmessage(inttostr(Ord(expte[0][1])));If you get 239 you've got a
BOM in your file.

----------------------------------------------------------------------
Marcelo Confortino · 2016-04-19 13:19

You are right! The very first bytes of the file are indeed 239. I
removed them with okteta and now the error is gone, and I learned
something new. Is it possible to ignore the BOM from the app instead
of modifying the input file? Thank you very much.

----------------------------------------------------------------------
rvk · 2016-04-19 13:28

You could check in the first string if you've got a BOM and if so...
strip it.
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 uses
FileUtil;//...if (Pos(UTF8FileHeader, S) = 1) then  System.Delete(S,
1, Length(UTF8FileHeader));You would only have to do this with the
first string you read.

----------------------------------------------------------------------
SymbolicFrank · 2016-04-19 13:32

Or if the first string in a StringList starts with those bytes, and
remove them when it does. (When using LoadFromFile.)

----------------------------------------------------------------------
ttomas · 2016-04-19 13:55

Put this code
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
Query.SQL.Text := sqle;Query.SQL.SaveToFile('test.sql'); and try to
execute test.sql in database