[SOLVED] Unclear error message for dbConnection lazarus / 16-Databases ---------------------------------------------------------------------- JanRoza · 2021-04-05 14:24 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i strDatabaseName := 'My Addresses';   // The backup database  qryTempBackup.Active   := false;  qryTempBackup.SQL.Text := 'SELECT * FROM ' + QuotedStr(strDatabaseName);  qryTempBackup.Active   := true;  qryTempBackup.First;   // The target database  DataModule1.qryTemp.Active   := false;  DataModule1.qryTemp.SQL.Text := 'SELECT * FROM ' + QuotedStr(strDatabaseName);  DataModule1.qryTemp.Active   := true;  DataModule1.qryTemp.First;   while not qryTempBackup.EOF do  begin    DataModule1.qryTemp.Append;  DataModule1.qryTemp.Edit;     for i := 0 to iFields do    begin      case aFieldFormats[i] of        'Character'   : DataModule1.qryTemp.FieldByName(aFieldNames[i]).AsString := qryTempBackup.FieldByName(aFieldNames[i]).AsString;        'Memo'      : DataModule1.qryTemp.FieldByName(aFieldNames[i]).AsString := qryTempBackup.FieldByName(aFieldNames[i]).AsString;        'Numeric'   : DataModule1.qryTemp.FieldByName(aFieldNames[i]).AsInteger := qryTempBackup.FieldByName(aFieldNames[i]).AsInteger;        'Boolean'   : DataModule1.qryTemp.FieldByName(aFieldNames[i]).AsBoolean := qryTempBackup.FieldByName(aFieldNames[i]).AsBoolean;        'Date'      : DataModule1.qryTemp.FieldByName(aFieldNames[i]).AsDateTime := qryTempBackup.FieldByName(aFieldNames[i]).AsDateTime;        'Picture'     : DataModule1.qryTemp.FieldByName(aFieldNames[i]).AsBytes := qryTempBackup.FieldByName(aFieldNames[i]).AsBytes;      end;    end;     DataModule1.qryTemp.Post;    qryTempBackup.Next;  end;   DataModule1.qryTemp.ApplyUpdates;    // <<<<<<<<<<<<<< This is where the error occurs  DataModule1.DbTransaction.CommitRetaining;  Can anyone set me straight here, I'm staring myself blind without seeing what I did wrong here. ---------------------------------------------------------------------- MarkMLl · 2021-04-05 17:43 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 · 2021-04-05 20:14 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 · 2021-04-05 20:21 Quote from: JanRoza on April 05, 2021, 10:14:24 pmI 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 · 2021-04-05 20:51 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 QuoteSELECT * 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 · 2021-04-05 20:54 Quote from: JanRoza on April 05, 2021, 10:51:08 pmThe 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 · 2021-04-05 21:21 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 · 2021-04-06 00:58 hello, JanRoza remember of this 2018 topic and the answer from rvk  : QuoteOne 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 · 2021-04-06 13:44 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   // The backup database  strSQL := 'SELECT * FROM ' + QuotedStr(strDatabaseName);  qryTempBackup.Active := false;  qryTempBackup.SQL.Text := strSQL;  qryTempBackup.Active := true;  qryTempBackup.First;   strSQL := 'INSERT INTO ' + QuotedStr(strDatabaseName);  strColumns := ' (';  strValues := ' VALUES (';   for i := 0 to iFields do  begin    if i = 0    then begin         strColumns := strColumns + aFieldNames[i];         strValues := strValues + ':' + aFieldNames[i];    end    else begin       strColumns := strColumns + ', ' + aFieldNames[i];         strValues := strValues + ', ' + ':' + aFieldNames[i];    end;  end;   strColumns := strColumns + ')';  strValues := strValues + ')';  strSQL := strSQL + strColumns + strValues;    qryTemp.Active := false;  qryTemp.SQL.Text := strSQL;   while not qryTempBackup.EOF do  begin  for i := 0 to iFields do    begin      if aFieldFormats[i] = 'Picture'      then qryTemp.Params.paramByName(aFieldNames[i]).AsBlob := qryTempBackup.FieldByName(aFieldNames[i]).AsBytes      else qryTemp.Params.ParamByName(aFieldNames[i]).AsString := qryTempBackup.FieldByName(aFieldNames[i]).AsString;    end;     qryTemp.ExecSQL;    qryTempBackup.Next;  end;   DataModule1.DbTransaction.CommitRetaining;  This works like a charm, so another lesson learned and happy! ---------------------------------------------------------------------- MarkMLl · 2021-04-06 13:59 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 · 2021-04-06 14:37 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.