Link to home
Start Free TrialLog in
Avatar of MerlaP83
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.
Avatar of Nick Upson
Nick Upson
Flag of United Kingdom of Great Britain and Northern Ireland image

you can use update or insert, the concatination operator is || so
update table1 set myblobtext = 'Label11.caption ' || ' Games field ' || GP ;
 
Avatar of MerlaP83
MerlaP83

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..

Open in new window

SQL.Add('update Spelare  set Historik = ''Matcher'' || 'Matcher'(this is the field) || etc.. where ....
 
should work
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.
the string delimiter is ' not "
 
values (''Sasong 1'' || 'Matcher')

becomes
values ('Sasong 1' || 'Matcher')
 
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.
insert into Spelare (Historik) values ('Matcher' || 'Matcher')
each literal string needs ' each end, your only had them around the first
|| is the concatinate operator

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
well for a start, gen_id () takes 2 arguments, the name of the generator and the increment
please also show the table structure
Oh, ok.

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 ; ^

Open in new window

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
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')
;
no, that is a different error
Thought I had posted that error before because I've got it several times before.

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
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.
ASKER CERTIFIED SOLUTION
Avatar of Nick Upson
Nick Upson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
mal || // LINE BREAK // Historik
becomes, x is a delphi variable containing the newline char
mal || :x || Historik
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.
spelare.close;
spelare.SQL.Clear;
spelare.sql.add('update SPELARE set Historik = '+ar.caption+' || '' - '' || klubb || '' [ '' || matcher || '' - '' || mal || '' + '' || ass || '' =  '' || poang || '' ] '' || :x || Historik');
spelare.ExecSQL;

Open in new window

x is just a 1 char delphi variable to hold the newline, you may have to experiment a bit to get it right
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.