mySQL & RevEnterprise: queries not returning expected values

[G. Wolfgang Gaich]

Hi Ian,

for varchar fields in a where clause:

put single quotes around the searchstring

e.g.: where certificate = '12a'

the like operator is only necessary if you want to use a pattern

e.g.: where name like '%Knight'

HTH

Wolfgang



Am 23.05.2010 15:12, schrieb Ian McKnight:
> Hi
>
> I am attempting to teach myself the rudiments of using mySQL databases
> on on-rev. To this end I have created and populated a simple single
> table database of six fields - a catalogue of DVD titles.
>
> I am coming across problems when I attempt to search of my CERTIFICATE
> field which is VARCHAR of length 3. This contains any one of NC, U,
> PG, 12, 12A, 15 and 18. and my GENRE field which contains words such
> as comedy, action, adventure etc separated by commas
>
> This query for certificate 12 returns a list of DVDs
>
>     put revDataFromQuery(tab, return, tDBID, "SELECT
> uniqid,title,format,duration,certificate,genre FROM mainTable  WHERE
> certificate LIKE 12 ORDER BY title") into tData
>
> However replacing 12 with U (or PG 12A etc) does not - it gives a
> revDBerr, Unknown column 'U' in 'where clause' - and a similar
> response when searching the GENRE field.
>
> It seems to work only for numeric values in REV - performing the same
> searches in myPGPAdmin everything works fine.
>
> Any advice would be most appreciated.
>
>