Firebird foreign keys
lazarus / 16-Databases

----------------------------------------------------------------------
nikel · 2017-01-16 13:17

Hi, I have Firebird 3 database and Lazarus installed. I used SQL
Manager for InterBase & Firebird to create the database. I want to
update or insert record, when record not present, setting max value of
foreign key.

My db structure:
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i /* SQL
Manager for InterBase and Firebird 5.4.2.48438              *//*
--------------------------------------------------------------- *//*
Host     : localhost                                            *//*
Database : C:\Users\Can\Desktop\laz_gazi_installer\DATABASE.FDB
*/  CREATE DATABASE
'C:\Users\Can\Desktop\laz_gazi_installer\DATABASE.FDB'  USER 'SYSDBA' 
PAGE_SIZE = 4096  DEFAULT CHARACTER SET ISO8859_5  COLLATION
ISO8859_5; /* Structure for the `TBL_EXECUTABLE` table :  */ CREATE
TABLE TBL_EXECUTABLE (  ID INTEGER NOT NULL,  CB_CHECKED BOOLEAN
DEFAULT 'FALSE',  TXT_ALIAS CHAR(50) NOT NULL,  TXT_PATH CHAR(95), 
ID_SWITCH INTEGER);  ALTER TABLE TBL_EXECUTABLE ADD PRIMARY KEY (ID)
USING INDEX RDB$PRIMARY7; /* Structure for the `TBL_SWITCHES` table :
 */ CREATE TABLE TBL_SWITCHES (  ID INTEGER NOT NULL,  TXT_PRE_SWITCH
CHAR(11),  TXT_POST_SWITCH CHAR(11));  ALTER TABLE TBL_SWITCHES ADD
PRIMARY KEY (ID) USING INDEX RDB$PRIMARY6; /* Structure for the
`TBL_EXECUTABLE` table :  */ ALTER TABLE TBL_EXECUTABLE ADD CONSTRAINT
FK_TBL_EXECUTABLE FOREIGN KEY (ID_SWITCH) REFERENCES
TBL_SWITCHES(ID); /* Structure for the `TBL_EXECUTABLE` table :
 */ ALTER TABLE TBL_EXECUTABLE ADD CONSTRAINT FK_TBL_EXECUTABLE
FOREIGN KEY (ID_SWITCH) REFERENCES TBL_SWITCHES(ID); /* Privileges for
the `TBL_EXECUTABLE` :  */  GRANT SELECT, INSERT, DELETE, REFERENCES,
UPDATE ON TBL_EXECUTABLE TO SYSDBA WITH GRANT OPTION;  /* Privileges
for the `TBL_SWITCHES` :  */  GRANT SELECT, INSERT, DELETE,
REFERENCES, UPDATE ON TBL_SWITCHES TO SYSDBA WITH GRANT OPTION;  
Last fpc code I tried:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.SQL.Clear;SQLQuery1.sql.text:='INSERT INTO TBL_SWITCHES
(TXT_PRE_SWITCH, TXT_POST_SWITCH) VALUES(:TXT_PRE_SWITCH,
:TXT_POST_SWITCH); ';SQLQuery1.Params.ParamByName('TXT_PRE_SWITCH').As
String:=preSwitch_edt.Text;SQLQuery1.params.ParamByName('TXT_POST_SWIT
CH').AsString:=postSwitch_edt.Text;// pk:=SQLQuery1.Params.ParamByName
('ID').AsInteger;DBConnection.Connected :=
True;SQLQuery1.ExecSQL;SQLTransaction1.Commit; //... and commit them
using the transaction. // Insert name after
switchesSQLQuery1.SQL.Clear;SQLQuery1.sql.text:='INSERT INTO
TBL_EXECUTABLE (CB_CHECKED, TXT_ALIAS, TXT_PATH, ID_SWITCH)
VALUES(:CB_CHECKED, :TXT_ALIAS, :TXT_PATH, (SELECT MAX(ID_SWITCH)); ';
SQLQuery1.Params.ParamByName('CB_CHECKED').AsBoolean:=true;SQLQuery1.p
arams.ParamByName('TXT_ALIAS').asstring := softwareName_edt.Text;SQLQu
ery1.params.ParamByName('TXT_PATH').asstring :=
filename_lbl.Caption;// SQLQuery1.params.ParamByName('ID_SWITCH').asin
teger:=-1;DBConnection.Connected :=
True;SQLQuery1.ExecSQL;SQLTransaction1.Commit; //... and commit them
using the transaction. 
I'm getting error on query.execsql, line 18. Can anyone help me?

----------------------------------------------------------------------
rvk · 2017-01-16 13:18

And WHAT error are you getting?

----------------------------------------------------------------------
nikel · 2017-01-16 13:23

Hi, thank you for your quick reply.

I'm getting error Debug exception notification

DBConnection prepare statement
-Dynamic sql error
-sql error code -104
-Token unknown - line 1 column 111
-)

----------------------------------------------------------------------
rvk · 2017-01-16 13:23

What does (SELECT MAX(SWITCH_ID) do?
I don't see a field (SWITCH_ID) and where are you selecting from?

----------------------------------------------------------------------
nikel · 2017-01-16 13:28

Oh that's my mistake I was just hurrying. I corrected my post. It
should be (SELECT MAX(ID_SWITCH). But still the same erro.

----------------------------------------------------------------------
rvk · 2017-01-16 13:33

Quote from: nikel on January 16, 2017, 02:28:39 pmOh that's my mistake
I was just hurrying. I corrected my post. It should be (SELECT
MAX(ID_SWITCH). But still the same erro.
Question remains the same... SELECT from where?
SELECT MAX(X) isn't a complete select statement.
And do you really want MAX(ID_SWITCH) from the same table or do you
want the largest ID from TBL_EXECUTABLE ??

I'm also missing triggers (to increase ID) on TBL_EXECUTABLE and
TBL_SWITCHES.

(even if you fix the SELECT in your insert... the next error will be
that ID is NULL in the insert (unless you have a not shown trigger on
it).

----------------------------------------------------------------------
nikel · 2017-01-16 13:48

Ok, after trying your suggestions I fixed this.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
TBL_EXECUTABLE (CB_CHECKED, TXT_ALIAS, TXT_PATH, ID_SWITCH)
VALUES(:CB_CHECKED, :TXT_ALIAS, :TXT_PATH, (SELECT MAX(ID) FROM
TBL_SWITCHES));
Thank you for the help.