Is it possible to use parameter binding from the sqlite3 command line utility? I would like to make a small illustration of my problem but it only seems to have an issue with I am doing an insert and using a bound parameter for a column which is a foreign key. I have foreign key on by: PRAGMA foreign_keys = ON;
BEGIN IMMEDIATE; CREATE TABLE IF NOT EXISTS [Microscopes] (MicroscopeID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,Name VARCHAR NOT NULL ,Description VARCHAR ); CREATE TABLE IF NOT EXISTS [Objectives] (ObjectiveID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,MicroscopeID INTEGER NOT NULL REFERENCES [Microscopes] DEFERRABLE INITIALLY DEFERRED ,TurretPos INTEGER ,Name VARCHAR NOT NULL ,Description VARCHAR NOT NULL ,Magnification REAL NOT NULL ,NA REAL NOT NULL ,WD REAL NOT NULL ); CREATE TABLE IF NOT EXISTS [Calibrations] (CalibrationID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,ObjectiveID INTEGER NOT NULL REFERENCES [Objectives] DEFERRABLE INITIALLY DEFERRED ,MicroscopeID INTEGER NOT NULL REFERENCES [Microscopes] DEFERRABLE INITIALLY DEFERRED ,Timestamp DATETIME NOT NULL UNIQUE ,Description VARCHAR ); INSERT INTO [Microscopes] VALUES(@MicroscopeID, 'nSpec', 'default'); INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, Description, Magnification, NA, WD) VALUES( 1, @MicroscopeID, 0, '5X' , 'CFI LU Plan Fluor BD 5X' , 5.0, 0.15, 18.0); INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, Description, Magnification, NA, WD) VALUES( 2, @MicroscopeID, 1, '10X' , 'CFI LU Plan Fluor BD 10X' , 10.0, 0.30, 15.0); INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, Description, Magnification, NA, WD) VALUES( 3, @MicroscopeID, 2, '20X' , 'CFI LU Plan Fluor BD 20X' , 20.0, 0.45, 4.5); INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, Description, Magnification, NA, WD) VALUES( 4, @MicroscopeID, 3, '50X' , 'CFI LU Plan Fluor BD 50X' , 50.0, 0.80, 1.0); INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, Description, Magnification, NA, WD) VALUES( 5, @MicroscopeID, 4, '100X', 'CFI LU Plan Fluor BD 100X', 100.0, 0.90, 1.0); COMMIT; The issue is with the @MicroscopeID in the 5 inserts into the Objectives table. If I remove the foreign key constraint on MicroscopeID in the Objectives table I get MicroscopeID of 1 NULL NULL NULL NULL My version is 3.7.3. I have SQLite Expert and have tried same SQL and it appears to work correctly after prompting me for a value of @MicroscopeID 6 times. In my code I have tried making 1 bind to @MicroscopeID and I have tried it with appending the digits 1 to 6 and making 6 calls to bind with same result. My app is 64 bit and SQLite Expert is 32 bit. Any thought or suggestion will be appreciated as I am not sure how to proceed. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
