Heads up with API2 DatabaseColumn.Value... Slight Change from DatabaseField.Value

Just a heads up. I filed <https://xojo.com/issue/58934> as I am hoping it is a bug and not a feature:

With the old database API, DatabaseField.Value returns a variant of the same type as the database table field. If the table has an integer column, the value will return an integer variant.

With database API2, DatabaseColumn.Value returns a string variant regardless of the type of data in the column.

As the documentation says, “Typically you should use the type-specific “Value” properties to get and set values from the database.” I agree, except if you have a method that takes a recordset, with the old API the method can figure out the data type of the columns. But now with RowSet, the method will find that all columns are String.

are you sure that it not converted the return value to your variable?

[quote]Var interestRate As Double
interestRate = rs.Column(“InterestRate”).Value // Converts from a Variant to a Double[/quote]

if you assign to

v.Type is TypeString 8 String ? mentioned here documentation.xojo.com/api/data_types/variant.html

[quote=472672:@Brandon Warlick]Just a heads up. I filed <https://xojo.com/issue/58934> as I am hoping it is a bug and not a feature:

With the old database API, DatabaseField.Value returns a variant of the same type as the database table field. If the table has an integer column, the value will return an integer variant.

With database API2, DatabaseColumn.Value returns a string variant regardless of the type of data in the column.

As the documentation says, “Typically you should use the type-specific “Value” properties to get and set values from the database.” I agree, except if you have a method that takes a recordset, with the old API the method can figure out the data type of the columns. But now with RowSet, the method will find that all columns are String.[/quote]

That would be a huge issue for some generic DB handling code I have written if I had started using API 2!

  • karen

The actual data type for the column is available in the FieldSchema. Assuming it from the Record/RowSet variant is an unreliable approach. You might consider updating this before making the move to API 2.

Using the FieldSchema will be difficult if the RowSet comes from joined tables and you have a generic method that processes the results.

I do a lot of that paricualry using views created from multiple tables… being able to do that that is kind of the point of using a relational database. :wink:

  • Karen