Retrieving text from mysql to datagrid form

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
gnor
Posts: 13
Joined: Fri May 08, 2020 10:32 pm

Retrieving text from mysql to datagrid form

Post by gnor » Sat May 09, 2020 12:23 pm

Hello everyone...god to be here!

I'm a brand new member, but reading this forum for over many years now. Using livecode opened a new world to me: it has brought an easy programming language and a lot to learn about creating cross-platform apps - many pleasures on the way learning. Thanks for that.

I've run into a situation with datagrid form, as follows:
- Using mysql DB
- user enters text into input field: multiline text, using cr, tab, enter backspace etc.

"let say something like this...
multiline using
this is a sample"

- save it to mysql /in the DB is text or varchar/ column names are "KERD" and "SORR"
- on the card that i have the datagrid form, there is the script in opencard to fill the form:
--------------------------------------
put revDataFromQuery(NUMTOCHAR(1),NUMTOCHAR(2), czConnID, tSQL) into tData
------------ trying to clear tData
replace CR with "" in tData
replace CRLF with "" in tData
replace tab with "" in tData
-----------replace tData to normal format tried this also: SPLIT tData BY numtochar(1) AND numtochar(2)
replace NUMTOCHAR(1) with tab in tData
replace NUMTOCHAR(2) with cr in tData
----------finally put it to an array for the Behaivor Script of the Row Template
put 0 into tLineCounter
set itemdel to TAB --- tried everything NUMTOCHAR(1),NUMTOCHAR(2) ----ONLY TAB OR CR ???
repeat for each line tLine in tData
add 1 to tLineCounter
put item 1 of tLine into pDataArray[tLineCounter]["KERD"]
put item 2 of tLine into pDataArray[tLineCounter]["SORR"]
end repeat

Set the dgData of group "DGK" OF CARD "FILL" to pDataArray
---------------------------- end --------------------------

- in the Behaivor Script of the Row Template have the following in FillInData:

set the text of field "KERDES" of me to pDataArray["KERD"]
set the text of field "SORREND" of me to pDataArray["SORR"]

- after all playing around with the lines of tData - before set it to pDataArray -, 1 have never received the originaly entered text into the field i have created in row template. Sometimes all of the lines of the original text went into a new row /probably cr,crlf etc/, another time all of the text which supposed to go to different rows, went into one single row, an such other nightamares occured.

What is i am doing wrong? Am i using the right approach to convert tData, am i doing it on the right place or should it be done in row template script etc. So many things i'm unsure. Please direct me to the right direction...

Klaus
Posts: 12204
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Retrieving text from mysql to datagrid form

Post by Klaus » Sat May 09, 2020 12:55 pm

Hi gnor,

welcome to the forum!

Setting the FIELD and RECORD delimiter to something different than the default TAB and CR
is the only way to deal with multiline text.

So you start great but then spoil it with the next lines! :D
The trick is to use the use the same delimiters in the rest of the script as in the query, too!

Do like this:

Code: Select all

...
put revDataFromQuery(NUMTOCHAR(1),NUMTOCHAR(2), czConnID, tSQL) into tData
## trying to clear tData

## Internally LC always uses CR on every platform!
## CRLF might add an empty line on some platforms like the Mac.
replace CRLF with CR in tData

put 0 into tLineCounter

## This does the trick!
## We set the ITEM delimiter just like in the query:
set itemdelimiter to NUMTOCHAR(1)
## Yes, we can also set the LINE delimiter:
set LINEDELIMITER to NUMTOCHAR(2)

## Now we can just:
repeat for each line tLine in tData
   add 1 to tLineCounter
   put item 1 of tLine into pDataArray[tLineCounter]["KERD"] 
   put item 2 of tLine into pDataArray[tLineCounter]["SORR"]
end repeat 
set the dgData of group "DGK" OF CARD "FILL" to pDataArray
...
Best

Klaus

gnor
Posts: 13
Joined: Fri May 08, 2020 10:32 pm

Re: Retrieving text from mysql to datagrid form

Post by gnor » Sat May 09, 2020 1:52 pm

Hello Klaus!

I knew need someone to show me the lowest fruit on the tree...
Havent excpected to get attention so soon, thank you for your help.

Klaus
Posts: 12204
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Retrieving text from mysql to datagrid form

Post by Klaus » Sat May 09, 2020 2:00 pm

You are welcome!

Post Reply

Return to “Databases”