We help IT Professionals succeed at work.

Exiting edit and insert into firebird table gives Dynamic SQL Error

marioway
marioway asked
on
1,115 Views
Last Modified: 2012-06-27
Hi, another problem emerged; when I exit an edit(box), and try to insert a value, other then the one allready in the table, it gives me a dynamic SQL Error : Data Type Unknown
Table : 1 field type Char; code:

procedure TAdministratie.ComboBox2Exit(Sender: TObject);
var A: string;
begin
  A := Trim(ComboBox2.Text);
  if A = '' then
  begin
  end
    else
  with Datamodule1 do
  begin
    IBQuery10.Close;
    IBQuery10.SQL.Clear;
    IBQuery10.SQL.Text := 'SELECT 1 from STRAAT WHERE Upper(STRATEN) = Upper(:A)';
    IBQuery10.ParamByName('A').AsString:= A ;
    IbQuery10.Open;
    if not IBQuery10.IsEmpty then
      Exit
    else
    begin
      IBQuery5.Close;
      IBQuery5.SQL.Clear;
      IBQuery5.SQL.Text :=
        'Insert INTO STRAAT (STRATEN) '+
        'SELECT :A As STRATEN FROM rdb$database '+
        'WHERE 0 = ( SELECT COUNT(*) X FROM STRAAT '+
        '  WHERE Upper(STRATEN) = Upper(:A)) ';
      IBQuery5.ParamByName('A').AsString:= A ;
      IBQuery5.ExecSQL;
    end;
  end;
end;

Problem ?
Comment
Watch Question

Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009

Commented:
the database doesn't know what type is required
and it looks like firebird doesn't convert automatically

IbQuery5.ParamByName('A').DataType := ftString;
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009

Commented:
it is pointless in writing so much useless code
strip it down ... unless somebody is paying you per line ???

procedure TAdministratie.ComboBox2Exit(Sender: TObject);
var A: string;
begin
  A := Trim(ComboBox2.Text);
  if A <> '' then
  with Datamodule1 do
  begin
    IBQuery5.Close;
    IBQuery5.SQL.Text :=
      'Insert INTO STRAAT (STRATEN) '+
      'SELECT :A As STRATEN FROM rdb$database '+
      'WHERE 0 = ( SELECT COUNT(*) X FROM STRAAT '+
      '  WHERE Upper(STRATEN) = Upper(:A)) ';
    IBQuery5.ParamByName('A').AsString:= A ;
    IBQuery5.ParamByName('A').DataType := ftString ;
    IBQuery5.ExecSQL;
  end;
end;

Open in new window


1: if you set Sql.Text then you can delete the Sql.Clear line preceding
2: there is no need to check with IbQuery10 for a duplicate
the insert statement with the contained select already does this
3: there is no point in writing a empty "begin" "end" clause
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009

Commented:
alternately, if you want to know if the insert added a record:

procedure TAdministratie.ComboBox2Exit(Sender: TObject);
var A: string;
begin
  A := Trim(ComboBox2.Text);
  if A <> '' then
  with Datamodule1 do
  begin
    IBQuery5.Close;
    IBQuery5.SQL.Text :=
      'Insert INTO STRAAT (STRATEN) '+
      'SELECT :A As STRATEN FROM rdb$database '+
      'WHERE 0 = ( SELECT COUNT(*) X FROM STRAAT '+
      '  WHERE Upper(STRATEN) = Upper(:A)) ';
    IBQuery5.ParamByName('A').AsString:= A ;
    IBQuery5.ParamByName('A').DataType := ftString ;
    IBQuery5.ExecSQL;
    // Show how many records were inserted
    ShowMessage(Format('%d records inserted.', [IbQuery5.RowsAffected]));
  end;
end;

Open in new window

Author

Commented:
Hi, Geert , I don't get payd .....  I'm going to follow your advise ! Now I'm going to try this thing out

Mario

Author

Commented:
Geert, still getting Dynamic SQL Error -804 Data Type Unkown

Mario
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009

Commented:
then use a cast or convert
http://www.firebirdsql.org/refdocs/langrefupd20-cast.html

 IBQuery5.SQL.Text :=
      'Insert INTO STRAAT (STRATEN) '+
      'SELECT CAST(:A As VARCHAR) FROM rdb$database '+
      'WHERE 0 = ( SELECT COUNT(*) X FROM STRAAT '+
      '  WHERE Upper(STRATEN) = Upper(Cast (:A as varchar))) ';
    IBQuery5.ParamByName('A').AsString:= A ;

Open in new window


it's possible you need to specify the length too
      'SELECT CAST(:A As VARCHAR(50)) FROM rdb$database '+

Author

Commented:
Geert, now I get another error: Dynamic SQL Error = -104 Token Unknown - line 1, Char 68 ).

also if I use VARCHAR(50)

Mario
Oracle dba
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Geert, I have done some testing, and finally got the data in the tables; I forgot to grant everything to SYSDBA for the tables.

thx
Mario
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.