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

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

×