[sqlite] Working with blob

[deltagam...@gmx.net]

What is a convenient way to store the path from a external blob (jpg,
pdf) ?
How can I retrieve this blob ?
Is there a Frontend which opens the jpg directly from the listed
table-content ?

If I store the blob directly in the sqlite, is there a way to open the
blob directly with the respective programm (e.g. irfanview or a pdf with
acroread) ? I just discovered recently sqlitespeed, but do not know how
to handle the problems described above.

[sqlite] newest 3 entries

[deltagam...@gmx.net]

I have a select statement like:
"Select id, eventdate, eventtype,  FROM eventlog WHERE eventtype in 
('special')  "


Now I like to receive only the newest 3 entries in the table eventlog 
where the eventtype is 'special'



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

Re: [sqlite] newest 3 entries

[deltagam...@gmx.net]

Am 08.11.2012 21:17, schrieb Igor Tandetnik:

On 11/8/2012 3:10 PM, deltagam...@gmx.net wrote:

I have a select statement like:
"Select id, eventdate, eventtype,  FROM eventlog WHERE eventtype in
('special')  "

Now I like to receive only the newest 3 entries in the table eventlog
where the eventtype is 'special'


select id, eventdate, eventtype FROM eventlog WHERE eventtype in 
('special')

order by eventdate desc limit 3;

Yes, thx Igor, I got it too now, long time that I was occupied with 
sqlite ;)


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

Re: [sqlite] SQLite Input with validation and lookup

[deltagam...@gmx.net]

Am 16.08.2013 17:47, schrieb Kai Peters:

Stephen,

you might want to look at

http://dabodev.com/

Among other DBs it does support Sqlite and is fully cross-platform

Cheers,
Kai




Hello Stephen,

maybe another alternative is, to run FoxPro on a linux machine with the 
windows emulator wine.


There is a lot of windows software running directly on linux, so give it 
a try. At least with older FoxPro versions, people achieved reasonable 
results.


http://appdb.winehq.org/objectManager.php?sClass=application=296

http://paulmcnett.com/vfp/wine/VFPonLinux_foxtalk2.html


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

[sqlite] How to use variables in statements ?

[deltagam...@gmx.net]

Im using c++ and like to write from within c++ into sqlite3 db.
I saw some tutorials on the sqlite pages, but the
statements have always been defined in advance and they have been constant.

How to add now new data to sqlite if you dont know in advance the data ?
I think the statement has to be build somehow with variables or binding 
(maybe better because of sql injection ? ).



I would appreciate it if someone has some example.


Thx in advance.


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

[sqlite] How to save images in sqlite3

[deltagam...@gmx.net]

Hello,

is it possible to save images or pdf in sqlite ?

Is it possible to just save the path to an image/ binary object
an sqlite gets the object then at runtime ?

thx in advance
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Insert and select date and time

[deltagam...@gmx.net]

In the GUI I have an calendar element.
Later in the GUI the date-boundaries should be choosen with the calendar 
element, to show the report

only for this timeframe.
In a "report window" date and time are 2 different columns.
The data itself is determined from another c++ programm and written in a 
sqlite3 db.


Now the question, is it ok to use the sqlite3 DATETIME('now') function 
to store the
data in sqlite and "parse/split" it when it is retrieved from the GUI 
into date and time part ?

In this case I thought of a trigger to fill the db automatically
-- 
CREATE TRIGGER insert_t1_timestamp AFTER  INSERT ON t1
BEGIN
 UPDATE t1 SET timestamp = DATETIME('NOW')  WHERE rowid = new.rowid;
END;
-- 
If I dont use the trigger, would I comitt a string to my timestamp column ?
Has someone an example ?
And the other way, if I select from the db, how to I get the timestamp 
in the

c++ prog ?

thx in advance
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] insert in db

[deltagam...@gmx.net]

I defined in main()  my char intype[]="chattinges";
and call the insert_in_db  with insert_in_db(intype, 7);
but in the db is written only chat instead of chattings.
Where is the problem ?


void insert_in_db(char const *eventtype, int zaehler) {

int rc;
char *exec_errmsg;

const char dbname[] = "ah.db";

sqlite3 *db = NULL;



rc = sqlite3_open(dbname, );
if(SQLITE_OK != rc) {
fprintf(stderr, "Can't open database %s (%i): %s\n", dbname, 
rc, sqlite3_errmsg(db));

sqlite3_close(db);
exit(1);
}


const char insert_sql[] = "INSERT INTO eventlog (eventtype, 
counter) VALUES (?,?)";

sqlite3_stmt *insert_stmt = NULL;



rc = sqlite3_prepare_v2(db, insert_sql, -1, _stmt, NULL);
if(SQLITE_OK != rc) {
fprintf(stderr, "Can't prepare insert statment %s (%i): %s\n", 
insert_sql, rc, sqlite3_errmsg(db));

sqlite3_close(db);
exit(1);
}



char injectionattack[] =  "contering";
// The NULL is "Don't attempt to free() the value when it's bound", 
since it's on the stack here
//rc = sqlite3_bind_text(insert_stmt, 1, injectionattack, 
sizeof(injectionattack), NULL);
rc = sqlite3_bind_text(insert_stmt, 1, eventtype, 
sizeof(eventtype), NULL);



