date:20161227

Re: [sqlite] How do I see a full query?

2016-12-27 Thread Keith Medcalf


sqlite3_expand_sql was added at checkin 
https://www.sqlite.org/src/info/99a6c51887d9d784

so far as I can tell the next release after it was added was 3.14 -- any 
release prior to 24 July 2016 cannot contain the function since it did not 
exist before the above checkin.  Are you using a sufficiently recent version of 
SQLite?

It is most certainly in the head of trunk version however.

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Igor Korot
> Sent: Tuesday, 27 December, 2016 21:09
> To: SQLite mailing list
> Subject: Re: [sqlite] How do I see a full query?
> 
> Keith,
> 
> On Tue, Dec 27, 2016 at 10:59 PM, Keith Medcalf 
> wrote:
> >
> >> > So whether hard coding the empty string in the query or using a
> >> parameter (in which you can use some value other than an empty string)
> >> depends on what you will need in the context of the application.  It
> will,
> >> of course, never return a row in which the abt_ownr field has a value
> >> other than ''.  One must presume this is intended.
> >
> >> My application is cross-database one.
> >> Since SQLite does not support catalog/schema, this field will always be
> >> empty.
> >
> >> I am just trying to write less specific code possible and generalize
> >> all queries.
> >
> >> Now, I just tried this code:
> >
> >> std::wstring query = L"SELECT * FROM \"sys.abcattbl\" WHERE
> >> \"abt_tnam\" = ? AND \"abt_ownr\" = \'\';";
> >
> >> and still got "SQLITE_DONE" instead of "SQLITE_ROW".
> >
> > std::wstring query = L"SELECT * FROM \"sys.abcattbl\" WHERE \"abt_tnam\"
> = ? AND \"abt_ownr\" = '';";
> >
> > You do not escape apostrophe's in a string, only quotes.
> 
> Still got 101 (SQLITE_DONE).
> 
> Any idea why sqlite3_expanded_sql() doesn't work?
> What do I need to include for it?
> 
> Thank you.
> 
> >
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] How do I see a full query?

2016-12-27 Thread Igor Korot

Keith,

On Tue, Dec 27, 2016 at 10:59 PM, Keith Medcalf  wrote:
>
>> > So whether hard coding the empty string in the query or using a
>> parameter (in which you can use some value other than an empty string)
>> depends on what you will need in the context of the application.  It will,
>> of course, never return a row in which the abt_ownr field has a value
>> other than ''.  One must presume this is intended.
>
>> My application is cross-database one.
>> Since SQLite does not support catalog/schema, this field will always be
>> empty.
>
>> I am just trying to write less specific code possible and generalize
>> all queries.
>
>> Now, I just tried this code:
>
>> std::wstring query = L"SELECT * FROM \"sys.abcattbl\" WHERE
>> \"abt_tnam\" = ? AND \"abt_ownr\" = \'\';";
>
>> and still got "SQLITE_DONE" instead of "SQLITE_ROW".
>
> std::wstring query = L"SELECT * FROM \"sys.abcattbl\" WHERE \"abt_tnam\" = ? 
> AND \"abt_ownr\" = '';";
>
> You do not escape apostrophe's in a string, only quotes.

Still got 101 (SQLITE_DONE).

Any idea why sqlite3_expanded_sql() doesn't work?
What do I need to include for it?

Thank you.

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

Re: [sqlite] How do I see a full query?

2016-12-27 Thread Keith Medcalf


> > So whether hard coding the empty string in the query or using a
> parameter (in which you can use some value other than an empty string)
> depends on what you will need in the context of the application.  It will,
> of course, never return a row in which the abt_ownr field has a value
> other than ''.  One must presume this is intended.
 
> My application is cross-database one.
> Since SQLite does not support catalog/schema, this field will always be
> empty.
 
> I am just trying to write less specific code possible and generalize
> all queries.
 
> Now, I just tried this code:
 
> std::wstring query = L"SELECT * FROM \"sys.abcattbl\" WHERE
> \"abt_tnam\" = ? AND \"abt_ownr\" = \'\';";
 
> and still got "SQLITE_DONE" instead of "SQLITE_ROW".

std::wstring query = L"SELECT * FROM \"sys.abcattbl\" WHERE \"abt_tnam\" = ? 
AND \"abt_ownr\" = '';";

You do not escape apostrophe's in a string, only quotes.




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

Re: [sqlite] How do I see a full query?

2016-12-27 Thread Igor Korot

Hi, Simon,

On Tue, Dec 27, 2016 at 12:14 AM, Simon Slavin  wrote:
>
> On 27 Dec 2016, at 4:24am, Igor Korot  wrote:
>
>> I have a weird situation where executing a query in a shell gives me a row,
>> but executing the same query through the C-interface: sqlite3_prepare_v2(),
>> sqlite3_bind_text() and sqlite3_step() produces SQLITE_DONE.
>>
>> So I wonder - is it possible to see a full query string inside 
>> sqlite3_step()?
>
> 
>
> One other way to test what’s going on is to temporarily hardwire your C code 
> to execute the bound string (as you think it is after binding) rather than 
> doing its own binding, and see if you get the same result.

