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