if(SQLITE_OK != rc) {
fprintf(stderr, "Error binding value in insert (%i): %s\n", rc, 
sqlite3_errmsg(db));

sqlite3_close(db);
exit(1);
} else {
printf("Successfully bound string for insert: '%s'\n", eventtype);
}


//double realvalue = 3.14159;
//int realvalue = 3;

//rc = sqlite3_bind_double(insert_stmt, 2, realvalue);
rc = sqlite3_bind_int(insert_stmt, 2, zaehler);

if(SQLITE_OK != rc) {
fprintf(stderr, "Error binding value in insert (%i): %s\n", rc, 
sqlite3_errmsg(db));

sqlite3_close(db);
exit(1);
} else {
printf("Successfully bound real for insert: %d\n", zaehler);
}


rc = sqlite3_step(insert_stmt);
if(SQLITE_DONE != rc) {
fprintf(stderr, "insert statement didn't return DONE (%i): 
%s\n", rc, sqlite3_errmsg(db));

} else {
printf("INSERT completed\n\n");
}

sqlite3_finalize(insert_stmt);

sqlite3_close(db);

};


int main() {

char intype[]="chattinges";

cout << endl;

// Create an in-memory database
//const char dbname[] = ":memory:";
const char dbname[] = "ah.db";

// Actual database handle
sqlite3 *db = NULL;

// Database commands
const char create_sql[] = "CREATE TABLE eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate datetime default current_timestamp"
"eventtype TEXT,"
"counter INTEGER"
")";

// SQLite return value
int rc;

// Open the database
rc = sqlite3_open(dbname, );
if(SQLITE_OK != rc) {
fprintf(stderr, "Can't open database %s (%i): %s\n", dbname, 
rc, sqlite3_errmsg(db));

sqlite3_close(db);
exit(1);
}


// SQLite exec returns errors with this
char *exec_errmsg;

// Use exec to run simple statements that can only fail/succeed
rc = sqlite3_exec(db, create_sql, NULL, NULL, _errmsg);
if(SQLITE_OK != rc) {
fprintf(stderr, "Error creating table (%i): %s\n", rc, 
exec_errmsg);

sqlite3_free(exec_errmsg);
sqlite3_close(db);
//exit(1);
}


sqlite3_close(db);

insert_in_db(intype, 7);

exit(0);


sqlite3_close(db);
return 0;
}

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

Re: [sqlite] insert in db

[deltagam...@gmx.net]

Am 18.06.2012 22:31, schrieb Pavel Ivanov:

rc = sqlite3_bind_text(insert_stmt, 1, eventtype, sizeof(eventtype),
NULL);

You measure size of pointer eventtype here. If you want to know size
of the string it points to you need to call strlen(eventtype) instead
of sizeof(eventtype).


Pavel


On Mon, Jun 18, 2012 at 4:26 PM, deltagam...@gmx.net
<deltagam...@gmx.net>  wrote:

I defined in main()  my char intype[]="chattinges";
and call the insert_in_db  with insert_in_db(intype, 7);
but in the db is written only chat instead of chattings.
Where is the problem ?


