SQLBindParameter error with TFDQuery / ODBC

[Nathan Wild 3]

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?!

 

 

[haentschman 92]

Hi...

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 November 22, 2018 by haentschman

[Nathan Wild 3]

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

[haentschman 92]

Hi...

Quote

when I call fdquery.Refresh

sorry...no notion

[p80286 1]

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

[Dmitry Arefiev 101]

This is known FireDAC issue:

https://quality.embarcadero.com/browse/RSP-20857

[Nathan Wild 3]

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?

[Dmitry Arefiev 101]

https://quality.embarcadero.com/browse/RSP-20857

Will be fixed in 10.3 Update 1

[Nathan Wild 3]

Lovely...  but where does that leave those of us who only have 10.1? 😞

[Nathan Wild 3]

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

 

 

[Nathan Wild 3]

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?

 

[eivindbakkestuen 47]

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.

[Nathan Wild 3]

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 😞