LiveCode Forums.

Posted: **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

Posted: **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

Posted: **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

Posted: **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

Posted: **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?

Posted: **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

Posted: **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?

Posted: **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

Posted: **Tue Feb 05, 2013 2:49 pm**

Aaaah genius!! thank you so much Klaus for your help...

LiveCode Forums.

MySQL - Datagrid

MySQL - Datagrid

Re: MySQL - Datagrid

Re: MySQL - Datagrid

Re: MySQL - Datagrid

Re: MySQL - Datagrid

Re: MySQL - Datagrid

Re: MySQL - Datagrid

Re: MySQL - Datagrid

Re: MySQL - Datagrid