LiveCode Forums.

I have a very simple MySQL database with a single table. To present the records I use the data Grid component. On the MySQL workbench they are perfectly presented.

In the Data Grid I have a weird presentation of some of the records, a situation that can be seen in the below and can be summarised as:

1. a square line is showing on the stack. It is totally unrelated to the grid and I cannot imagina how to take it out!
2. the first records look ok, but then , suddenly, the columns do not keep the column reference of the original dataset
3. the sort order is, at least one time, not operating well and "Suleiman" comes before "Sábado".



can anyone help?

joao

jlspereira wrote: 1. a square line is showing on the stack. It is totally unrelated to the grid and I cannot imagina how to take it out!

Menu "View" / disable "Show invisible objects"

jlspereira wrote: 2. the first records look ok, but then , suddenly, the columns do not keep the column reference of the original dataset

You probably have some CR or LF or Tab character hidden within "Nome" in your MySQL data.

To be sure : don't use default values for line and row delimiters (cr and tab). But instead do :

jlspereira wrote: 3. the sort order is, at least one time, not operating well and "Suleiman" comes before "Sábado".

I told you before : make your SORT in your SQL query, instead of sorting in the datagrid afterward.

Hi -

about the sort order you are absolutely right and I'll program that a bit later on (on the SQL), so I am totally forgetting about the issue. Please excuse me for insisting on it; the rationale was only that the data grid already contains the specific functionality and it looked a very convenient way of implementing...

On the other situation... I am scratching my head.

I have two buttons: GetData and GridBH.

GridBH contains the new behaviour of the grid as recommended on a post:


--> all handlers

on FillInData pData
-- This message is sent when the Data Grid needs to populate
-- this template with the column data. pData is the value to be displayed.

-- set the text of the long ID of me to pData ## temp workaround for
   set the unicodetext of the long ID of me to uniencode(pData, "utf8")
end FillInData


on LayoutControl pControlRect
-- A default column is just a field. Nothing to change here.
end LayoutControl


on ResetData
-- Sent when column data is being emptied because the control is no longer being used to display data
set the text me to empty
end ResetData


on PreFillInData
-- Sent right before new data is going to replace existing data in the column
end PreFillInData


setprop dgHilite pBoolean
-- This custom property is set when the highlight of your column template has
-- changed. You only add script here if you want to customize the highlight.
if pBoolean then
set the foregroundColor of me to the dgProp["hilited text color"] of the dgControl of me
else
set the foregroundColor of me to empty
end if
end dgHilite


getprop dgDataControl
-- Required by library so that it can locate the control.
return the long ID of me
end dgDataControl


-- Data grid will call this if a user action asks to edit cell content.
command EditValue
EditFieldText the long ID of me, the dgIndex of me, the dgColumn of me
end EditValue


on mouseDoubleUp pMouseBtnNum
if pMouseBtnNum is 1 then
if the dgProps["allow editing"] of the dgControl of me \
and the dgColumnIsEditable[the dgColumn of me] of the dgControl of me then
-- Edit field contents if the user double-clicks
EditCellOfIndex the dgColumn of me, the dgIndex of me
exit mouseDoubleUp
end if
end if
pass mouseDoubleUp
end mouseDoubleUp


