4

My configuration:

Delphi XE
Firebird 2.1
IBObjects 4.9.12
Windows 7 64bits

I get an exception when I try to set a value to a IBOQuery parameter ("Could not convert variant of type (UnicodeString) into type (Double)").

The exception is raised from TIB_Column.SetAsVariant procedure in IB_Components.pas (line 42795). To create this situation, just try to pass a string to a date parameter:

myQuery.paramByName('mydate').AsString := DateToStr(IncDay(Now,5));

During last 25 days I'm trying to solve this situation, but in IBO support list I've got no answers.
Someone have an idea?

Hugues Van Landeghem
  • 6,755
  • 3
  • 34
  • 59
TiagoTecchio
  • 308
  • 3
  • 9
  • 3
    What type is your date field in the database? Suppose, for example, it is a Delphi TDateTime, then converting to string is wrong. You should just use something like: `myQuery.paramByName('mydate').AsDateTime := IncDay(Now,5);` The right answer will depend on the type of data used in the database field. – David Heffernan Jan 24 '11 at 11:42
  • That's the message raised by VarCastError from the Variants unit when a bad conversion is attempted; Try building the application with "Use Debug DCU's" and you'll see it raised from Variants.pas; My guess: If IBObjects has a way of assigning an "data type" to the parameter, your parameter is defined as DATE (TDateTime = Double in Delphi, and Double is a sort of Float). Shows us the code in IB_Components.pas at the given line number; – Cosmin Prund Jan 24 '11 at 12:01
  • 1
    Database field is DATE. As I said, in Delphi 2007 it works fine... I did not try in Delphi 2009/2010 but I guess is an Unicode issue. – TiagoTecchio Jan 24 '11 at 12:23
  • 1
    Well then, IBO probably got smarter. Firebird itself is perfectly capable of transforming a STRING into a DATE, and the previous version of IBO probably passed the string unchanged. None the less, the error is actually an good thing, because passing DATE values as STRING is a very bad idea: Firebird assumes your date is in one of two accepted formats, both fixed and unrelated to the Windows Locale. DateToStr() converts according to your locale, so if you run into an user that has the date format set up as "DD.MM.YYYY", the double conversion would fail. – Cosmin Prund Jan 24 '11 at 12:56
  • @Cosmin thanks for your tips. I'll pay more attention to this from now. – TiagoTecchio Jan 24 '11 at 15:48

1 Answers1

3

IBObjects's architecture is converting(at a moment of execution) all parameters, fields, etc to String or Variants. If your 'mydate' parameter is 'DateTime'(numeric) type then you must fill it up with a corespondent type value. Is not logic to fill an 'numeric' type parameter with a string...

try this

myQuery.paramByName('mydate').AsDateTime:= Now+5; //is the same as David's answer.

or

myQuery.paramByName('mydate').AsFloat:=Now+5; //or IncDay(Now,5)

Best regards,
Radu

RBA
  • 12,337
  • 16
  • 79
  • 126
  • 1
    I guess the database behind this could be holding the date using something other than Delphi's `TDateTime`. – David Heffernan Jan 24 '11 at 11:59
  • 1
    Converting from "STRING" to "STRING or VARIANT" should never fail. Converting from STRING to DateTime might fail, especially if the string is the result of DateToStr() – Cosmin Prund Jan 24 '11 at 12:06
  • 1
    Radu, thank you for your answer. I've tried this and works fine, but the problem is to find all "ParamByName" and fix. In previous Delphi versions, it works fine. – TiagoTecchio Jan 24 '11 at 12:16
  • Try using GExpert's Grep Search utility. Or by using Delphi's Search function from the IDE (Search in files). – RBA Jan 24 '11 at 12:20
  • 1
    Yes of course, Grep Search is a great utility. But, is frustrating to see a functionality that works fine for years suddenly not works anymore. – TiagoTecchio Jan 24 '11 at 13:48
  • @Tiago It probably didn't work before. As soon as you take your app to a machine with date format settings that didn't match Firebirds then you would have had problems. – David Heffernan Jan 24 '11 at 14:16
  • @Tiago What's more, I can't believe it's hard to find all the date fields. You must know their names? They'll be in your source code wrapped inside single quotes. Basic Delphi Find in Files will get the job done in no time. If you have hundreds of uses then consider factoring your code better so that it is more amenable to change and centralise the database field setting. – David Heffernan Jan 24 '11 at 14:17
  • @David Thanks for your answers, I know I'm capable to replace all entries that might go wrong, but as I said, in my case, setting a string to date parameter always worked perfectly. I'm no sure about other surprises I'll get during the migration from Delphi 7/2007 to XE - this seems like the top of iceberg. – TiagoTecchio Jan 24 '11 at 15:45