LiveCode Forums

Posted: **Wed Dec 23, 2009 9:28 am**

Hello all...

I've got a rev app I'm working on that displays db data in a table-style datagrid. DB text fields that are intended to contain potentially long prose/notes/etc, which would include linefeeds in the content, are giving me trouble.

In Rev, I can write to the DB just fine, even when my text-to-save contains linefeeds. However, when I try to read the data out and put it into a datagrid, the linefeeds inside the text field are goofing things up. My dg-populating script is interpreting the linefeeds in the text field as a new record, which is not entirely unexpected.

I have tried to use a substitution method, wherein the linefeeds are not stored as such in actuality, but a replacement character set that would almost never come up on its own is used. However, when I try to convert that character set back into linefeeds inside the field's Custom Behavior, I get some really wild results, culminating in a "bad parameter" error in the FillInData behavior.

So, what would be the best way to store & display long db text fields with linefeed-containing data inside a datagrid?

For reference, here are my relevant script bits:

the script which populates the dgData from the DB:

Code: Select all

         if tResultID ="" then
            repeat for each item thisField in myFields
               put "" into notesArray[tNoteCount][thisField]
            end repeat 
         else
            put revDataFromQuery(,,gConID, tSQL) into tResultData
            set the itemdelimiter to tab
            put 0 into n
            repeat for each line thisLine in tResultData
               add 1 to n
               put 0 into i
               repeat for each item thisField in myFields
                  add 1 to i
                  put item i of thisLine into notesArray[n][thisField]
               end repeat
            end repeat
         end if
         set the dgData of group "notes_dglist" to notesArray

the text field's Custom Behavior:

Code: Select all

on FillInData pData
    set the text of field 1 of me to replaceText(pData, "|*|", cr)
end FillInData

Assume that when I write to the DB, I'm doing a replaceText in the other direction, which I know to be working.

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

Hi,

I think the problem you are having is due to the revDataFromQuery function, rather than the Data Grid. By default the revDataFromQuery function will return one record per line if i remember correctly.

Because your records span multiple lines, it will be hard to loop through each one. I would do it this way instead (not tested, I just typed this straight out) :

Code: Select all

local tCursor
put revQueryDatabase(gConID, tSQL) into tCursor
if tCursor is not a number then
  answer "Database error: " & tCursor
end if

local tArray
put createOrderedArrayFromCursor(tCursor) into tArray
revCloseCursor tCursor

set the dgData of group "notes_dglist" to tArray

You'll notice that i've used a function called createOrderedArrayFromCursor. This is an operation that comes up very frequently for me, so I abstract this into a function
to prevent doing it again and again. The implementation for the function is here:

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

Hope this is some help, you may need to tweak it to get it working properly for your application

Regards
Oliver

Posted: **Wed Dec 23, 2009 5:26 pm**

Thanks Oliver!

I kept working at it before your reply came in and figured out that the idea on using a short substitution character block was sound, the problem was the particular characters I chose. Apparently "|*|" means something special and was throwing things off. When I made the substitution something like "QQQ", everything worked perfectly. Given the "bad parameter" error I was getting I knew one of the 3 params sent to replaceText was something it didn't like, and the other 2 were things that couldn't change, so that left only one possibility. So then, what's the special meaning of "|*|"? And here I thought it unlikely to be used for anything, silly me.

Of course, the problem is that however unlikely it may be, any substitution character I choose could eventually come up in actual use by somebody, so if I don't have to use it, then why should I, eh? So, I'll definitely be giving your method a shot. Seems easy enough to follow now that I see it written out, should be able to fix any small goofs in it, if there are any (which I doubt, it looks right). Looks a lot like what would be going on under the hood in PHP's mysql_fetch_assoc() function, which I use all the time.

Many thanks again, will give it a shot later on and see how it goes...

Posted: **Wed Dec 23, 2009 6:43 pm**

Works perfectly, as entered! Future readers of this post can directly use your code and have no issues.

Mega Kudos to Oliver K!

LiveCode Forums

datagrid with scrolling text field, linefeeds

datagrid with scrolling text field, linefeeds

Re: datagrid with scrolling text field, linefeeds

Re: datagrid with scrolling text field, linefeeds

Re: datagrid with scrolling text field, linefeeds