Recent

Author Topic: Sanity Check Please on SQL Statement  (Read 4123 times)

daveinhull

  • Full Member
  • ***
  • Posts: 234
  • 1 divided by nothing must still be 1!
Sanity Check Please on SQL Statement
« 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!
« Last Edit: January 16, 2019, 11:51:22 pm by daveinhull »
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

daveinhull

  • Full Member
  • ***
  • Posts: 234
  • 1 divided by nothing must still be 1!
Re: Sanity Check Please on SQL Statement
« Reply #1 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
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

sfeinst

  • Full Member
  • ***
  • Posts: 184
Re: Sanity Check Please on SQL Statement
« Reply #2 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.

daveinhull

  • Full Member
  • ***
  • Posts: 234
  • 1 divided by nothing must still be 1!
Re: Sanity Check Please on SQL Statement
« Reply #3 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!
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

daveinhull

  • Full Member
  • ***
  • Posts: 234
  • 1 divided by nothing must still be 1!
Re: Sanity Check Please on SQL Statement
« Reply #4 on: January 14, 2019, 12:26:18 pm »
Can anyone help?
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

daveinhull

  • Full Member
  • ***
  • Posts: 234
  • 1 divided by nothing must still be 1!
Re: Sanity Check Please on SQL Statement
« Reply #5 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
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

korba812

  • Jr. Member
  • **
  • Posts: 90
Re: Sanity Check Please on SQL Statement
« Reply #6 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']);

guest63552

  • Guest
Re: Sanity Check Please on SQL Statement
« Reply #7 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.

madref

  • Hero Member
  • *****
  • Posts: 565
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Sanity Check Please on SQL Statement
« Reply #8 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
« Last Edit: January 16, 2019, 10:15:49 am by madref »
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.0 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

daveinhull

  • Full Member
  • ***
  • Posts: 234
  • 1 divided by nothing must still be 1!
Re: Sanity Check Please on SQL Statement
« Reply #9 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 '
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64