TEXT fields and records count

[bangkok]

A question not directly related to RunRev.

In a MYSQL database, I use a TEXT field (for text up to 65 000 chars).

I can write successfuly data with RunRev, with texts (that contain carriage returns).

My problem is to read the data : using revDataFromQuery, I can't know how many records I get, because if I count the lines, I get a high number (with all the CR contained in the text field).

Do you have any elegant trick to bypass this issue ?

Or do I have to remove all the CR characters when I write the data, to replace them by something else, and then after reading the data, doing the reverse ?

[BvG]

you want the revNumberOfRecords function.

[oliverk]

Hi,

There are two ways to do this, either you encode the data so that it is all a single line, and decode it again in revTalk, or you can use a database cursor instead of using revDataFromQuery. The cursor method is definitely the way I would go here.

Something like this for example:

Code: Select all

# Query the database, getting back a cursor object, which allows the data to be retrieved bit by bit
local tCursor
put revQueryDatabase(sConnectionId, tSQL) into tCursor

# Check that the query worked
if tCursor is not a number then
 answer "Database error: " & tCursor
end if

# Get all the data from the cursor, and put it into an array so its easier to process
local tDataArray
put createOrderedArrayFromCursor(tCursor) into tDataArray

# Now you have the data, you need to write some code to display it. If you have created a data grid, you can do this:
set the dgData of group "DataGrid 1" to tDataArray

# Or to manually loop through the data
local tNumberOfRecords
put item 2 of line 1 of the extents of tDataArray into tNumberOfRecords

local tRecord
repeat with x = 1 to tNumberOfRecords
   put tDataArray[x] into tRecord
   
    # The keys of the array tRecord will match the column names of your query, eg: 
   put "Name = " & tRecord["Name"] & return & "Address = " & tRecord["Address"]
end repeat

Note that I used a handy utility function to help with the the cursor stuff, here is its implementation:

Code: Select all

function createOrderedArrayFromCursor pCursor
  local tColumnNames
  put revDatabaseColumnNames(pCursor) into tColumnNames

  local tRecordNumber
  put 1 into tRecordNumber
  
  local tResult, tRow
  repeat until revQueryIsAtEnd(pCursor)
    repeat for each item tColumn in tColumnNames
       local tValue 
       get revDatabaseColumnNamed(pCursor, tColumn, "tValue")
       put tValue into tRow[tColumn]
    end repeat
      
    put tRow into tResult[tRecordNumber]
    
    revMoveToNextRecord pCursor
    add 1 to tRecordNumber
  end repeat
   
  return tResult
end createOrderedArrayFromCursor

You'll have to tweak this to make it work with your application, but hopefully this helps you to get the idea.

Regards
Oliver

[bangkok]

Thanks for your answers.

Yep, I totally forgot about the cursor function. That should do the trick.