SQL unable to get query results, HELP!

[ShaneK]

I am writing an application for my brothers company. Here is what I am trying to accomplish:

Access an SQL database hosted on a remote server. Lookup a unique code and return several fields of data back to the user. The code is for a mail piece sent to prospective customers and the data returned verifies who the mailing went to.

I have a single card in my application, that card displays the fields of data and allows the input of the mail piece code and a search button. I have made what is reported as a valid connection to the database and received a connection ID. Then I execute the query and finally look to put the data into the respective fields to be displayed.

I am looking for the database experts to help look at my code to see what I am doing wrong here. I really appreciate your feedback. I have put some XXXXXX in database name, username and password in the code below to mask that information. There are also some parts inserted to verify connection made, record found, etc. I plan to remove these from final code as this should be invisible to the users.

I get the message that the database connection is good. I get the message that the record was found. However I am not getting the data to display...

Here is the code:

global gMCode, gConnectionID, sRecordSetID

on mouseUp
put ID_Field into gMCode
OpenDB
end mouseUp

on OpenDB
-- ## Connect to Database
-- use a global variable to hold the connection ID so other scripts can use it
global gConnectionID

-- set up the connection parameters - edit these to suit your database
put "xxxxxxxxxx.hostedresource" into tDatabaseAddress
put "xxxxxxxxxx" into tDatabaseName
put "xxxxxxxxxx" into tDatabaseUser
put "xxxxxxxxxx" into tDatabasePassword

-- connect to the database
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult

-- check if it worked and display an error message if it didn't
-- & set the connection ID global

if tResult is a number then
put tResult into gConnectionID
answer info "Connected to the database." & cr & "Connection ID = " & gConnectionID
SearchDB
else
put empty into gConnectionID
answer error "Unable to connect to the database:" & cr & tResult
end if
end OpenDB

On SearchDB
put revQueryDatabase(gConnectionID, "SELECT * from BFFMail where 'MailCode' like ':1'", "gMCode") into sRecordSetID
if sRecordSetID is a number then
answer info "record found"
DisplayRecord
else
answer info "No Record Found: " & cr & sRecordSetID
end if
end SearchDB

On DisplayRecord
put RevDatabaseColumnNamed (sRecordSetID, "LastName") into field "LName_Field"
put RevDatabaseColumnNamed (sRecordSetID, "FirstName") into field "FName_Field"
put RevDatabaseColumnNamed (sRecordSetID, "City") into field "City_Field"
put RevDatabaseColumnNamed (sRecordSetID, "State") into field "State_Field"
end DisplayRecord

[bangkok]

First : Do you really need to use a cursor ?

If not, it's much easier to use : revDataFromQuery (instead of revQueryDatabase)

Second point: before to use variable in your query, you should try a regular query. Easier to debug.

Code: Select all

put revQueryDatabase(gConnectionID, "SELECT * from BFFMail where MailCode like '9501'") into sRecordSetID

Third point : case sensitive ? are you sure with "BFFMail" and "MailCode" ?

Fourth point : don't you get any error message in one of your fields (starting with "revdberr" ?)

Fifth point : maybe... it's just your query that returns (really, without any glitch)... 0 record ! Try another one to be sure

[ShaneK]

Bangkok:

Thank you for your help. I got it working with the original code and a slight modification to the query (and your help from your second point):

Here is what I changed (including the fixed query per your point 2):
-- Original: put revQueryDatabase(gConnectionID, "SELECT * from BFFMail where 'MailCode' like ':1'", "gMCode") into sRecordSetID
-- Changed: put revQueryDatabase(gConnectionID, "SELECT * from BFFMail where MailCode = 'BFFtest1'") into sRecordSetID

It was the single quote around the database field name that prevented the query from working. So I will have to be more cautious with that in the future (Not sure if I mentioned that this is the first LiveCode app I am writing, I have programmed in RealBasic, Pascal and a little C & C++)

Let me address your questions to me in case others may find benefit in the responses:

First : Do you really need to use a cursor ?

No, I was just trying to do this without writing a parsing routine (and get my feet wet more with SQL integration...)

If not, it's much easier to use : revDataFromQuery (instead of revQueryDatabase)

I have also a working revDataFromQuery call but I would have to write a parsing routing in order to display the data the way we want in the various fields displayed on the card.

Second point: before to use variable in your query, you should try a regular query. Easier to debug.

Yes, VERY good point. I put in a fixed query and was able to track down the error when doing so.

Code: Select all
put revQueryDatabase(gConnectionID, "SELECT * from BFFMail where MailCode like '9501'") into sRecordSetID

Third point : case sensitive ? are you sure with "BFFMail" and "MailCode" ?

Yes, I have always used this type of variable structure and am very careful about using the proper case.

Fourth point : don't you get any error message in one of your fields (starting with "revdberr" ?)

I had one with a revDataFromQuery but I fixed that. I did not get any errors reported with the code that I posted. And now that I know where the error was I am now wondering why was there no error? What was going on behind the scenes there? What was I passing?

Fifth point : maybe... it's just your query that returns (really, without any glitch)... 0 record ! Try another one to be sure

I only have 2 records in the database right now (it is a new database) and I had tried locating both records without success. Now with the change in the query I am able to locate both records and the data populates the fields on the card just as it should.

So as a final step I will add a RevCloseCursor command to clean up and a revCloseDatabase command as well.

Again, I really appreciated the help and it did get me closer to a finished product so I am very grateful!!!

[phaworth]

I had one with a revDataFromQuery but I fixed that. I did not get any errors reported with the code that I posted. And now that I know where the error was I am now wondering why was there no error? What was going on behind the scenes there? What was I passing?

The query you passed in was valid terms of syntax. The single quotes around 'Mailcode' and ':1' made sql think they were literal strings not a column name and a placeholder respectively.

Pete