void insert_in_db(char const *eventtype, int zaehler) {

int rc;
char *exec_errmsg;

const char dbname[] = "ah.db";

sqlite3 *db = NULL;



rc = sqlite3_open(dbname,);
if(SQLITE_OK != rc) {
fprintf(stderr, "Can't open database %s (%i): %s\n", dbname, rc,
sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}


const char insert_sql[] = "INSERT INTO eventlog (eventtype, counter)
VALUES (?,?)";
sqlite3_stmt *insert_stmt = NULL;



rc = sqlite3_prepare_v2(db, insert_sql, -1,_stmt, NULL);
if(SQLITE_OK != rc) {
fprintf(stderr, "Can't prepare insert statment %s (%i): %s\n",
insert_sql, rc, sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}



char injectionattack[] =  "contering";
// The NULL is "Don't attempt to free() the value when it's bound", since
it's on the stack here
//rc = sqlite3_bind_text(insert_stmt, 1, injectionattack,
sizeof(injectionattack), NULL);
rc = sqlite3_bind_text(insert_stmt, 1, eventtype, sizeof(eventtype),
NULL);


if(SQLITE_OK != rc) {
fprintf(stderr, "Error binding value in insert (%i): %s\n", rc,
sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
} else {
printf("Successfully bound string for insert: '%s'\n", eventtype);
}


//double realvalue = 3.14159;
//int realvalue = 3;

//rc = sqlite3_bind_double(insert_stmt, 2, realvalue);
rc = sqlite3_bind_int(insert_stmt, 2, zaehler);

if(SQLITE_OK != rc) {
fprintf(stderr, "Error binding value in insert (%i): %s\n", rc,
sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
} else {
printf("Successfully bound real for insert: %d\n", zaehler);
}


rc = sqlite3_step(insert_stmt);
if(SQLITE_DONE != rc) {
fprintf(stderr, "insert statement didn't return DONE (%i): %s\n", rc,
sqlite3_errmsg(db));
} else {
printf("INSERT completed\n\n");
}

sqlite3_finalize(insert_stmt);

sqlite3_close(db);

};


int main() {

char intype[]="chattinges";

cout<<  endl;

// Create an in-memory database
//const char dbname[] = ":memory:";
const char dbname[] = "ah.db";

// Actual database handle
sqlite3 *db = NULL;

// Database commands
const char create_sql[] = "CREATE TABLE eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate datetime default current_timestamp"
"eventtype TEXT,"
"counter INTEGER"
")";

// SQLite return value
int rc;

// Open the database
rc = sqlite3_open(dbname,);
if(SQLITE_OK != rc) {
fprintf(stderr, "Can't open database %s (%i): %s\n", dbname, rc,
sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}


// SQLite exec returns errors with this
char *exec_errmsg;

// Use exec to run simple statements that can only fail/succeed
rc = sqlite3_exec(db, create_sql, NULL, NULL,_errmsg);
if(SQLITE_OK != rc) {
fprintf(stderr, "Error creating table (%i): %s\n", rc, exec_errmsg);
sqlite3_free(exec_errmsg);
sqlite3_close(db);
//exit(1);
}


sqlite3_close(db);

insert_in_db(intype, 7);

exit(0);


sqlite3_close(db);
return 0;
}

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

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

Thx Pavel Ivanov,
I additional missed the ; at the end of the line  rc = 
sqlite3_bind_text(insert_stmt, 1, eventtype, strlen(eventtype), NULL);
BTW, if i go through the programm with the debugger, there is nothing 
written in the sqlitedb.

Why ?
Im using mscv 2010


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

[sqlite] How to select from table

[deltagam...@gmx.net]

Hello,

how can i select from sqlite3 db ?

How do I retrieve the number of records in a table ?

Thx in advance
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select from table

[deltagam...@gmx.net]

Am 20.06.2012 12:02, schrieb deltagam...@gmx.net:

Hello,

how can i select from sqlite3 db  ?

How do I retrieve the number of records in a table ?

Thx in advance


Sorry forgot to tell, I'm using the c++ API
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with select

[deltagam...@gmx.net]

Hello,

I have a problem with the selection of the row, errorcode 21 s

Indeed I would like to retrieve all rows, but as far as I understand it, 
this construction will retrieve only the last row/insert.


What happens if  the db is closed after the insert and reopened then for 
the select-statement ?




===

#include 
#include 
#include "sqlite3.h"

#include 
#include 
#include 
#include 

using namespace std;


int main() {



string filename = "errorlog.txt";
std::ofstream outfile(filename);

char eventtype[]="zweiter Anlauf/Versuch";
int zaehler = strlen(eventtype);


int rc;
char *exec_errmsg;

const char dbname[] = "ef.db";
sqlite3 *db = NULL;

rc = sqlite3_open(dbname, );
if(SQLITE_OK != rc) {
outfile << "Can't open database "<< dbname << " (" << rc << "): 
" << sqlite3_errmsg(db) << std::endl;
//fprintf(stderr, "Can't open database %s (%i): %s\n", dbname, 
rc, sqlite3_errmsg(db));

sqlite3_close(db);
exit(1);
}

/maybe discard this part 
///

const char create_sql[] = "CREATE TABLE eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate DATETIME default current_timestamp,"
"eventtype TEXT,"
"counter INTEGER"
")";

// Use exec to run simple statements that can only fail/succeed
rc = sqlite3_exec(db, create_sql, NULL, NULL, _errmsg);
if(SQLITE_OK != rc) {
outfile << "Error creating table (" << rc << "): " << 
sqlite3_errmsg(db) << std::endl;
//fprintf(stderr, "Error creating table (%i): %s\n", rc, 
exec_errmsg);

sqlite3_free(exec_errmsg);
//sqlite3_close(db);
//exit(1);
}

/maybe discard this part 
///



const char insert_sql[] = "INSERT INTO eventlog (eventtype, 
counter) VALUES (?,?)";

sqlite3_stmt *insert_stmt = NULL;



rc = sqlite3_prepare_v2(db, insert_sql, -1, _stmt, NULL);
if(SQLITE_OK != rc) {
outfile << "Can't prepare insert statment " << insert_sql << " 
(" << rc << "): " << sqlite3_errmsg(db) << std::endl;
//fprintf(stderr, "Can't prepare insert statment %s (%i): 
%s\n", insert_sql, rc, sqlite3_errmsg(db));

sqlite3_close(db);
exit(1);
}



rc = sqlite3_bind_text(insert_stmt, 1, eventtype, 
strlen(eventtype), NULL);

if(SQLITE_OK != rc) {
outfile << "Error binding value in insert (%i): %s\n", rc, 
sqlite3_errmsg(db);
//fprintf(stderr, "Error binding value in insert (%i): %s\n", 
rc, sqlite3_errmsg(db));

sqlite3_close(db);
exit(1);
} else {
printf("Successfully bound string for insert: '%s'\n", eventtype);
}

rc = sqlite3_bind_int(insert_stmt, 2, zaehler);
if(SQLITE_OK != rc) {
outfile << "Error binding value in insert (%i): %s\n", rc, 
sqlite3_errmsg(db);
//fprintf(stderr, "Error binding value in insert (%i): %s\n", 
rc, sqlite3_errmsg(db));

sqlite3_close(db);
exit(1);
} else {
printf("Successfully bound real for insert: %d\n", zaehler);
}


