Using bind_text for numeric columns

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Using bind_text for numeric columns

curmudgeon
Suppose I have a query with several parameters on columns that could be integer, real or text. The values for these parameters are taken from the result set of another query which is stored in a string array.

Is there any pitfalls or disadvantages to binding them all as text as opposed to the more difficult process of checking the column type and converting the string value to that type? (Assume none of the values are NULL).

I’d like to store all the values from the first query as sqlite3_value rather than string (I could then use bind_value) but I’ve been unable to find a simple way of doing so.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using bind_text for numeric columns

Simon Slavin-3
On 18 Feb 2018, at 12:44pm, x <[hidden email]> wrote:

> Suppose I have a query with several parameters on columns that could be integer, real or text. The values for these parameters are taken from the result set of another query which is stored in a string array.
>
> Is there any pitfalls or disadvantages to binding them all as text as opposed to the more difficult process of checking the column type and converting the string value to that type? (Assume none of the values are NULL).

Be certain that your column affinities are set correctly in the table definition.  Otherwise you risk bad sorting orders and comparisons, because with string values 12 < 5 .

Apart from that, I think it should work.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using bind_text for numeric columns

curmudgeon
Thanks Simon. I’ve not encountered any problems so far but I would’ve preferred a sqlite3_value solution so I could be sure.

At the moment I’m messing about with a ‘dummy’ query ‘select ?1, ?2, .....,?n’ and binding the results I need from the first query to the dummy one using bind_value. When I need to retrieve the results I step over dummy and retrieve the column values using column_value and bind to the main query using bind_value. I’ve made it sound easier than it is (it’s certainly not as elegant as just using bind_text) so I’m not sure if I’ll persevere.

From: Simon Slavin<mailto:[hidden email]>
Sent: 18 February 2018 15:29
To: SQLite mailing list<mailto:[hidden email]>
Subject: Re: [sqlite] Using bind_text for numeric columns

On 18 Feb 2018, at 12:44pm, x <[hidden email]> wrote:

> Suppose I have a query with several parameters on columns that could be integer, real or text. The values for these parameters are taken from the result set of another query which is stored in a string array.
>
> Is there any pitfalls or disadvantages to binding them all as text as opposed to the more difficult process of checking the column type and converting the string value to that type? (Assume none of the values are NULL).

Be certain that your column affinities are set correctly in the table definition.  Otherwise you risk bad sorting orders and comparisons, because with string values 12 < 5 .

Apart from that, I think it should work.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users