Wolfgang Schaible wrote:
hi,
Have a DB 1:n, n:0|1, 0|1:1 and some ro-tables feeding listboxes,
all together driven by BASIC and DIALOG (was not able to create a
understandable, simple GUI using FORMs and REPORTs).
During inserting a new record 'n' one must supply the reference to
the higher record '1'. My clumsy method is:
'getREF SID (the higher level):
s_SQL(16)="SELECT SID FROM "+sJS+ " ORDER BY SID DESC"
oReSe =oStmt.executeQuery(s_SQL(16))
oReSe.next()
SID =oReSe.getInt(1)
The (H)SQL-Syntax knows the function 'CALL IDENTITY()'
but
how can I use it?
oStmt.executeUpdate(...) goes on strike...
Any solution?
Kind regards
Wolfgang
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Hi Wolfgang,
A couple of things you need to change to make that work. You want to use
executeQuery against the statement for one, Executequery returns a
result set, and that is what we need to retrieve the result of the
identity call.
Here is an example, called from a button on a form:
sub onClick( oEv as object )
dim stmt
dim rs
stmt = oEv.Source.Model.Parent.ActiveConnection.createStatement
rs = stmt.executequery( "call identity() " )
rs.next
print rs.columns(0).getInt
end sub
I might mention also, the identity() function returns the last identity
value inserted by THIS connection. It is not the next ideneity value of
any given tables identity column. So for your 1:n table, an insert of
the 'n' record needs to happen BEFORE you call identity(), which thtn
tells you what the value just used was. If I am reading your message
correctly, that is not what you are looking for. But then if the primary
key in te 'n' table is of type IDENTITY, then you don't have to supply
any value - the engine is going to do that. So I am not really sure I am
reading your message properly.
HTH
Drew
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
