Re: [dba-users] Base.HSQL: Reference, Call Identity

[Andrew Jensen]

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