Lazarus

Programming => Databases => Topic started by: daveinhull on January 12, 2019, 03:22:55 pm

Title: Sanity Check Please on SQL Statement
Post by: daveinhull on January 12, 2019, 03:22:55 pm
Hi,

I think I'm going crazy!

I have an SQL statement to a MS Access DB as follows
Code: SQL  [Select][+][-]
  1. SELECT Table1.Entries
  2. FROM Table1
  3. WHERE (((Table1.Entries)="Test1"));

It works perfectly in Access, but in Pascal it returns a "Too few parameters. Expected 1.

I just can't see what I'm doing wrong.....help!
Title: Re: Sanity Check Please on SQL Statement
Post by: daveinhull on January 12, 2019, 05:18:58 pm
3 open and 3 closed; this was taken from the SQL statement in Access although I know it doesn't really need any parentheses.

That's a bit like the old management training exercise about counting the F's  :D
Title: Re: Sanity Check Please on SQL Statement
Post by: sfeinst on January 12, 2019, 05:25:05 pm
Hi,

I think I'm going crazy!

I have an SQL statement to a MS Access DB as follows
Code: SQL  [Select][+][-]
  1. SELECT Table1.Entries
  2. FROM Table1
  3. WHERE (((Table1.Entries)="Test1"));

It works perfectly in Access, but in Pascal it returns a "Too few parameters. Expected 1.

I just can't see what I'm doing wrong.....help!

Try surrounding Test1 with single quotes instead of double quotes.  Double quotes is an Access specific variation to SQL.  Using Pascal means you are most likely using ODBC which would use standard SQL.
Title: Re: Sanity Check Please on SQL Statement
Post by: daveinhull on January 12, 2019, 07:58:40 pm
Hi sfeinst, tries that, but just to double check tried again.

This works
Code: Text  [Select][+][-]
  1. 'SELECT Entries FROM Table1'

This doesn't
Code: SQL  [Select][+][-]
  1. 'SELECT Entries FROM Table1 WHERE Entries = ''Test1'''

Now comes up with Syntax error in FROM clause;

Code: Pascal  [Select][+][-]
  1. Format ('SELECT Entries FROM Table1 WHERE Entries = %s',['Test1'])

And this comes up with the too few parameters error;

I've just got to be doing something silly, not unusual for me!
Title: Re: Sanity Check Please on SQL Statement
Post by: daveinhull on January 14, 2019, 12:26:18 pm
Can anyone help?
Title: Re: Sanity Check Please on SQL Statement
Post by: daveinhull on January 15, 2019, 08:42:04 pm
Hi, can anyone help with this problem as its stopped me continuing to develop my app. I just can't see what is wrong.
Thanks
Title: Re: Sanity Check Please on SQL Statement
Post by: korba812 on January 15, 2019, 08:53:38 pm
I think that the string value should be in quotes:
Code: Pascal  [Select][+][-]
  1. Format('SELECT Entries FROM Table1 WHERE Entries = ''%s''',['Test1']);
Title: Re: Sanity Check Please on SQL Statement
Post by: guest63552 on January 15, 2019, 09:12:54 pm
Hi, can anyone help with this problem as its stopped me continuing to develop my app. I just can't see what is wrong.

I have used MS Access with Delphi last time more than 15 years ago. I can hardly remember, but double and single quote where differently meaning. I have used ADO component with Delphi, however I have no idea what is using with Lazarus.  Even worse, I do not use Windows anymore...

However,  you can provide some very simple Lazarus source with test database, perhaps someone will help.

However, I do not think that kind of SQL expression will be accepted from MS Access driver, I believe strings (and table names) need to be double quoted.

If I can remember correctly, with ADO there was two separated methods: one was to provide clean string and execute commands only (UPDATE and similar), EXECUTE method or similar. Another was to use QUERY methods with SQL queries which returns data, as upper.
Title: Re: Sanity Check Please on SQL Statement
Post by: madref on January 16, 2019, 10:12:17 am
It's an easy fix. Use
Code: Pascal  [Select][+][-]
  1. Chr(39)


So:
Code: Pascal  [Select][+][-]
  1. 'SELECT Entries FROM Table1 WHERE Entries = ' + chr(39) + 'Test1' + Chr(39)


I had the sake problem with switching from Windows to Osx. Osx has NO MS Access so i had to write everything myself.
I found this little program/app DB Browser for SQLite. In this app you can not only edit your database, but also make and test your SQL queries/views so you know if it's a valid one or a crap one
Title: Re: Sanity Check Please on SQL Statement
Post by: daveinhull on January 16, 2019, 11:51:14 pm
Thanks everyone, now sorted. Turned out to be a combination of little mistakes, but the Chr(39) approach certainly stopped some confusion of double/triple '
TinyPortal © 2005-2018