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. http://forums.devart.com/ — Arioch 'The, Jan 11 '13 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, Jan 11 '13 at 15:56
SUMINISTRO.OBSERVACIONESMEDIDOR Is Text (50) if you want send me your email to atach the project ;) — Americo Torres, Jan 11 '13 at 15:59
The field observacionesMedidor is Text(50), i uploaded the project to http://www.unibytes.com/Pe63KcXG8RgLqw-Us4P3UgBB If you want take a look Thanks — Americo Torres, Jan 11 '13 at 16:11
did u considered using TAccessApplication ? Also standard SQL requires percent sign not asterisk to expand strings. http://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, Jan 11 '13 at 16:22
^+1, learn to use parameters from the start, it will save you from headaches later on... — whosrdaddy, Jan 11 '13 at 17:23

score 5 · Accepted Answer · answered Jan 11 '13 at 16:24

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.

DELPHI / ACCESS SELECT SQL ERROR

1 Answers1