[SOLVED] MS Access

[KryptKicker5]

I'm connecting to an MS Access database using ODBCConnection and using an SQLQuery component. Everything works, mostly. I cannot under any circumstances send Access a double quoted string:

Code: MySQL  [Select][+][-]

1. SELECT * FROM premis WHERE (AddNum LIKE ''417'');

Works like you would think. No method of double quoting has worked: AnsiQuotedStr, QuotedString, Chr(34), ect. Double quotes are needed in Access for the wildcard characters to work. Why? I don't know, but it does. I have tested all of these queries in Access against the database itself. Entering these generated queries into Acccess via copy and paste works every time it should. Below is a hardcoded example that I've been experimenting with.

Code: Pascal  [Select][+][-]

1. tmp := 'SELECT * FROM address WHERE( AddStr LIKE "*elm*");';

And here's what I'm using to connect DSN-less to the database.

Code: Pascal  [Select][+][-]

1.  
2.   ODBCConnection1.Driver := 'Microsoft Access Driver (*.mdb, *.accdb)';
3.   ODBCConnection1.Params.Clear;
4.   ODBCConnection1.Params.Add('DBQ=D:\mydatabase.mdb');
5.  
6.   ODBCConnection1.Connected      := true;
7.   ODBCConnection1.KeepConnection := true;
8.  
9.   SQLTransaction1.Database := ODBCConnection1;
10.   SQLTransaction1.Action   := caCommit;
11.   SQLTransaction1.Active   := True;
12.  
13.   SQLQuery1.DataBase := ODBCConnection1;
14.   SQLQuery1.UsePrimaryKeyAsKey := False;
15.   SQLQuery1.ParseSQL           := False;
16.  
17.  

Mostly when I use double quotes I get ODB driver errors with "Too few parameters." If I do not get the error it pulls nothing from the database.

I'm going to sleep, if anyone has any suggestions, please let me know, and thank you in advance! I'm guessing it's something silly that I'm missing. I've been beating my head against the wall for hours now.

P.S. I also get an "External Access Violation" whenever I close the program while debugging. Literally, a blank form application with a ODBCConnection component connecting to a database is enough to throw the violation every single time on exit. And it's always beyond the scope of the program. In my work environment I run Lazarus as admin and it goes away. At home it shows up every time. These errors NEVER show up in release; all connections to the database are closed successfully. So I don't know if any of that has anything to do with it or not.

[PascalDragon]

I'm connecting to an MS Access database using ODBCConnection and using an SQLQuery component. Everything works, mostly. I cannot under any circumstances send Access a double quoted string:

Code: MySQL  [Select][+][-]

1. SELECT * FROM premis WHERE (AddNum LIKE ''417'');

Works like you would think. No method of double quoting has worked: AnsiQuotedStr, QuotedString, Chr(34), ect. Double quotes are needed in Access for the wildcard characters to work. Why? I don't know, but it does. I have tested all of these queries in Access against the database itself. Entering these generated queries into Acccess via copy and paste works every time it should. Below is a hardcoded example that I've been experimenting with.

Code: Pascal  [Select][+][-]

1. tmp := 'SELECT * FROM address WHERE( AddStr LIKE "*elm*");';

You can get a single ' inside a string by writing ''. So to get a '' you need to write ''''. Thus your query string becomes:

Code: Pascal  [Select][+][-]

1. tmp := 'SELECT * FROM address WHERE( AddStr LIKE ''''*elm*'''');';

[Zvoni]

1) " is a Double Quote, ' is a single Quote
2) Correct. In Access you need to Double Quote Strings/Text
3) Have you tried:

Code: Pascal  [Select][+][-]

1. tmp := 'SELECT * FROM address WHERE AddStr LIKE "*elm*";';

Note i've removed the Paranthesis.
Or use Parameters, which should take care of quoting

Code: Pascal  [Select][+][-]

1. MyQuery.SQL.Text:= 'SELECT * FROM address WHERE AddStr LIKE "*"+:pElm+"*";';
2. MyQuery.ParamByName('pElm').AsString:='elm';
3. MyQuery.Open;

[KryptKicker5]

1) " is a Double Quote, ' is a single Quote
2) Correct. In Access you need to Double Quote Strings/Text
3) Have you tried:

Code: Pascal  [Select][+][-]

1. tmp := 'SELECT * FROM address WHERE AddStr LIKE "*elm*";';

Note i've removed the Paranthesis.
Or use Parameters, which should take care of quoting

Code: Pascal  [Select][+][-]

1. MyQuery.SQL.Text:= 'SELECT * FROM address WHERE AddStr LIKE "*"+:pElm+"*";';
2. MyQuery.ParamByName('pElm').AsString:='elm';
3. MyQuery.Open;

I did try the first, any time I try to use params I get an error message that the "Parameter cannot be found."

[KryptKicker5]

I'm connecting to an MS Access database using ODBCConnection and using an SQLQuery component. Everything works, mostly. I cannot under any circumstances send Access a double quoted string:

Code: MySQL  [Select][+][-]

1. SELECT * FROM premis WHERE (AddNum LIKE ''417'');

Works like you would think. No method of double quoting has worked: AnsiQuotedStr, QuotedString, Chr(34), ect. Double quotes are needed in Access for the wildcard characters to work. Why? I don't know, but it does. I have tested all of these queries in Access against the database itself. Entering these generated queries into Acccess via copy and paste works every time it should. Below is a hardcoded example that I've been experimenting with.

Code: Pascal  [Select][+][-]

1. tmp := 'SELECT * FROM address WHERE( AddStr LIKE "*elm*");';

You can get a single ' inside a string by writing ''. So to get a '' you need to write ''''. Thus your query string becomes:

