Just Discovered Returning - Need Help

Databases
1

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

What does Ā“bugs outĀ” mean?

3

What does ā€œbugs outā€ mean specifically?

Edit: Gregā€™s nickname should change to ā€œspeedyā€.

4

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

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

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.

7

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

8

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

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)

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

To hijack my own threadā€¦

Kem/Jay, why use rs.IdxField( 1 ) instead of rs.Field( ā€œidā€ )?

11

should be faster ?

12

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

13

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

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.