SQL Lite query does not give me expected results

Anything beyond the basics in using the LiveCode language. Share your handlers, functions and magic here.

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
mrcoollion
Posts: 396
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

SQL Lite query does not give me expected results

Post by mrcoollion » Wed Apr 20, 2016 8:47 pm

Hope someone sees what I am doing wrong.
I have an SQL query that selects multiple records from a database based upon customerID and PC name.
Here is the database info.
See image DBRecs.jpg in attachment

Here is the query info.
See image TheQuery.jpg

The query selected 12 record but it should be 7.
See image NbrRecsSelected.jpg

When I do a put tRec in the MsgBox and then copy paste into notepad I get 12 records of which 5 are incomplete?
See image WhatsInPut_tRecInMsgBox.jpg

I cant seem to find what I am doing wrong ? I have another update statement (follows this one) which does give correct results (7 touched records).
The query > See image UpdateQueryWorks.jpg

The result in tRes (7) > See image UpdateResultWorks.jpg

Maybe someone sees what I am doing wrong ?

Decided to add the routine with some explanation for you to test
KeyFields > put in here the fieldnames as in the database you wish to select the records on
e.g. "CustomerID,ProductID"
DataArrayKeyFields > put in here the data of the fields you wish to select the records on
e.g. put "C12345" into DataArrayKeyFields["" CustomerID"]
put "Acer54321" into DataArrayKeyFields["" ProductID"]
DatabaseID and TableName should be self explanatory
The output is:
ExistsYesNo > Yes is record exists, No is does not exist, Error is problem
theMessage > Holds a message if something went wrong
theFieldNames > Holds all fieldnames of the table in the database
RecordDataArray > Holds the selected records

Code: Select all

#######################################################################################
// Check how many records exists and get them
#######################################################################################
command CheckHowmanyRecordsExistsV1 DatabaseID, TableName, KeyFields, DataArrayKeyFields, @ExistsYesNo @theMessage @theFieldNames @RecordDataArray 
   // Use like this : CheckHowmanyRecordsExistsV1 DatabaseID, TableName, KeyFields, DataArrayKeyFields, ExistsYesNo, theMessage, theFieldNames, RecordDataArray
   // Nbr of records and field info is in:  RecordDataArray["nbrrecords"], RecordDataArray["nbrfields"], RecordDataArray["fieldslist"]
   // The data is in :  RecordDataArray [reccounter][theArrayFieldName]
   if  DatabaseID is empty then 
      put "CheckHowmanyRecordsExistsV1 routine error!: I need at least to have a database id number of the open database for it to work!" into theMessage
      Put "Error" into ExistsYesNo
   end if
   
   if  TableName is empty  then 
      put "CheckHowmanyRecordsExistsV1 routine error!: You need to enter a table name to get the records from for it to work!" into theMessage
      Put "Error" into ExistsYesNo
   end if
   
   if  DataArrayKeyFields is empty  then 
      put "CheckHowmanyRecordsExistsV1 routine error!: You need to enter of the key search field(s) data to determine what records to search for or get!" into theMessage
      Put "Error" into ExistsYesNo
   end if
   
   if  KeyFields is empty  then 
      put "CheckHowmanyRecordsExistsV1 routine error!: You need to enter at least one key search fieldname from the table as it is defined in the database for it to work!" into theMessage
      Put "Error" into ExistsYesNo
   end if  
   
   if ExistsYesNo is "Error" 
   then
      exit CheckHowmanyRecordsExistsV1
   end if
   
   // Build the sql string to get the records
   set itemdelimiter to ","
   put the number of items of KeyFields into nbrofKeyFields
   put 1 into counter
   repeat for nbrofKeyFields times
      put item counter of KeyFields into tFieldname
      if counter is 1 then Put tFieldname &"='" &  DataArrayKeyFields[tFieldname] & "'" into AndString
      if counter > 1 then put AndString &" AND " & tFieldname &"='" & DataArrayKeyFields[tFieldname]  & "'" into AndString
      add 1 to counter
   end repeat
   put "SELECT * FROM "& TableName&" WHERE " & AndString into tSQL
   put revDataFromQuery(,,DatabaseID,tSQL) into tRec
   // put revDataFromQuery(tab,return,DatabaseID,tSQL) into tRec 
   if tRec begins with "revdberr"
   then
      put "There was a problem accessing the "&TableName&" Table: " & tRec  into theMessage
      Put "Error" into ExistsYesNo
      exit CheckHowmanyRecordsExistsV1
   else
      put "Yes"into ExistsYesNo 
      put revDatabaseColumnNames(DatabaseID, TableName) into theFieldNames
   end if
   if ExistsYesNo is not "Error" and tRec is empty then put "No" into ExistsYesNo
   if ExistsYesNo is not "Error" and tRec is not empty then put "Yes" into ExistsYesNo
   if ExistsYesNo is "Yes"  then   
      put tRec into AllRecordData 
      //Build the dataArray
      put the number of lines of AllRecordData into NbrRecordsInScope  // Find out howmany record we are talking about.
      set the itemdelimiter to ","
      put the number of items of theFieldNames into NbrOfFields
      put NbrOfFields into RecordDataArray["nbrfields"]
      put theFieldNames into RecordDataArray["fieldslist"]
      put NbrRecordsInScope into RecordDataArray["nbrrecords"]
      put 1 into reccounter
      repeat for NbrRecordsInScope times
         put line reccounter of AllRecordData into RecordData
         put 1 into fieldcounter
         repeat for NbrOfFields times
            set the itemdelimiter to tab
            put item fieldcounter of RecordData into theData
            set the itemdelimiter to ","
            put item fieldcounter of theFieldNames into theArrayFieldName
            put theData into RecordDataArray [reccounter][theArrayFieldName]
            add 1 to fieldcounter
         end repeat   
         add 1 to reccounter
      end repeat
      // End build dataArray
   else
      put empty into RecordData
      put empty into RecordDataArray
   end if
end CheckHowmanyRecordsExistsV1
// End check how many records exists
Attachments
PrintScreens.zip
The Printscreens
(173.33 KiB) Downloaded 29 times

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: SQL Lite query does not give me expected results

Post by phaworth » Thu Apr 21, 2016 8:10 pm

I'm guessing that you have nulls in some of your rows, judging by the message box display. If that's a permissible data condition, try adding a check for not null to the where statement.

mrcoollion
Posts: 396
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: SQL Lite query does not give me expected results

Post by mrcoollion » Thu Apr 21, 2016 9:18 pm

Thanks phaworth for the tip :D

I decided to delete all records and as of that time all goes well.
There probably were some NULL or strange character in the data that got there during early testing.

Regards,

Paul

Post Reply

Return to “Talking LiveCode”