Jump to content
Nathan Wild

SQLBindParameter error with TFDQuery / ODBC

Recommended Posts

I am at a loss to explain this, but it feels like I am missing something obvious so I thought I would try here...

 

If I have a query that does a simple select * with two parameters it works properly.  If I modify the underlying table with another query, and then attempt to call .Refresh() or to activate and deactivate the select query I get a FireDAC ODBC error stating that SQLBindParameter has not been called for parameter #4.  Firstly, there are only two parameters.  Secondly, I don't ever call SQLBindParameter on anything.  If I  clear the SQL, and reset it, then reassign the parameters, it works fine?

 

The exact error message is: [FireDAC][Phys][ODBC][Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]SQLBindParameter has not been called for parameter #4.

 

Sample code:

 

fdquery.Active := FALSE;

fdquery.SQL.Clear();

fdquery.SQL.Add('SELECT * FROM MyTable WHERE Key1=:KEY1 AND Key2=:KEY2;');

fdquery.ParamByName('KEY1').AsString = 'value1';

fdquery.ParamByName('KEY2').AsString = 'value2';

fdquery.Active := TRUE;

// This works fine.

 

If I then execute an "INSERT INTO MyTable" query which generates data in [MyTable], and attempt to refresh the original query. I get the above error.  If I set Active := FALSE and then back to TRUE again, I get that error.

 

The only thing that seems to work is to clear and re-add the SQL, reassign the parameters, and then reactivate it.

 

Questions:

1) Why is this error occurring at all?

2) Why is it coming up with parameter 4 when there are only two parameters to begin with?!

 

 

Share this post


Link to post

Hi...:classic_cool:

Quote

// This works fine.

...rather not.

 

Why is this constellation always used?

fdquery.SQL.Clear();
fdquery.SQL.Add

instead of

fdquery.SQL.Text

?

 

Imho SQL.Text also resets the parameters. With SQL.Clear only the SQL is removed. The parameters are retained! Consistent would be however:

fdquery.SQL.Clear;
fdquery.Params.Clear;

PS: Better Open than Active = True, Close or Active = False is not necessary with Firedac

 

Give it a try:

fdquery.SQL.Text := 'SELECT * FROM MyTable WHERE Key1=:KEY1 AND Key2=:KEY2';
fdquery.ParamByName('KEY1').AsString = 'value1';
fdquery.ParamByName('KEY2').AsString = 'value2';
fdquery.Open;  

 

Edited by haentschman

Share this post


Link to post
On ‎11‎/‎22‎/‎2018 at 6:07 PM, Nathan Wild said:

I will try this!  Any idea why it would fail with that error when I call fdquery.Refresh()?

Without the complete Source code?

No idea.

 

K-H

Share this post


Link to post

So what is the best approach to do this?  My end result is that I want to allow the end user to input a key value and then pass that to a a query as a parameter.  This works perfectly the first time, but when I close the query, set a new parameter value and then re-open the query, I get this "SQLBindParameter has not been called for Parameter #2" error.  Sure;y clearing and re-setting the entire SQL statement is not required?

Share this post


Link to post

Bumping this one as I am still encountering this issue.  I have been able to work around it up to now, but I have a circumstance in my current project where I REALLY need what I think is a simple master/detail relationship to work properly.

 

Situation is this:

1) I have master data in one table

2) I have detail data in another table

3) Detail data is related to the master table through a GUID (stored as a string field).

4) There are either 0 or exactly 1 detail record for every master record in the table

5) I have a master FDQuery with SQL="SELECT * FROM MASTER" this works fine.

6) I have a detail FDQuery with SQL="SELECT Field FROM MASTER WHERE GUID=:GUID.  MasterSource is set to the datasource for the master query, MasterFields is set to GUID

 

When I open both queries it works perfectly until I try to move to the next record of the master dataset or to refresh it.  Then I get "[FireDAC][Phys][ODBC][Pervasive][ODBC Client Interface][LNA][Pervasive][ODBCE Engine Interface]SQLBindParameter has not been called for parameter #2'"

 

Sample project that reproduces this: https://www.dropbox.com/s/ol9us4i71my3gm7/ClientTest.zip?dl=0

 

 

Share this post


Link to post

UPDATE: I created a stand-alone MS-Access database for use with the above test application and the bug does not manifest.  Could it be problem specific to my ODBC driver?  If so, any idea how to even troubleshoot that?

 

Share this post


Link to post

Are you still using version 10.1? The issue was apparently fixed in 10.3.1, perhaps test with the Community version to make sure before you go for the upgrade.

Share this post


Link to post

Does anyone have a copy of 10.3.1 that they can build that test project in and send it back to me?  I am 99.9% sure that you can not connect to ODBC datasources in the community edition.  It is Enterprise-only functionality now 😞

 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×