Recent

Author Topic: [SOLVED] Unclear error message for dbConnection  (Read 769 times)

JanRoza

  • Hero Member
  • *****
  • Posts: 618
    • http://www.silentwings.nl
[SOLVED] Unclear error message for dbConnection
« on: April 05, 2021, 04:24:17 pm »
I have a strange problem when using query.ApplyUpdates for which I don't see the cause (or solution).
It's a fairly normal construction with query.Append, query.Edit and query.Post which works everywhere else in the program but in this piece of code it fails with the attached error message.

Code:
Code: Pascal  [Select][+][-]
  1. strDatabaseName := 'My Addresses';
  2.  
  3.   // The backup database
  4.   qryTempBackup.Active   := false;
  5.   qryTempBackup.SQL.Text := 'SELECT * FROM ' + QuotedStr(strDatabaseName);
  6.   qryTempBackup.Active   := true;
  7.   qryTempBackup.First;
  8.  
  9.   // The target database
  10.   DataModule1.qryTemp.Active   := false;
  11.   DataModule1.qryTemp.SQL.Text := 'SELECT * FROM ' + QuotedStr(strDatabaseName);
  12.   DataModule1.qryTemp.Active   := true;
  13.   DataModule1.qryTemp.First;
  14.  
  15.   while not qryTempBackup.EOF do
  16.   begin
  17.     DataModule1.qryTemp.Append;
  18.     DataModule1.qryTemp.Edit;
  19.  
  20.     for i := 0 to iFields do
  21.     begin
  22.       case aFieldFormats[i] of
  23.         'Character'   : DataModule1.qryTemp.FieldByName(aFieldNames[i]).AsString := qryTempBackup.FieldByName(aFieldNames[i]).AsString;
  24.         'Memo'        : DataModule1.qryTemp.FieldByName(aFieldNames[i]).AsString := qryTempBackup.FieldByName(aFieldNames[i]).AsString;
  25.         'Numeric'     : DataModule1.qryTemp.FieldByName(aFieldNames[i]).AsInteger := qryTempBackup.FieldByName(aFieldNames[i]).AsInteger;
  26.         'Boolean'     : DataModule1.qryTemp.FieldByName(aFieldNames[i]).AsBoolean := qryTempBackup.FieldByName(aFieldNames[i]).AsBoolean;
  27.         'Date'        : DataModule1.qryTemp.FieldByName(aFieldNames[i]).AsDateTime := qryTempBackup.FieldByName(aFieldNames[i]).AsDateTime;
  28.         'Picture'     : DataModule1.qryTemp.FieldByName(aFieldNames[i]).AsBytes := qryTempBackup.FieldByName(aFieldNames[i]).AsBytes;
  29.       end;
  30.     end;
  31.  
  32.     DataModule1.qryTemp.Post;
  33.     qryTempBackup.Next;
  34.   end;
  35.  
  36.   DataModule1.qryTemp.ApplyUpdates;    // <<<<<<<<<<<<<< This is where the error occurs
  37.   DataModule1.DbTransaction.CommitRetaining;
  38.  

Can anyone set me straight here, I'm staring myself blind without seeing what I did wrong here.
« Last Edit: April 06, 2021, 03:45:19 pm by JanRoza »
OS: Windows 10 (64 bit) / Linux Mint (64 bit)
Laz: Lazarus 2.0.12 FPC 3.2.0
       CodeTyphon 7.4 FPC 3.3.1

MarkMLl

  • Hero Member
  • *****
  • Posts: 2521
Re: Unclear error message for dbConnection
« Reply #1 on: April 05, 2021, 07:43:21 pm »
And that is why I /loathe/ database systems which hide the queries.

Is there any way you can get at the queries that are sent to the database? Apart from that I think a good first step would be to do a dump of the affected tables and look to see if there's unexpected parentheses somewhere.

MarkMLl
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

JanRoza

  • Hero Member
  • *****
  • Posts: 618
    • http://www.silentwings.nl
