Re: [sqlite] Able to differentiate between No query and empty results?

[Simon Slavin]

On 14 Feb 2012, at 6:48pm, Marc L. Allen wrote:

> Does the data type start at being the same as the column affinity in the 
> underlying table and then adjust if the actual data isn't of that type?



section 2.3 gives examples of what happens.  But an overriding rule is about 
losing data.  So SQLite might try turning a string into an integer, then back 
to a string again.  If it gets the same string back, then storing it as an 
integer didn't lose any data, so it'll do that.  Bad example, but I hope it 
helps.

> And, if I don't know the underlying table structure, I am guessing that I 
> should always treat INTEGER as a potential int64?

Use sqlite3_column_int64() or sqlite3_column_int() from



whichever suits you, yielding different sized integers.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Able to differentiate between No query and empty results?

[Stephan Beal]

On Tue, Feb 14, 2012 at 7:48 PM, Marc L. Allen
wrote:

> Does the data type start at being the same as the column affinity in the
> underlying table and then adjust if the actual data isn't of that type?
>

i'll have to let someone else answer that, but...


> And, if I don't know the underlying table structure, I am guessing that I
> should always treat INTEGER as a potential int64?
>

Assuming your platform supports 64-bit integers, that's probably a safe
bet. If you're writing for a target which doesn't support them explicitly
(e.g. JavaScript engines traditionally don't) then using double is an
option.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Able to differentiate between No query and empty results?

[Marc L. Allen]

Thanks, everyone.  I appreciate all the insight.  As for the data type, I have 
a few follow up questions...

Does the data type start at being the same as the column affinity in the 
underlying table and then adjust if the actual data isn't of that type?

And, if I don't know the underlying table structure, I am guessing that I 
should always treat INTEGER as a potential int64?

Thanks,

Marc

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Rob Richardson
> Sent: Tuesday, February 14, 2012 1:09 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Able to differentiate between No query and empty
> results?
> 
> -Original Message
> 
> Also, Stephan is quite right: it's not the columns but the values which
> have datatypes, and if you're looking at some random SQLite database
> that wasn't carefully created the value in r1c1 may be an integer but
> the value in r2c1 may be text.
> 
> Simon.
> --
> A fact that has driven me up nearby walls on several occasions,
> especially when working with date/times.
> 
> RobR
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Able to differentiate between No query and empty results?

[Kees Nuyt]

On Tue, 14 Feb 2012 18:08:39 +, Rob Richardson
 wrote:

>>-Original Message
>>
>> Also, Stephan is quite right: it's not the columns
>> but the values which have datatypes, and if you're
>> looking at some random SQLite database that wasn't
>> carefully created the value in r1c1 may be an
>> integer but the value in r2c1 may be text.
>>
>>Simon.
>>--
>
> A fact that has driven me up nearby walls on several
> occasions, especially when working with date/times.
>
> RobR

You can more or less restrict the datatypes a table
can contain by specifying CHECK constraints in your
table definitions. You'll have to experiment to see
what works and what doesn't.


-- 
Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Able to differentiate between No query and empty results?

[Rob Richardson]

-Original Message

Also, Stephan is quite right: it's not the columns but the values which have 
datatypes, and if you're looking at some random SQLite database that wasn't 
carefully created the value in r1c1 may be an integer but the value in r2c1 may 
be text.

Simon.
--
A fact that has driven me up nearby walls on several occasions, especially when 
working with date/times.

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Able to differentiate between No query and empty results?

[Simon Slavin]

On 14 Feb 2012, at 5:17pm, Simon Slavin wrote:

> Yes, the columncount returned will reflect what columns you asked for, even 
> if the SELECT returns zero rows.  So if columncount is zero, either it's not 
> a SELECT command, or you've somehow managed to make a SELECT which returns 
> zero rows.  At least, that's what it did a few releases ago, and I see no 
> reason it should have changed.

In the above, I should have typed "or you've somehow managed to make a SELECT 
which returns zero -->columns<--", not zero rows.  Sorry for any confusion, 
which was unfortunately key to the statement I was trying to make.

Also, Stephan is quite right: it's not the columns but the values which have 
datatypes, and if you're looking at some random SQLite database that wasn't 
carefully created the value in r1c1 may be an integer but the value in r2c1 may 
be text.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Able to differentiate between No query and empty results?

[Stephan Beal]

To expand on Simon's warning about types: any given column can contain
different types in different rows in sqlite, so examining just the first
row to get the types is not necessarily reliable (but may be for any given
app, depending on how the app inserts/updates data).

- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
On Feb 14, 2012 6:17 PM, "Simon Slavin"  wrote:

>
> On 14 Feb 2012, at 4:50pm, Marc L. Allen wrote:
>
> > Ok.. I just want to make sure I understand.  A SELECT which returns no
> rows will still have a non-zero column count?  And, I would check the
> column count after step() returns SQLITE_DONE?
>
> Yes, the columncount returned will reflect what columns you asked for,
> even if the SELECT returns zero rows.  So if columncount is zero, either
> it's not a SELECT command, or you've somehow managed to make a SELECT which
> returns zero rows.  At least, that's what it did a few releases ago, and I
> see no reason it should have changed.
>
> Worth noting that other commands besides SELECT can also return columns.
>  For instance, PRAGMA.
>
> Also worth noting that using a SELECT which returns no rows, you can
> iterate along the columns and find the column names.  However, you can't
> find the datatypes of the columns, because columns don't have datatypes.
>  Only the data contained in the rows has datatypes, and there are no rows.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Able to differentiate between No query and empty results?

[Simon Slavin]

On 14 Feb 2012, at 4:50pm, Marc L. Allen wrote:

> Ok.. I just want to make sure I understand.  A SELECT which returns no rows 
> will still have a non-zero column count?  And, I would check the column count 
> after step() returns SQLITE_DONE?

Yes, the columncount returned will reflect what columns you asked for, even if 
the SELECT returns zero rows.  So if columncount is zero, either it's not a 
SELECT command, or you've somehow managed to make a SELECT which returns zero 
rows.  At least, that's what it did a few releases ago, and I see no reason it 
should have changed.

Worth noting that other commands besides SELECT can also return columns.  For 
instance, PRAGMA.

Also worth noting that using a SELECT which returns no rows, you can iterate 
along the columns and find the column names.  However, you can't find the 
datatypes of the columns, because columns don't have datatypes.  Only the data 
contained in the rows has datatypes, and there are no rows.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Able to differentiate between No query and empty results?

[Stephan Beal]

Hi!

Check the column count after sqlite3_prepare(). You don't need to execute
the query to get the column count.

- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
On Feb 14, 2012 5:50 PM, "Marc L. Allen" 
wrote:

> > > Without examining the SQL itself, is it possible to determine whether
> > > an SQL statement returned no results because no rows matched the
> > > criteria or because the statement doesn't return rows?
> >
> > sqlite3_column_count should do the trick: SELECT would always have at
> > least one, while UPDATE and such would have zero.
>
> Ok.. I just want to make sure I understand.  A SELECT which returns no
> rows will still have a non-zero column count?  And, I would check the
> column count after step() returns SQLITE_DONE?
>
> Marc
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Able to differentiate between No query and empty results?

[Marc L. Allen]

> > Without examining the SQL itself, is it possible to determine whether
> > an SQL statement returned no results because no rows matched the
> > criteria or because the statement doesn't return rows?
> 
> sqlite3_column_count should do the trick: SELECT would always have at
> least one, while UPDATE and such would have zero.

Ok.. I just want to make sure I understand.  A SELECT which returns no rows 
will still have a non-zero column count?  And, I would check the column count 
after step() returns SQLITE_DONE?

Marc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Able to differentiate between No query and empty results?

[Igor Tandetnik]

On 2/14/2012 11:34 AM, Marc L. Allen wrote:

I'm trying to write my own function that takes a set of SQL, parses into 
statements, executes and returns the results set(s).

Without examining the SQL itself, is it possible to determine whether
an SQL statement returned no results because no rows matched the
criteria or because the statement doesn't return rows?


sqlite3_column_count should do the trick: SELECT would always have at 
least one, while UPDATE and such would have zero.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Able to differentiate between No query and empty results?

[Marc L. Allen]

I'm trying to write my own function that takes a set of SQL, parses into 
statements, executes and returns the results set(s).

Without examining the SQL itself, is it possible to determine whether an SQL 
statement returned no results because no rows matched the criteria or because 
the statement doesn't return rows?

Thanks,

Marc

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users