[SOLVED] Unclear error message for dbConnection

[JanRoza]

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.

[MarkMLl]

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

[JanRoza]

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.

[MarkMLl]

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

[JanRoza]

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

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.

[MarkMLl]

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

[JanRoza]

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.

[Jurassic Pork]

hello,
JanRoza remember of this 2018 topic and the answer from rvk  :

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

[JanRoza]

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! 

[MarkMLl]

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

[JanRoza]

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.