PostgreSQL SQLSelect fails without throwing error

  1. 6 days ago

    Tim S

    Nov 7 Pre-Release Testers, Xojo Pro Phoenix Arizona USA
    Edited 6 days ago

    Hello all,
    A very weird problem I have run into. Unpredictably, when I execute the following line code, it will bail out of the method without throwing an error.

    RsAlarmDefaults= db.SQLSelect("SELECT * FROM " + Tbl )
      RsAlarmDefaults= db.SQLSelect("SELECT * FROM " + Tbl )
      dbMessage= db.ErrorMessage
      dbErrorNo =db.ErrorCode
      if db.Error = true then
        Return db.ErrorCode
      end if
    
    
    RecordCount = RsAlarmDefaults.RecordCount
    If RecordCount= 1 And UpdateProperties = True Then FillVars
    Return RecordCount
    
    Exception e as RuntimeException
      Dim Logs As New clsLogging
      Dim Flds() As String = array("EXCEPTION", "cls" +Tbl + ".getAll ", Cstr( e.Type) )
      Logs.AddFields Flds
      Logs.LogFileAppend( Logs.K_Except_App_Err)
      

    This happens something like every other time, but I have not yet found the rhythm to it yet.

    Has anyone else seen this or have an idea how to solve? BTY this is one example, it will occur in other code too but always at the SQL Select code line.

    Happens in 2017R3 and 2019R1.1

    Thanks,
    Tim

  2. Dave S

    Nov 7 San Diego, California USA

    why are you expecting an error? because "tbl" doesn't exist?

  3. Louis D

    Nov 7 Pre-Release Testers, Xojo Pro QC, Canada

    @Dave Sisemore nailed it. tbl does not exist.

    Whenever I get this sort of error, I copy the SQL statement from the Xojo debug pane and paste it in the query editor of pgAdmin4. Invariably, there is an error in my SQL. A comma, a typo in the table name, some other insignificant (well, not) error.

  4. Jay M

    Nov 7 Pre-Release Testers, Xojo Pro NC, USA

    What do you mean by "bail out without throwing an error"? How do you know it did this? Since you're returning either db.ErrorCode or RecordCount, how do know when there was an error vs just a count of records?

  5. Tim S

    Nov 7 Pre-Release Testers, Xojo Pro Phoenix Arizona USA

    Hi Dave.
    I am not expecting an error, but something happens. Tbl is a constant. I think however I found the problem. An event fires elsewhere, code gets executed then it pops back to where it was - so the reference was lost to it. I put a small flag in where the event was firing so the rest of the code would not execute. That *appears* to have fixed it. But I thought that at minimum a error would be thrown.

    @Louis - I checked it in pgAdmin 3 and the code executed perfectly.

    Thank you both for your responses!
    Tim

  6. Tim S

    Nov 7 Pre-Release Testers, Xojo Pro Phoenix Arizona USA

    Hi Jay,

    Nothing was ever returned and the code following was never executed. Very hard to describe when I wrote that since it was cloudy to say the least. Once I saw the debugger jump to an event being fired I think I figured it out! Never saw that before and nobody complaining of PostgreSQL plugin issues -

    Thanks for your reply!
    Tim

  7. Jay M

    Nov 7 Pre-Release Testers, Xojo Pro NC, USA

    @Tim S I think however I found the problem. An event fires elsewhere, code gets executed then it pops back to where it was - so the reference was lost to it.

    This doesn't make sense. What "reference" do you think is being lost? Plus, I thought an event couldn't interrupt a method - it has to wait until the method is finished.

  8. Jay M

    Nov 7 Pre-Release Testers, Xojo Pro NC, USA

    @Tim S Nothing was ever returned and the code following was never executed.

    Yes, this is cloudy. The method can't return "nothing". It's an integer. And what "code following" wasn't executed? Code in the method, or code after the method call in the calling routine?

  9. Tim S

    Nov 7 Pre-Release Testers, Xojo Pro Phoenix Arizona USA

    It is a serial DataAvailable event. In the Microcontroller world, events always preempt other code. Thats why in that world there should be very very little code, really on a flag that gets checked in the main loop..... This is a different world tho and trying to follow it is sometimes hard!

    That's about the most I can figure on this. Has been 3 days trying to find out what is going on!
    Tim

  10. Tim S

    Nov 7 Pre-Release Testers, Xojo Pro Phoenix Arizona USA

    I agree! It is cloudy. After executing in the DataAvailable event, the IDE would go back to the SQLExecute line then jump to another location - Very strange! Just spent 3 days trying to figure it out, and although I do not understand it, by putting a single boolean flag at the start of the Dataavailable event I was able to kill the problem - hopefully it will remain dead.

    Thanks again Jay et al.
    Tim

or Sign Up to reply!