Code: Pascal  [Select][+][-]

1. tmp := 'SELECT * FROM address WHERE( AddStr LIKE ''''*elm*'''');';

I understand, the first example I gave was meant to illustrate that escaping single quotes for values worked as intended. But no method of trying to send double-quoted values has worked.

[tetrastes]

Just guess.
May be driver parses strings like C compiler?

Code: Pascal  [Select][+][-]

1. tmp := 'SELECT * FROM address WHERE( AddStr LIKE \"*elm*\");';

[wp]

There used to be a document "Intermediate Microsoft Jet SQL for Access 2000" on one of the Microsoft pages, and it is difficult to find nowadays. Here is one: https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&ved=2ahUKEwjS8vKfz5X7AhVkSfEDHbAgBVQQFnoECBEQAQ&url=http%3A%2F%2Fblog.pagesd.info%2Fpublic%2F2006%2Fintermediate-microsoft-jet-sql-for-access.pdf&usg=AOvVaw3t0gowb2r6pAi0W8Sq_Wkr.

And this document contains a list of the wildcards accepted by Jet and ADO.

Wildcard Character	Description
* (asterisk)	Matches any number of characters and can be used anywhere in the pattern string.
% (percent sign)	Matches any number of characters and can be used anywhere in the pattern string. (ADO and the Jet OLE DB provider only)
? (question mark)	Matches any single character and can be used anywhere in the pattern string.
_ (underscore)	Matches any single character and can be used anywhere in the pattern string. (ADO and the Jet OLE DB provider only)
# (number sign)	Matches any single digit and can be used anywhere in the pattern string.
[] (square brackets)	Matches any single character within the list that is enclosed within brackets, and can be used anywhere in the pattern string.
! (exclamation point)	Matches any single character not in the list that is enclosed within the square brackets.
- (hyphen)	Matches any one of a range of characters that is enclosed within the square brackets.

I did several tests on this and found that the '*', '?' and '#' wildcards always yield a empty recordset with ODBC in Lazarus. But the others are working. The other conditions are: The search string must be single-quoted, and the conditon expression must be in normal parenthesis.

Example:
The following SELECT query finds all records where the field AddStr contains a substring 'elm' somewhere.

Code: [Select]

tmp := 'SELECT * FROM address WHERE ( AddStr LIKE ''%elm%'')';
Maybe I should note that I did this with the mdb format, not with the new accdb.

[Zvoni]

Wp, good to know.
Maybe something for the Wiki?

[KryptKicker5]

There used to be a document "Intermediate Microsoft Jet SQL for Access 2000" on one of the Microsoft pages, and it is difficult to find nowadays. Here is one: https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&ved=2ahUKEwjS8vKfz5X7AhVkSfEDHbAgBVQQFnoECBEQAQ&url=http%3A%2F%2Fblog.pagesd.info%2Fpublic%2F2006%2Fintermediate-microsoft-jet-sql-for-access.pdf&usg=AOvVaw3t0gowb2r6pAi0W8Sq_Wkr.

And this document contains a list of the wildcards accepted by Jet and ADO.

Wildcard Character	Description
* (asterisk)	Matches any number of characters and can be used anywhere in the pattern string.
% (percent sign)	Matches any number of characters and can be used anywhere in the pattern string. (ADO and the Jet OLE DB provider only)
? (question mark)	Matches any single character and can be used anywhere in the pattern string.
_ (underscore)	Matches any single character and can be used anywhere in the pattern string. (ADO and the Jet OLE DB provider only)
# (number sign)	Matches any single digit and can be used anywhere in the pattern string.
[] (square brackets)	Matches any single character within the list that is enclosed within brackets, and can be used anywhere in the pattern string.
! (exclamation point)	Matches any single character not in the list that is enclosed within the square brackets.
- (hyphen)	Matches any one of a range of characters that is enclosed within the square brackets.

I did several tests on this and found that the '*', '?' and '#' wildcards always yield a empty recordset with ODBC in Lazarus. But the others are working. The other conditions are: The search string must be single-quoted, and the conditon expression must be in normal parenthesis.

Example:
The following SELECT query finds all records where the field AddStr contains a substring 'elm' somewhere.

Code: [Select]

tmp := 'SELECT * FROM address WHERE ( AddStr LIKE ''%elm%'')';
Maybe I should note that I did this with the mdb format, not with the new accdb.

That's AWESOME! It works perfectly! Thank you!

[wp]

Maybe something for the Wiki?

Done: https://wiki.freepascal.org/MS_Access#Wildcards_in_SQL_queries

I am attaching the application in which I tested all this (it is hosted on my github: https://github.com/wp-xyz/LazSamples/tree/master/database/access/select_query)