SQLite INNER JOIN Issue

Databases
1

My Web App is coming along pretty well. But I’ve had trouble regarding an INNER JOIN.

I’ve checked the SQLite Statement in SQLiteStudio and it returns perfectly what I’m looking for with no errors, with both Patient & Doctor data, but it doesn’t work in my Xojo Program. I’ve ensured that all column names are different (even the ID columns) as I heard that can produce issues.

GetPatientNumber Method

Dim stmt As SQLitePreparedStatement = Session.db.Prepare("SELECT Patient.ID, Patient.Forename, Patient.Surname, Patient.DateOfBirth, Patient.Address, Patient.Allergies, Patient.Notes, Patient.Type, Doctor.DocID, Doctor.DoctorName, Doctor.DoctorAddress FROM Patient INNER JOIN Doctor ON Patient.DoctorID = Doctor.DocID WHERE Patient.ID=?") 
stmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
stmt.Bind(0, patientID)

Dim rs As RecordSet = stmt.SQLSelect

Return rs

EditPatient Method

Dim rs As RecordSet
rs = GetPatientNumber(patID)

If rs <> Nil Then
  txtID.Text = patID
  txtPatientForeName.Text = rs.Field("Forename").StringValue 
  txtPatientSurname.Text = rs.Field("Surname").StringValue
  txtDOB.Text = rs.Field("DateOfBirth").StringValue
  txtAddress.Text = rs.Field("Address").StringValue
  txtAllergies.Text = rs.Field("Allergies").StringValue
  txtDoctorName.Text = rs.Field("DoctorName").StringValue
  txtDoctorAddress.Text = rs.Field("DoctorAddress").StringValue
End If

No errors are returned, but the Text Fields that I am trying to fill just end up completely blank.

The SQLite statement returns fine outside of Xojo.

If I remove the ‘INNER JOIN Doctor ON Patient.DoctorID = Doctor.DocID’ It successfully fills the text fields from the Patient table.

This leads me to believe that there is a problem with the way I’ve Implemented INNER JOIN here. What’s confusing is that it works fine in SQLiteStudio, so is there some extra ruling regarding INNER JOIN use in Xojo?

I should also mention that when I add the ‘INNER JOIN Doctor ON Patient.DoctorID = Doctor.DocID’ part, none of the Patient text fields get filled like they were before.

Many Thanks

2

Have you tried it in the sqlite3 shell program?

3

You need to check for errors after every database operation. So in your case, put a “If Session.db.Error” statement after “Dim rs As RecordSet = stmt.SQLSelect”. If an error occurs, display the error message using MsgBox.

Also, is Patient.ID a number or a string? Your BindType statement indicates it’s a string, but you use it in the WHERE clause like a number.

IF none of that helps, try running the SELECT without a prepared statement, just to see if it returns the correct data (but still check for db.error).

4

Thanks for reply. My University is actually in Canterbury :).
I haven’t, I use SQLiteStudio because I’m unable to use the command line.

[quote=440033:@Jay Madren]You need to check for errors after every database operation. So in your case, put a “If Session.db.Error” statement after “Dim rs As RecordSet = stmt.SQLSelect”. If an error occurs, display the error message using MsgBox.

Also, is Patient.ID a number or a string? Your BindType statement indicates it’s a string, but you use it in the WHERE clause like a number.

IF none of that helps, try running the SELECT without a prepared statement, just to see if it returns the correct data (but still check for db.error).[/quote]

Thanks for response. I’ve added an error check and I do get a message box indicating an error has occurred.

The Parameter for the method is ‘patientID As String’, I believe it’s how I saw it in the Eddies Electronics Example and a Database Webinar, though it confused me as to why that’s the case.

Sorry, this is my first Xojo Database program, but how would it be best to procced with running the SELECT without the prepared statement? Would that not prevent the use of Bind to determine the ‘WHERE Patient.ID=?’ ?

Thanks

5

Put a breakpoint and dig into the RecordSet contents in the debugger. You’ll see the field names and data in the recordset to verify you’re collecting it correctly.

6

What is the error message?

But what is the data type of the Patient.ID column in the database? Even though SQLite is loosely typed, how you format parameters in a SQL statement can make a difference. If it is defined as TEXT, then you need to enclose it in quotes in the SQL statement.

Issue this instead:

Dim rs As RecordSet = Session.db.SQLSelect("SELECT Patient.ID, Patient.Forename, Patient.Surname, Patient.DateOfBirth, Patient.Address, Patient.Allergies, Patient.Notes, Patient.Type, Doctor.DocID, Doctor.DoctorName, Doctor.DoctorAddress FROM Patient INNER JOIN Doctor ON Patient.DoctorID = Doctor.DocID WHERE Patient.ID='" + patientID + "'")

or ...WHERE Patient.ID=" + Str(patientID)) if patientID is a number data type

7

One thing to worry about is what version of the sqlite library is in use in Xojo vs SQLIte Studio vs the shell
Since SQLite is fully embedded database library this can have an important impact as a newer one might have some function that older ones dont and the engine IS part of whatever application is manipulating the database file

