IntegerValue.ToString or StringValue?

Desktop & Console
#1

Hello Everyone,

Both of these commands seem-to work equally, and which is the best (subjective) way to write data that comes from a SQLite database?

This makes it clearer to the programmer that this is an integer value and may make it easier to work with this data in the future. The code is longer though.
or

This converts everything in the column to a string, which is a smaller line of code, although it could be ambiguous to a programmer when going through many lines of code.

Both are correct and work well, and what would your suggestion be to someone that is starting to use the Xojo language, or has used Xojo for a while?

Thanks for your opinion :slight_smile:

#2

That is the form I use. The thought is that the other way is 2 methods calls vs 1 and the data type is defined in the DB schema, and I do prefer shorter code.

-Karen

1 Like
#3

Flip a coin :slight_smile:

This conversion to String is all done in the plugins C/C++ code and may be ever so slightly quicker

this requires 2 conversions

  1. from the db native to integer (in the plugin)
  2. from integer to string (in xojo’s framework)

But I doubt you’d ever notice the speed difference

1 Like
#4

Please be aware, that Xojo may internally convert all values to string for the record set.
So asking for integerValue may do a val() call internally and ToText convert it back to string.

2 Likes
#5

Hard to know without access to Xojo’s code unless you point a debugger at the code & disassemble it
(but that would be a EULA violation)
Many db’s have API’s for directly moving values into native types like integers, doubles etc without having to use strings for each retrieved value. I’d expect Xojo uses those and only converts when necessary.
But without code … who knows ?

#6

If you do insert via DatabaseColumn in the plugin SDK, you get all column values as string.
The plugin than converts back to what data type is needed.

#7

We’re not talking about inserts here though

#8

You are right. And it looks like the MySQL plugin from Xojo does use native types to return data in recordset.

So there is a good change that StringValue and IntegerValue.ToString will do the same, but I assume the second one takes more time.

#9

That would be my expectation a well
Conversion to StringValue would need to convert the native to a string then return it
It would add overhead but it shouldnt be big unless you’re dealing with a huge number of records at which point string creation, locking, etc may add enough overhead you would want to avoid that extra cost

#10

Thanks for the good discussion.

In summary, it seems like there likely is very little difference. If there is a heavy load on the database, then StringValue is the slightly faster method to use.

:slight_smile:

#11

I’d expect stringvalue to be slower

1 Like
#12

Thanks Norman. :slight_smile:

#13

Why? At worst i would have expected:
TFID.Text = rs.Column(“ID”).StringValue

to be at least as fast as
TFID.Text = rs.Column(“ID”).IntegerValue.ToString

I (and most it seems) would intuitively expect it to be faster!

-karen

#14

had to go back & reread

yes… StringValue should be marginally (and I do mean marginally) faster than Integer.ToText
and I’d expect both to be slower than integerValue since they have to convert from a native integer to a string

that christian sees that everything is treated as strings on inserts is somewhat disconcerting since I’m sure every db has an API for handling native types
And if you give on it should be used as is not converted to a string then inserted

EDIT :
using .StringValue to retrieve 102401024 ints took 50142546.863037 microseconds
using .IntegerValue.ToString to retrieve 102401024 ints took 130769368.690918 microseconds

I actually expected the second one to be faster than this since I had run the two queries one after the other & expected some degree of caching would improve the second by more than it apparently did :slight_smile:

1 Like
#15

I know this issue exists for PostgreSQL. This feedback case demonstrates that the plugin coerces prepared statement parameters into strings. The bug is several years old, but as usual they have not addressed it, even in API2.

I infer from this behavior that they are not using any type of db API to execute the prepared statement, but are just issuing standard SQL queries the same as you would type into a db query tool. So they are using the standard Xojo functions to convert all parameters to their string representation for concatenating into the SQL query string.

1 Like