Using Prop:SQLRowSet on Dynamic File Driver

1

Has anyone managed to make it running?
I have this code, tried but doesn’t build the structure when prop:sqlrowset is used, but no errorcode or fileerrorcode

   MyDynamicFile &= NEW(FILE)
   MyDynamicFile{PROP:Driver} = 'ODBC'
   MyDynamicFile{PROP:DriverString} = '\TURBOSQL=TRUE'
   MyDynamicFile{PROP:Owner} = GLO:SQLConnectString
   OPEN(MyDynamicFile)
   IF SUB(CLIP(LEFT(p_SQLQuery)),1,4) = 'WITH'
      MyDynamicFile{PROP:SQLRowSet} = p_SQLQuery
   ELSE
      MyDynamicFile{PROP:SQL} = p_SQLQuery
   END
   FIXFORMAT(MyDynamicFile)
   !map columns
   DO DeepAssignColumns

Kind Regards,
Christian

2

That’s true. I changed {prop:SQL} to {prop:SQLRowSet} in DynFile.CreateFromSQL PROCEDURE (DynFile.clw) and FillVirtualListBox.sln example still works fine with predefined SQL SELECT queries against Northwind sample database.

Unfortunately, following CTE and stored procedure queries fail with errorcode 80 “Unsupported File Diver Function” using either {prop:SQL} or {prop:SQLRowSet} with Dynamic File Driver

WITH q (Records) AS (SELECT COUNT(*) FROM Products) SELECT * FROM q

exec dbo.CustOrderHist 'VINET'
1 Like
3

I still don’t understand why it doesn’t work if it works with regular SQL. It would be great if SV added support for this, as it’s becoming increasingly common.

There has to be some flag in the driver.

4

No idea what the problem with the Dynamic driver is, but PROP:SQLRowSet is exactly that: it’s a flag. It tells Clarion: even if this doesn’t look like a query that returns a rowset (because it doesn’t start with SELECT) , it really is, so you have to set up a place for the results to be stored.