[sqlite] Retrieving the table info fails
On 2015/11/16 7:59 PM, Igor Korot wrote:
> Stephan,
>
> On Mon, Nov 16, 2015 at 12:42 PM, Stephan Beal
> wrote:
>> On Mon, Nov 16, 2015 at 6:11 PM, Igor Korot wrote:
>>
>>> The variables referenced are defined as "std::string" and the code is in
>>> C++.
>>>
>> the std::string(char const *) constructor does not, last time i checked,
>> accept a NULL value. You will need to pass it "" in that case.
>>
>> [stephan at host:~/tmp]$ cat foo.cpp
>> #include
>>
>> int main(){
>> std::string s(0);
>> return 0;
>> }
>>
>> [stephan at host:~/tmp]$ gcc -o foo foo.cpp -lstdc++
>> [stephan at host:~/tmp]$ ./foo
>> terminate called after throwing an instance of 'std::logic_error'
>>what(): basic_string::_S_construct null not valid
>> Aborted
> OK, it looks like the value is NULL instead of "".
> I will change the code accordingly.
>
> BTW, are only name, type and pk fields are guaranteed to have a value?
Nothing is guaranteed to have a value unless created with NOT NULL in
the field specification in the CREATE TABLE schema SQL.
Usually (in most DBs) a Primary key is guaranteed to not have NULL
values, but in SQLite it is permissible if the PK is not specifically
stated to be NOT NULL.
[sqlite] Retrieving the table info fails
On 16 Nov 2015, at 9:09pm, Igor Korot wrote: > 1|name|varchar(100)|0 | |0 Note that this does not tell you anything about the affinity of the column, or the types of the values in it. SQLite doesn't even have a varchar type. > "dflt_value" field may or may not have a value. It might be interesting to know what type the value in the above line has. Simon.
[sqlite] Retrieving the table info fails
On 16 Nov 2015, at 7:40pm, Igor Korot wrote: > But if I issue this PRAGMA command the field name, field type and the PK > are guaranteed to have some values, right? No. Create a field without a declared type and use that as your primary key. > You can't create a field without a name Correct. At least I think so. > or the type Wrong. 178:~ simon$ sqlite3 ~/Desktop/fred.sql SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. sqlite> CREATE TABLE myTable (fred PRIMARY KEY); sqlite> PRAGMA table_info(myTable); 0|fred||0||1 sqlite> PRAGMA index_list(myTable); 0|sqlite_autoindex_myTable_1|1|pk|0 sqlite> PRAGMA index_info(sqlite_autoindex_myTable_1); 0|0|fred sqlite> PRAGMA index_xinfo(sqlite_autoindex_myTable_1); 0|0|fred|0|BINARY|1 1|-1||0|BINARY|0 sqlite> I'd like to emphasise something Scott Hess wrote. There is considerable leeway in the documentation for PRAGMAs and things can change from one version of SQLite to another. Pragmas like this are for use only when you don't know the answers. For example for database management software which has to operate on any SQLite database. Simon.
[sqlite] Retrieving the table info fails
On 16 Nov 2015, at 5:51pm, Igor Korot wrote: > It looks like I falsely assumed that it will return an empty string instead. > Guess I was wrong. The empty string is a perfectly legitimate default value for a column. And it's not the same as NULL, a different perfectly legitimate default value for a column. These two do not do the same thing: CREATE TABLE myTable (fred DEFAULT NULL); CREATE TABLE myTable (fred DEFAULT ''); > Also, are you saying that if I have a integer field with the default value of > 1, > I will not be able to retrieve it with sqliteColumnText()? Correct. These two do not do the same thing: CREATE TABLE myTable (fred DEFAULT 1); CREATE TABLE myTable (fred DEFAULT '1'); Simon.
[sqlite] Retrieving the table info fails
On Mon, Nov 16, 2015 at 6:42 PM, Stephan Beal wrote:
> On Mon, Nov 16, 2015 at 6:11 PM, Igor Korot wrote:
>
>> The variables referenced are defined as "std::string" and the code is in
>> C++.
>>
>
> the std::string(char const *) constructor does not, last time i checked,
> accept a NULL value. You will need to pass it "" in that case.
>
> [stephan at host:~/tmp]$ cat foo.cpp
> #include
>
> int main(){
> std::string s(0);
> return 0;
> }
>
Minor clarification: what you're doing is using the copy constructor, which
also doesn't like NULL:
[stephan at host:~/tmp]$ cat foo.cpp
int main(){
//std::string s(0);
std::string s = 0;
return 0;
}
[stephan at host:~/tmp]$ gcc -o foo foo.cpp -lstdc++
[stephan at host:~/tmp]$ ./foo
terminate called after throwing an instance of 'std::logic_error'
what(): basic_string::_S_construct null not valid
Aborted
--
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] Retrieving the table info fails
On Mon, Nov 16, 2015 at 6:11 PM, Igor Korot wrote:
> The variables referenced are defined as "std::string" and the code is in
> C++.
>
the std::string(char const *) constructor does not, last time i checked,
accept a NULL value. You will need to pass it "" in that case.
[stephan at host:~/tmp]$ cat foo.cpp
#include
int main(){
std::string s(0);
return 0;
}
[stephan at host:~/tmp]$ gcc -o foo foo.cpp -lstdc++
[stephan at host:~/tmp]$ ./foo
terminate called after throwing an instance of 'std::logic_error'
what(): basic_string::_S_construct null not valid
Aborted
--
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] Retrieving the table info fails
On 11/16/2015 5:26 PM, Igor Korot wrote: > Do you have any idea what would be the affinity if I do this: > > CREATE TABLE test( field1 PRIMARY KEY, field2); > > for both field1 and field2? From the aforementioned documentation article: If the declared type for a column contains the string "BLOB" or *if no type is specified* then the column has affinity BLOB. Emphasis mine. > Again I would guess TEXT, but this is just my guess... May I respectfully suggest that, in place of guessing, you might want to consider reading the fine manual? Starting from the link I humbly offered, perchance? -- Igor Tandetnik
[sqlite] Retrieving the table info fails
Igor, On Mon, Nov 16, 2015 at 4:50 PM, Igor Tandetnik wrote: > On 11/16/2015 4:20 PM, Simon Slavin wrote: >> >> >> On 16 Nov 2015, at 9:09pm, Igor Korot wrote: >> >>> 1|name|varchar(100)|0 | |0 >> >> >> Note that this does not tell you anything about the affinity of the column > > > Does too. Column affinity is deduced from declared type, following the rules > described here: http://www.sqlite.org/datatype3.html#affname > >> SQLite doesn't even have a varchar type. > > > ... but it has TEXT column affinity, which "varchar" indicates. Do you have any idea what would be the affinity if I do this: CREATE TABLE test( field1 PRIMARY KEY, field2); for both field1 and field2? Again I would guess TEXT, but this is just my guess... Thank you. > -- > Igor Tandetnik > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Retrieving the table info fails
On 11/16/2015 4:20 PM, Simon Slavin wrote: > > On 16 Nov 2015, at 9:09pm, Igor Korot wrote: > >> 1|name|varchar(100)|0 | |0 > > Note that this does not tell you anything about the affinity of the column Does too. Column affinity is deduced from declared type, following the rules described here: http://www.sqlite.org/datatype3.html#affname > SQLite doesn't even have a varchar type. ... but it has TEXT column affinity, which "varchar" indicates. -- Igor Tandetnik
[sqlite] Retrieving the table info fails
Simon, On Mon, Nov 16, 2015 at 4:20 PM, Simon Slavin wrote: > > On 16 Nov 2015, at 9:09pm, Igor Korot wrote: > >> 1|name|varchar(100)|0 | |0 > > Note that this does not tell you anything about the affinity of the column, > or the types of the values in it. SQLite doesn't even have a varchar type. No, it does not. But I would guess it should have a "string" affinity. Is there a command to check on that? > >> "dflt_value" field may or may not have a value. > > > It might be interesting to know what type the value in the above line has. The type or affinity? Thank you. P.S.: I just made that table as compatible with other DBMS. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Retrieving the table info fails
Simon, On Mon, Nov 16, 2015 at 3:13 PM, Simon Slavin wrote: > > On 16 Nov 2015, at 7:40pm, Igor Korot wrote: > >> But if I issue this PRAGMA command the field name, field type and the PK >> are guaranteed to have some values, right? > > No. Create a field without a declared type and use that as your primary key. > >> You can't create a field without a name > > Correct. At least I think so. > >> or the type > > Wrong. > > 178:~ simon$ sqlite3 ~/Desktop/fred.sql > SQLite version 3.8.10.2 2015-05-20 18:17:19 > Enter ".help" for usage hints. > sqlite> CREATE TABLE myTable (fred PRIMARY KEY); > sqlite> PRAGMA table_info(myTable); > 0|fred||0||1 > sqlite> PRAGMA index_list(myTable); > 0|sqlite_autoindex_myTable_1|1|pk|0 > sqlite> PRAGMA index_info(sqlite_autoindex_myTable_1); > 0|0|fred > sqlite> PRAGMA index_xinfo(sqlite_autoindex_myTable_1); > 0|0|fred|0|BINARY|1 > 1|-1||0|BINARY|0 > sqlite> > > I'd like to emphasise something Scott Hess wrote. There is considerable > leeway in the documentation for PRAGMAs and things can change from one > version of SQLite to another. Pragmas like this are for use only when you > don't know the answers. For example for database management software which > has to operate on any SQLite database. Let's go to the example (I understand better this way): sqlite> PRAGMA table_info(leagues); cid|name|type |notnull| dflt_value|pk 0|id |integer |0 | |1 1|name|varchar(100)|0 | |0 2|drafttype |integer(1)|0 | |0 3|scoringtype |integer(1)|0 | |0 4|roundvalues |integer(1)|0 | |0 5|leaguetype|char(5)|0 | |0 6|salary |integer|0 | |0 7|benchplayers|integer(1) |0 | |0 In this output the field name is always will have a value. The field "type" may or may not have it. The "notnull" field I believe also will always have a value - if one won't set it on the table creation, the engine will assign it. "dflt_value" field may or may not have a value. And finally a "pk" field will always have a value - it is a boolean field which wil be defined either by the programmer or engine. Am I right? Thank you. BTW, you don't have to make a field primary key in order for it not to have a type. I guess its just a beauty of the SQLite engine. And of course those things should be documented somewhere - inconsistencies between PRAGMA's in different SQLite versions. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Retrieving the table info fails
Hi,
On Mon, Nov 16, 2015 at 2:20 PM, R Smith wrote:
>
>
> On 2015/11/16 7:59 PM, Igor Korot wrote:
>>
>> Stephan,
>>
>> On Mon, Nov 16, 2015 at 12:42 PM, Stephan Beal
>> wrote:
>>>
>>> On Mon, Nov 16, 2015 at 6:11 PM, Igor Korot wrote:
>>>
The variables referenced are defined as "std::string" and the code is in
C++.
>>> the std::string(char const *) constructor does not, last time i checked,
>>> accept a NULL value. You will need to pass it "" in that case.
>>>
>>> [stephan at host:~/tmp]$ cat foo.cpp
>>> #include
>>>
>>> int main(){
>>> std::string s(0);
>>> return 0;
>>> }
>>>
>>> [stephan at host:~/tmp]$ gcc -o foo foo.cpp -lstdc++
>>> [stephan at host:~/tmp]$ ./foo
>>> terminate called after throwing an instance of 'std::logic_error'
>>>what(): basic_string::_S_construct null not valid
>>> Aborted
>>
>> OK, it looks like the value is NULL instead of "".
>> I will change the code accordingly.
>>
>> BTW, are only name, type and pk fields are guaranteed to have a value?
>
>
> Nothing is guaranteed to have a value unless created with NOT NULL in the
> field specification in the CREATE TABLE schema SQL.
>
> Usually (in most DBs) a Primary key is guaranteed to not have NULL values,
> but in SQLite it is permissible if the PK is not specifically stated to be
> NOT NULL.
Yes.
But if I issue this PRAGMA command the field name, field type and the PK
are guaranteed to have some values, right?
You can't create a field without a name or the type and the field is either part
of the PK or not.
Please correct me if I'm wrong.
Thank you.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Retrieving the table info fails
Stephan,
On Mon, Nov 16, 2015 at 12:42 PM, Stephan Beal wrote:
> On Mon, Nov 16, 2015 at 6:11 PM, Igor Korot wrote:
>
>> The variables referenced are defined as "std::string" and the code is in
>> C++.
>>
>
> the std::string(char const *) constructor does not, last time i checked,
> accept a NULL value. You will need to pass it "" in that case.
>
> [stephan at host:~/tmp]$ cat foo.cpp
> #include
>
> int main(){
> std::string s(0);
> return 0;
> }
>
> [stephan at host:~/tmp]$ gcc -o foo foo.cpp -lstdc++
> [stephan at host:~/tmp]$ ./foo
> terminate called after throwing an instance of 'std::logic_error'
> what(): basic_string::_S_construct null not valid
> Aborted
OK, it looks like the value is NULL instead of "".
I will change the code accordingly.
BTW, are only name, type and pk fields are guaranteed to have a value?
Thank you.
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
> those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Retrieving the table info fails
On 11/16/2015 12:51 PM, Igor Korot wrote: > Also, are you saying that if I have a integer field with the default value of > 1, > I will not be able to retrieve it with sqliteColumnText()? You might be - I think SQLite will automatically convert it to the string "1". -- Igor Tandetnik
[sqlite] Retrieving the table info fails
Igor, On Mon, Nov 16, 2015 at 12:42 PM, Igor Tandetnik wrote: > On 11/16/2015 12:11 PM, Igor Korot wrote: >> >> The crash occurs when the program tries to retrieve the value for >> "fieldDefaultValue". >> Looking at the output of "PRAGMA table_info();" I see >> that the default value column is empty (i.e. the column does not have >> any value) and the column type is integer and field is a PK. >> I think it also may be set as "Auto-Increment". >> >> The crash happens because the program hit a NULL pointer. >> >> Any idea on what to look for? > > > I'm not sure I understand the nature of the problem. Yes, it is entirely > possible - common even - for a column to have no declared default value; in > that case, PRAGMA table_info would report NULL in the corresponding column. > Further, where there is a default value, it doesn't have to be of TEXT type. It looks like I falsely assumed that it will return an empty string instead. Guess I was wrong. Also, are you saying that if I have a integer field with the default value of 1, I will not be able to retrieve it with sqliteColumnText()? > > You seem to assume the default value always exists and is always a string, > and so it's OK to use sqliteColumnText unconditionally - either assumption > may not hold, and so it's not OK. See above. I know the default value is optional, but thought it will be "". Thank you. > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Retrieving the table info fails
On 11/16/2015 12:11 PM, Igor Korot wrote: > The crash occurs when the program tries to retrieve the value for > "fieldDefaultValue". > Looking at the output of "PRAGMA table_info();" I see > that the default value column is empty (i.e. the column does not have > any value) and the column type is integer and field is a PK. > I think it also may be set as "Auto-Increment". > > The crash happens because the program hit a NULL pointer. > > Any idea on what to look for? I'm not sure I understand the nature of the problem. Yes, it is entirely possible - common even - for a column to have no declared default value; in that case, PRAGMA table_info would report NULL in the corresponding column. Further, where there is a default value, it doesn't have to be of TEXT type. You seem to assume the default value always exists and is always a string, and so it's OK to use sqliteColumnText unconditionally - either assumption may not hold, and so it's not OK. -- Igor Tandetnik
[sqlite] Retrieving the table info fails
Hi, ALL,
I was successfully able to use Mr. Hipp' suggestion:
char *z = sqlite3_mprintf("PRAGMA table_info(\"%w\");", zTableName);
The prepare and step z.
Then: sqlite3_free(z);
However, for some reason, I'm getting crash. Here is the relevant code:
fieldName = reinterpret_cast( sqliteColumnText( stmt2, 1 ) );
fieldType = reinterpret_cast( sqliteColumnText( stmt2, 2 ) );
fieldIsNull = sqliteColumnInt( stmt2, 3 );
fieldDefaultValue = reinterpret_cast( sqliteColumnText(
stmt2, 4 ) );
fieldPK = sqliteColumnInt( stmt2, 5 );
fields.push_back( Field( fieldName, fieldType, fieldDefaultValue,
fieldIsNull, fieldPK == 1 ? true : false ) );
The crash occurs when the program tries to retrieve the value for
"fieldDefaultValue".
Looking at the output of "PRAGMA table_info();" I see
that the default value column is empty (i.e. the column does not have
any value) and the column type is integer and field is a PK.
I think it also may be set as "Auto-Increment".
The crash happens because the program hit a NULL pointer.
Any idea on what to look for?
The variables referenced are defined as "std::string" and the code is in C++.
Thank you.
[sqlite] Retrieving the table info fails
On Mon, Nov 16, 2015 at 11:20 AM, R Smith wrote: > On 2015/11/16 7:59 PM, Igor Korot wrote: >> >> BTW, are only name, type and pk fields are guaranteed to have a value? >> > > Nothing is guaranteed to have a value unless created with NOT NULL in the > field specification in the CREATE TABLE schema SQL. > > Usually (in most DBs) a Primary key is guaranteed to not have NULL values, > but in SQLite it is permissible if the PK is not specifically stated to be > NOT NULL. I'll go one further - since this is the results from a PRAGMA call, IMHO you should make no assumptions at all about whether any of the return values can be NULL. PRAGMA are entirely non-standard, and unless something is specifically guaranteed the outcome is open to changes due to future implementation changes. -scott
