problem with inserting non NULL values into sqlite table

[TTTTT]

hello everyone !

I`m trying to use sqlite database with c++, but something isnt working right...

The problem I`m dealing here is that i cant insert other datas into the table i`ve created except NULL, because i get following error message:

Error in select statement : INSERT INTO a (a,b,c,d) VALUES (NULL, NULL, something_else, NULL) [no such column: something_else].


when i write :

insert(4, aa, "NULL", "NULL", "NULL");

then NULL "values" are inserted

I guess i`ve made some mistake with using pointers, but i dont see it.. I`m not very experienced programmer..

here is the main function:



int main ()
{  

char data_base_name [20];
char *db_name;
db_name = data_base_name;

char tab_name [20];
table_name=tab_name;

open_db ("db_name");

cout<<"Insert table name:"<<endl;
cin>>tab_name;

char a[10]="a";
char aa[100]="NULL";

select_stmt ("DROP TABLE a");

create_table (4, a, "b", "c", "d");

insert(4, aa, "NULL", "something_else", "NULL");

sqlite3_close(db);

getchar ();

return 0;
}






function create_table is working ok, if i`m not wrong..
here it is:




int create_table (int no_col, char *fmt, ...)

{
int i;
char f[500] = "CREATE TABLE ";

va_list ptr;
va_start (ptr, fmt);
        for (i=0; i<(no_col-1); i++)
        {
        strcat (fmt, ",");            // fmt = a ,b ,c ,d
        strcat(fmt, (va_arg (ptr, char*)));
        }
va_end (ptr);

fmt1 = fmt;   //fmt1- global pointer

strcat (f, table_name);   // "CREATE TABLE x
strcat (f, " (");         // "CREATE TABLE x "(
strcat (f, fmt);          // "CREATE TABLE x ( a,b,c,d
strcat (f,")");           // "CREATE TABLE x (a,b,c,d)"

char * stmt = f  ;
printf ("\nstmt = %s\n", stmt);
select_stmt (stmt);

return 0;
}






insert function:




int insert (int no_col, char *fmt2, ... )
{
int i;
va_list ap;
va_start (ap, fmt2);

for (i=0; i<(no_col-1); i++)
{
        strcat (fmt2, ",");
        strcat (fmt2, (va_arg(ap, char*)));
}
va_end (ap);

char k[500]= "INSERT INTO ";
strcat (k, table_name );
strcat (k, " ( ");
strcat (k,  fmt1);
strcat (k, ") ");
strcat (k, "VALUES (");
strcat (k, fmt2);
strcat (k, ")");
printf ("\nk = %s\n\n", k);

char * stmt = k;
select_stmt (stmt);

return 0;
}







any help and auggestions very appreciated..

T

[Pavel Ivanov-2]

> Error in select statement : INSERT INTO a (a,b,c,d) VALUES (NULL, NULL,
> something_else, NULL) [no such column: something_else].

So what are you trying to insert by this statement? What is
"something_else"? If you suppose it to be a string then you have to
enclose it in single quotes like this:

INSERT INTO a (a,b,c,d) VALUES (NULL, NULL, 'something_else', NULL)

Pavel

On Thu, Oct 29, 2009 at 11:06 AM, TTTTT <erlichkeit@...> wrote:

>
> hello everyone !
>
> I`m trying to use sqlite database with c++, but something isnt working
> right...
>
> The problem I`m dealing here is that i cant insert other datas into the
> table i`ve created except NULL, because i get following error message:
>
> Error in select statement : INSERT INTO a (a,b,c,d) VALUES (NULL, NULL,
> something_else, NULL) [no such column: something_else].
>
>
> when i write :
>
> insert(4, aa, "NULL", "NULL", "NULL");
>
> then NULL "values" are inserted
>
> I guess i`ve made some mistake with using pointers, but i dont see it.. I`m
> not very experienced programmer..
>
> here is the main function:
>
>
>
> int main ()
> {
>
> char data_base_name [20];
> char *db_name;
> db_name = data_base_name;
>
> char tab_name [20];
> table_name=tab_name;
>
> open_db ("db_name");
>
> cout<<"Insert table name:"<<endl;
> cin>>tab_name;
>
> char a[10]="a";
> char aa[100]="NULL";
>
> select_stmt ("DROP TABLE a");
>
> create_table (4, a, "b", "c", "d");
>
> insert(4, aa, "NULL", "something_else", "NULL");
>
> sqlite3_close(db);
>
> getchar ();
>
> return 0;
> }
>
>
>
>
>
>
> function create_table is working ok, if i`m not wrong..
> here it is:
>
>
>
>
> int create_table (int no_col, char *fmt, ...)
>
> {
> int i;
> char f[500] = "CREATE TABLE ";
>
> va_list ptr;
> va_start (ptr, fmt);
>        for (i=0; i<(no_col-1); i++)
>        {
>        strcat (fmt, ",");            // fmt = a ,b ,c ,d
>        strcat(fmt, (va_arg (ptr, char*)));
>        }
> va_end (ptr);
>
> fmt1 = fmt;   //fmt1- global pointer
>
> strcat (f, table_name);   // "CREATE TABLE x
> strcat (f, " (");         // "CREATE TABLE x "(
> strcat (f, fmt);          // "CREATE TABLE x ( a,b,c,d
> strcat (f,")");           // "CREATE TABLE x (a,b,c,d)"
>
> char * stmt = f  ;
> printf ("\nstmt = %s\n", stmt);
> select_stmt (stmt);
>
> return 0;
> }
>
>
>
>
>
>
> insert function:
>
>
>
>
> int insert (int no_col, char *fmt2, ... )
> {
> int i;
> va_list ap;
> va_start (ap, fmt2);
>
> for (i=0; i<(no_col-1); i++)
> {
>        strcat (fmt2, ",");
>        strcat (fmt2, (va_arg(ap, char*)));
> }
> va_end (ap);
>
> char k[500]= "INSERT INTO ";
> strcat (k, table_name );
> strcat (k, " ( ");
> strcat (k,  fmt1);
> strcat (k, ") ");
> strcat (k, "VALUES (");
> strcat (k, fmt2);
> strcat (k, ")");
> printf ("\nk = %s\n\n", k);
>
> char * stmt = k;
> select_stmt (stmt);
>
> return 0;
> }
>
>
>
>
>
>
>
> any help and auggestions very appreciated..
>
> T
>
>
>
>
>
>
>
>
> --
> View this message in context: http://www.nabble.com/problem-with-inserting-non-NULL-values-into-sqlite-table-tp26114852p26114852.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@...
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Igor Tandetnik]

