MerlaP83
asked on
Firebird 2.1 Insert using BLOB TEXT
I have a field called HISTORIK which is a BLOB Text.
What I would want is for it to add the field Games, Goals, Assists, Points for each record.
Something like:
Label11.caption+' - '+Games field+' GP, '+Goals field+' G, '+Assists field+' A, '+Points field+' PTS.'
I suppose I have to use Insert and not Update to achieve this, but how? Using Delphi 7 and Firebird 2.1.
What I would want is for it to add the field Games, Goals, Assists, Points for each record.
Something like:
Label11.caption+' - '+Games field+' GP, '+Goals field+' G, '+Assists field+' A, '+Points field+' PTS.'
I suppose I have to use Insert and not Update to achieve this, but how? Using Delphi 7 and Firebird 2.1.
ASKER
Can't get it working. I will have to use INSERT instead of UPDATE.
SQL.Add('insert into Spelare (Historik) values (''Matcher'' || 'Matcher'(this is the field) || etc..
SQL.Add('update Spelare set Historik = ''Matcher'' || 'Matcher'(this is the field) || etc.. where ....
should work
should work
ASKER
insert into spelare (HISTORIK) values (''Sasong 1'' || 'Matcher');
Gives me:
SQL Error: Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 42 Sasong. Error Code: -104. Invalid token The SQL: insert into spelare (HISTORIK) values (''Sasong 1'' || 'Matcher')
;
Need to use INSERT, instead of UPDATE in this case.
Gives me:
SQL Error: Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 42 Sasong. Error Code: -104. Invalid token The SQL: insert into spelare (HISTORIK) values (''Sasong 1'' || 'Matcher')
;
Need to use INSERT, instead of UPDATE in this case.
the string delimiter is ' not "
values (''Sasong 1'' || 'Matcher')
becomes
values ('Sasong 1' || 'Matcher')
values (''Sasong 1'' || 'Matcher')
becomes
values ('Sasong 1' || 'Matcher')
ASKER
Still errors.
When I run:
update Spelare set Historik = 'Matcher' || Matcher
It works.
//
But when I do the same, but for Insert into I get:
SQL Error: Dynamic SQL Error SQL error code = -206 Column unknown MATCHER At line 1, column 53. Error Code: -206. Column does not belong to referenced table The SQL: insert into Spelare (Historik) values ('Matcher' || Matcher)
;
Tried several different string delimiters, but no luck as of yet.
When I run:
update Spelare set Historik = 'Matcher' || Matcher
It works.
//
But when I do the same, but for Insert into I get:
SQL Error: Dynamic SQL Error SQL error code = -206 Column unknown MATCHER At line 1, column 53. Error Code: -206. Column does not belong to referenced table The SQL: insert into Spelare (Historik) values ('Matcher' || Matcher)
;
Tried several different string delimiters, but no luck as of yet.
insert into Spelare (Historik) values ('Matcher' || 'Matcher')
each literal string needs ' each end, your only had them around the first
|| is the concatinate operator
each literal string needs ' each end, your only had them around the first
|| is the concatinate operator
ASKER
Think the problem was with the fact that I'm using a primary key(ID). Still can't get it right though, posted an image.
image.jpg
image.jpg
well for a start, gen_id () takes 2 arguments, the name of the generator and the increment
please also show the table structure
please also show the table structure
ASKER
Oh, ok.
Here goes:
Here goes:
-- Table: SPELARE
-- DROP TABLE SPELARE;
CREATE TABLE SPELARE (
ID INTEGER NOT NULL,
FNAMN VARCHAR(30) CHARACTER SET ISO8859_1 COLLATE SV_SV,
ENAMN VARCHAR(30) CHARACTER SET ISO8859_1 COLLATE SV_SV,
NATION VARCHAR(20) CHARACTER SET ISO8859_1 NOT NULL COLLATE SV_SV,
KLUBB VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL COLLATE SV_SV,
ALDER INTEGER DEFAULT 20 NOT NULL,
POS VARCHAR(1),
LANGD INTEGER,
VIKT INTEGER,
OFF INTEGER DEFAULT 0 NOT NULL,
DEF INTEGER DEFAULT 0 NOT NULL,
POTENTIAL INTEGER DEFAULT 0 NOT NULL,
PERSONLIGHET INTEGER DEFAULT 0 NOT NULL,
RATTIGHETER CHAR(50) CHARACTER SET ISO8859_1 DEFAULT NULL COLLATE SV_SV,
DRAFTAR INTEGER DEFAULT 0,
DRAFTPLATS INTEGER,
PLATSAR INTEGER DEFAULT 0,
SKADAD INTEGER DEFAULT 0,
MATCHER INTEGER DEFAULT 0,
MAL INTEGER DEFAULT 0,
ASS INTEGER DEFAULT 0,
POANG INTEGER DEFAULT 0,
KONTRAKT INTEGER DEFAULT 1,
LON INTEGER DEFAULT 0,
KLUBBINT VARCHAR(30) CHARACTER SET ISO8859_1 COLLATE SV_SV,
PP INTEGER DEFAULT 0,
BP INTEGER DEFAULT 0,
SKOTTEMOT INTEGER DEFAULT 0,
INSLAPPTA INTEGER DEFAULT 0,
VARDE INTEGER DEFAULT 0,
LANDSLAG INTEGER DEFAULT 0,
SVARSTID INTEGER DEFAULT 0,
HISTORIK BLOB SUB_TYPE TEXT SEGMENT SIZE 100 CHARACTER SET ISO8859_1,
VARVAD INTEGER DEFAULT 0,
BOOST INTEGER DEFAULT 0,
TOTAL DOUBLE PRECISION DEFAULT 0.0,
INSATS INTEGER DEFAULT 0,
TEMPRANDCOL INTEGER DEFAULT 0,
/* Keys */
CONSTRAINT PK_SPELARE
PRIMARY KEY (ID)
);
SET TERM ^ ;
CREATE TRIGGER SPELARE_BI FOR SPELARE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
if ((new.id is null) or (new.id = 0)) then
NEW.ID = GEN_ID(MARTIN,1);
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER "autoinc" FOR SPELARE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(MARTIN,1);
END^
SET TERM ; ^
a few items:
you have 2 triggers that do the same thing, assigning a value to id from gen_id(), - drop one of them
as the trigger is there, you don't need to do it in the insert either, making the insert into "insert into SPELARE (Historic) values ('Hello' || 'other text'); "
the datatype of ID should be bigint rather than int as bigint is what gen_id returns
you have 2 triggers that do the same thing, assigning a value to id from gen_id(), - drop one of them
as the trigger is there, you don't need to do it in the insert either, making the insert into "insert into SPELARE (Historic) values ('Hello' || 'other text'); "
the datatype of ID should be bigint rather than int as bigint is what gen_id returns
ASKER
Still gives me the same error as before:
SQL Error: validation error for column NATION, value "*** null ***". Error Code: -625. The insert failed because a column definition includes validation constraints. The SQL: insert into SPELARE (Historik) values ('Hello' || 'other text')
;
SQL Error: validation error for column NATION, value "*** null ***". Error Code: -625. The insert failed because a column definition includes validation constraints. The SQL: insert into SPELARE (Historik) values ('Hello' || 'other text')
;
no, that is a different error
ASKER
Thought I had posted that error before because I've got it several times before.
Any ideas why?
Any ideas why?
typically this would be when a field not used in the insert is defined as not null and does not have a default defined either
ASKER
Ah, thanks a lot. Seems to be working now, no errors.
However, maybe I've understood it all wrong with the Update/Insert procedures.
With Insert, it creates a new record with the statements we've tried here - this is not what I want to accomplish.
I want every record to be updated with a new line that stores the information from the record's field Matcher and Mal, for example.
FNAMN: Jon
ENAMN: Anderson
Matcher: 36
Mal: 5
Historik should now insert a new line in the BLOB saying "Matcher: 36, Mal: 5" and should do this for every record in the table.
Sorry if I've provided you with wrong information about what I want to achieve.
Using Update statements will remove everything that's already in the Historik BLOB field - I want it to keep that information and add the new information.
However, maybe I've understood it all wrong with the Update/Insert procedures.
With Insert, it creates a new record with the statements we've tried here - this is not what I want to accomplish.
I want every record to be updated with a new line that stores the information from the record's field Matcher and Mal, for example.
FNAMN: Jon
ENAMN: Anderson
Matcher: 36
Mal: 5
Historik should now insert a new line in the BLOB saying "Matcher: 36, Mal: 5" and should do this for every record in the table.
Sorry if I've provided you with wrong information about what I want to achieve.
Using Update statements will remove everything that's already in the Historik BLOB field - I want it to keep that information and add the new information.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yay, it's finally working :)
One last thing before I close this question.
Can I add a line break to it?
update SPELARE set Historik = 'Matcher: ' || matcher || ' Mal: ' || mal || // LINE BREAK // Historik
One last thing before I close this question.
Can I add a line break to it?
update SPELARE set Historik = 'Matcher: ' || matcher || ' Mal: ' || mal || // LINE BREAK // Historik
mal || // LINE BREAK // Historik
becomes, x is a delphi variable containing the newline char
mal || :x || Historik
becomes, x is a delphi variable containing the newline char
mal || :x || Historik
ASKER
How do you mean with the X?
Do I need to declare it somehow in Delphi? I'm a beginner as you may have understood :)
The code below is working great without the :X, but if I add it then it seems like the Historik field is cleared.
Do I need to declare it somehow in Delphi? I'm a beginner as you may have understood :)
The code below is working great without the :X, but if I add it then it seems like the Historik field is cleared.
spelare.close;
spelare.SQL.Clear;
spelare.sql.add('update SPELARE set Historik = '+ar.caption+' || '' - '' || klubb || '' [ '' || matcher || '' - '' || mal || '' + '' || ass || '' = '' || poang || '' ] '' || :x || Historik');
spelare.ExecSQL;
x is just a 1 char delphi variable to hold the newline, you may have to experiment a bit to get it right
ASKER
Couldn't figure it out, but I will give it a shot later on. I will accept your previous answer, since this new line question is off topic. Thanks for your patience and help NickUpson.
update table1 set myblobtext = 'Label11.caption ' || ' Games field ' || GP ;