• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2411
  • Last Modified:

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.
0
MerlaP83
Asked:
MerlaP83
  • 11
  • 11
1 Solution
 
NickUpsonCommented:
you can use update or insert, the concatination operator is || so
update table1 set myblobtext = 'Label11.caption ' || ' Games field ' || GP ;
 
0
 
MerlaP83Author Commented:
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

0
 
NickUpsonCommented:
SQL.Add('update Spelare  set Historik = ''Matcher'' || 'Matcher'(this is the field) || etc.. where ....
 
should work
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
MerlaP83Author Commented:
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.
0
 
NickUpsonCommented:
the string delimiter is ' not "
 
values (''Sasong 1'' || 'Matcher')

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

0
 
MerlaP83Author Commented:
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
0
 
NickUpsonCommented:
well for a start, gen_id () takes 2 arguments, the name of the generator and the increment
please also show the table structure
0
 
MerlaP83Author Commented:
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

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

Any ideas why?
0
 
NickUpsonCommented:
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
0
 
MerlaP83Author Commented:
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.
0
 
NickUpsonCommented:
update SPELARE  set Historik = 'Matcher: ' || matcher || ' Mal: ' || mal || Historik
this will add to the existing data held in the Historik field
0
 
MerlaP83Author Commented:
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
0
 
NickUpsonCommented:
mal || // LINE BREAK // Historik
becomes, x is a delphi variable containing the newline char
mal || :x || Historik
0
 
MerlaP83Author Commented:
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

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now