Adding values from Postgres DB

General
1

I am using Xojo 2022r4.1 with MacOS 13.4.1 and code from Xojo API1.

I am having an issue with adding values from a RecordSet from a Postgres 10.4 database.

For example, if I have a table transactions with the following columns:

charge float8
paid float8
credits float8

and a row with the following values:

Charge Paid
10 10

the following gives the the wrong answer.

dim s as String
dim rs as RecordSet
//Search record here
s = Str(rs.Field("paid").Value +rs.Field("credits").Value ,"###,##0.00")

The value returned for s is: 1,010.00
//Not 20.00

There are numerous instances in this project where values from the database are added using .value and I am wondering if there is something with the way this code is formatted causes the error.

Thanks for your help.

2

Looks like it’s treating them as strings. Something like:

Var  pd, cr, tot as Double

pd = rs.Field("paid").Value
cr = rs.Field("credits").Value
tot = pd + cr
s = str(tot, "###,##0.00")
3

True. I was able to correct this using the following:

s = Str(Cdbl(rs.Field("paid").stringvalue) + Cdbl(rs.Field("credits").stringvalue) ,"###,##0.00")

If this is now broken, I am wondering how many wrong values are everywhere in my code.

4

Why not DoubleValue, then you avoid converting with Cdbl.

5

I can see if that works. I didn’t realize that was an option.

6

That did work correctly. Thanks.

So I am guessing that Value returns a string in Xojo2022 if the value has a decimal.

In earlier versions of Xojo, was there a DoubleValue option? I had no knowledge of this.

7

See:

https://docs.xojo.com/DatabaseField

Time spent perusing the documentation is generally not wasted.

8

I agree. But check this out also from the docs:

Get the double value of a column in a RecordSet:

// rs is a RecordSet with a double column called “InterestRate”
Dim interestRate As Double
interestRate = rs.Field(“InterestRate”).Value // Converts from a Variant to a Double

It can get confusing at times.

9

Try this:

dim s as String
dim rs as RecordSet
//Search record here
s = Str(rs.Field("paid").DoubleValue +rs.Field("credits").DoubleValue ,"###,##0.00")

-Karen

10

Thank you. Yes, that works.

11

I would suggest that the comment is misleading. No conversion is being done. IOW, a variant is not like Schroedinger’s Cat: it has a definite state (type) before you look at it. As you have this definition:

then .Value returns a Double for those columns. But it will return an integer if the column was defined as integer. So although a variant will actually convert if it can, this won’t work:

Var  myvar as Variant, myint as Integer
myvar = new Dictionary
myint = myvar

Note that the compiler doesn’t pick this up; you get a runtime error.

12

Why not add the fields together in your SELECT. Such as: SELECT (paid + credits) as MYTotal

1 Like
13

That’s a thought. I think in this case a lot more data manipulation was needed, but for sure I will keep that in mind.