Re: Base.HSQL: Reference, Call Identity

Wolfgang Schaible wrote:

>hi Drew,
>Am Sunday 21 May 2006 18:21 schrieb Andrew Jensen:
><>
>  
>
>>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. 
>>    
>>
>That's right.
>At the moment I use the DB as a thumb table. I was frustrated 
>using 'Forms' und wrote the code in a very short time.
>The application is running, all are happy but now I want to learn, 
>why it works. 
>
>  
>
>>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.
>>    
>>
>   ^^^^^^^^^^^^^^^^
>With other words, all what I have to do is alter the 'n'-table with 
><ALTER TABLE 'n' ADD FOREIGN KEY refTo1 REFERENCES '1'(identityKey)>
>and remove my own solution?
>Does 'oStmt.executeUpdate(..)' allow the command above?
>Will <ADD FOREIGN KEY> destroy the current DBs (the field 'refTo1' 
>exists)?
>
>  
>
>>So I am not really sure I am reading 
>>your message properly.
>>    
>>
>Your interpretation of my english is good :-)  Thanks
>
>  
>
Well, to be honest now I am sure I mislead you.

First - you can issue an alter table command using executeUpdate on a 
statement yes.

For example if I have the following table defintitions:

CREATE TABLE "Table1" ( "ID" IDENTITY, "Name" VARCHAR(50) );
CREATE TABLE "Table2" ( "ID" IDENTITY, "NameID" INTEGER, "Alias" 
VARCHAR(50));

Then I could add the foreign key constraint with this code:

sub onClickAltertable( oev as object )

dim stmt

dim sCmd as string

    sCmd = "ALTER TABLE ""Table2"" " _

            + "ADD FOREIGN KEY (""ID2"") " _

            + "REFERENCES ""Table1"" (""ID"")" _

            + "ON DELETE CASCADE " _

            + "ON UPDATE CASCADE"

    stmt = oev.source.model.parent.ActiveConnection.createStatement

    stmt.executeUpdate( sCmd )

end sub


Of course you can also just open the SQL window in Base ( Tools>SQL ) 
and enter the alter table command directly also.

That doesn't in and of itself help you with inserting new records in 
Table2 however, you still need to get the correct value for the ID2 
field in Table2.

So if I had a dialog box that had 5 text controls ( txtBox1...txtBox5), 
1 for a name and 4 for aliases and wanted to enter them all into the 
database when the dialog is submitted I could code something like:

sub onInsertRecords

dim stmt

dim rs

dim FK_Val

    '......

    'get connection and create statment

    '

    InsertMaster( Stmt, oDialog.GetControl("txtBox1").text )

    rs = Stmt.executeQuery( " CALL IDENTITY() " )

    rs.next

    FK_Val = rs.columns(0).getInt

    InsertChil( Stmt, FK_VAL, oDialog.GetControl("txtBox2").Text )

    .....

    'do the same fo the other aliases

    .....

end sub

sub InsertMaster( aStmt as variant, aName as string )

    aStmt.executeUpdate( "INSERT INTO ""Table1"" ( ""NAME"" ) VALUES ( " + aName + " ) "

end sub

sub InsertChild( aStmt as variant, aFK as integer, aAlias as string )

    aStmt.executeUpdate( "INSERT INTO ""Table2"" ( ""ID2"", ""Alias"" ) VALUES ( " + aFK + ", " + aAlias + " )"

end sub

I would guess that is something similar to what you have already.

Drew