TTTTT <erlichkeit@...> wrote:
> The problem I`m dealing here is that i cant insert other datas into
> the table i`ve created except NULL, because i get following error
> message:
>
> Error in select statement : INSERT INTO a (a,b,c,d) VALUES (NULL,
> NULL, something_else, NULL) [no such column: something_else].

Your statement should read

INSERT INTO a (a,b,c,d) VALUES (NULL, NULL, 'something_else', NULL)

Note single quotes around 'something_else'. Without quotes, something_else is an identifier which is supposed to name a column - hence the error. With quotes, it's a string literal.

Better still, use a parameterized statement - see http://sqlite.org/c3ref/bind_blob.html

Igor Tandetnik


_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Nataraj]

Hi

How do I catch a error raised by a trigger from a programming language
like  C++ or freepascal?

Do we have a call back to do the same?

regards

Nataraj




On Thu, Oct 29, 2009 at 8:36 PM, TTTTT <erlichkeit@...> wrote:

>
> hello everyone !
>
> I`m trying to use sqlite database with c++, but something isnt working
> right...
>
> The problem I`m dealing here is that i cant insert other datas into the
> table i`ve created except NULL, because i get following error message:
>
> Error in select statement : INSERT INTO a (a,b,c,d) VALUES (NULL, NULL,
> something_else, NULL) [no such column: something_else].
>
>
> when i write :
>
> insert(4, aa, "NULL", "NULL", "NULL");
>
> then NULL "values" are inserted
>
> I guess i`ve made some mistake with using pointers, but i dont see it.. I`m
> not very experienced programmer..
>
> here is the main function:
>
>
>
> int main ()
> {
>
> char data_base_name [20];
> char *db_name;
> db_name = data_base_name;
>
> char tab_name [20];
> table_name=tab_name;
>
> open_db ("db_name");
>
> cout<<"Insert table name:"<<endl;
> cin>>tab_name;
>
> char a[10]="a";
> char aa[100]="NULL";
>
> select_stmt ("DROP TABLE a");
>
> create_table (4, a, "b", "c", "d");
>
> insert(4, aa, "NULL", "something_else", "NULL");
>
> sqlite3_close(db);
>
> getchar ();
>
> return 0;
> }
>
>
>
>
>
>
> function create_table is working ok, if i`m not wrong..
> here it is:
>
>
>
>
> int create_table (int no_col, char *fmt, ...)
>
> {
> int i;
> char f[500] = "CREATE TABLE ";
>
> va_list ptr;
> va_start (ptr, fmt);
>        for (i=0; i<(no_col-1); i++)
>        {
>        strcat (fmt, ",");            // fmt = a ,b ,c ,d
>        strcat(fmt, (va_arg (ptr, char*)));
>        }
> va_end (ptr);
>
> fmt1 = fmt;   //fmt1- global pointer
>
> strcat (f, table_name);   // "CREATE TABLE x
> strcat (f, " (");         // "CREATE TABLE x "(
> strcat (f, fmt);          // "CREATE TABLE x ( a,b,c,d
> strcat (f,")");           // "CREATE TABLE x (a,b,c,d)"
>
> char * stmt = f  ;
> printf ("\nstmt = %s\n", stmt);
> select_stmt (stmt);
>
> return 0;
> }
>
>
>
>
>
>
> insert function:
>
>
>
>
> int insert (int no_col, char *fmt2, ... )
> {
> int i;
> va_list ap;
> va_start (ap, fmt2);
>
> for (i=0; i<(no_col-1); i++)
> {
>        strcat (fmt2, ",");
>        strcat (fmt2, (va_arg(ap, char*)));
> }
> va_end (ap);
>
> char k[500]= "INSERT INTO ";
> strcat (k, table_name );
> strcat (k, " ( ");
> strcat (k,  fmt1);
> strcat (k, ") ");
> strcat (k, "VALUES (");
> strcat (k, fmt2);
> strcat (k, ")");
> printf ("\nk = %s\n\n", k);
>
> char * stmt = k;
> select_stmt (stmt);
>
> return 0;
> }
>
>
>
>
>
>
>
> any help and auggestions very appreciated..
>
> T
>
>
>
>
>
>
>
>
> --
> View this message in context: http://www.nabble.com/problem-with-inserting-non-NULL-values-into-sqlite-table-tp26114852p26114852.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@...
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[TTTTT]

