LiveCode Forums.

Posted: **Tue Dec 22, 2009 2:40 pm**

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 ?

Posted: **Wed Dec 23, 2009 10:38 am**

you want the revNumberOfRecords function.

Posted: **Wed Dec 23, 2009 3:46 pm**

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

Posted: **Fri Dec 25, 2009 9:25 am**

Thanks for your answers.

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

LiveCode Forums.

TEXT fields and records count

TEXT fields and records count

Re: TEXT fields and records count

Re: TEXT fields and records count

Re: TEXT fields and records count