Anything beyond the basics in using the LiveCode language. Share your handlers, functions and magic here.
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
bsouthuk
- Posts: 261
- Joined: Fri Dec 05, 2008 7:25 pm
Post
by bsouthuk » Mon Feb 04, 2013 7:10 pm
Hi
I am calling data from my mySQL database and putting the data into my datagrid. Quite straight forward, but I hit a few issues when I call data from 2 separate tables in my database. I have the following code:
Code: Select all
on mouseUp
## Connect to the database
put "mysql" into theDBType
put "www.xxxx.co.uk" into theDBHost
put "xxxx" into theDBName
put "xxx" into theDBUser
put "xxx" into theDBPassword
put revOpenDatabase( theDBType, theDBHost, theDBName, theDBUser, theDBPassword ) into theConnectionID
if theConnectionID is an integer then
## Query the database for data
put revQueryDatabase( theConnectionID, "select * from QG where Dealer LIKE '" & (fld "Dealer") & "%'") into theCursor
put revQueryDatabase( theConnectionID, "select * from QGNew where DealerID LIKE '" & (fld "Dealer") & "%'") into theCursor2
//answer the result
if theCursor is an integer then
ConvertSQLCursorToArray theCursor, theDataGridArray
ConvertSQLCursorToArray theCursor2, theDataGridArray2
put the result into theError
if theError is empty then
## The cursor was successfully converted to an array.
## Assign it to the data grid. The 'firstname' and 'lastname' columns
## from the database cursor will appear in the matching columns
## in the data grid.
set the dgData of group "Glance" to theDataGridArray
end if
## Close the database cursor
revCloseCursor theCursor
end if
## Close the database connection
revCloseDatabase theConnectionID
else
answer "Error connecting to the database:" && theConnectionID & "."
end if
end mouseUp
command ConvertSQLCursorToArray pCursor, @pOutArrayA
local i,j
local theFields
local theError
## Get the names of all the columns in the database cursor
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
## Loop through all rows in cursor
repeat until revQueryIsAtEnd(pCursor)
add 1 to i
## Move all fields in row into next dimension of the array
repeat for each item theField in theFields
put revDatabaseColumnNamed(pCursor, theField) into pOutArrayA[i][ theField ]
end repeat
revMoveToNextRecord pCursor
end repeat
end if
return theError
end ConvertSQLCursorToArray
Problem is, some of the data overwrites from my 2 queries and not quite sure why. Can anyone spot the error in my code?
Thanks
Daniel
-
Klaus
- Posts: 14213
- Joined: Sat Apr 08, 2006 8:41 am
-
Contact:
Post
by Klaus » Mon Feb 04, 2013 10:14 pm
Hi Daniel,
bsouthuk wrote:...
Problem is, some of the data overwrites from my 2 queries and not quite sure why. Can anyone spot the error in my code?
Code looks OK, but what do you mean with "some of the data overwrites from my 2 queries"?
In any way, you are only closing ONE cursor and do not use the second one, at least not in this script.
Maybe you should only work with one cursor at a time?
Code: Select all
...
put revOpenDatabase( theDBType, theDBHost, theDBName, theDBUser, theDBPassword ) into theConnectionID
## Avoid uneccessary long IF... THEN... clauses :-)
if theConnectionID is NOT an integer then
answer "Error connecting to the database:" && theConnectionID & "."
exit mouseup
end if
# Handle first cursor:
put revQueryDatabase( theConnectionID, "select * from QG where Dealer LIKE '" & (fld "Dealer") & "%'") into theCursor
if theCursor is an integer then
ConvertSQLCursorToArray theCursor, theDataGridArray
put the result into theError
if theError is empty then
set the dgData of group "Glance" to theDataGridArray
revCloseCursor theCursor
end if
end if
## Now the same for the second cursor:
put revQueryDatabase( theConnectionID, "select * from QGNew where DealerID LIKE '" & (fld "Dealer") & "%'") into theCursor2
##...
Maybe this will work for you.
Best
Klaus
-
bsouthuk
- Posts: 261
- Joined: Fri Dec 05, 2008 7:25 pm
Post
by bsouthuk » Tue Feb 05, 2013 11:23 am
Thats great thanks Klaus.
The problem I am having though is that the the first cursor displays correctly into the data grid but the the 2nd cursor overwrites the data. What change do I need to make to the code so that both cursor data displays in my datagrid?
Many thanks
Daniel
-
Klaus
- Posts: 14213
- Joined: Sat Apr 08, 2006 8:41 am
-
Contact:
Post
by Klaus » Tue Feb 05, 2013 12:56 pm
Hi Danaiel,
AHA! Well that was not obvious from your script.
OK, of course setting "the dgdata of grp XYZ" will always overwrite the previous content!
I really hope this did NOT surprise you too much
So you need to convert BOTH of your cursors to ONE array and then set the dgdata to that array.
Maybe you can expand your "ConvertSQLCursorToArray" command so it will also APPEND data to an existing array if nexxessary.
Something like this (out of my head, UNTESTED!)
Code: Select all
## New Parameter DOAppend whch can be TRUE or false/empty
## if TRUE then teh script will APPEND new data to the existing array
command ConvertSQLCursorToArray pCursor, @pOutArrayA,DoAppend
local i,j
local theFields
local theError
## Get the names of all the columns in the database cursor
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
## Check here:
## Append:
if DOAppend = TRUE then
put the num of keys of pOutArrayA into i
else
## Start new:
put 0 into i
end if
## Loop through all rows in cursor
repeat until revQueryIsAtEnd(pCursor)
add 1 to i
## Move all fields in row into next dimension of the array
repeat for each item theField in theFields
put revDatabaseColumnNamed(pCursor, theField) into pOutArrayA[i][ theField ]
end repeat
revMoveToNextRecord pCursor
end repeat
end if
return theError
end ConvertSQLCursorToArray
Then use it like this:
Code: Select all
...
if theCursor is an integer then
ConvertSQLCursorToArray theCursor, theDataGridArray
## Use the SAME array and append data:
ConvertSQLCursorToArray theCursor2, theDataGridArray,TRUE
...
You get the picture
Best
Klaus
-
bsouthuk
- Posts: 261
- Joined: Fri Dec 05, 2008 7:25 pm
Post
by bsouthuk » Tue Feb 05, 2013 1:45 pm
Thanks Klaus - do you mean the following code - just a bit confused sorry:
Code: Select all
put revQueryDatabase( theConnectionID, "select * from QG where Dealer LIKE '" & (fld "Dealer") & "%'") into theCursor
put revQueryDatabase( theConnectionID, "select * from QGNew where DealerID LIKE '" & (fld "Dealer") & "%'") into theCursor2
if theCursor is an integer then
ConvertSQLCursorToArray theCursor, theDataGridArray
ConvertSQLCursorToArray theCursor2, theDataGridArray, TRUE
put the result into theError
if theError is empty then
set the dgData of group "Glance" to theDataGridArray
revCloseCursor theCursor
end if
I am getting the following error
CANT FIND OBJECT (put the num of keys of pOutArrayA into i)
Any ideas on this one?
-
Klaus
- Posts: 14213
- Joined: Sat Apr 08, 2006 8:41 am
-
Contact:
Post
by Klaus » Tue Feb 05, 2013 2:08 pm
Hi Daniel,
not sure, what's happening, try to pass the keys of the array directly to the handler:
Code: Select all
...
put revQueryDatabase( theConnectionID, "select * from QG where Dealer LIKE '" & (fld "Dealer") & "%'") into theCursor
put revQueryDatabase( theConnectionID, "select * from QGNew where DealerID LIKE '" & (fld "Dealer") & "%'") into theCursor2
if theCursor is an integer then
ConvertSQLCursorToArray theCursor, theDataGridArray
put the keys of theDataGridArray into tKeys
put the num of lines of tKeys into tNumberOfKeys
## Add a parameter to the command:
ConvertSQLCursorToArray theCursor2, theDataGridArray, TRUE,tNumberOfKeys
...
Code: Select all
command ConvertSQLCursorToArray pCursor, @pOutArrayA,DoAppend,tNumberOfKeys
local i,j
local theFields
local theError
## Get the names of all the columns in the database cursor
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
## Check here:
## Append:
if DOAppend = TRUE then
put tNumberOfKeys into i
else
## Start new:
put 0 into i
end if
...
Best
Klaus
-
bsouthuk
- Posts: 261
- Joined: Fri Dec 05, 2008 7:25 pm
Post
by bsouthuk » Tue Feb 05, 2013 2:32 pm
Hi Klaus
So now I have:
Code: Select all
# Handle first cursor:
put revQueryDatabase( theConnectionID, "select * from QG where Dealer LIKE '" & (fld "Dealer") & "%'") into theCursor
put revQueryDatabase( theConnectionID, "select * from QGNew where DealerID LIKE '" & (fld "Dealer") & "%'") into theCursor2
if theCursor is an integer then
ConvertSQLCursorToArray theCursor, theDataGridArray
put the keys of theDataGridArray into tKeys
put the num of lines of tKeys into tNumberOfKeys
## Add a parameter to the command:
ConvertSQLCursorToArray theCursor2, theDataGridArray, TRUE, tNumberOfKeys
put the result into theError
if theError is empty then
set the dgData of group "Glance" to theDataGridArray
revCloseCursor theCursor
end if
And then:
Code: Select all
command ConvertSQLCursorToArray pCursor, @pOutArrayA,DoAppend,tNumberOfKeys
local i,j
local theFields
local theError
## Get the names of all the columns in the database cursor
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
## Check here:
## Append:
if DOAppend = TRUE then
put tNumberOfKeys into i
else
## Start new:
put 0 into i
end if
end if
## Move all fields in row into next dimension of the array
repeat for each item theField in theFields
put revDatabaseColumnNamed(pCursor, theField) into pOutArrayA[i][ theField ]
revMoveToNextRecord pCursor
end repeat
return theError
end ConvertSQLCursorToArray
Whats happening now is that the correct data is showing in the datagrid for theCursor but for theCursor2 only one of the 4 records.
What do you think?
-
Klaus
- Posts: 14213
- Joined: Sat Apr 08, 2006 8:41 am
-
Contact:
Post
by Klaus » Tue Feb 05, 2013 2:37 pm
Hi Daniel,
I think you forgot:
...
add 1 to i
...
at the right place
Check your initial script!
Best
Klaus
-
bsouthuk
- Posts: 261
- Joined: Fri Dec 05, 2008 7:25 pm
Post
by bsouthuk » Tue Feb 05, 2013 2:49 pm
Aaaah genius!! thank you so much Klaus for your help...