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

Databases
#1

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.

#2

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

#3

[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
#4

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.

#5

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

#6

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
#7