Recent

Author Topic: Getting SQL error I don't understand  (Read 2984 times)

KarenT

  • Full Member
  • ***
  • Posts: 120
Getting SQL error I don't understand
« on: March 06, 2018, 06:03:24 pm »
Hi All,

SQLIte, laz 1.8.0RC1, Ubuntu 16.04

Here's what  get and it shows the sqlConn (my Connection) as tossing the error, or does it? :)

Project sData raised exception class 'ESQLDatabaseError' with message: sqlConn: no such column: zDate


Code: Pascal  [Select][+][-]
  1. procedure TDM.RefreshSerial(aFld, aDir : String);
  2. var
  3.   sqlStr : String;
  4. begin
  5. //  sqlSerial.Close;
  6.   sqlStr:='SELECT * FROM sqlSerial ORDER BY '+aFld+' '+aDir+';';
  7.   sqlSerial.SQL.Text:=sqlStr;
  8.   sqlSerial.ExecSQL;
  9. //  sqlSerial.Open;
  10. end;
  11.  

If I single step through the code it is showing a correct string, sqlStr = 'SELECT * FROM sqlSerial ORDER BY zDate ASC'' And, Yes, zDate is a valid Field in the Table.

Thoughts?

Thanks

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: Getting SQL error I don't understand
« Reply #1 on: March 06, 2018, 06:17:35 pm »
in short wrong database. A bit longer usually the end user has a sqlite.dll in the search path so it can be accessed by lazarus.exe and his own application. Most users assume that setting the database name to a file name will always open the file in the same directory as their own executable. That is false if the database is connected from lazarus the file will be expected in the same folder as the lazarus.exe unless an other application has changed the current directory variable to something else. If you combine all that with the uncommon ability of the sqlite.dll to auto create the database if it is not found instead of rasing an error I come to the conclusion you are not opening the database you think you open.

So to sum it up, search your drives for multiple databases and make sure that your application opens the correct one (define the full path to the database).
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

KarenT

  • Full Member
  • ***
  • Posts: 120
Re: Getting SQL error I don't understand
« Reply #2 on: March 06, 2018, 07:53:58 pm »
in short wrong database.

Thanks, but I doubt that as the database is visually open in the underlying grid when the Refresh is called.

Also I am using the "GetAppConfigDir" and assigning it at run-time, so pretty sure the database is the right one.

Am I correct in assuming that?

Thank you.

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: Getting SQL error I don't understand
« Reply #3 on: March 06, 2018, 08:30:39 pm »
the second most probable reason is that a secondary data control is linked to the query and has a a field name that is not found on the query after open but you did have already rule that out. The next logical step is to post  a minimalistic example that reproduces the problem so we can take a look.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Getting SQL error I don't understand
« Reply #4 on: March 07, 2018, 08:25:55 am »
Quote
sqlStr:='SELECT * FROM sqlSerial ORDER BY '+aFld+' '+aDir+';';
This is not correct. function ORDER BY can not work wit spaces.
Code: Pascal  [Select][+][-]
  1. sqlStr := format('SELECT * FROM sqlSerial ORDER BY  %s,%s',[aFld,aDir])
  2.  
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Getting SQL error I don't understand
« Reply #5 on: March 07, 2018, 08:29:12 am »
Quote
sqlStr:='SELECT * FROM sqlSerial ORDER BY '+aFld+' '+aDir+';';
This is not correct. function ORDER BY can not work wit spaces.
Code: Pascal  [Select][+][-]
  1. sqlStr := format('SELECT * FROM sqlSerial ORDER BY  %s,%s',[aFld,aDir])
  2.  
aDir is meant for ASC or DESC and that does need a space. It's not meant for a second field here.

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Getting SQL error I don't understand
« Reply #6 on: March 07, 2018, 08:31:47 am »
okay, forgot to read the last line.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

luckylynx

  • New Member
  • *
  • Posts: 19
Re: Getting SQL error I don't understand
« Reply #7 on: March 07, 2018, 06:50:09 pm »
Hi, I am working with Firebird and here I use e.g.
 
      Close;
      SQL.Text := 'SELECT * from tablename where customer = :A order by street desc';
      ParamByName('A').AsInteger := custno;
      Open; 

Parameters is the key.
Your  'sqlSerial.ExecSQL' ' statement together with a 'Select' would never work.
For 'Select ' statements use close...open. For insert, delete and update the statements 'execsql' is required.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Getting SQL error I don't understand
« Reply #8 on: March 07, 2018, 07:04:12 pm »
Your  'sqlSerial.ExecSQL' ' statement together with a 'Select' would never work.
For 'Select ' statements use close...open. For insert, delete and update the statements 'execsql' is required.
Good spot that ExecSQL is used. Although SELECT with an ExecSQL shouldn't give an error on ExecSQL. It just doesn't give back a dataset (so you shouldn't access it as a dataset).

But it's probably that sqlSerial is used elsewhere in the program or a datasource is connected with a DBGrid expecting field zDate, which doesn't exist anymore because ExecSQL is executed and no dataset is returned.

sqlSerial.Open was commented out (which would be incorrect because it is needed).
You're the only one who spotted it  8-)

 

TinyPortal © 2005-2018