Re: Unclear error message for dbConnection
« Reply #2 on: April 05, 2021, 10:14:24 pm »
I use SQLite for a long time now and never had a problem so far.
To see more I think I will have to find a way to recompile fpc with debugger on because I know no other way to see what dbconnection is complaining about.
OS: Windows 10 (64 bit) / Linux Mint (64 bit)
Laz: Lazarus 2.0.12 FPC 3.2.0
       CodeTyphon 7.4 FPC 3.3.1

MarkMLl

  • Hero Member
  • *****
  • Posts: 2521
Re: Unclear error message for dbConnection
« Reply #3 on: April 05, 2021, 10:21:31 pm »
I use SQLite for a long time now and never had a problem so far.
To see more I think I will have to find a way to recompile fpc with debugger on because I know no other way to see what dbconnection is complaining about.

I don't use SQLite but I think there are text-oriented command/management tools: do your  select *... and pipe the result through grep looking for parentheses.

MarkMLl
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

JanRoza

  • Hero Member
  • *****
  • Posts: 618
    • http://www.silentwings.nl
Re: Unclear error message for dbConnection
« Reply #4 on: April 05, 2021, 10:51:08 pm »
I doubt if the error is in one of the two SQL select statements as the loop is completed without errors and all reading and posting is done there without any complaint.
For both queries the SQL statement while debugging shows
Quote
SELECT * FROM ''My Addresses''
so no strange characters there.
The same SQL is used in many other places in the program and works fine there, so I'm at a loss why here I suddenly get this error.
OS: Windows 10 (64 bit) / Linux Mint (64 bit)
Laz: Lazarus 2.0.12 FPC 3.2.0
       CodeTyphon 7.4 FPC 3.3.1

MarkMLl

  • Hero Member
  • *****
  • Posts: 2521
Re: Unclear error message for dbConnection
« Reply #5 on: April 05, 2021, 10:54:53 pm »
The same SQL is used in many other places in the program and works fine there, so I'm at a loss why here I suddenly get this error.

My guess is that there's something unexpected in the data, hence my suggestion.

MarkMLl
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

JanRoza

  • Hero Member
  • *****
  • Posts: 618
    • http://www.silentwings.nl
Re: Unclear error message for dbConnection
« Reply #6 on: April 05, 2021, 11:21:29 pm »
The target table is newly created in the database just before this coding and the backup database contains the table with content.
I checked the backup database content and it has only normal data, no empty/uninitialized fields and no non-standard characters in the data.
So it is still a great riddle to me.
OS: Windows 10 (64 bit) / Linux Mint (64 bit)
Laz: Lazarus 2.0.12 FPC 3.2.0
       CodeTyphon 7.4 FPC 3.3.1

Jurassic Pork

  • Hero Member
  • *****
  • Posts: 963
