Re: Base.HSQL: Reference, Call Identity

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