I have a TSQLQuery (DXE5） connected via a TSQLConnection to a mySQL database v.5.1.

If I use a SELECT query that has an escaped ' (apostrophe) with a : (colon) later on in the string then it treats it as a param.

    SELECT id FROM p_name 
    WHERE fieldname = '01subst' 
    AND name = 'DILITHIUM DISODIUM (5,5\'-DIAMINO-(MU-4,4\'-DIHYDROXY-1:2-KAPPA-2,O4,O4\',-3,3\'-[3,3\'-DIHYDROXY-1:2-KAP'

so when I run
    sSQL := 'SELECT id FROM p_name WHERE fieldname = ''01subst'' AND name = ''DISODIUM (5,5\''-DIAMINO-(MU-4,4\''-DIHYDROXY-1:2-KAPPA-2,O4,O4\'',-3,3\''-[3,3\''-DIHYDROXY-1:2-KAP'''
    SQLQuery.ParamCheck := False;    // just to try to stop it from checking!
    SQLQuery.SQL.Text := sSQL;
    SQLQuery.Open;
    if SQLQuery.RecordCount = 1 then
      iID := SQLQuery.Fields[0].AsInteger;
then as soon as it tries SQLQuery.RecordCount it comes up with an exception.
Any other combination of apostrophe and/or colon doesn't produce the bug.

If I try to make it a parameterized query with ":name" then SQLQuery.RecordCount will always come up with an exception.

> 
> >If I try to make it a parameterized query with ":name" then SQLQuery.RecordCount will always come up with an exception.
> 

Many thanks. As mentioned above, when I use a parameterized query and then check SQLQuery.RecordCount to test if I should pick up the value returned or not, then this always produces an exception regardless of the contents of the parameter :Subst

sSQL := 'SELECT id FROM p_name WHERE fieldname = ''01subst'' AND name = :Subst';
SQLQuery.ParamByName('Subst').AsString := sSubst;  // sSubst value obtained from other table
SQLQuery.SQL.Text := sSQL;
SQLQuery.Open;
if SQLQuery.RecordCount = 1 then  <--- this blows it
   iID := SQLQuery.Fields[0].AsInteger;
"Remote Error: [0x0005]: Operation not supported" - every time.

This is why I've been obliged to put the entire query in one line non-parameterized.

I do not know if this has something to do specifically with using mySQL or the mybe the driver. I note article
http://edn.embarcadero.com/article/28494
from here.

How good is you Spanish?

http://www.clubdelphi.com/foros/showthread.php?t=79207

Translate (by Bing;-) :

If the component you are using has a property of type "FetchAllRecords" and is True, then it is the reason for which the recordcount you work.
But keep in mind that that is just what I have commented, for the value of a property, since a select to a database may not know how many records involved, unless you go to the last is something elementary that you'll see in any sql tutorial of any database.
Please keep this present, and you do not encabezones, RecordCount not returns the number of records. Except that it is a flat table, which is not the case.

> {quote:title=Robert Triest wrote:}{quote}
> How good is you Spanish?
Si, soy bilingue pero me puedes enviar tu mensaje original de abajo en el castellano a Private Messages porque no entiendo nada de la traduccion de Bing!
Yes, I'm bilingual but could you please send your original message below in Spanish to Private Message because I don't understand the Bing translation at all!
> 
> Translate (by Bing;-) :
> 
> If the component you are using has a property of type "FetchAllRecords" and is True, then it is the reason for which the recordcount you work.
> But keep in mind that that is just what I have commented, for the value of a property, since a select to a database may not know how many records involved, unless you go to the last is something elementary that you'll see in any sql tutorial of any database.
> Please keep this present, and you do not encabezones, RecordCount not returns the number of records. Except that it is a flat table, which is not the case.

Gracias
Thanks

Edited by: Chris Jones on Mar 27, 2014 12:05 PM

> Yes, I'm bilingual but could you please send your original message below in Spanish to Private Message because I don't understand the Bing translation at all!
The original message you can read on that site. It is the last comment of the reactions.

OK, think I've finally found a solution to this - another 20 hours spent on finding solutions rather than programming :-(

First of all have swapped the driver to Devart (which cost me a hundred euros or more but worth it). This is far less buggy that the one that comes with XE5, dbxmys.dll, easier to set up (just fill out the basic parameters - took me another 20 hours to get the dbxmys ones right) and in direct connection mode it works considerably faster too.

Second I've found that mySQL queries prefer " (doublequotes) around text fields rather than single quotes. All misinterpretation of colons in quotes disappears when you use this.

I've still no idea why a parameterized query followed by .RecordCount brings up an exception here but at least I have a way round it.

> OK, think I've finally found a solution to this - another 20 hours spent on finding solutions rather than programming :-(
That's quite normal. But I'm sure you will find also a programming task where you think it will take a week and that problem you solve within a hour.

>RecordCount brings up an exception here but at least I have a way round it. 
May I ask why you want that RecordCount so badly returned as a property of the component?
(Somehow the system has to force to fetch all the records and if it doesn't the recordcount is not safe to use
that';s why you can try to set this FetchAll property..)

You can Always ask "count" in your sql statement.
You can verify if there is a result set by "if not query.eof"
You can add the count together with your select by the OVER statement : "select *, count(*) OVER() AS Count from "Table"

Edited by: Robert Triest on Mar 27, 2014 5:12 PM

Very many thanks Robert and Remy. Lots of really helpful advice and comments there. I've tried a bit of sample code like that and it works great.