SQLite INNER JOIN Issue

  1. ‹ Older
  2. 3 months ago

    Tim S

    Jun 4 Canterbury, UK

    Have you tried it in the sqlite3 shell program?

  3. Jay M

    Jun 4 Pre-Release Testers, Xojo Pro NC, USA
    Edited 3 months ago

    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. @Tim S Have you tried it in the sqlite3 shell program?

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

    @Jay M 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).

    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. Tim P

    Jun 4 Pre-Release Testers Rochester, NY

    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. Jay M

    Jun 4 Pre-Release Testers, Xojo Pro NC, USA

    @Azi P Thanks for response. I've added an error check and I do get a message box indicating an error has occurred.

    What is the error message?

    @Azi P The Parameter for the method is 'patientID As String',

    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.

    @Azi P 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=?' ?

    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. Norman P

    Jun 4 Pre-Release Testers, Xojo Pro great-white-software.com/blog
    Edited 3 months ago

    @Azi P I haven't, I use SQLiteStudio because I'm unable to use the command line.

    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. Jay M

    Jun 4 Pre-Release Testers, Xojo Pro NC, USA

    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. @Tim P 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.

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

    @Norman P One thing to worry about is what version of the sqlite library is in use in Xojo vs SQLIte Studio vs the shell

    I see, thanks for that!

    @Jay M What is the error message?

    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=?"

    @Jay M 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

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

  10. Jay M

    Jun 4 Pre-Release Testers, Xojo Pro NC, USA

    @Azi P The error messages is as put in the message box.

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

    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. Emile S

    Jun 4 Europe (France, Strasbourg)

    @Azi P MsgBox("Error Has Occured")

    Of course, the docs is a bit wrong there: http://docs.xojo.com/SQLiteDatabase

    You can add the Error Code (http://docs.xojo.com/Database.ErrorCode):

    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. Emile S

    Jun 4 Europe (France, Strasbourg)

    More:

    You can use http://docs.xojo.com/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. Jay M

    Jun 4 Pre-Release Testers, Xojo Pro NC, USA

    @Emile S 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. @Jay M 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

    @Emile S Of course, the docs is a bit wrong there: http://docs.xojo.com/SQLiteDatabase

    You can add the Error Code (http://docs.xojo.com/Database.ErrorCode):

    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

    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. Jay M

    Jun 4 Pre-Release Testers, Xojo Pro NC, USA

    @Azi P 'No such Column; Doctor.DocID'

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

  16. Edited 3 months ago

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

    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.

    -image-

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

  17. Emile S

    Jun 4 Europe (France, Strasbourg)

    @Azi P Dim rs As RecordSet = stmt.SQLSelect
    Return rs

    You return the RecordSet without testing. I hope you do that in the calling method.

    http://docs.xojo.com/RecordSet
  18. Jay M

    Jun 4 Pre-Release Testers, Xojo Pro Answer NC, USA

    @Azi P 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.

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

  19. Edited 3 months ago

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

    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. Jay M

    Jun 4 Pre-Release Testers, Xojo Pro NC, USA

    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.

  21. Emile S

    Jun 5 Europe (France, Strasbourg)

    @Azi P I don't know how many hours I've spent on this today

    When the above happens to me, I am starting to undestand less and less of what happens. When possible, I do something else for a day or two and came back to that later. This greatly help me to understand what was wrong.

    Good luck.

or Sign Up to reply!