Using Firebird 2.1.3, Delphi XE2 Update 1

Database with a table defined as:

CREATE TABLE TEST1 (
    ID    INTEGER,
    NAME  VARCHAR(10)
);

{code}
  qryWork.SQL.Text := 'update test1 set NAME = :XNAME where ID = :XID';
  qryWork.PrepareStatement; (* After this the query is NOT prepared, at least qrywork.Prepared = false *)
  qryWork.Params[0].AsString := 'foo1';
  qryWork.Params[1].AsInteger := 1;
  qryWork.ExecSQL;
{code}

The above results in an error: qryWork: Unable to determine field names for %s.

SQLMonitor component outputs this:

INTERBASE - isc_dsql_allocate_statement
INTERBASE - isc_start_transaction
update test1 set NAME =  ?  where ID =  ? 

INTERBASE - isc_dsql_prepare
INTERBASE - isc_dsql_sql_info
INTERBASE - isc_portable_integer
INTERBASE - isc_dsql_describe_bind
INTERBASE - isc_commit_transaction
INTERBASE - isc_dsql_free_statement
INTERBASE - isc_dsql_free_statement

Seems like parameterized TSQLQuery is broken in this situation.  I tried various combinations of flag settings on the TSQLQuery component (GetMetadata, NumericMapping, ObjectView, ParamCheck).  The TSQLQuery.Prepared is always FALSE even after a call to PrepareStatement (which doesn't result in any errors at the application at least).

Any thoughts?  I've prepared a simple example program and database so I can try things quickly/easily.  And of course it could be packaged up for a bug report if need be.

I just tried the new DBX FB driver posted here:

http://groups.google.com/group/dbxfirebird/browse_thread/thread/7616ebee828906b9

Same behaviour.  I also posted this message to the dbxfirebird forum...

> {quote:title=brenden walker wrote:}{quote}
> Using Firebird 2.1.3, Delphi XE2 Update 1
> 
> Database with a table defined as:
> 
> CREATE TABLE TEST1 (
>     ID    INTEGER,
>     NAME  VARCHAR(10)
> );
> 
> {code}
>   qryWork.SQL.Text := 'update test1 set NAME = :XNAME where ID = :XID';
>   qryWork.PrepareStatement; (* After this the query is NOT prepared, at least qrywork.Prepared = false *)
>   qryWork.Params[0].AsString := 'foo1';
>   qryWork.Params[1].AsInteger := 1;
>   qryWork.ExecSQL;
> {code}
> 
> The above results in an error: qryWork: Unable to determine field names for %s.
> 
> SQLMonitor component outputs this:
> 
> INTERBASE - isc_dsql_allocate_statement
> INTERBASE - isc_start_transaction
> update test1 set NAME =  ?  where ID =  ? 
> 
> INTERBASE - isc_dsql_prepare
> INTERBASE - isc_dsql_sql_info
> INTERBASE - isc_portable_integer
> INTERBASE - isc_dsql_describe_bind
> INTERBASE - isc_commit_transaction
> INTERBASE - isc_dsql_free_statement
> INTERBASE - isc_dsql_free_statement
> 
> Seems like parameterized TSQLQuery is broken in this situation.  I tried various combinations of flag settings on the TSQLQuery component (GetMetadata, NumericMapping, ObjectView, ParamCheck).  The TSQLQuery.Prepared is always FALSE even after a call to PrepareStatement (which doesn't result in any errors at the application at least).
> 
> Any thoughts?  I've prepared a simple example program and database so I can try things quickly/easily.  And of course it could be packaged up for a bug report if need be.

I have the same problem problem in c++builder XE2 (update 1)....

So i guess my question is the same as the op.

Solved my "Unable to determine field names for %s." problem....

I did not read the TSQLQuery documentation very well (my own stupid fault i gues...):
 [http://docs.embarcadero.com/products/rad_studio/delphiAndcpp2009/HelpUpdate2/EN/html/delphivclwin32/SqlExpr_TSQLQuery_PrepareStatement.html] 

Do not call PrepareStatement when preparing a query for execution. Instead, set the Prepared property to true. When you use the Prepared property instead, the query keeps track of the fact that it has already been prepared and need not repeat the process every time it executes the query. In addition, when you set the Prepared property, the query initializes internal properties that keep track of the number of rows affected by the query. 

{code}
SQLQuery1->SQL->Clear();
SQLQuery1->SQL->Add(string2ustring(y));
SQLQuery1->Prepared = true;
SQLQuery1->Params->Items[0]->AsAnsiString = "Alexander";
if (SQLQuery1->Prepared)
{
	SQLQuery1->ExecSQL();
	ClientDataSet1->Active = true;
}
else
{
	ShowMessage("Error preparing statement");
}
{code}

Also i have to say that i use sql server. So i dont know this will work for the op too.

Yack, I figured .Prepared was readonly.

Now I get a different error.

Assertion failure
c:\Users\coder\Project\source\moon\core\source\firebird\firebird.dsql.pas line 1208

Sounds like that's down in FB driver code I'd guess, so I'll be off to the Google group for this.

** I just went back to using the FB driver included with Delphi XE2 and the problem is resolved.  

Seems to me that if we're not supposed to use PrepareStatement an error would be appropriate?

Edited by: brenden walker on Oct 17, 2011 6:12 AM

> {quote:title=brenden walker wrote:}{quote}
> Yack, I figured .Prepared was readonly.
> 
> Now I get a different error.
> 
> Assertion failure
> c:\Users\coder\Project\source\moon\core\source\firebird\firebird.dsql.pas line 1208
> 
> Sounds like that's down in FB driver code I'd guess, so I'll be off to the Google group for this.
> 
> ** I just went back to using the FB driver included with Delphi XE2 and the problem is resolved.  
> 
> Seems to me that if we're not supposed to use PrepareStatement an error would be appropriate?
Could anybody shed a light here? I'm getting the error 'Column unknow' for the parameterized update/insert query with firebird. (the select query is ok though).
What is the FB driver with XE2 different from the one otherwise? I'm using XE2 so that should be arelady installed?
Thanks
changsong

> {quote:title=Changsong Bu wrote:}{quote}
> > {quote:title=brenden walker wrote:}{quote}
> > Yack, I figured .Prepared was readonly.
> > 
> > Now I get a different error.
> > 
> > Assertion failure
> > c:\Users\coder\Project\source\moon\core\source\firebird\firebird.dsql.pas line 1208
> > 
> > Sounds like that's down in FB driver code I'd guess, so I'll be off to the Google group for this.
> > 
> > ** I just went back to using the FB driver included with Delphi XE2 and the problem is resolved.  
> > 
> > Seems to me that if we're not supposed to use PrepareStatement an error would be appropriate?
> Could anybody shed a light here? I'm getting the error 'Column unknow' for the parameterized update/insert query with firebird. (the select query is ok though).
> What is the FB driver with XE2 different from the one otherwise? I'm using XE2 so that should be arelady installed?
> Thanks
> changsong

Have you tried the workaround, set the query.Prepared := TRUE rather than calling the .PrepareStatement method?  If that doesn't work, I suspect you're having a different issue.

Yes I used Prepared=true instead of calling PrepareStatement.
The wield thing is the select statement works, while update/insert don't.
For example, this works
	SQLQuery1->SQL->Add("select * from MasterTbl  WHERE PID = :pra1");
	SQLQuery1->Params->Items[0]->AsString = "aaa";
	SQLQuery1->Open();
	SQLQuery1->First();

	if (!QueryKB->Eof)
	{
		int i = SQLQuery1->Fields->FieldByNumber( 1 )->AsInteger;
.....
	} .........................
------------------------------------------------------------------------------------------------------------------

Howerver the following doesn't work. It give the 'Column unknow PRA1' error. If I just hardcarded the value instead of using the parameterized query, it works also.
	SQLQuery1->SQL->Add("update  MasterTbl set PIndex = 3 WHERE PID = :pra1");
	SQLQuery1->Params->Items[0]->AsString = "aaa";
	SQLQuery1->ExecSQL(true);

I don't see a problem with oracle database. So this seems a problem with firebird driver.

You mentioned after switching to use the dbx driver from XE2 it worked. Could you explain this please? What file did you change?
Thanks a lot.
Changsong

> {quote:title=Changsong Bu wrote:}{quote}
.....snip
> 
> I don't see a problem with oracle database. So this seems a problem with firebird driver.
> 
> You mentioned after switching to use the dbx driver from XE2 it worked. Could you explain this please? What file did you change?
> Thanks a lot.
> Changsong

There was a new DBX driver posted google groups, the actual DLL is dbx4fb.dll.. the dbx driver that came with XE2 is named dbxfb.dll.

These are specific to Firebird SQL, so not going to be much help for oracle.  

You might want to start a new thread and mention oracle in the subject.. perhaps someone out there has seen this before.

Good luck, wish I could help further.

brenden : Thank you!

Just realized I made a mistake here:

SQLQuery1->ExecSQL(true); // got 'Column unknow PRA1' error.

should be
SQLQuery1->ExecSQL(false); // no error now
or
SQLQuery1->ExecSQL(); // no error now