Problem with MySQL view returning data

[Jules]

I have finally got some time to allow me to getting round to testing Livecode. But I seem to have a problem. I have a simple view linking a number of tables.
I am using the following to get the data:

put vehicleBookingsLoadAll() into tRecord
set the dgText of group "dgVehicleBookings" to tRecord

and here is the function

function vehicleBookingsLoadAll
local rtnRecords, lclQuery

put "SELECT * FROM vehicleBookings " into lclQuery
put revDataFromQuery(tab,cr,gConnID,lclQuery) into rtnRecords
return rtnRecords

end vehicleBookingsLoadAll

this all works fine except its not returning all the data. I think its because when I look at rtnRecords

43 W1** *** TX1 Available 0
45 L*** Y*U TXII Available 0
47 A*** **P TXII Available 12 Marks Boddington 58977 34534 3453 33234545 2
49 **** T** Fairway Available 0
50 L*** U** TX4 Available 0
52 J*** F** Fairway Available 0
53 W*** U** TXI Available 0
54 L*** U** TXII Available 0
58 L*** L** TX4 Available 0
59 Y*** T** TXII Available 0
61 G*** T** Fairway Available 0
62 J*** F** Fairway Available 0
63 W5** *** TX1 Available 0
65 **02 *** TXII Available 0
70 L**5 *** TXII Available 0
44 **76 *** TX1 Workshop 0
46 **34 *** Fairway Workshop 14 Sidney Berkenslov 456546 456456

(** Blanked out some data)
it seems that the tabs are causing a problem. The data was created with different applications (I write this application over and over again in different programming languages when I'm trying or testing things out). There should be 30 rows returned. I tested the sql copied it from the value in lclQuery into MYSQL Admin and it returned 30 rows as it should.

I tested this in Realbasic, PHP,C# and Lazarus and all returned the data correctly but Livecode seems to have a problem. I am probably missing something obvious. I can't see it being a problem if Live code is the only application entering the data but I do see a problem if it has to play with other applications since its not reading the data correctly.

[bangkok]

1-don't put the result in a datagrid.Use a regular text field, just to check

2-if rows are missing then it's not a problem with tab but rather CR,right ?
So there is a turnaround. Change the delimiter characters in revDataFromQuery.

Code: Select all

put revDataFromQuery("[","|",gConnID,lclQuery) into rtnRecords

then make :

Code: Select all

replace CR with "" in rtnRecords  --to delete any extra CR that could pollute the result
replace tab with "" in rtnRecords  --to delete any extra TAB
replace "|" with CR in rtnRecords
replace "[" with tab in rtnRecords

[/code]

[Jules]

I tried what you suggested but it did not work. You see the problem is rtnRecord I use the debugger to show me what is being returned and Live code is not returning the data. At a guess I think it is a problem with identifying unique rows in a view. The first column is unique, however to Live code it does not see a primary key of unique key on the view so I think it bombs out. I can only think to get round this is to write multiple selects and sort the view out on the client but this increases the number of round trips. I'm not sure how to get round this. Most tools either return a recordset as defined by the query or you can define the unique row identifier. But this is only a issue if the view need to be updated. I am new to Live code so I think there is a solution out there if anyone knows one be glad to hear from you.

[Jules]

Ok got a solution that worked got this here 7350-Converting-a-Database-Cursor-To-a-Data-Grid-Array

here is the code I used:

put vehicleBookingsLoadAll() into tRecord
set the dgData of group "dgVehicleBookings" to tRecord


function vehicleBookingsLoadAll
local theDataA,theCursor

put revQueryDatabase(gConnID,"SELECT * FROM vehicleBookings") into theCursor

ConvertSQLCursorToArray theCursor, theDataA

return theDataA

end vehicleBookingsLoadAll

Got this from the above link which I can reuse
command ConvertSQLCursorToArray pCursor, @pOutArrayA
local i,j
local theFields
local theError

put revDatabaseColumnNames(pCursor) into theFields
if theFields begins with "revdberr," then
put item 2 to -1 of theFields into theError
end if

if theError is empty then
put 0 into i
repeat until revQueryIsAtEnd(pCursor)
add 1 to i

repeat for each item theField in theFields
put revDatabaseColumnNamed(pCursor, theField) into pOutArrayA[ theField ]
end repeat

revMoveToNextRecord pCursor
end repeat
end if


This now allows me to write a way to automate all the above by reading the schema. I can or should (with a little trial and error) be able to automatically create most my database access/updates and store in a single stack similar to ORM tools.

[bangkok]

The first column is unique, however to Live code it does not see a primary key of unique key on the view so I think it bombs out.
.

From my understanding, LiveCode "sees" nothing... In your case it just transmits a SQL query through a MySQL driver to the MySQL engine. My point : if there is no issue with your SQL query, then it should worlk.

Let's go back to your first example.

put "SELECT * FROM vehicleBookings " into lclQuery

Could you expand this query, using the names of columns.

put "SELECT col1,col2,col3 FROM vehicleBookings " into lclQuery

Start with 1, then 2, then all of them, instead for "SELECT *".

Other idea : narrow the query to one record adding a WHERE statement, a record that "looks" incomplete when returned by a "SELECT *"

Last : what are the datatype of the columns in your db vehicleBookings ?

I think you should continue to digg, because what you're experiencing could be a major issue : "SELECT *" not returning all datas, leaving us with the only solution to name all the columns in SELECT queries and/or using cursors (revQueryDatabase).

Your are on Mac ? Windows ? Which version of LiveCode ? Which version of MySQL server ?

I continue to think that the data could be the cause of your problem, with special characters hidden inside.

You should call the revQueryDatabase with special delimiters (my first pos) and replace with numtochar all those :
09 HT(Horizontal Tab)
10 LF(Line feed)
11 VT(Vertical Tab)
12 FF(Form feed)
13 CR(Carriage return)

[Jules]

Sorry I have not got back to you because I am still testing Live code. I ran the test on a different view without a key and it still bombs. This is not just a issue in Live code its also a problem in the Entity Framework of visual studio. Normally I just go into the code and define the unique identifier. In oracle you can define a unique identifier/PK when you create the view so its not such a issue there.

Just for information purposes its on a outer join view where I want all the rows in one table linked to possible entries in another. I'm using MySQL at the moment but I have not tested with PostgreSQL. Yet! I'm still working out the problem with the combo in a revlet.