rc = sqlite3_step(insert_stmt);
if(SQLITE_DONE != rc) {
outfile << "insert statement didn't return DONE (%i): %s\n", 
rc, sqlite3_errmsg(db);
//fprintf(stderr, "insert statement didn't return DONE (%i): 
%s\n", rc, sqlite3_errmsg(db));

} else {
printf("INSERT completed\n\n");
}


// start selecting ///
const char select_sql[] = "SELECT eventtype, counter FROM eventlog 
WHERE id=?";
//const char insert_sql[] = "INSERT INTO eventlog (eventtype, 
counter) VALUES (?,?)";


sqlite3_stmt *select_stmt = NULL;

// Now attempt to get that row out
sqlite3_int64 id = sqlite3_last_insert_rowid(db);
sqlite3_bind_int64(select_stmt, 1, id);


// This is your standard pattern
while(SQLITE_ROW == (rc = sqlite3_step(select_stmt))) {
int col;
printf("Found row\n");
for(col=0; col

Re: [sqlite] How to select from table

[deltagam...@gmx.net]

Am 20.06.2012 14:55, schrieb Igor Tandetnik:

deltagam...@gmx.net wrote:

how can i select from sqlite3 db ?

By executing a SELECT statement, of course. See this example:

http://books.google.com/books?id=VsZ5bUh0XAkC=PA222


How do I retrieve the number of records in a table ?

By running this statement: select count(*) from MyTable;


thx for the hint, it is an interesting book, but in the example on page 222
I get 2 errors

  select_all_from_db.cpp(23): error C3861: 'setup': identifier not found
  select_all_from_db.cpp(41): error C2440: '=' : cannot convert from 
'const unsigned char *' to 'char *'

line 41 is :   name = sqlite3_column_text(stmt, 2 );

I checked on the documentation side http://sqlite.org/capi3ref.html , 
there is nothing about setup ...

And what is the problem with name ?


==
#include 
#include 
#include "sqlite3.h"

#include 
#include 
#include 
#include 

using namespace std;


void select_all_rows_db() {

int rc, i, ncols, id, cid;
char *name, *sql;
sqlite3 *db;
sqlite3_stmt *stmt;

sql = "Select ID, EVENTTYPE FROM eventlog";
sqlite3_open("ah.db", );

setup(db);

sqlite3_prepare(db, sql, strlen(sql), , NULL);
rc = sqlite3_step(stmt);

/*Print column information */
for (i=0; i< ncols; i++ ) {
fprintf(stdout, "Column: name=%s, storage class=%i, declared=%s\n",
sqlite3_column_name(stmt, i ),
sqlite3_column_type(stmt, i ),
sqlite3_column_decltype(stmt, i ));
} // for

fprintf(stdout, "\n");

while(rc == SQLITE_ROW) {
id = sqlite3_column_int(stmt, 0 );
cid = sqlite3_column_int(stmt, 1 );
name = sqlite3_column_text(stmt, 2 );

if (name != NULL ) {
fprintf(stderr, "Row: id=%i, cid=%i, name='%s'\n", id, cid, name);
} else {
/* Field is NULL */
fprintf(stderr, "Row: id=%i, cid=%i, name=NULL\n", id, cid);
}
rc = sqlite3_step(stmt);
} // while

sqlite3_finalize(stmt);
sqlite3_close(db);


} // select_all_rows_db


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

[sqlite] Does sqlite3_exec work with sqlite3_bind ?

[deltagam...@gmx.net]

sqlite3_prepare_v2() , 
sqlite3_step() , and 
sqlite3_finalize() ,


From the documentation  I saw, that sqlite3_exec "includes" 
sqlite3_prepare_v2 , sqlite3_step, sqlite3_finalize.
But if the select_statement contains some variables to bind, is it 
possible to use then the wrapper sqlite3_exec ?


If the select_statement would be
select_statement = "SELECT id, eventdate, eventtype, counter FROM 
eventlog WHERE eventdate BETWEEN ? AND ?";


how would I use the wrapper sqlite3_exec ?


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

Re: [sqlite] Does sqlite3_exec work with sqlite3_bind ?

[deltagam...@gmx.net]

Am 22.06.2012 12:01, schrieb Richard Hipp:

On Fri, Jun 22, 2012 at 1:03 AM, deltagam...@gmx.net <deltagam...@gmx.net>wrote:


sqlite3_prepare_v2() 
<http://sqlite.org/c3ref/**prepare.html<http://sqlite.org/c3ref/prepare.html>>,
sqlite3_step() 
<http://sqlite.org/c3ref/step.**html<http://sqlite.org/c3ref/step.html>>,
and sqlite3_finalize() 
<http://sqlite.org/c3ref/**finalize.html<http://sqlite.org/c3ref/finalize.html>

,

 From the documentation  I saw, that sqlite3_exec "includes"
sqlite3_prepare_v2 , sqlite3_step, sqlite3_finalize.
But if the select_statement contains some variables to bind, is it
possible to use then the wrapper sqlite3_exec ?

If the select_statement would be
select_statement = "SELECT id, eventdate, eventtype, counter FROM eventlog
WHERE eventdate BETWEEN ? AND ?";

how would I use the wrapper sqlite3_exec ?


It is not possible to use sqlite3_bind_() with sqlite3_exec().  Any
variables in the sqlite3_exec() SQL will have the value of NULL.




__**_
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>



Thx for the quick response.

So sqlite3_exec can only be used with "static" sql_statements.

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

[sqlite] Problem with including sqlite3.c into c++ project

[deltagam...@gmx.net]

Hello,

Im using MSVS 2010 for an c++ GUI project.
After including sqlite3.h and sqlite3.c from the amalgamation-3071200
and with the Project Properties--> C/C++  --> Precompiled Headers --> 
Precompiled Header --> Use (/Yu)

I get the error
sqlite3.c : fatal error C1853: 'Debug\Contegos_UI.pch' precompiled 
header file is from a previous version of the compiler, or the 
precompiled header is C++ and you are using it from C (or vice versa)


If I change to Precompiled Header --> Create (/Yc)
I get the error
sqlite3.c(136660): error C2857: '#include' statement specified with the 
/YcStdAfx.h command-line option was not found in the source file



How can I solve this problem ?


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

Re: [sqlite] Problem with including sqlite3.c into c++ project

[deltagam...@gmx.net]

Am 25.06.2012 22:18, schrieb John Drescher:

On Mon, Jun 25, 2012 at 4:15 PM, deltagam...@gmx.net
<deltagam...@gmx.net> wrote:

Hello,

Im using MSVS 2010 for an c++ GUI project.
After including sqlite3.h and sqlite3.c from the amalgamation-3071200
and with the Project Properties--> C/C++  --> Precompiled Headers -->
Precompiled Header --> Use (/Yu)
I get the error
sqlite3.c : fatal error C1853: 'Debug\Contegos_UI.pch' precompiled header
file is from a previous version of the compiler, or the precompiled header
is C++ and you are using it from C (or vice versa)

This is something I see with Visual Studio from time to time (not with
sqlite but I use that with Qt):

Delete Debug\Contegos_UI.pch

and try again.

John


Hello John,
deleting the pch file did not lead to success.
In another non-GUI project , i did succeed by deleting the whole 
build-branch, nor only the pch file. And setting the Create (/Yc) option

But here it does not work.


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

[sqlite] How to retrieve number of records

[deltagam...@gmx.net]

I want to retrieve the number of records in a table,
but dont know how to get access to the result of  " select count(*) from 
tablename "





#include "db_functions.h"

//using namespace std;


void select_count( ) {

int rc, i, ncols, id;
char *sql;
char *evdate, *evtype;
int evctr;

sqlite3 *db;
sqlite3_stmt *stmt;



sql = "Select count(*) from eventlog";
sqlite3_open("ah.db", );

sqlite3_prepare(db, sql, strlen(sql), , NULL);


char create_sql[] = "CREATE TABLE if not exists eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate DATETIME default current_timestamp,"
"eventtype TEXT,"
"counter INTEGER"
")";

rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);

rc = sqlite3_prepare(db, sql, strlen(sql), , NULL);


//while(rc == SQLITE_ROW) {



rc = sqlite3_step(stmt);


//} // while


sqlite3_finalize(stmt);
sqlite3_close(db);
}







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

[sqlite] access from 2 different programms to same sqlite3-db

[deltagam...@gmx.net]

I have a c++ GUI application from where the db is read and the content 
is displayed in a Clistbox.

Then I try to delete some rows from the sqlite3-db from the console.
After rereading from within the GUI the deleted rows are still there.

How is this possible ?

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

Re: [sqlite] access from 2 different programms to same sqlite3-db

[deltagam...@gmx.net]

Am 26.06.2012 16:49, schrieb Richard Hipp:

On Tue, Jun 26, 2012 at 10:46 AM, deltagam...@gmx.net
<deltagam...@gmx.net>wrote:


I have a c++ GUI application from where the db is read and the content is
displayed in a Clistbox.
Then I try to delete some rows from the sqlite3-db from the console.
After rereading from within the GUI the deleted rows are still there.

How is this possible ?


The GUI is holding a read transaction open.  Hence it sees a consistent
snapshot of the database from the moment in time when the transaction was
started.  Subsequent writes to the database are ignored by the GUI until it
closes its current transaction and starts a new one.





How can I close the transaction , and later open a new one ?
BTW, transaction is still open although i use a sqlite3_close(db) ?

Here is the code for reading from the db. By changing within the GUI 
from tab viewevents to another tab and back again to tab viewevents, the 
db is read again and should display all changes to the db which appeared 
during that time.


==

int ReadViewEventsFormDBData()
{
int nRetCode = ERROR_SUCCESS;

// Remove all events from array
m_arrEvents.RemoveAll();

// write  events
Event newEvent;

int rc, id, total_events;
char *sql, *sqltotal;
char *evdate, *evtype;
int evctr;

int the_event_ctr = 0;

CString datetime;
CString datepart;
CString timepart;

sqlite3 *db;
sqlite3_stmt *stmt;

sqlite3_open("ah.db", );

// check if table eventlog exists
char create_sql[] = "CREATE TABLE if not exists eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate DATETIME default current_timestamp,"
"eventtype TEXT,"
"counter INTEGER"
")";

rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);

// select count(*) from eventlog
sqltotal = "Select count(*) from eventlog";
rc = sqlite3_prepare(db, sqltotal, strlen(sqltotal), , NULL);
rc = sqlite3_step(stmt);
total_events = sqlite3_column_int(stmt, 0 );

// select * from eventlog
sql = "Select id, eventdate, eventtype, counter FROM eventlog";
sqlite3_prepare(db, sql, strlen(sql), , NULL);


rc = sqlite3_step(stmt);

while(rc == SQLITE_ROW) {
id = sqlite3_column_int(stmt, 0 );
//cid = sqlite3_column_int(stmt, 1 );
evdate = (char*)sqlite3_column_text(stmt, 1 );
evtype = (char*)sqlite3_column_text(stmt, 2 );
evctr = sqlite3_column_int(stmt, 3 );

datetime = evdate;

datepart = datetime.Mid(0,10);
timepart = datetime.Mid(11,5);

std::cout << datepart << "\t" << timepart << std::endl;

newEvent.m_nEvent = the_event_ctr;
newEvent.m_strLastEventDate = datepart ;
newEvent.m_strEventTime = timepart;
newEvent.m_strEventType = evtype;
newEvent.m_nCount = evctr;

// add the new element to array
m_arrEvents.Add(newEvent);

rc = sqlite3_step(stmt);

// increment eventcounter
the_event_ctr++;

} // while

sqlite3_finalize(stmt);
sqlite3_close(db);
nRetCode = rc;

return nRetCode;

} // ReadViewEventsFormDBData

