Just Discovered Returning - Need Help

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. :frowning:

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
  end if
  return rs.Field("id").IntegerValue
end if

if self.Error then
  return 0
end if

End Function

What does “bugs out” mean?

What does “bugs out” mean specifically?

Edit: Greg’s nickname should change to “speedy”.

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?

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

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

And @Jay Madren, 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.

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.

Ok…to respond to you quick responders. :slight_smile:

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)

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

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

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!!!

To hijack my own thread…

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

should be faster ?

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

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?

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.