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 <[email protected]> wrote: > Hi, Keith, > On Dec 27, 2016 7:23 AM, "Keith Medcalf" <[email protected]> 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:[email protected]] > > 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 > > [email protected] > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