=

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

Re: [sqlite] access from 2 different programms to same sqlite3-db

[deltagam...@gmx.net]

Am 26.06.2012 17:08, schrieb Pavel Ivanov:

You are leaking stmt statement (re-preparing without finaliznig) and
your call to sqlite3_close returns SQLITE_ERROR because of that, but
you don't even check that so you are leaking database connections as
well.

Pavel


On Tue, Jun 26, 2012 at 11:01 AM, deltagam...@gmx.net
<deltagam...@gmx.net> wrote:

Am 26.06.2012 16:49, schrieb Richard Hipp:


On Tue, Jun 26, 2012 at 10:46 AM, deltagam...@gmx.net
<deltagam...@gmx.net>wrote:


I have a c++ GUI application from where the db is read and the content is
displayed in a Clistbox.
Then I try to delete some rows from the sqlite3-db from the console.
After rereading from within the GUI the deleted rows are still there.

How is this possible ?


The GUI is holding a read transaction open.  Hence it sees a consistent
snapshot of the database from the moment in time when the transaction was
started.  Subsequent writes to the database are ignored by the GUI until
it
closes its current transaction and starts a new one.







void InitialReadEventsData()
{

// Remove all events from array
m_arrEvents.RemoveAll();

// write  events
Event newEvent;

// sqlite3 reading ///
int rc, id, total_events;
char *sql, *sqltotal;
char *evdate, *evtype;
int evctr;

int the_event_ctr = 0;

CString datetime;
CString datepart;
CString timepart;


sqlite3 *db;
sqlite3_stmt *stmt;

sqlite3_open("ah.db", );
/*
// check if table eventlog exists
char create_sql[] = "CREATE TABLE if not exists eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate DATETIME default current_timestamp,"
"eventtype TEXT,"
"counter INTEGER"
")";

rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);


*/

// select count(*) from eventlog
sqltotal = "Select count(*) from eventlog";
rc = sqlite3_prepare(db, sqltotal, strlen(sqltotal), , NULL);
rc = sqlite3_step(stmt);
total_events = sqlite3_column_int(stmt, 0 );

std::cout << total_events << std::endl;


// select * from eventlog
sql = "Select id, eventdate, eventtype, counter FROM eventlog";
sqlite3_prepare(db, sql, strlen(sql), , NULL);

rc = sqlite3_step(stmt);

while(rc == SQLITE_ROW) {
id = sqlite3_column_int(stmt, 0 );
//cid = sqlite3_column_int(stmt, 1 );
evdate = (char*)sqlite3_column_text(stmt, 1 );
evtype = (char*)sqlite3_column_text(stmt, 2 );
evctr = sqlite3_column_int(stmt, 3 );

datetime = evdate;

datepart = datetime.Mid(0,10);
timepart = datetime.Mid(11,5);

std::cout << datepart << "\t" << timepart << std::endl;

newEvent.m_nEvent = the_event_ctr;
newEvent.m_strLastEventDate = datepart ;
newEvent.m_strEventTime = timepart;
newEvent.m_strEventType = evtype;
newEvent.m_nCount = evctr;
WriteEvent(newEvent, the_event_ctr);

rc = sqlite3_step(stmt);

// increment eventcounter
the_event_ctr++;

} // while

rc = sqlite3_reset(stmt);

rc = sqlite3_finalize(stmt);
rc = sqlite3_close(db);

// sqlite3 reading ///

}