hey! Thank you very much..
i wanted to insert string and i have forgotten about a fact i need to use these quotes  ' '..
that has solved the problem...
Thank you..



Nataraj,
 
i am using sqlite3_exec function to get error... and yes, call back is one of arguments passed to the function..
check this:   http://www.sqlite.org/c3ref/exec.html 

[TTTTT]

Your statement should read

INSERT INTO a (a,b,c,d) VALUES (NULL, NULL, 'something_else', NULL)

Note single quotes around 'something_else'. Without quotes, something_else is an identifier which is supposed to name a column - hence the error. With quotes, it's a string literal.

Better still, use a parameterized statement - see http://sqlite.org/c3ref/bind_blob.html

Igor Tandetnik


_______________________________________________
sqlite-users mailing list
[e-mail]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users







Could you please explain me the difference between using select_statement function, for example: "INSERT INTO x (a,b) VALUES ('value1', '' value2')"  and using parametrized statement like sqlite3_bind_text ?

i have made a function, which uses variable list of arguments, to insert string values into a table.. Here is how it looks like:


int insert (int no_column, char *fmt2, ... )
{
int i;
va_list ap;
va_start (ap, fmt2);

for (i=0; i<(no_column-1); i++)
{
        strcat (fmt2, ", '");
        strcat (fmt2, (va_arg(ap, char*)));
        strcat (fmt2, "'");
}
va_end (ap);

char k[500]= "INSERT INTO ";
strcat (k, table_name );
strcat (k, " ( ");
strcat (k,  fmt1);
strcat (k, ") ");
strcat (k, "VALUES (");
strcat (k, fmt2);
strcat (k, ")");

char * stmt = k;
select_stmt (stmt);

return 0;
}



 Would it be better to use parameterized  statement. like bind_text, and if so, why is it better ?

[Swithun Crowe]

Hello

T Could you please explain me the difference between using
T select_statement function, for example: "INSERT INTO x (a,b) VALUES
T ('value1', '' value2')" and using parametrized statement like
T sqlite3_bind_text ?

Parameterised statements let you reuse your statements. If you want to
insert several rows, then you parse your statement, then bind the
parameters, do the insert and can then rebind different values to the
parameters. It is faster than having to parse a statement each time.

Also, parameterised statements will handle escaping for you. Your function
would generate invalid or dangerous SQL if some of the arguments contained
quote characters.

Swithun.
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[SimonDavies]

2009/11/5 TTTTT <erlichkeit@...>:
>
> Could you please explain me the difference between using select_statement
> function, for example: "INSERT INTO x (a,b) VALUES ('value1', '' value2')"
> and using parametrized statement like sqlite3_bind_text ?
>
> i have made a function, which uses variable list of arguments, to insert
> string values into a table.. Here is how it looks like:
>
.
.
.
>
>
>  Would it be better to use parameterized  statement. like bind_text, and if
> so, why is it better ?
>

Yes

Performance is better if the same select is repeated many times.

Also you are vulnerable to SQL injection if some of your input args
are user input;
see http://en.wikipedia.org/wiki/SQL_injection

Rgds,
Simon
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Igor Tandetnik]

Simon Davies
<simon.james.davies@...> wrote:
> Also you are vulnerable to SQL injection if some of your input args
> are user input;
> see http://en.wikipedia.org/wiki/SQL_injection

See also http://xkcd.com/327/

Igor Tandetnik

_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[P Kishor-3]

On Thu, Nov 5, 2009 at 11:43 AM, Igor Tandetnik <itandetnik@...> wrote:

> Simon Davies
> <simon.james.davies@...> wrote:
> > Also you are vulnerable to SQL injection if some of your input args
> > are user input;
> > see http://en.wikipedia.org/wiki/SQL_injection
>
> See also http://xkcd.com/327/


ha!

This should be in the sqlite wiki



>
>
> Igor Tandetnik
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@...
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users