http://coldfusion.com logo
Join Slack
Powered by
Hello Everyone! I am stuck in finding the right CF...
# lucee
w
Hello Everyone! I am stuck in finding the right CF_SQL_TYPE for the cfqueryparam. The DBMS is PostgresSQL and the table column data type is “money”. I tried several cf_sql_type but it not works. Also see at https://docs.lucee.org/guides/cookbooks/Sql-Types.html but not find any “money” type. What is the solution? change the column type in postgreSQL or there any type that works with cfqueryparam for “money”? Thank you!
c
https://cfdocs.org/cfsqltype-cheatsheet If CF_SQL_MONEY is not supported by Lucee.. then perhaps CF_SQL_DECIMAL
a
Yeah I was gonna suggest CF_SQL_DECIMAL.
(not based on any awareness of PgSQL, that said; but it's the right type)
(I googled "jdbc postgresql money")
Whatever you choose... write integration tests to verify nothing daft goes on between Lucee & PgSQL vis-a-vis precision / rounding / floating point inaccuracies (Lucee end, the rest of it won't be impacted) if you are doing arithmetic on the values though. For all currencies you use.
If you are storing data for product pricing (so: real world "whole" units of currencies, eg £ and p), rather than financial values (where small fractions of units need to be stored), using an integer for the value and an offset for the fractional unit decimal places (eg: store it in pence, and store that 1p is 100th of a £) might be better from an accuracy perspective. Might not be an option.
w
@Adam Cameron I changed cfsqltype to money and decimal but for both same error ERROR: column “XXXXX” is of type money but expression is of type double precision Hint: You will need to rewrite or cast the expression. Position: 486
a
Did you read the thing I linked to above? Did you google the error you got?
Open in Slack
PreviousNext
Powered by