Re: Unclear error message for dbConnection
« Reply #7 on: April 06, 2021, 02:58:37 am »
hello,
JanRoza remember of this 2018 topic and the answer from rvk  :-[ :

Quote
One warning beforehand... NEVER EVER use spaces in table- and fieldnames. Sometimes you can but it's not common practice and eventually gets you in trouble (like it did now).
The problem is that FPC tries to parse the SQL statement. Probably with the quoted table-name this goes horribly wrong. (Internally somehow the "FTableName" has the value "WHERE") With a table name without spaces it works correctly.

There is a simple solution though. Just set the TSQLQuery.ParseSQL to false and you won't get the error-message anymore.

Friendly, J.P
« Last Edit: April 06, 2021, 03:04:38 am by Jurassic Pork »
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

JanRoza

  • Hero Member
  • *****
  • Posts: 618
    • http://www.silentwings.nl
[SOLVED] Unclear error message for dbConnection
« Reply #8 on: April 06, 2021, 03:44:46 pm »
As I want to make this program a simple to use database for home users with as little restrictions as possible the program allows for table names with spaces (only spaces in field names are restricted) I don't want to change that.
But I found a solution around this error by not using Query.ApplyUpdates and handling the actions all via SQL.

Working solution:
Code: Pascal  [Select][+][-]
  1.   // The backup database
  2.   strSQL := 'SELECT * FROM ' + QuotedStr(strDatabaseName);
  3.   qryTempBackup.Active := false;
  4.   qryTempBackup.SQL.Text := strSQL;
  5.   qryTempBackup.Active := true;
  6.   qryTempBackup.First;
  7.  
  8.   strSQL := 'INSERT INTO ' + QuotedStr(strDatabaseName);
  9.   strColumns := ' (';
  10.   strValues := ' VALUES (';
  11.  
  12.   for i := 0 to iFields do
  13.   begin
  14.     if i = 0
  15.     then begin
  16.          strColumns := strColumns + aFieldNames[i];
  17.          strValues := strValues + ':' + aFieldNames[i];
  18.     end
  19.     else begin
  20.          strColumns := strColumns + ', ' + aFieldNames[i];
  21.          strValues := strValues + ', ' + ':' + aFieldNames[i];
  22.     end;
  23.   end;
  24.  
  25.   strColumns := strColumns + ')';
  26.   strValues := strValues + ')';
  27.   strSQL := strSQL + strColumns + strValues;
  28.  
  29.   qryTemp.Active := false;
  30.   qryTemp.SQL.Text := strSQL;
  31.  
  32.   while not qryTempBackup.EOF do
  33.   begin
  34.     for i := 0 to iFields do
  35.     begin
  36.       if aFieldFormats[i] = 'Picture'
  37.       then qryTemp.Params.paramByName(aFieldNames[i]).AsBlob := qryTempBackup.FieldByName(aFieldNames[i]).AsBytes
  38.       else qryTemp.Params.ParamByName(aFieldNames[i]).AsString := qryTempBackup.FieldByName(aFieldNames[i]).AsString;
  39.     end;
  40.  
  41.     qryTemp.ExecSQL;
  42.     qryTempBackup.Next;
  43.   end;
  44.  
  45.   DataModule1.DbTransaction.CommitRetaining;
  46.  

This works like a charm, so another lesson learned and happy!  :D
OS: Windows 10 (64 bit) / Linux Mint (64 bit)
Laz: Lazarus 2.0.12 FPC 3.2.0
       CodeTyphon 7.4 FPC 3.3.1

MarkMLl

  • Hero Member
  • *****
  • Posts: 2521
Re: [SOLVED] Unclear error message for dbConnection
« Reply #9 on: April 06, 2021, 03:59:38 pm »
Which obviously might still leave problems in the components. I'm afraid that I'm a Neanderthal who prefers to apply "Pascal identifier" naming conventions to files, tables and the rest, but regrettably there's a subspecies of H.sapiens which has forgotten its predecessors' hard-won knowledge.

As I think I said earlier, I prefer to build my own queries and (possibly) to parse my own results, largely because of the full exposure that gives when problems like this occur. A lot of people criticise this approach on the grounds that it leaves systems open to SQL-injection exploits (obligatory xkcd: https://xkcd.com/327/), quite frankly I think their indignation would be better directed towards the provision of a decent set of SQL filtering and debugging libraries or components.

While I'm on this micro-rant, I'd also suggest that hand-driven SQL can be rather more tolerant of connection-pooling servers etc. Components such as are traditionally found in Delphi et al. have very little tolerance of query interruption: something I found to my cost when Debian automatically upgraded and then restarted a server.

MarkMLl
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

JanRoza

  • Hero Member
  • *****
  • Posts: 618
    • http://www.silentwings.nl
Re: [SOLVED] Unclear error message for dbConnection
« Reply #10 on: April 06, 2021, 04:37:04 pm »
I agree with your rant.  ;)
As you can see from the coding I showed the solution is now solely by SQL (not completely manual, but verifiable in the debugger) and in the end not much more complicated than a component solution.
I will certainly use this method of updating more from now on.
Since my database program is a offline desktop program I'm not so afraid for SQL-injections, the only online handling is when a program update is being downloaded.
OS: Windows 10 (64 bit) / Linux Mint (64 bit)
Laz: Lazarus 2.0.12 FPC 3.2.0
       CodeTyphon 7.4 FPC 3.3.1

 

TinyPortal © 2005-2018