=

What am I missing now ? There is a rc = sqlite3_reset(stmt);  but the rc 
= sqlite3_close(db);  still returns error_code 5
The sqlite3_exec is now comment. Do I have to "reset " and finalize this 
part normally too ? How is this done ?


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

Re: [sqlite] access from 2 different programms to same sqlite3-db

[deltagam...@gmx.net]

Am 26.06.2012 18:00, schrieb Pavel Ivanov:

On Tue, Jun 26, 2012 at 11:50 AM, deltagam...@gmx.net
<deltagam...@gmx.net> wrote:

Am 26.06.2012 17:08, schrieb Pavel Ivanov:


You are leaking stmt statement (re-preparing without finaliznig) and
your call to sqlite3_close returns SQLITE_ERROR because of that, but
you don't even check that so you are leaking database connections as
well.

Pavel


On Tue, Jun 26, 2012 at 11:01 AM, deltagam...@gmx.net
<deltagam...@gmx.net> wrote:

Am 26.06.2012 16:49, schrieb Richard Hipp:


On Tue, Jun 26, 2012 at 10:46 AM, deltagam...@gmx.net
<deltagam...@gmx.net>wrote:


I have a c++ GUI application from where the db is read and the content
is
displayed in a Clistbox.
Then I try to delete some rows from the sqlite3-db from the console.
After rereading from within the GUI the deleted rows are still there.

