DELPHI / ACCESS SELECT SQL ERROR

Question

im using a UNIDAC TUniQuery component, this is my sql:

q_ContribSem2.Close;
q_ContribSem2.SQL.Clear;
q_ContribSem2.SQL.Add('SELECT SUMINISTRO.SUMTITIPOPERSONA, SUMINISTRO.SUMCHRAZONSOCIAL, SUMINISTRO.SUMCHAPELLIDOPATERNO,');
q_ContribSem2.SQL.Add('SUMINISTRO.SUMCHAPELLIDOMATERNO, SUMINISTRO.SUMCHNOMBRES, SUMINISTRO.SUMchCodigo, SUMINISTRO.SUMTITIPOCALLE, ');
q_ContribSem2.SQL.Add('SUMINISTRO.SUMCHNOMBRECALLE, SUMINISTRO.SUMCHNUMEROCALLE, SUMINISTRO.OBSERVACIONESMEDIDOR ');
q_ContribSem2.SQL.Add('FROM SUMINISTRO ');
q_ContribSem2.SQL.Add('WHERE ((((SUMINISTRO.OBSERVACIONESMEDIDOR) Like ' + chr(39) + 'NVO*' + chr(39) + ' Or ');
q_ContribSem2.SQL.Add('(SUMINISTRO.OBSERVACIONESMEDIDOR) Like ' + chr(39) + 'NUEVO*' + chr(39) + ') And');
q_ContribSem2.SQL.Add('((SUMINISTRO.OBSERVACIONESMEDIDOR) Like ' + chr(39) + '*JULIO*' + chr(39) + ' Or ');
q_ContribSem2.SQL.Add(' (SUMINISTRO.OBSERVACIONESMEDIDOR) Like ' + chr(39) + '*AGOSTO*' + chr(39) + ' Or ');
q_ContribSem2.SQL.Add(' (SUMINISTRO.OBSERVACIONESMEDIDOR) Like ' + chr(39) + '*SETIEMBRE*' + chr(39) + ' Or ');
q_ContribSem2.SQL.Add(' (SUMINISTRO.OBSERVACIONESMEDIDOR) Like ' + chr(39) + '*OCTUBRE*' + chr(39) + ' Or ');
q_ContribSem2.SQL.Add(' (SUMINISTRO.OBSERVACIONESMEDIDOR) Like ' + chr(39) + '*NOVIEMBRE*' + chr(39) + ' Or ');
q_ContribSem2.SQL.Add(' (SUMINISTRO.OBSERVACIONESMEDIDOR) Like ' + chr(39) + '*DICIEMBRE*' + chr(39) + ') And ');
q_ContribSem2.SQL.Add(' (SUMINISTRO.OBSERVACIONESMEDIDOR) Like ' + chr(39) + '*' + cboAnio.Text + '*' + chr(39) + '));');
q_ContribSem2.Open;

This statement return rows inside ACCESS but zero rows inside Delphi...

well any idea?

Thanks

did u tried their support ? they are usually quite responsive. forums.devart.com — Arioch 'The
– Arioch 'The, Commented Jan 11, 2013 at 15:55
what is table column type of SUMINISTRO.OBSERVACIONESMEDIDOR ? is it string or date ? "Tried Access" what do you mean ? Your database is in Ms Access ? Or u use some unnamed SQL server and u used Access to read data fro mthat server ? — Arioch 'The
– Arioch 'The, Commented Jan 11, 2013 at 15:56
SUMINISTRO.OBSERVACIONESMEDIDOR Is Text (50) if you want send me your email to atach the project ;) — Americo Torres
– Americo Torres, Commented Jan 11, 2013 at 15:59
did u considered using TAccessApplication ? Also standard SQL requires percent sign not asterisk to expand strings. msdn.microsoft.com/en-us/library/ms179859.aspx But i think you'd better to ask their support and also move cboAnio.Text into query parameter — Arioch 'The
– Arioch 'The, Commented Jan 11, 2013 at 16:22

Rob Kennedy · Accepted Answer · 2013-01-11 16:24:06Z

In regular SQL, the asterisk character is not a wildcard. The wildcard character that means "match anything" is the percent sign, %. Unidac offers "server-independent SQL," which probably means that it takes the SQL you give it, which should be in the Unidac-recognized dialect, and converts it to the dialect of the database you're targeting, so the SQL you write needs to be valid for Unidac, not necessarily for your actual target database. Unidac will handle the translation. If anything, Unidac is probably taking your asterisks and escaping them, so that the target database ends up receiving a request for fields that contain actual asterisks in them.

A way of determining this by yourself would first be to pare down your SQL until it does return something. Then you can gradually re-add pieces of your desired query until it starts to fail again, at which point you've identified which SQL your program has trouble with.

You might also wish to consider storing your dates as date fields instead of storing them as Spanish text. It would greatly simplify your query.

Also, beware of incorporating cboAnio.Text directly into your query. It could offer an SQL injection vulnerability. To avoid that, use a parameterized query.

Collectives™ on Stack Overflow

DELPHI / ACCESS SELECT SQL ERROR

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related