Bug or...? Native Querying

[Bob Sneidar]

<sigh> I spoke too soon. I appended some SQL onto the initial query and there was an error in the SQL. I used

...where custid = 56 AND fileversion = (select max(fileversion) from files t2 where t2.filename = t1.filename)

I should have used

...where custid = 56 AND fileversion = (select max(t1.fileversion) from files t2 where t2.filename = t1.filename)

I was making an ambiguous reference to fileversion. Odd that the SQL ran anyway. Apparently if the reference is inside a function, mySQL does not balk!

Bob S



On Aug 7, 2023, at 3:48 PM, Bob Sneidar via use-livecode <use-livecode at lists.runrev.com> wrote:

I have code that uses the native API for SQL (as opposed to sqlYoga) because I have to do some joining that I do not know how to do with sqlYoga. The query string looks like this:

select fileid,filename,fileversion,filecategory,custid,siteid,deviceid,serviceid,softwareid,parentidlist,filemodified,tempfilemodified,tmpfilesmodified,checkoutid,checkoutname,checkoutpath,rfapath,lastupdate,updateby
from files t1
where custid = 56;

When I execute this query in mySQLWorkbench, I get 6 records. When I use the native API:

put revQueryDatabase( tDBID, tSQL) into tCursor

Then I get the revNumberOfRecords(tCursor), but it’s only 5!!!!!

The same query gets 6 using mySQLWorkbench and only 5 are counted in the cursor!

Please tell my I haven’t stumbled across a Lex Luther universe!

Bob S