How is this possible ?


The GUI is holding a read transaction open.  Hence it sees a consistent
snapshot of the database from the moment in time when the transaction
was
started.  Subsequent writes to the database are ignored by the GUI until
it
closes its current transaction and starts a new one.





First, I would like to thank all for the great support, a special thanks 
to Pavel Ivanov and Richard Hipp


I think with
==
char create_sql[] = "CREATE TABLE if not exists eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate DATETIME default current_timestamp,"
"eventtype TEXT,"
"counter INTEGER"
")";
rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);
=
it is a convenient way to check if a table exists.

Is it possible to check even if a db file exists and if necessary create 
it from within c++ ?
So I would open the db file  sqlite3_open("ah.db", );   only if it 
really exists ..



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

[sqlite] convert CString for statement

[deltagam...@gmx.net]

Hello I have as input parameter

CString likeexp

but I think in the following statement  likeexp hast to be char []

rc = sqlite3_bind_text(stmt, 1, likeexp, strlen(likeexp), NULL);


How can i convert likeexp to fit the above statement ?

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

[sqlite] sqlite time is 2 hours to late

[deltagam...@gmx.net]

Hello,

>sqlite3 event.db "select datetime('now')";
gives me a time that is 2 hours too late ( 2012-06-27 15:33:13)
than my system time ( win 7 )  17::33:13

How can this be fixed ?


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

Re: [sqlite] sqlite time is 2 hours to late

[deltagam...@gmx.net]

Am 27.06.2012 17:40, schrieb Jay A. Kreibich:


On Wed, Jun 27, 2012 at 05:37:55PM +0200, deltagam...@gmx.net scratched on the 
wall:


Hello,


sqlite3 event.db "select datetime('now')";

gives me a time that is 2 hours too late ( 2012-06-27 15:33:13)
than my system time ( win 7 )  17::33:13

How can this be fixed ?

   Move two timezones to the west.

   (By default all times and dates are UTC.)

-j





I use this from within a c++ application
char create_sql[] = "CREATE TABLE if not exists eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate DATETIME default current_timestamp,"
"eventtype TEXT,"
")";

How do I get the right time in the the column eventdate ?
How to move timezones ?



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

[sqlite] database busy error

[deltagam...@gmx.net]

If I have a pool of external (c++) progs, all writing into the same 
sqlit3 db,

how do i handle this correctly ?
Now, sometimes I get db busy error (5)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] max size of a TEXT field

[deltagam...@gmx.net]

Hello,

I couldnt find in the documentation what the max size of TEXT 
field/column is.


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

[sqlite] using SQLITE_BUSY

[deltagam...@gmx.net]

In http://ideone.com/7lhIh is my example code.

I would like to know if this would be an appropiate implemetation/use of 
BEGIN IMMEDIATE

and sqlite3_busy_timeout.

As I understood from the documentation and literature about Transaction 
and locks
the sqlite3_step is the action state part so I enclosed sqlite3_step 
with BEGIN IMMEDIATE and COMMIT, but then occurred errors from the 
sqlite3_prepare_v2


Thats why i enclosed now all db-concerning steps with BEGIN IMMEDIATE 
and COMMIT.
With this construction appear still some database lock errors but lesser 
then with a

