IllegalCastException: _VariantString cannot be cast to Date

Databases
1

I would like to convert my app to API 2.0, but I got an error message, when I try to get a value from a SQLite DateTime database field.

Error: IllegalCastException: _VariantString cannot be cast to Date

// API 2.0

var sql as string
sql = "SELECT pur_date, pur_price, pur_currency, pur_ordernumber, " +_
"categories.catname, arch.pname FROM license " +_
"LEFT JOIN categories ON catid = categories.serial " +_
"LEFT JOIN arch ON platform = arch.serial "

var KDate as DateTime
Var rowsFound As RowSet

Try
rowsFound = MyDB.SelectSQL(sql)
If rowsFound <> Nil Then

For Each row As DatabaseRow in rowsFound
  KDate = row.DateTimeColumn("[b]pur_date[/b]")
Next
rowsFound.Close

End if

Catch error as DatabaseException
MessageBox("Error: " + error.Message)
End Try

2

Are-you sure for this line syntax:

KDate = row.DateTimeColumn("pur_date

How do you write data in “pur_date” ?

Maybe: KDate.SQLDateTime = … ?

3

Yes. When I change it to Date.SQLDateTime, I got this error:

wList.FilterEntries, line 130
Type mismatch error. Expected String, but got class DateTime
KDate.SQLDateTime = row.DateTimeColumn(“pur_date”)

4

When I change it to “KDate.SQLDateTime = row.Column(“pur_date”)”, the error is also IllegalCastException.

5

Shouldn’t it be KDate = row.Column("pur_date").DateTime ?

6

[Code]
Try
rowsFound = MyDB.SelectSQL(sql)

If rowsFound <> Nil Then

While Not rowsFound.AfterLastRow

  KDate = row.Column("pur_date").DateTime
  rowsFound.MoveToNextRow

Wend

rowsFound.Close // You can leave that one out. rowsFound will be trashed after you have left the Method/Event ;)

End if

Catch error as DatabaseException
MessageDialog.Show( CurrentMethodName + " - Error: " + error.Message )
// System.Log( System.LogLevelError, CurrentMethodName + " - Error: " + error.Message )
End Try[/Code]

I am not at my Dev.Desk. Code written without Xojo IDE… :slight_smile:

8

row.Column is a string and it doesn’t accept .DateTime.

wList.FilterEntries, line 131
Type “String” has no member named “DateTime”
KDate = row.Column(“pur_date”).DateTime

I can only use:
KDate = row.DateTimeColumn(“pur_date”) or KDate = row.DateColumn(“pur_date”), if Kate is as Date.

With the old API1 recordSet there is now problem with DateTime value from a SQLite field.

9

This is now working:

var rowsFound As RowSet
var KDate as DateTime
var sql as string

sql = “SELECT appname, pur_date FROM license”

Try
rowsFound = MyDB.SelectSQL(sql)

If rowsFound <> Nil Then

While Not rowsFound.AfterLastRow
  
  if rowsFound.Column("pur_date").StringValue.Length > 9 then
    KDate = rowsFound.Column("pur_date").DateTimeValue
  end if
  rowsFound.MoveToNextRow
  
Wend
system.DebugLog "Date: " + KDate.SQLDateTime

End if

Catch error as DatabaseException
MessageDialog.Show( CurrentMethodName + " - Error: " + error.Message )
End Try

10

No, it works just fine if the Column is really a DateTime Column.

But, if the Column is just a VarChar/String Column which contains DateTime Strings, why not just do a KDate = DateTime.FromString( rowsFound.Column("pur_date").StringValue ) ?

As long as you are catching Exceptions and handling them accordingly, you should be fine.

[code]Try

rowsFound = MyDB.SelectSQL(sql)

If rowsFound <> Nil Then

While Not rowsFound.AfterLastRow

KDate = DateTime.FromString( rowsFound.Column(“pur_date”).StringValue )
rowsFound.MoveToNextRow

Wend
system.DebugLog "Date: " + KDate.SQLDateTime

End if

Catch error as DatabaseException
// KDate = DateTime.Now // or whatever makes sense here :slight_smile:
system.DebugLog "Date (Calculated): " + KDate.SQLDateTime
MessageDialog.Show( CurrentMethodName + " - Error: " + error.Message )
End Try[/code]

11

Did you checked in the debugger the object types ?

12

He knows the Types. I think he expected that the autoconversion from String to DateTime was a valid one, but It’s not. But in this case, the FromString Method can be helpfull.

13

If the first example we have these items:
rowsFound As RowSet
row as DatabaseRow

A DatabaseRow has these values
BlobColumn
BooleanColumn
Column
CurrencyColumn
DateColumn
DateTimeColumn
DoubleColumn
Int64Column
IntegerColumn
PictureColumn

All these value a working except DateColumn and DateTimeColumn. My field in the database is a DateTime field.

In the second example we have these items:
rowsFound As RowSet
rowsFound.Column as DatabaseColumn
rowsFound.Column has the same values like row in the first example, because it is the same type.

The difference is this:

  1. Example
    KDate = row.DateTimeColumn(“pur_date”)
  2. Example
    KDate = rowsFound.Column(“pur_date”).DateTimeValue

I don’t know why I can use .DateTimeValue in rowsFound.Column, but not in row.DateTimeColumn or row.Column. Why row.Column(“pur_date”). has only the values?
CLong
Encoding
Split
ToArray
ToInt64
TotText

14

To be honest, i also do not have an answer to that. Maybe worth a FR? :wink: