hi,
i have a MSSQL table with fields defined as ‘Money’ type
i can’t see a type for this in either odbcpreparedstatement or mssqlpreparedstatement
what is the correct bind type please?
Russ
I’m in the same boat. I don’t see a currency bind type for SQLitePreparedStatement either. That’s where I get a little confused.
And if SQLITE_DOUBLE will do the same, then why is there a DatabaseField property, CurrencyValue? Couldn’t we just use DoubleValue and get the same result? Or does CurrencyValue just tell it not to round the floating-point number when we pull it from the database?
I posted this as a response to my General forum post a few days ago, but it’s more relevant here. It was suggested by Christian S to change my code to use a prepared statement.
In the LostFocus event of a money text field I have:
[code]Sub LostFocus()
dim ps as SQLitePreparedStatement
dim sql as String
dim c as Currency
c = me.text.CurrencyVal
me.text = Format( c, kCurrencyFormat )
sql = “UPDATE Record SET price = ? WHERE id = ?”
ps = App.db.Prepare(sql)
ps.BindType(0, SQLitePreparedStatement.SQLITE_DOUBLE)
ps.Bind(0, c)
ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(1, id)
ps.SqlExecute
End Sub[/code]
[code]Function CurrencyVal(Extends s As String) As Currency
dim v as Variant
v = Round(s.CDbl * 100) / 100 //Round to 2 decimal places
return v.CurrencyValue
End Function[/code]
hi Mark S,
after some digging around it seems money may actually be an int64 with some formatting
not that that helps me because bigint doesn’t work for me either …
There is no Currency type in SQLite, per se, and although I couldn’t find it, I assume Currency is an alias for Numeric. At any rate, SQLite is a special animal since you can define your column as Text but still pull out Currency. The RecordSet will round it to the 4th decimal place.
See:
Russ,
I actually began using Int64, but was convicned to make the switch to using the Currency Data type. See this thread.
Kem,
The XOJO SQLiteDatabase documentation here says, “Use the REAL data type to store Currency values in conjunction with DatabaseRecord.CurrencyColumn and DatabaseField.CurrencyValue.” But I don’t see how this could work since SQLite can only store floating point anyway.
SQLite takes the data types as mere suggestions. You can store any type of data in any type of column, so Xojo fakes it. 
Kem,
Are you saying I should define my money field as TEXT when creating the table? If so, when using a prepared statement does it matter if I then use SQLITE_TEXT or SQLITE_DOUBLE when inserting?
It doesnt really matter with SQLite, but no, I think you should define it as NUMERIC, and I don’t think the bind type matters when you store it. Text will work just as well as Double.
BTW, if you use a RecordSet for your update, it’s the same as using a PreparedStatement, if that helps you.
Thanks Kem that does clear things up a lot. Just one more thing, I assume InsertRecord is also the same as using a PreparedStatement?
Right.