(this is the only one I have changed and only for FillInData because of the special characters problem


GetData contains the following code, now adapted as per your recommendation:

on mouseUp
    -- check the global connection ID to make sure we have a database connection
  global gConnectionID

    if gConnectionID is not a number then
        answer error "Please connect to the database first."
        exit to top
    end if
     -- construct the SQL (this selects all the data from the specified table)
   put "Militantes" into tTableName    -- set this to the name of a table in your database?
put " Where Mil_Regiao = " & quote & "Oio" & quote & " " into tRegiao
   put "SELECT * FROM " & tTableName & tRegiao into tSQL
put tSQL & " ORDER BY Mil_Nome COLLATE utf8 "
    -- query the database
revExecuteSQL gConnectionID, "SET NAMES 'utf8'"

     -- check the result and display the data or an error message
    if item 1 of tData = "revdberr" then
        answer error "There was a problem querying the database:" & cr & tData
   else
       -- put tData into field "DadosX"

put revDataFromQuery("|","$",gConnectionID,dbSQL) into tData
replace CR with "" in tData
replace CRLF with "" in tData
replace tab with "" in tData
replace "|" with CR in tData
replace "$" with tab in tData
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
set the dgText of group "DataT" to tData
   end if
end mouseUp



now the problem persists and I am not able to identify the cause

can you still help?

Joao

No need to make 2 revDataFromQuery

   

I think we are getting really close. still... now I am getting a totally different error. The SQL syntax.

My code, now, corrected after your suggestions:

on mouseUp
    -- check the global connection ID to make sure we have a database connection
  global gConnectionID

    if gConnectionID is not a number then
        answer error "Please connect to the database first."
        exit to top
    end if

revExecuteSQL gConnectionID, "SET NAMES 'utf8'"

 -- check the result and display the data or an error message

    if item 1 of tData = "revdberr" then
        answer error "Zone 1 Query error:" & cr & tData
   else
      -- put tData into field "DadosX
      -- construct the SQL (this selects all the data from the specified table)
      put "Militantes" into tTableName    -- set this to the name of a table in your database?
    put " Where Mil_Regiao = " & quote  & "Oio" & quote & " "  into tRegiao
      put "SELECT *  FROM " & tTableName & tRegiao into tSQL
    put tSQL & " ORDER BY Mil_Nome COLLATE utf8 " into tSQL  
      put revDataFromQuery("|","$",gConnectionID,tSQL) into tData
  if item 1 of tData = "revdberr" then
answer error "Zone 2 Query error::" & cr & tData
else
      replace CR with "" in tData
      replace CRLF with "" in tData
      replace tab with "" in tData
      replace "|" with CR in tData
      replace "$" with tab in tData
      set the dgText of group "DataT" to tData
end if
end if
end mouseUp


the error:



can you tell me what is wrong now?

bes regards

joao

replace
by
["utf8" only for COLLATE doesn't exist]
[you have to check the "collation" of your table. is it "utf8_general_ci" or another one ?]

Collation should be the one you are mentioning: itf-8_general_ci.

Nevertheless... still getting an error on the syntax. I took 2 screenshots to show you. The syntax looks alright but it still does not work...

Kindesg regards

joao

oops... missed the second screenshot...

This is really strange.

What I found disturbing is the "?" at the begining of the error message. It's like there is weird character that MySQL rejects.

I tell you what :

in your script, after you build tSQL, type another query, without where and order, like :

sorry for this late reply. yesterdau was a full day of meetings out of office....

anyway, now the code is:

on mouseUp
    -- check the global connection ID to make sure we have a database connection
  global gConnectionID

    if gConnectionID is not a number then
        answer error "Please connect to the database first."
        exit to top
    end if

revExecuteSQL gConnectionID, "SET NAMES 'utf8'"

 -- check the result and display the data or an error message

    if item 1 of tData = "revdberr" then
        answer error "Zone 1 Query error:" & cr & tData
   else
put "select * from militantes" into tSQL
put revDataFromQuery("|","$",gConnectionID,tSQL) into tData
      -- put tData into field "DadosX
      -- construct the SQL (this selects all the data from the specified table)
      -- put "Militantes" into tTableName    -- set this to the name of a table in your database?
    -- put " Where Mil_Regiao = " & quote  & "Oio" & quote & " "  into tRegiao
      -- put "SELECT *  FROM " & tTableName & tRegiao into tSQL
    -- put tSQL & " ORDER BY Mil_Nome COLLATE utf8_general_ci" into tSQL   
      -- put revDataFromQuery("|","$",gConnectionID,tSQL) into tData
  if item 1 of tData = "revdberr" then
answer error "Zone 2 Query error::" & cr & tData
else
      replace CR with "" in tData
      replace CRLF with "" in tData
      replace tab with "" in tData
      replace "|" with CR in tData
      replace "$" with tab in tData
      set the dgText of group "DataT" to tData
end if
end if
end mouseUp


and the result is:

You have to work and investigate a little bit by yourself...

-the problem of columns misaligned has been solved
-the problem of the SQL error has been solved
-but now, all the columns... behave like rows
-ergo, you have to invert row and column delimiters

Copy :
instead of :

thanks a lot for your extraordinary help. I I got the message "You have to work and investigate a little bit by yourself...". Of course you are absolutely right and that is what I am trying to do.

On my defense pleas acknowledge that I am a 61 years' old rookie. I did start programming in 1970 at the age of 17 and did a lot of "primitive" languages such as "Assemble" "FORTRAN II", "FORTRAN IV", "PL/I", "PASCAL", etc... I even wrote my own compilers and OS cores. But those days are gone and life moved to other planets. ICT systems progressed a lot and I lost the bandwagon. A few years ago I came back for tasks such as interface design using Visual Studio (but no real programming) and never did SQL in my life.

Actually, all my problems were sorted in a very efficient manner resorting to ms/access and VBA. But I decided to move to a MAC and got really hooked on LiveCode as, with it, I can deploy my ideas and prototypes to a vast number of platforms, Web included.

So I started a real project with a small database and try to catch with all I need. When I stumbled with the Data Grid, I found it wonderful and loaded with functionality. I bought the lessons to have a quick start, but the documentation (and the training) are not that good. Actually you find some recipes such as the one I used to learn how to connect to the database and fill in the data, but I have found an understandable place really explaining which were the parameters and WHY we are using them. This is my real problem.

Once again, MANY THANKS FOR YOUR EXTRAORDINARY HELP.

Still I am scratching my head on the reason why in the beginning I was suggested to use those "cr" parameters and now, inverting (or not) orders, I am using "$" and "|". I do not know (and even cannot find a place explaining what is "tSQL" and why is there

I have tried to go to the LifeCode Academy but found no real usage for it... I do pay for the "Experts Exchange" help but they do not help on LiveCode..

So I will persist on this as I have a hunch that LiveCode is really fantastic, but please excuse me on asking so much from you.

If you can point me to books/manuals that can speed up my learning phase, I would really appreciate.

Great many thanks once again

Joao

jlspereira wrote: Still I am scratching my head on the reason why in the beginning I was suggested to use those "cr" parameters and now, inverting (or not) orders, I am using "$" and "|". I do not know (and even cannot find a place explaining what is "tSQL" and why is there

We use the trick of "$" and "|" as column and row delimiters, as a trick, in order to solve the first misaligned columns problem you had.

It's because in your data, in the database, you probably have some text with CR characters within. Those CR fools the datagrid, that will interpret the data as 2 distinct rows, instead of 1.

By using "weird" characters like "|" and "$" it allows you by a simple replace game, to get back the data with a proper format to be displayed by the datagrid :
-columns are separated by tab
-rows are separated by CR (carriage return)

tSQL is the name of the variable containing the SQL query you build in your script.

Don't be discouraged : you try to use 2 difficult beast here : datagrids (difficult to use, at first) and SQL database (a whole world by itself !).

We all faced the same problems at the beginning. I started SQL 5 years ago, it drove me crazy. And when LiveCode delivered Datagrid, I thought it was so complex that I would never user it.

And actually now, I use datagrid (and SQL) in all my projects.

So if I do understand well, the LiveCode statement:

put revDataFromQuery("|","$",gConnectionID,tSQL) into tData

is stating: run the sql statement on tSQL, putting all results in the tData variable and, by the, way, separate records with "I" and fields with "$" (or vice-versa). then clean the whole mess using the "replace" statement.

Honestly: a big THANK YOU!!!! now I am going to try to better read any available documentation on both Data Grid an SQL. The next challenge will be to develop a simple report with PDF with some totals!

and thanks again for encouraging! I must admit that I owe you a big one

joao