Is there a faster way to display a large SQL result?

Paul D · Post by **Paul D** » Mon Jul 09, 2007 6:52 pm

My code is pretty simple.

put "SELECT * FROM VEH_DATA where license = '" & vLic & "';" into vSQL
put fld "dbid" into dbconnect
put revQueryDatabase(dbconnect, vSQL) into cID
put revNumberofRecords(cID) into vRecNo
put revDatabaseColumnCount(cID) into vColNum
revMoveToFirstRecord cID
Repeat vRecNo of times
repeat with c = 1 to vColNum
put revDatabaseColumnNumbered(cID, c) into vRow
replace cr with empty in vrow
put vRow & tab after fld "history"
end repeat
put cr after fld "history"
revMovetoNextRecord cID
end repeat

When this code executes my stack is some what suspended and I can not work with it while it is populating the text field list. The text field has 8 columns. Some queries could be hundreds of lines long and the user has to wait for revolution to get done filling the text field before any other buttons can be pressed or start viewing the data. Is there a faster way to display the results of the SQL query? If Im unclear on my question let me know, Ill try to be more specific.

Mark · Post by **Mark** » Mon Jul 09, 2007 8:31 pm

Hi Paul D,

Can you use revDataFromQuery?

Best,

Mark

Janschenkel · Post by **Janschenkel** » Tue Jul 10, 2007 6:21 am

Hi Paul,

As Mark suggested, the 'revdb_querylist'/'revDataFromQuery' functions may do the trick more quickly, as the concatenation is done in compiled C++ code rather than the Revolution scripting language.

If these functions are still taking too long, you could try and fake a multi-threading approach.

Code: Select all

local cID, vRecNo, sColNum, msgID

on mouseUp
  -- prevent a mess by cleaning up the previous thread
  if msgID is not empty then
    try
      cancel msgID
    end try
  end if
  -- proceed to select data from the database
  put "SELECT * FROM VEH_DATA where license = '" & vLic & "';" into vSQL 
  put fld "dbid" into dbconnect 
  put revQueryDatabase(dbconnect, vSQL) into cID 
  put revNumberofRecords(cID) into vRecNo 
  put revDatabaseColumnCount(cID) into vColNum
  -- now read the first 20 records, and kick off the 'send in time'
  ReadNextBatch
end mouseUp

on ReadNextBatch
  -- we read a batch of 20 records at a time
  repeat 20 times
    repeat with c = 1 to vColNum 
      put revDatabaseColumnNumbered(cID, c) into vRow 
      replace cr with empty in vrow 
      put vRow & tab after fld "history" 
    end repeat 
    put cr after fld "history" 
    -- make sure to exit after the last record
    if revCurrentRecordIsLast(cID) then 
      put empty into msgID
      exit ReadNextBatch
    end if
    -- otherwise go to the next record
    revMovetoNextRecord cID 
  end repeat
  -- make sure we're called again in a bit
  send "ReadNextBatch" to me in 50 milliseconds
  put the result into msgID
end ReadNextBatch(

You may have to put in some code to prevent the field from scrolling back to the top while the user interacts with it - but I'll leave that as an exercise to the reader

Hope this helped,

Jan Schenkel.

Paul D · Post by **Paul D** » Wed Jul 11, 2007 7:03 pm

revDataFromQuery did the trick but I also like the example you gave me Jan. Thanks again!