sqlite3_busy_timeout(db, 23) e.g.

As I understood, the BEGIN IMMEDIATE would try to get the RESERVED lock 
once, and if it fails it gets a SQLITE_BUSY.
The BEGIN IMMEDIATE will however try to get the RESERVED lock many times 
, if a sqlite3_busy_timeout is defined, but all tries happen within the 
time defined by the sqlite3_busy_timeout.  Or is it like this, that 
BEGIN IMMEDIATE tries it again exactly once again after the timeout ?
In literature it is also mentioned  that the BEGIN IMMEDIATE has to be 
resetted.

How and where do I have to do this in my example code ?


I would appreciate some suggestions
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] using the SQLITE_BUSY and BEGIN IMMEDIATE

[deltagam...@gmx.net]

In  http://ideone.com/eWPWD  is my example code.

I would like to know if this would be an appropiate implemetation/use of
BEGIN IMMEDIATE
and sqlite3_busy_timeout.

As I understood from the documentation and literature about Transaction
and locks
the sqlite3_step is the action state part so I enclosed sqlite3_step
with BEGIN IMMEDIATE and COMMIT, but then occurred errors from the
sqlite3_prepare_v2

In http://www.mail-archive.com/sqlite-users@sqlite.org/msg17107.html is 
explained why the sqlite3_prepare_v2

can cause a SQLITE_BUSY


So  I enclosed now all db-concerning steps with BEGIN IMMEDIATE
and COMMIT.
With this construction appear still some database lock errors but lesser
then with a
sqlite3_busy_timeout(db, 23) e.g.

As I understood, the BEGIN IMMEDIATE would try to get the RESERVED lock
once, and if it fails it gets a SQLITE_BUSY.

The BEGIN IMMEDIATE will however try to get the RESERVED lock many times
, if a sqlite3_busy_timeout is defined, but all tries happen within the
time defined by the sqlite3_busy_timeout.
This behaviour is promoted here 
http://www.mail-archive.com/sqlite-users@sqlite.org/msg10447.html



Or is it like this, that
BEGIN IMMEDIATE tries it again exactly once again after the timeout ?
In literature it is also mentioned  that the BEGIN IMMEDIATE has to be
resetted.
How and where do I have to do this in my example code ?


I would appreciate some suggestions

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

[sqlite] Selecting NULL

[deltagam...@gmx.net]

Hello,

how to process if the select-statement selects a column with NULL  ?


==
char *mydetails;
char *sql;
sqlite3_stmt *stmt;
sqlite3 *db;
const char dbname[] = "mysqlite.db";


sql = "Select mydetails from mytable";

rc = sqlite3_prepare(db, sql, strlen(sql), , NULL);

rc = sqlite3_step(stmt);


while(rc == SQLITE_ROW) {

// here maybe NULL is returned ?
mydetails = (char*)sqlite3_column_text(stmt, 0 );
rc = sqlite3_step(stmt);
}
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting NULL

[deltagam...@gmx.net]

Am 11.07.2012 19:45, schrieb Pavel Ivanov:

 // here maybe NULL is returned ?
 mydetails = (char*)sqlite3_column_text(stmt, 0 );

Check sqlite3_column_type() before calling sqlite3_column_text(). If
it returns SQLITE_NULL then you have NULL.

Pavel


On Wed, Jul 11, 2012 at 1:40 PM, deltagam...@gmx.net
<deltagam...@gmx.net> wrote:

Hello,

how to process if the select-statement selects a column with NULL  ?


==
char *mydetails;
char *sql;
sqlite3_stmt *stmt;
sqlite3 *db;
const char dbname[] = "mysqlite.db";


sql = "Select mydetails from mytable";

rc = sqlite3_prepare(db, sql, strlen(sql), , NULL);

rc = sqlite3_step(stmt);


while(rc == SQLITE_ROW) {

 // here maybe NULL is returned ?
 mydetails = (char*)sqlite3_column_text(stmt, 0 );
 rc = sqlite3_step(stmt);
}
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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


Pavel, thanks a lot.


If mydetails contains a value, and I delete this value with "SQLite 
Database Browser"
it seems not to be a "real NULL", has someone an explanation for this 
behaviour ?



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

[sqlite] how many rows deleted

[deltagam...@gmx.net]

Can I retrieve how many rows  are affected from a delete statement like
std::string delete_stmt("DELETE FROM mylog WHERE 
strftime('%Y-%m-%d',thedate) BETWEEN ? AND ? ");





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

Re: [sqlite] how many rows deleted

[deltagam...@gmx.net]

Am 12.07.2012 01:36, schrieb Igor Tandetnik:

On 7/11/2012 7:21 PM, deltagam...@gmx.net wrote:

Can I retrieve how many rows  are affected from a delete statement


sqlite3_changes



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

[sqlite] Concurrency and sqlite4

[deltagam...@gmx.net]

Hello,

will there be some improvement regarding concurrency in the new sqlite4 ?

Sqlite3 fits quite good to my needs, only the concurrency causes some 
problems, so I am eager to know about improvements in this area.


Thx for your efforts

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