Just Discovered Returning - Need Help

  1. 8 months ago

    Robert L

    7 Nov 2018 Pre-Release Testers, Xojo Pro

    I just discovered the RETURNING command for Postgres, however, I am unable to get it to work despite reading the two threads related to it. :(

    I want to save a new security group name, and then get the ID back so I can do other work with it. It bugs out at the "dim rs as RecordSet = ps.SQLSelect(GroupName)" line every-time.

    My code is:

    Function SaveGroupName(GroupID as integer, GroupName as string) As integer
    dim b as integer
    dim sql as String
    
    if groupID = 0 then
      sql = "INSERT INTO sec_groups(name) VALUES ($1) RETURNING id;"
      Dim ps As PostgreSQLPreparedStatement //Build prepared statement
      ps = self.Prepare(sql)
      
      dim rs as RecordSet = ps.SQLSelect(GroupName)
      If rs <> Nil Then
        Do Until rs.EOF
          b = rs.Field("id").IntegerValue
          
          self.Commit
          
          rs.MoveNext
        Loop
      end if
      return rs.Field("id").IntegerValue
      
    else
      
    end if
    
    if self.Error then
      return 0
    end if
    
    End Function

    But I just noticed this code is buggy. You are cycling through the RecordSet, advancing it to EOF, so when you try to return a value, there will be no value there. If nothing else, you should be returning b, not rs.Field. Also, I'd use rs.IdxField( 1 ) instead of rs.Field( "id" ).

    Why not check the db.Error after executing the Prepared Statement?

  2. What does “bugs out” mean?

  3. Kem T

    7 Nov 2018 Pre-Release Testers, Xojo Pro, XDC Speakers Connecticut
    Edited 8 months ago

    What does "bugs out" mean specifically?

    Edit: Greg's nickname should change to "speedy".

  4. Kem T

    7 Nov 2018 Pre-Release Testers, Xojo Pro, XDC Speakers Answer Connecticut

    But I just noticed this code is buggy. You are cycling through the RecordSet, advancing it to EOF, so when you try to return a value, there will be no value there. If nothing else, you should be returning b, not rs.Field. Also, I'd use rs.IdxField( 1 ) instead of rs.Field( "id" ).

    Why not check the db.Error after executing the Prepared Statement?

  5. Jay M

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

    Are you sure "self" is the database? Have you tried executing the sql statement outside of Xojo? (substituting an actual value, of course)

    Also, the RETURNING clause does not return a named column, just a value. You have to access the returned value like this:

    rs.IdxField( 1 ).IntegerValue
  6. Kem T

    7 Nov 2018 Pre-Release Testers, Xojo Pro, XDC Speakers Connecticut

    BTW, just to be clear, RETURNING works just fine with Xojo, we use it all the time.

    And @Jay M, I just tested and it will return id as a named column, but I'd use IdxField anyway.

  7. Jay M

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

    @Kem T I just tested and it will return id as a named column, but I'd use IdxField anyway.

    Hmm, maybe it didn't use to? Or maybe I use IdxField in all my db classes to make it more portable.

  8. Kem T

    7 Nov 2018 Pre-Release Testers, Xojo Pro, XDC Speakers Connecticut

    I think it always did (or perhaps, has for so long that I thought it was "always"). We use it with our orm class to return * and populate the properties by name.

  9. Robert L

    7 Nov 2018 Pre-Release Testers, Xojo Pro
    Edited 8 months ago

    Ok...to respond to you quick responders. :)

    Greg (speedy)/Kem:
    Excellent question. I get a NilOjectException error on that line. I looked into that in more detail (forgot I could see the error) and found it was a permission problem. Once I fixed that it allowed the code to run, however, I do not get back the ID...but I do get back a 0. (see next response)

    Kem:
    Great point...when I change the return to b I received the response I expected.

    Jay:
    Yes, Self is my DB because I put ALL my db methods in my Data Class. This helps me organize them.

    Everyone:
    It is now working thanks to Greg's push and Kem's recommendation. I LOVE this feature as it will allow me to clean up a number of my other Web applications that make second calls and use stupid temp IDs to do the same function.

    Thanks for your help!!!!

  10. Robert L

    7 Nov 2018 Pre-Release Testers, Xojo Pro

    To hijack my own thread...

    Kem/Jay, why use rs.IdxField( 1 ) instead of rs.Field( "id" )?

  11. Jean-Yves P

    7 Nov 2018 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    should be faster ?

  12. Kem T

    7 Nov 2018 Pre-Release Testers, Xojo Pro, XDC Speakers Connecticut

    As a general rule, if I am asking for only one column back, I access that column by index, nothing more than that.

  13. Robert L

    7 Nov 2018 Pre-Release Testers, Xojo Pro

    @Jean-YvesPochez should be faster ?

    Using the index rather than the column name will provide faster results? Is this in all cases, or only if I'm returning a single column?

  14. Kem T

    7 Nov 2018 Pre-Release Testers, Xojo Pro, XDC Speakers Connecticut

    We don't know the code behind those calls, of course, but we can guess that it has to match the column name to the index and ultimately look it up by index, so yes, using IdxField would be faster in that case.

or Sign Up to reply!