IBQuery insert - Column unknown / Unsupported feature

Question

Im trying to insert a row into a firebird database (embedded), but geting an exception when calling:

datamodule1.IBQuery1.prepare

Project xyz.exe raised exception class EIBInterBaseError with message 'Dynamic SQL Error SQL error code = -206 Column unknown INDEX_ At line, column 25'.

  with datamodule1.IBQuery1 do
  begin
    close; 
    With SQL do
    begin
      clear; 
      Add( 'INSERT INTO MST_EVENTS (eventindex, state_, event_, param_, date_, time_, devID_, gateway_)' );
      Add( 'VALUES (:eventindex, :state_, :event_, :param_, :date_, :time_, :devid_, :gateway_') );
    end;
    //
    GeneratorField.Field := 'Nr_';
    GeneratorField.Generator := 'GEN_MST_EVENTS_ID';
    //
    Params[0].AsInteger := FMst.EventRecordIndex; 
    Params[1].AsSmallInt := FMst.EventRecordState; 
    Params[2].AsString := eventToStr(FMst.EventRecordEvent);          
    Params[3].AsSmallInt := 0;
    Params[4].AsDate := FMst.EventRecordDate; 
    Params[5].AsTime := FMst.EventRecordTime; 
    Params[6].AsLongWord := FMst.EventRecordDevID; 
    Params[7].AsString := FMst.EventRecordIP; 
    //
    if ( prepared = false ) then
      prepare;  //Throws an exception here (SOLVED)
    execSQL;    //Now getting exception here 
  end;

I have the following components tied together:

IBDatabase
IBTransaction
DataSource
IBQuery

Above problem solved - Edit >>

Ok, i have changed

Add( 'INSERT INTO MST_EVENTS (eventindex, state_, event_, param_, date_, time_, devID_, gateway_)' );

to

Add( 'INSERT INTO MST_EVENTS ("eventindex", "state_", "event_", "param_", "date_", "time_", "devID_", "gateway_")' );

... (so im using quotation marks) and now it finds the fields, but get another exception at line:

 IBQuery1.execSQL:

Exception class EIBClientError with message 'Unsupported feature'

My fields are:

Nr_        : INTEGER
eventindex : INTEGER
state_     : SMALLINT
event_     : VARCHAR(50)
param_     : SMALLINT
date_      : DATE
time_      : TIME
devID_     : BIGINT
gateway_   : VARCHAR(50)

Firebird version is 2.5 embedded 32bit

I took out all the string and date/time parameters, yet i get the exception. Using IBExpert and the same client/server .dll i can insert the row flawlessly (using all the values).

Slow down. Make sure you have all the column changes committed and that all your column names matches to those listed in the query. Using quotes should work. What is mismatched are the names of `devID_` and `gateway_` column names. But hard to say *which version* have you posted here. — TLama, Aug 06 '14 at 13:43
@Tlama sorry, i modified the code i pasted here, but the first part of the problem was already solved, now faced with another exception... — john_who_is_doe, Aug 06 '14 at 13:48
@Abelisto sorry, yes its just another thing i left out from the pasted code, but in the code editor its ok, so thats not the problem. — john_who_is_doe, Aug 06 '14 at 14:02
I edited my question so hopefully there are no more mistakes. — john_who_is_doe, Aug 06 '14 at 14:14
The error _EIBClientError with message 'Unsupported feature'_ (and the distinction between client and server with `EIBInterBaseError`) seems to indicate you are using a feature not implemented in the component. — Mark Rotteveel, Aug 06 '14 at 14:24
@MarkRotteveel now im 'only' getting the 'Unsupported feature' exception, but i left the topic name as it is, because it can help finding solution for others, related to my first problem. — john_who_is_doe, Aug 06 '14 at 14:29
@tdiop I understand, but I want to indicate that the problem is most likely client side not server side. But as I don't know Delphi nor the component I can't offer further insights). I can suggest to try to simplify (eg leave out the `GeneratorField`, try without parameters, etc) and to check the documentation of the component — Mark Rotteveel, Aug 06 '14 at 14:55
Another hint: Call `prepare` before you set parameters values or do not call it at all. — Abelisto, Aug 06 '14 at 17:33
_Unsupported feature_ can often be related to the driver you're using. Verify that you're using fbclient.dll found in the syswow64 folder if you're using 64 bit server and 32bit client. It's possible that your connection is using gds32.dll which may/may not be the problem. — Jason, Aug 07 '14 at 00:26
Just to make a test try to insert `Nr_` value explicitly without using a generator and see what will happen. — Wodzu, Aug 07 '14 at 05:46
@Jason the fact that before (with unquoted columns) he got an error from the server seems to indicate that the driver is loaded correctly and working. — Mark Rotteveel, Aug 07 '14 at 06:25
I tried everything you guys came up with (thank you!), but no luck. Btw im using fb 2.5 embedded 32bit (tried x64 version also but with that i cannot even connect to the database. — john_who_is_doe, Aug 07 '14 at 08:27

john_who_is_doe · Answer 1 · 2014-08-07T12:20:36.773

The solution was changing line

Params[6].AsLongWord := FMst.EventRecordDevID;

to

Params[6].AsLargeInt := FMst.EventRecordDevID;

But please how to auto-increment the field 'Nr_'?

  with datamodule1.IBQuery1 do
  begin
    close; 
    With SQL do
    begin
      clear; 
      Add( 'INSERT INTO MST_EVENTS (eventindex, state_, event_, param_, date_, time_, devID_, gateway_)' );
      Add( 'VALUES (:eventindex, :state_, :event_, :param_, :date_, :time_, :devid_, :gateway_') );
    end;
    //
    GeneratorField.Field := 'Nr_';
    GeneratorField.Generator := 'GEN_MST_EVENTS_ID';
    //
    Params[0].AsInteger := FMst.EventRecordIndex; 
    Params[1].AsSmallInt := FMst.EventRecordState; 
    Params[2].AsString := eventToStr(FMst.EventRecordEvent);          
    Params[3].AsSmallInt := 0;
    Params[4].AsDate := FMst.EventRecordDate; 
    Params[5].AsTime := FMst.EventRecordTime; 
    Params[6].AsLargeInt := FMst.EventRecordDevID; 
    Params[7].AsString := FMst.EventRecordIP; 
    //
    if ( prepared = false ) then
      prepare;  //Throws an exception here (SOLVED)
    execSQL;    //Now getting exception here 
  end;

I made the generator in flamerobin.

enter image description here

But getting exception (at calling 'execSQL'): Validation error

EDIT >>

I set up a generator and a BEFORE INSERT trigger in IBExpert:

enter image description here

And now its ok.

You really shouldn't ask (and then answer) another question in an answer. I suggest that you post another question and answer it yourself, and then remove that part from this answer. — Mark Rotteveel, Aug 07 '14 at 16:36
@MarkRotteveel Yes you are right, but please just let me leave this here as it is. I completed my question with the 'auto-increment' tag, so someone can find the answer for that question too. Next time i will be more consistent. — john_who_is_doe, Aug 08 '14 at 05:56

IBQuery insert - Column unknown / Unsupported feature

1 Answers1