Trying to hardwire the 'leagues' value into the query - everything works.
I do get the record.

>
> When this happens it’s usually because of some weird problem with escaping or 
> quoting characters.  Sixteen posts later someone points out that section 
> 14(b)(F) says that after a backslash you have to triple-double-quote.  I 
> don’t know if this is what your particular problem is but it happens a lot.

I have no idea - "CREATE TABLE \"sys.abcattbl\"();" worked OK.

Thank you.

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

Re: [sqlite] How do I see a full query?

2016-12-27 Thread Igor Korot

Hi, Hick,

On Tue, Dec 27, 2016 at 3:02 AM, Hick Gunter  wrote:
> The functions sqlite3_sql() and sqlite3_expanded_sql() will return the 
> original and expanded (i.e. with the bound values insted of parameter names) 
> SQL strings for a statement created with the sqlite3_prepare_V2() interface.

Trying to compile the call to char *result_query =
sqlite3_expanded_sql( stmt ); I got:

error C3861: 'sqlite3_expanded_sql': identifier not found

I am including only sqlite3.h file.

Thank you.

>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Igor Korot
> Gesendet: Dienstag, 27. Dezember 2016 05:24
> An: Discussion of SQLite Database ; 
> General Discussion of SQLite Database 
> Betreff: [sqlite] How do I see a full query?
>
> Hi, ALL,
> I have a weird situation where executing a query in a shell gives me a row, 
> but executing the same query through the C-interface: sqlite3_prepare_v2(),
> sqlite3_bind_text() and sqlite3_step() produces SQLITE_DONE.
>
> So I wonder - is it possible to see a full query string inside sqlite3_step()?
>
> I'm using MSVC++2010 ATM with Windows 8.1. Program is compiled as 32-bit app.
>
> From shell:
> C:\Users\Igor\OneDrive\Documents>sqlite3 draft.db SQLite version 3.13.0 
> 2016-05-18 10:57:30 Enter ".help" for usage hints.
> sqlite> SELECT * FROM "sys.abcattbl" WHERE "abt_tnam" = "leagues" AND 
> "abt_ownr"
>  = "";
> leaguesLeagues table
> sqlite>
>
> From the code routine (it is C++):
>
> sqlite3_stmt *stmt = NULL;
> std::wstring errorMessage;
> int result;
> std::wstring query = L"SELECT * FROM \"sys.abcattbl\" WHERE \"abt_tnam\" 
> = ? AND \"abt_ownr\" = \"\";";
> const unsigned char *dataFontName, *headingFontName, *labelFontName;
> int res = sqlite3_prepare_v2( m_db,
> sqlite_pimpl->m_myconv.to_bytes( query.c_str() ).c_str(), (int) 
> query.length(), , 0 );
> if( res == SQLITE_OK )
> {
> res = sqlite3_bind_text( stmt, 1, sqlite_pimpl->m_myconv.to_bytes( 
> table->GetTableName().c_str() ).c_str(), -1, SQLITE_STATIC );
> if( res == SQLITE_OK )
> {
> while( true )
> {
> res = sqlite3_step( stmt );
>
>
> where sqlite_pimpl is:
>
> struct SQLiteDatabase::SQLiteImpl
> {
> std::wstring m_catalog;
> std::wstring_convert m_myconv; };
>
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of the 
> intended recipient(s) only and may contain information that is confidential, 
> privileged or legally protected. Any unauthorized use or dissemination of 
> this communication is strictly prohibited. If you have received this 
> communication in error, please immediately notify the sender by return e-mail 
> message and delete all copies of the original communication. Thank you for 
> your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I see a full query?

2016-12-27 Thread Igor Korot

Hi, Keith,

On Tue, Dec 27, 2016 at 9:18 PM, Keith Medcalf  wrote:
>
> Yes.  Either method would work, though you say:
>
>> because abt_ownr field will always be empty (unless there is a way to know
>> what user connected to the DB).
>
> So whether hard coding the empty string in the query or using a parameter (in 
> which you can use some value other than an empty string) depends on what you 
> will need in the context of the application.  It will, of course, never 
> return a row in which the abt_ownr field has a value other than ''.  One must 
> presume this is intended.

My application is cross-database one.
Since SQLite does not support catalog/schema, this field will always be empty.

I am just trying to write less specific code possible and generalize
all queries.

Now, I just tried this code:

std::wstring query = L"SELECT * FROM \"sys.abcattbl\" WHERE
\"abt_tnam\" = ? AND \"abt_ownr\" = \'\';";

and still got "SQLITE_DONE" instead of "SQLITE_ROW".

C:\Users\Igor\OneDrive\Documents>sqlite3 draft.db
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite> SELECT * FROM "sys.abcattbl" WHERE "abt_tnam" = 'leagues' AND "abt_ownr"
 = '';
leaguesLeagues table
sqlite> .schema "sys.abcattbl"
CREATE TABLE "sys.abcattbl"("abt_tnam" char(129) NOT NULL, "abt_tid" integer, "a
bt_ownr" char(129) NOT NULL, "abd_fhgt" smallint, "abd_fwgt" smallint, "abd_fitl
" char(1), "abd_funl" char(1), "abd_fchr" smallint, "abd_fptc" smallint, "abd_ff
ce" char(18), "abh_fhgt" smallint, "abh_fwgt" smallint, "abh_fitl" char(1), "abh
_funl" char(1), "abh_fchr" smallint, "abh_fptc" smallint, "abh_ffce" char(18), "
abl_fhgt" smallint, "abl_fwgt" smallint, "abl_fitl" char(1), "abl_funl" char(1),
 "abl_fchr" smallint, "abl_fptc" smallint, "abl_ffce" char(18), "abt_cmnt" char(
254), PRIMARY KEY( "abt_tnam", "abt_ownr" ));
sqlite>

Also, see my replies to Simon and Hick.

Thank you.

>
>
> On Tuesday, 27 December, 2016 18:11, Igor Korot  wrote:
>
>> Hi, Keith,
>
>> On Dec 27, 2016 7:23 AM, "Keith Medcalf"  wrote:
>>
>> The problem is that your query is broken -- they are not the same.
>>
>> Double-quotes surround names (table names, column names, index names, view
>> names, alias names).
>> Strings (text values) are surrounded by single-quotes.
>>
>> You just happen to be lucky that your query works since it is malformed
>> from the get go.  If "leagues" was a column in the table then it would not
>> work since you would be looking for column equality, not a text value in
>> the abt_tnam column.
>>
>> It ought to be:
>>
>> SELECT *
>>   FROM "sys.abcattbl"
>>  WHERE "abt_tnam" = 'leagues'
>>AND "abt_ownr" = '';
>>
>>
>> Your other problem is that you did not bind a value to parameter 2 in the
>> prepared statement.
>>
>> SELECT *
>>   FROM "sys.abcattbl"
>>  WHERE "abt_tnam" = ?
>>AND "abt_ownr" = ?;
>>
>> So, by default parameter 2 is NULL.  Nothing equals NULL.  Ever.  This is
>> the definition of a NULL.  Any comparison of any type to NULL always has a
>> False result.  That is (anything including NULL)  NULL -> NULL
>> (which is taken as False),  where operator is any of =, <, >, <=, >=, <>.
>> The only operators that can ever have a non-False result when one side is
>> a
>> NULL are the operators IS and IS NOT.  Three value logic is a separate
>> topic.
>>
>> See the second last paragraph (excluding the see also reference
>> paragraphs)
>> of any of the sqlite3_bind* functions, such as
>> https://www.sqlite.org/c3ref/bind_blob.html
>>
>> To understand the difference between a value (of which an empty string is
>> a
>> value) vs a NULL, see
>> https://en.wikipedia.org/wiki/Sql_null
>>
>> So, in your C code you need to bind an empty string to parameter 2, and in
>> the query run in the shell use single-quotes around text values, in order
>> for the two queries to actually be the same.  Once the queries are the
>> same, you should expect identical results.
>>
>> IK>> Does this mean I can't just try to run this in my C code:
>>
>> "SELECT * FROM \"sys.abcattbl\" WHERE \"abt_tnam\" = ? AND \"abt_ownr\" =
>> \'\';";
>>
>> because abt_ownr field will always be empty (unless there is a way to know
>> what user connected to the DB).
>>
>> Also I'm just curious - am I to run something like:
>>
>> res = sqlite3_bind_text( stmt, 2, "", -1, SQLITE_STATIC );
>>
>> Thank you.
>>
>>
>> > -Original Message-
>> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> > On Behalf Of Igor Korot
>> > Sent: Monday, 26 December, 2016 21:24
>> > To: Discussion of SQLite Database; General Discussion of SQLite Database
>> > Subject: [sqlite] How do I see a full query?
>> >
>> > Hi, ALL,
>> > I have a weird situation where executing a query in a shell gives me a
>> > row,
>> > but executing the same query through the C-interface:
>> > sqlite3_prepare_v2(),
>> > sqlite3_bind_text() and sqlite3_step() produces

Re: [sqlite] How do I see a full query?

2016-12-27 Thread Keith Medcalf


Yes.  Either method would work, though you say:

> because abt_ownr field will always be empty (unless there is a way to know
> what user connected to the DB).

So whether hard coding the empty string in the query or using a parameter (in 
which you can use some value other than an empty string) depends on what you 
will need in the context of the application.  It will, of course, never return 
a row in which the abt_ownr field has a value other than ''.  One must presume 
this is intended.


On Tuesday, 27 December, 2016 18:11, Igor Korot  wrote:

> Hi, Keith,
 
> On Dec 27, 2016 7:23 AM, "Keith Medcalf"  wrote:
> 
> The problem is that your query is broken -- they are not the same.
> 
> Double-quotes surround names (table names, column names, index names, view
> names, alias names).
> Strings (text values) are surrounded by single-quotes.
> 
> You just happen to be lucky that your query works since it is malformed
> from the get go.  If "leagues" was a column in the table then it would not
> work since you would be looking for column equality, not a text value in
> the abt_tnam column.
> 
> It ought to be:
> 
> SELECT *
>   FROM "sys.abcattbl"
>  WHERE "abt_tnam" = 'leagues'
>AND "abt_ownr" = '';
> 
> 
> Your other problem is that you did not bind a value to parameter 2 in the
> prepared statement.
> 
> SELECT *
>   FROM "sys.abcattbl"
>  WHERE "abt_tnam" = ?
>AND "abt_ownr" = ?;
> 
> So, by default parameter 2 is NULL.  Nothing equals NULL.  Ever.  This is
> the definition of a NULL.  Any comparison of any type to NULL always has a
> False result.  That is (anything including NULL)  NULL -> NULL
> (which is taken as False),  where operator is any of =, <, >, <=, >=, <>.
> The only operators that can ever have a non-False result when one side is
> a
> NULL are the operators IS and IS NOT.  Three value logic is a separate
> topic.
> 
> See the second last paragraph (excluding the see also reference
> paragraphs)
> of any of the sqlite3_bind* functions, such as
> https://www.sqlite.org/c3ref/bind_blob.html
> 
> To understand the difference between a value (of which an empty string is
> a
> value) vs a NULL, see
> https://en.wikipedia.org/wiki/Sql_null
> 
> So, in your C code you need to bind an empty string to parameter 2, and in
> the query run in the shell use single-quotes around text values, in order
> for the two queries to actually be the same.  Once the queries are the
> same, you should expect identical results.
> 
> IK>> Does this mean I can't just try to run this in my C code:
> 
> "SELECT * FROM \"sys.abcattbl\" WHERE \"abt_tnam\" = ? AND \"abt_ownr\" =
> \'\';";
> 
> because abt_ownr field will always be empty (unless there is a way to know
> what user connected to the DB).
> 
> Also I'm just curious - am I to run something like:
> 
> res = sqlite3_bind_text( stmt, 2, "", -1, SQLITE_STATIC );
> 
> Thank you.
> 
> 
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Igor Korot
> > Sent: Monday, 26 December, 2016 21:24
> > To: Discussion of SQLite Database; General Discussion of SQLite Database
> > Subject: [sqlite] How do I see a full query?
> >
> > Hi, ALL,
> > I have a weird situation where executing a query in a shell gives me a
> > row,
> > but executing the same query through the C-interface:
> > sqlite3_prepare_v2(),
> > sqlite3_bind_text() and sqlite3_step() produces SQLITE_DONE.
> >
> > So I wonder - is it possible to see a full query string inside
> > sqlite3_step()?
> >
> > I'm using MSVC++2010 ATM with Windows 8.1. Program is compiled as 32-bit
> > app.
> >
> > From shell:
> > C:\Users\Igor\OneDrive\Documents>sqlite3 draft.db
> > SQLite version 3.13.0 2016-05-18 10:57:30
> > Enter ".help" for usage hints.
> > sqlite> SELECT * FROM "sys.abcattbl" WHERE "abt_tnam" = "leagues" AND
> > "abt_ownr"
> >  = "";
> > leaguesLeagues table
> > sqlite>
> >
> > From the code routine (it is C++):
> >
> > sqlite3_stmt *stmt = NULL;
> > std::wstring errorMessage;
> > int result;
> > std::wstring query = L"SELECT * FROM \"sys.abcattbl\" WHERE
> > \"abt_tnam\" = ? AND \"abt_ownr\" = \"\";";
> > const unsigned char *dataFontName, *headingFontName, *labelFontName;
> > int res = sqlite3_prepare_v2( m_db,
> > sqlite_pimpl->m_myconv.to_bytes( query.c_str() ).c_str(), (int)
> > query.length(), , 0 );
> > if( res == SQLITE_OK )
> > {
> > res = sqlite3_bind_text( stmt, 1,
> > sqlite_pimpl->m_myconv.to_bytes( table->GetTableName().c_str()
> > ).c_str(), -1, SQLITE_STATIC );
> > if( res == SQLITE_OK )
> > {
> > while( true )
> > {
> > res = sqlite3_step( stmt );
> >
> >
> > where sqlite_pimpl is:
> >
> > struct SQLiteDatabase::SQLiteImpl
> > {
> > std::wstring m_catalog;
> > std::wstring_convert m_myconv;
> > };
> >
> > Thank you.
> >

Re: [sqlite] How do I see a full query?

2016-12-27 Thread Igor Korot

Hi, Keith,

On Dec 27, 2016 7:23 AM, "Keith Medcalf"  wrote:

The problem is that your query is broken -- they are not the same.

Double-quotes surround names (table names, column names, index names, view
names, alias names).
Strings (text values) are surrounded by single-quotes.

You just happen to be lucky that your query works since it is malformed
from the get go.  If "leagues" was a column in the table then it would not
work since you would be looking for column equality, not a text value in
the abt_tnam column.

It ought to be:

SELECT *
  FROM "sys.abcattbl"
 WHERE "abt_tnam" = 'leagues'
   AND "abt_ownr" = '';

Your other problem is that you did not bind a value to parameter 2 in the
prepared statement.

SELECT *
  FROM "sys.abcattbl"
 WHERE "abt_tnam" = ?
   AND "abt_ownr" = ?;

So, by default parameter 2 is NULL.  Nothing equals NULL.  Ever.  This is
the definition of a NULL.  Any comparison of any type to NULL always has a
False result.  That is (anything including NULL)  NULL -> NULL
(which is taken as False),  where operator is any of =, <, >, <=, >=, <>.
The only operators that can ever have a non-False result when one side is a
NULL are the operators IS and IS NOT.  Three value logic is a separate
topic.

See the second last paragraph (excluding the see also reference paragraphs)
of any of the sqlite3_bind* functions, such as
https://www.sqlite.org/c3ref/bind_blob.html

To understand the difference between a value (of which an empty string is a
value) vs a NULL, see
https://en.wikipedia.org/wiki/Sql_null

So, in your C code you need to bind an empty string to parameter 2, and in
the query run in the shell use single-quotes around text values, in order
for the two queries to actually be the same.  Once the queries are the
same, you should expect identical results.

IK>> Does this mean I can't just try to run this in my C code:

"SELECT * FROM \"sys.abcattbl\" WHERE \"abt_tnam\" = ? AND \"abt_ownr\" =
\'\';";

because abt_ownr field will always be empty (unless there is a way to know
what user connected to the DB).

Also I'm just curious - am I to run something like:

res = sqlite3_bind_text( stmt, 2, "", -1, SQLITE_STATIC );

Thank you.

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Igor Korot
> Sent: Monday, 26 December, 2016 21:24
> To: Discussion of SQLite Database; General Discussion of SQLite Database
> Subject: [sqlite] How do I see a full query?
>
> Hi, ALL,
> I have a weird situation where executing a query in a shell gives me a
> row,
> but executing the same query through the C-interface:
> sqlite3_prepare_v2(),
> sqlite3_bind_text() and sqlite3_step() produces SQLITE_DONE.
>
> So I wonder - is it possible to see a full query string inside
> sqlite3_step()?
>
> I'm using MSVC++2010 ATM with Windows 8.1. Program is compiled as 32-bit
> app.
>
> From shell:
> C:\Users\Igor\OneDrive\Documents>sqlite3 draft.db
> SQLite version 3.13.0 2016-05-18 10:57:30
> Enter ".help" for usage hints.
> sqlite> SELECT * FROM "sys.abcattbl" WHERE "abt_tnam" = "leagues" AND
> "abt_ownr"
>  = "";
> leaguesLeagues table
> sqlite>
>
> From the code routine (it is C++):
>
> sqlite3_stmt *stmt = NULL;
> std::wstring errorMessage;
> int result;
> std::wstring query = L"SELECT * FROM \"sys.abcattbl\" WHERE
> \"abt_tnam\" = ? AND \"abt_ownr\" = \"\";";
> const unsigned char *dataFontName, *headingFontName, *labelFontName;
> int res = sqlite3_prepare_v2( m_db,
> sqlite_pimpl->m_myconv.to_bytes( query.c_str() ).c_str(), (int)
> query.length(), , 0 );
> if( res == SQLITE_OK )
> {
> res = sqlite3_bind_text( stmt, 1,
> sqlite_pimpl->m_myconv.to_bytes( table->GetTableName().c_str()
> ).c_str(), -1, SQLITE_STATIC );
> if( res == SQLITE_OK )
> {
> while( true )
> {
> res = sqlite3_step( stmt );
>
>
> where sqlite_pimpl is:
>
> struct SQLiteDatabase::SQLiteImpl
> {
> std::wstring m_catalog;
> std::wstring_convert m_myconv;
> };
>
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Possible bug with union and join.

2016-12-27 Thread Hick Gunter

Incorrect. All data is stored as NULL, integer, float, text or blob. The 
"declared type" is taken as a hint from the designer in respect to the kind of 
values he intends to store there. The result of the hint is called an 
"affinity", i.e. the kind of data the field "likes to" store.  If the type of 
the value presented differs from the affinity, it may be converted, if 
losslessly and reversibly possible (e.g. '1' <=> 1); this is called "applying 
affinity". Or, if not possible ('hugo' cannot be converted to a number), simply 
stored without conversion. This is the sense in which SQLite is "typeless". 
Even if the field has a declared type, any kind of value may be stored there 
without error.

"Affinity" is a volatile property. It may be lost (by using a value in a 
general expression) or gained (by using a cast or in the context of a 
comparison). This is mainly for the benefit of users who for some reason or 
other need to compare (text, e.g. a text constant) '1' with (integer, e.g. a 
value from a field) 1 and have it come out equal. Note that constants have a 
type but no affinity, so SELECT '1' = 1; returns 0.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Don V Nielsen
Gesendet: Dienstag, 27. Dezember 2016 16:18
An: SQLite mailing list 
Betreff: Re: [sqlite] Possible bug with union and join.

Theory related question. I'm being argumentative, I know. But this issue is in 
the same category as one discussed weeks ago.

SQLite is, in a sense, typeless. All data is stored as text (ignore blob). 
Correct? It is when one casts a column to something other than text that 
triggers SQLite to treat the text differently.

Disregarding auto-incremented key values, why have an integer key.
Even if the key value will only be numeric digits like 1, 255, 1024, etc..., 
are they "truly" integers? If the value is not used in a mathematical formula, 
why think of it as an integer? It is still just text...a string of ascii 
digits... but still text. Is there something behind the scenes of how text data 
comprised of numeric digits is stored?

Like the previous issue I suggested keeping the keys between tables the same 
data type. The issue resolves itself. The same would be true, here. One table 
has text which could be '1,10'. But in the other table, it is integer 1 & 10. 
It could be text '1' & '10'. No type conversion problems.

I don't know. I would like to hear what others have to say.
dvn

On Sun, Dec 25, 2016 at 2:43 PM, Adrian Stachlewski 
 wrote:
> Fortunately names of columns are much more transparent and documented
> in our internal specification. 'Id' was created only for example, but
> thanks for advice :)
>
> Adrian
>
> 2016-12-25 13:44 GMT+01:00 Simon Slavin :
>
>>
>> On 23 Dec 2016, at 4:55pm, Adrian Stachlewski
>> 
>> wrote:
>>
>> > Id field in one table is defined as TEXT, because there are stored
>> > identifiers which can be numeric or text mostly like in the example
>> > ("4", "4,5", "10-1") (to be precise this map is created on the fly
>> > by concatenating some ids and names from another tables).  In
>> > second table there are stored identifiers which are integer only.
>> > This ids means something entirely different, but there is one case,
>> > when table with date keeps ids from both tables. Unfortunately I
>> > cannot change input data - it is taken from some APIs using csv files.
>>
>> Okay.  You’re wedded to a data format created by someone else.  That
>> explains the problem.
>>
>> If you have the opportunity to rename your columns when you import
>> from the CSV files, I might recommend that you do not call the TEXT field 
>> 'id'.
>> The convention for 'id' is for an INTEGER PRIMARY KEY and it might
>> confuse other people who see your database.
>>
>> Good luck with problem you posted about.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or

Re: [sqlite] Possible bug with union and join.

2016-12-27 Thread Don V Nielsen

Thanks for the correction/information! Merry Christmas and Happy New
Year to you.

On Tue, Dec 27, 2016 at 9:48 AM, Richard Hipp  wrote:
> On 12/27/16, Don V Nielsen  wrote:
>> Theory related question. I'm being argumentative, I know. But this
>> issue is in the same category as one discussed weeks ago.
>>
>> SQLite is, in a sense, typeless. All data is stored as text (ignore
>> blob). Correct? It is when one casts a column to something other than
>> text that triggers SQLite to treat the text differently.
>
> Incorrect.  SQLite stores content in memory and on disk in multiple
> formats, including 2's-complement integers, IEEE 754 floating point
> numbers, text formatted as UTF8, UTF16be, or UTF16le, and binary
> blobs.  See, for example,
> https://www.sqlite.org/fileformat2.html#serialtype
>
>>
>> Disregarding auto-incremented key values, why have an integer key.
>
> Special optimizations apply to tables with an INTEGER PRIMARY KEY that
> make such tables particularly fast.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

2016-12-27 Thread Richard Hipp

On 12/27/16, Don V Nielsen  wrote:
> Theory related question. I'm being argumentative, I know. But this
> issue is in the same category as one discussed weeks ago.
>
> SQLite is, in a sense, typeless. All data is stored as text (ignore
> blob). Correct? It is when one casts a column to something other than
> text that triggers SQLite to treat the text differently.

Incorrect.  SQLite stores content in memory and on disk in multiple
formats, including 2's-complement integers, IEEE 754 floating point
numbers, text formatted as UTF8, UTF16be, or UTF16le, and binary
blobs.  See, for example,
https://www.sqlite.org/fileformat2.html#serialtype

>
> Disregarding auto-incremented key values, why have an integer key.

Special optimizations apply to tables with an INTEGER PRIMARY KEY that
make such tables particularly fast.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug with union and join.

2016-12-27 Thread Don V Nielsen

Theory related question. I'm being argumentative, I know. But this
issue is in the same category as one discussed weeks ago.

SQLite is, in a sense, typeless. All data is stored as text (ignore
blob). Correct? It is when one casts a column to something other than
text that triggers SQLite to treat the text differently.

Disregarding auto-incremented key values, why have an integer key.
Even if the key value will only be numeric digits like 1, 255, 1024,
etc..., are they "truly" integers? If the value is not used in a
mathematical formula, why think of it as an integer? It is still just
text...a string of ascii digits... but still text. Is there something
behind the scenes of how text data comprised of numeric digits is
stored?

Like the previous issue I suggested keeping the keys between tables
the same data type. The issue resolves itself. The same would be true,
here. One table has text which could be '1,10'. But in the other
table, it is integer 1 & 10. It could be text '1' & '10'. No type
conversion problems.

I don't know. I would like to hear what others have to say.
dvn

On Sun, Dec 25, 2016 at 2:43 PM, Adrian Stachlewski
 wrote:
> Fortunately names of columns are much more transparent and documented in
> our internal specification. 'Id' was created only for example, but thanks
> for advice :)
>
> Adrian
>
> 2016-12-25 13:44 GMT+01:00 Simon Slavin :
>
>>
>> On 23 Dec 2016, at 4:55pm, Adrian Stachlewski 
>> wrote:
>>
>> > Id field in one table is defined as TEXT, because there are stored
>> > identifiers which can be numeric or text mostly like in the example ("4",
>> > "4,5", "10-1") (to be precise this map is created on the fly by
>> > concatenating some ids and names from another tables).  In second table
>> > there are stored identifiers which are integer only. This ids means
>> > something entirely different, but there is one case, when table with date
>> > keeps ids from both tables. Unfortunately I cannot change input data - it
>> > is taken from some APIs using csv files.
>>
>> Okay.  You’re wedded to a data format created by someone else.  That
>> explains the problem.
>>
>> If you have the opportunity to rename your columns when you import from
>> the CSV files, I might recommend that you do not call the TEXT field 'id'.
>> The convention for 'id' is for an INTEGER PRIMARY KEY and it might confuse
>> other people who see your database.
>>
>> Good luck with problem you posted about.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I see a full query?

2016-12-27 Thread Keith Medcalf


The problem is that your query is broken -- they are not the same.

Double-quotes surround names (table names, column names, index names, view 
names, alias names).
Strings (text values) are surrounded by single-quotes.

You just happen to be lucky that your query works since it is malformed from 
the get go.  If "leagues" was a column in the table then it would not work 
since you would be looking for column equality, not a text value in the 
abt_tnam column.  

It ought to be:

SELECT * 
  FROM "sys.abcattbl" 
 WHERE "abt_tnam" = 'leagues' 
   AND "abt_ownr" = '';

Your other problem is that you did not bind a value to parameter 2 in the 
prepared statement.

SELECT * 
  FROM "sys.abcattbl" 
 WHERE "abt_tnam" = ?
   AND "abt_ownr" = ?;

So, by default parameter 2 is NULL.  Nothing equals NULL.  Ever.  This is the 
definition of a NULL.  Any comparison of any type to NULL always has a False 
result.  That is (anything including NULL)  NULL -> NULL (which is 
taken as False),  where operator is any of =, <, >, <=, >=, <>.  The only 
operators that can ever have a non-False result when one side is a NULL are the 
operators IS and IS NOT.  Three value logic is a separate topic.

See the second last paragraph (excluding the see also reference paragraphs) of 
any of the sqlite3_bind* functions, such as
https://www.sqlite.org/c3ref/bind_blob.html

To understand the difference between a value (of which an empty string is a 
value) vs a NULL, see
https://en.wikipedia.org/wiki/Sql_null

So, in your C code you need to bind an empty string to parameter 2, and in the 
query run in the shell use single-quotes around text values, in order for the 
two queries to actually be the same.  Once the queries are the same, you should 
expect identical results.

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Igor Korot
> Sent: Monday, 26 December, 2016 21:24
> To: Discussion of SQLite Database; General Discussion of SQLite Database
> Subject: [sqlite] How do I see a full query?
> 
> Hi, ALL,
> I have a weird situation where executing a query in a shell gives me a
> row,
> but executing the same query through the C-interface:
> sqlite3_prepare_v2(),
> sqlite3_bind_text() and sqlite3_step() produces SQLITE_DONE.
> 
> So I wonder - is it possible to see a full query string inside
> sqlite3_step()?
> 
> I'm using MSVC++2010 ATM with Windows 8.1. Program is compiled as 32-bit
> app.
> 
> From shell:
> C:\Users\Igor\OneDrive\Documents>sqlite3 draft.db
> SQLite version 3.13.0 2016-05-18 10:57:30
> Enter ".help" for usage hints.
> sqlite> SELECT * FROM "sys.abcattbl" WHERE "abt_tnam" = "leagues" AND
> "abt_ownr"
>  = "";
> leaguesLeagues table
> sqlite>
> 
> From the code routine (it is C++):
> 
> sqlite3_stmt *stmt = NULL;
> std::wstring errorMessage;
> int result;
> std::wstring query = L"SELECT * FROM \"sys.abcattbl\" WHERE
> \"abt_tnam\" = ? AND \"abt_ownr\" = \"\";";
> const unsigned char *dataFontName, *headingFontName, *labelFontName;
> int res = sqlite3_prepare_v2( m_db,
> sqlite_pimpl->m_myconv.to_bytes( query.c_str() ).c_str(), (int)
> query.length(), , 0 );
> if( res == SQLITE_OK )
> {
> res = sqlite3_bind_text( stmt, 1,
> sqlite_pimpl->m_myconv.to_bytes( table->GetTableName().c_str()
> ).c_str(), -1, SQLITE_STATIC );
> if( res == SQLITE_OK )
> {
> while( true )
> {
> res = sqlite3_step( stmt );
> 
> 
> where sqlite_pimpl is:
> 
> struct SQLiteDatabase::SQLiteImpl
> {
> std::wstring m_catalog;
> std::wstring_convert m_myconv;
> };
> 
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] How do I see a full query?

2016-12-27 Thread Hick Gunter

The functions sqlite3_sql() and sqlite3_expanded_sql() will return the original 
and expanded (i.e. with the bound values insted of parameter names) SQL strings 
for a statement created with the sqlite3_prepare_V2() interface.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Igor Korot
Gesendet: Dienstag, 27. Dezember 2016 05:24
An: Discussion of SQLite Database ; 
General Discussion of SQLite Database 
Betreff: [sqlite] How do I see a full query?

Hi, ALL,
I have a weird situation where executing a query in a shell gives me a row, but 
executing the same query through the C-interface: sqlite3_prepare_v2(),
sqlite3_bind_text() and sqlite3_step() produces SQLITE_DONE.

So I wonder - is it possible to see a full query string inside sqlite3_step()?

I'm using MSVC++2010 ATM with Windows 8.1. Program is compiled as 32-bit app.

From shell:
C:\Users\Igor\OneDrive\Documents>sqlite3 draft.db SQLite version 3.13.0 
2016-05-18 10:57:30 Enter ".help" for usage hints.
sqlite> SELECT * FROM "sys.abcattbl" WHERE "abt_tnam" = "leagues" AND "abt_ownr"
 = "";
leaguesLeagues table
sqlite>

From the code routine (it is C++):

sqlite3_stmt *stmt = NULL;
std::wstring errorMessage;
int result;
std::wstring query = L"SELECT * FROM \"sys.abcattbl\" WHERE \"abt_tnam\" = 
? AND \"abt_ownr\" = \"\";";
const unsigned char *dataFontName, *headingFontName, *labelFontName;
int res = sqlite3_prepare_v2( m_db,
sqlite_pimpl->m_myconv.to_bytes( query.c_str() ).c_str(), (int) query.length(), 
, 0 );
if( res == SQLITE_OK )
{
res = sqlite3_bind_text( stmt, 1, sqlite_pimpl->m_myconv.to_bytes( 
table->GetTableName().c_str() ).c_str(), -1, SQLITE_STATIC );
if( res == SQLITE_OK )
{
while( true )
{
res = sqlite3_step( stmt );


where sqlite_pimpl is:

struct SQLiteDatabase::SQLiteImpl
{
std::wstring m_catalog;
std::wstring_convert m_myconv; };

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] How do I see a full query?

Re: [sqlite] How do I see a full query?

Re: [sqlite] How do I see a full query?

Re: [sqlite] How do I see a full query?

Re: [sqlite] How do I see a full query?

Re: [sqlite] How do I see a full query?

Re: [sqlite] How do I see a full query?

Re: [sqlite] How do I see a full query?

Re: [sqlite] Possible bug with union and join.

Re: [sqlite] Possible bug with union and join.

Re: [sqlite] Possible bug with union and join.

Re: [sqlite] Possible bug with union and join.

Re: [sqlite] How do I see a full query?

Re: [sqlite] How do I see a full query?

14 matches

Site Navigation

Mail list logo

Footer information