In this case however it should not but I have seen other places where it really matters (like FTS version support)

8

Note that I’m not advocating to use the above SQLSelect method in your production app - you definitely need to use a prepared statement to help protect against SQL injections. This is just one way to help track down the problem.

9

Providing I’m placing the breakpoint in the correct place, by rs it says ‘Nil’

I see, thanks for that!

The error messages is as put in the message box.

If Session.db.Error Then
  MsgBox("Error Has Occured")
End If

This error message didn’t come up when I only loaded from the Patient table without any INNER JOIN using the very simple:

 "SELECT * FROM PATIENT WHERE Patient.ID=?"

[quote=440040:@Jay Madren]
Issue this instead:

Dim rs As RecordSet = Session.db.SQLSelect("SELECT Patient.ID, Patient.Forename, Patient.Surname, Patient.DateOfBirth, Patient.Address, Patient.Allergies, Patient.Notes, Patient.Type, Doctor.DocID, Doctor.DoctorName, Doctor.DoctorAddress FROM Patient INNER JOIN Doctor ON Patient.DoctorID = Doctor.DocID WHERE Patient.ID='" + patientID + "'")

or ...WHERE Patient.ID=" + Str(patientID)) if patientID is a number data type[/quote]

It appears to be as before. The text fields still don’t get filled and the MsgBox error still comes up.

10

[quote=440045:@Azi Pam]The error messages is as put in the message box.

If Session.db.Error Then
MsgBox(“Error Has Occured”)
End If[/quote]
No. I meant display the actual error message, Session.db.ErrorMessage:

If Session.db.Error Then MsgBox("Database Error has occurred:" + Session.db.ErrorMessage) End If

11

Of course, the docs is a bit wrong there: SQLiteDatabase — Xojo documentation

You can add the Error Code (http://documentation.xojo.com/api/deprecated/deprecated_class_members/database.error.htmlCode):

Dim db As New SQLiteDatabase If db.Connect Then // Connected to in-memory database Else If db.Error Then MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage) End If End If

You can even “geo-localize” the error using something like:

MsgBox "Method: GetPatientNumber; line M44" + EndOfLine + EndOfLine +_ "Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)

Where M44 is in a Comments line before some critical part you want to test.

12

More:

You can use http://documentation.xojo.com/api/os/system.html#system-debuglog

Syntax: System.DebugLog String
and place important information in String and get the error in both the IDE / Console.

MsgBox is a bit so so for error reporting for debugging purposes.

13

@Emile Schwarz True, but in this case it’s not just for debugging. You should always inform the user of a database error, unless you are handling the error in some way.

14

[quote=440046:@Jay Madren]No. I meant display the actual error message, Session.db.ErrorMessage:

If Session.db.Error Then MsgBox("Database Error has occurred:" + Session.db.ErrorMessage) End If[/quote]

[quote=440047:@Emile Schwarz]Of course, the docs is a bit wrong there: http://documentation.xojo.com/api/databases/sqlitedatabase.html

You can add the Error Code (http://documentation.xojo.com/api/deprecated/deprecated_class_members/database.error.htmlCode):

Dim db As New SQLiteDatabase If db.Connect Then // Connected to in-memory database Else If db.Error Then MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage) End If End If
[/quote]

Thanks for these, I wasn’t aware that Xojo could give you accurate error reports. It could certainly save a lot of time rather then just staring blankly at what could be wrong.

Both Error Messages state that ‘No such Column; Doctor.DocID’

I guess this is what appears to be the Issue, but I don’t understand how this comes to be.

15

Are you sure the column is DocID, not DoctorID like the Patient table?

16

Yes, I had changed so the names of the columns were not the same, as I heard that can be an issue, also to avoid use of Aliases.

DoctorID in the Patient table is the foreign key of DocID in Doctor table.

17

[quote=440030:@Azi Pam]Dim rs As RecordSet = stmt.SQLSelect
Return rs[/quote]
You return the RecordSet without testing. I hope you do that in the calling method.

http://documentation.xojo.com/api/deprecated/recordset.html
18

Are you sure the web app is connecting to the same database that you tested in SQLiteStudio?

19

I don’t know how many hours I’ve spent on this today, but Thank You so very much for reliving me! I must of used a different one to test out as a dummy and never connected it back to the one being used in the program. Since I had only recently changed the column names to ‘doc’ rather then leaving it as ‘ID’ to avoid using the same names, it didn’t understand the old one either.

Thank you very much to everyone who helped! Also, learning about the database error reporting is going to help me immensely as I continue forward In finishing this Web App.

Thank you very much again! Sorry for taking up all your time on such an error of mine, but I would of never seen it for it not for you!
God Bless!

20

Glad to be of help.

I still think you need to cleanup how you’re referring to the ID values. If they are defined as an Integer in the database, then you need to treat them as such in Xojo. It may be working for now, but it is likely to bite you somewhere down the road.