Hi, Keith,
On Tue, Dec 27, 2016 at 9:18 PM, Keith Medcalf <kmedc...@dessus.com> 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"
= '';
leagues||||||||||||||||||||||||Leagues 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 <ikoro...@gmail.com> wrote:
>
>> Hi, Keith,
>
>> On Dec 27, 2016 7:23 AM, "Keith Medcalf" <kmedc...@dessus.com> 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) <operator> 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"
>> > = "";
>> > leagues||||||||||||||||||||||||Leagues 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(), &stmt, 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<std::codecvt_utf8<wchar_t> > 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
>
>
>
> _______________________________________________
> 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
