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?
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.
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.
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.