"No value for parameter" headaches moving from D7 to D2007

[robsoft]

Hi folks - I hope I'm not about to ask a stupid question, but...

I have a big project that uses Corelabs SQLServer dbexpress driver in Delphi 7. I'm trying to port it to D2007 and am stuck.

I paid for an upgrade to version 4 (for D2007) of the DLL, which will arrive in the next day or so I guess, but in the meantime I'm trying to make this work with the trial DLL. I've also tried to make this work with the version 3 DLL, to no avail.

I have a lot of code where I use this kind of approach;

Code: Select all

with dmMain.qryWorker do
begin
  SQL.Text:='select col_1, col_2, col_3 from mytable where field1=:pValue1 and field2=:pValue2';
  ParamByName('pValue1').AsInteger:=whatever;
  ParamByName('pValue2').AsString:=whatever;
  Open;
  //etc
end;

where qryWorker is a straightforward TSQLQuery and dmMain is a datamodule.

Moving to D2007, this all works fine. UNLESS one of the parameters happens to be a datetime, at which point I get a "no value found for parameter 'blah' " message.

eg, in the example above, if I used

Code: Select all

ParamByName('pValue2').AsDateTime:=Now;

then it would crash with the no value found message when it hits the 'open' statement.

I've tried setting the 'bound' property to be true. I've tried explicitly telling it that the parameter's datatype is ftDateTime etc.... but it doesn't work.

Has something changed in dBExpress? Everything is the same (query, database, remote server) but either D2007 does something new/different with datetime parameters, or the DLL driver does - but either way I'm stuck.

Am I doing something 'wrong' for DBExpress (my background is usually ADO so I could be approaching this wrong)? I'm really puzzled because this works in D7 with the 3.0.3.0 version of the dbexpsda.dll file, and I didn't expect something as fundamental as passing parameters to the database to have changed that much.

Has anyone else encountered this problem? Due to the vague/possibly misleading nature of the error message you get back, it's been quite frustrating trying to google for similar reports.

If this is a Delphi issue, not a DBExpress issue, then please accept my apologies.

[robsoft]

Okay, I think I have a workaround. I'd appreciate anyone who might have been down this path giving me their opinion on this. I'm not sure if it's a DBExpress issues, a Delphi D2007 issue or a SQL Server Express/2005 issue, but either way I've got Delphi 7 code that works fine and just doesn't work in D2007.

Anyway, if I do this;

Code: Select all

// - commented out doesn't work in D2007
// ParamByName('pValue2').AsDateTime:=Now;
ParamByName('pValue2).AsSQLTimeStamp := DateTimeToSQLTimeStamp(Now);

then the queries seem to work. (It's a bad example, I know, but anything more substantial requires context and it just confuses the issue).

The datatypes on the SQL Server are NOT timestamps - they're very much DateTime datatypes, but this approach is appearing to work.

Anyone seen anything like this at all? Like I said, this wasn't an issue in D7 so something somewhere has changed between D7 and D2007. I'm starting to suspect it's inside Delphi actually, although I can't quantify why I feel this.

[Antaeus]

This is a dbExpress issue. Assigning parameter value thorough AsDateTime makes the DataType property of the parameter set to ftDateTime. When dbExpress maps data types to its internal format, ftDateTime is mapped to 0. See definition of FldTypeMap in SqlExpr.pas:

Code: Select all

... , 0{TDBXTypes.TIMESTAMP}, ...

0 means unknown data type (TDBXDataTypes.UnknownType). This causes the error.
To avoid this problem you can also use the following code:

Code: Select all

  SQLQuery1.ParamByName('dt').DataType := ftTimeStamp;
  SQLQuery1.ParamByName('dt').Value := now;
  SQLQuery1.Open;

[99Percent]

This really sucks, is there a fix soon?

Was it a bug by Borland?

[Antaeus]

This issue was discovered in RAD Studio 2007 of CodeGear. You can take a look at the code of the SqlExpr.pas unit to check whether this field type is restored in later builds of Rad Studio. For more information about this issue, refer to CodeGear support, please.