[sqlite] Problems Getting Started
I'm using SQLite version 3.4.2 and for some reason, I cannot read from any SQLite databases. When I create the database, all the create and insert statements execute with no problems, and by using SQLite Database Browser, I can see that the tables are there and that they are populated. However, whenever I try to run a select statement, I get the error "no such table: ". Again, looking in the browser, the table is there, it's just not visible to my application. I did some investigating and found that a "select * from sqlite_master" returns 0 rows. However, doing that in the browser, once again, returns all the information you'd expect. I've spent hours searching for the solution to this, stepping through each line of code, but everything seems to be in proper order. I've even tried connecting to SQLite databases created by other applications and get the same result when selecting from the sqlite_master table. Here's my connection code: int sqlErr = sqlite3_open(filename, _conn); if( sqlErr != SQLITE_OK ) { return (false); } Here is my prepare code: sqlite3_stmt* sqlStmt; const char* sqlTail = 0; if( sqlite3_prepare(m_conn, query, queryLength, , ) != SQLITE_OK ) { return (false); } Here is my step code: bool loop = true; while(loop) { switch( sqlite3_step(rs) ) { case SQLITE_DONE: loop = false; break; case SQLITE_ROW: // process row stuff here break; case SQLITE_ERROR: // handle... loop = false; break; case SQLITE_MISUSE: // handle... loop = false; break; } } I've cut some unnecessary code from the snippets, but that's the important stuff. If anyone has any idea what is going on here, please let me know. Josh
Re: [sqlite] Problems Getting Started
I thought of that. I did a search of my harddrive and it resulted in no duplicate files. In my code, I check if the file exists before trying to open it. Also, I debugged the application stepping through it line by line and the directory path is fine. Is there a problem in SQLite 3.4.2 with long file names? On 8/21/07, John Stanton <[EMAIL PROTECTED]> wrote: > > PokerAce wrote: > > I'm using SQLite version 3.4.2 and for some reason, I cannot read from > any > > SQLite databases. > > > > When I create the database, all the create and insert statements execute > > with no problems, and by using SQLite Database Browser, I can see that > the > > tables are there and that they are populated. However, whenever I try > to > > run a select statement, I get the error "no such table: ". > > Again, looking in the browser, the table is there, it's just not visible > to > > my application. > > > > I did some investigating and found that a "select * from sqlite_master" > > returns 0 rows. However, doing that in the browser, once again, returns > all > > the information you'd expect. I've spent hours searching for the > solution > > to this, stepping through each line of code, but everything seems to be > in > > proper order. I've even tried connecting to SQLite databases created by > > other applications and get the same result when selecting from the > > sqlite_master table. > > > > Here's my connection code: > > > > int sqlErr = sqlite3_open(filename, _conn); > > if( sqlErr != SQLITE_OK ) { > > return (false); > > } > > > > > > Here is my prepare code: > > > > sqlite3_stmt* sqlStmt; > > const char* sqlTail = 0; > > > > if( sqlite3_prepare(m_conn, query, queryLength, , ) != > > SQLITE_OK ) { > > return (false); > > } > > > > Here is my step code: > > > > bool loop = true; > > while(loop) { > > switch( sqlite3_step(rs) ) > > { > > case SQLITE_DONE: > > loop = false; > > break; > > > > case SQLITE_ROW: > > // process row stuff here > > break; > > > > case SQLITE_ERROR: > > // handle... > > loop = false; > > break; > > > > case SQLITE_MISUSE: > > // handle... > > loop = false; > > break; > > } > > } > > > > I've cut some unnecessary code from the snippets, but that's the > important > > stuff. If anyone has any idea what is going on here, please let me > know. > > > > Josh > > > Your filename is not the correct pathname for your existing database and > your program is creating a new, empty database. > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] Problems Getting Started
I guess I should've stepped through again. Since having the initial problem, I added addition code, one of which caused the filename to be an empty string just prior to calling the function. It's working now. Sorry about that and thanks for the help. On 8/21/07, PokerAce <[EMAIL PROTECTED]> wrote: > > I thought of that. I did a search of my harddrive and it resulted in no > duplicate files. In my code, I check if the file exists before trying to > open it. Also, I debugged the application stepping through it line by line > and the directory path is fine. Is there a problem in SQLite 3.4.2 with > long file names? > > > On 8/21/07, John Stanton <[EMAIL PROTECTED]> wrote: > > > > PokerAce wrote: > > > I'm using SQLite version 3.4.2 and for some reason, I cannot read from > > any > > > SQLite databases. > > > > > > When I create the database, all the create and insert statements > > execute > > > with no problems, and by using SQLite Database Browser, I can see that > > the > > > tables are there and that they are populated. However, whenever I try > > to > > > run a select statement, I get the error "no such table: ". > > > Again, looking in the browser, the table is there, it's just not > > visible to > > > my application. > > > > > > I did some investigating and found that a "select * from > > sqlite_master" > > > returns 0 rows. However, doing that in the browser, once again, > > returns all > > > the information you'd expect. I've spent hours searching for the > > solution > > > to this, stepping through each line of code, but everything seems to > > be in > > > proper order. I've even tried connecting to SQLite databases created > > by > > > other applications and get the same result when selecting from the > > > sqlite_master table. > > > > > > Here's my connection code: > > > > > > int sqlErr = sqlite3_open(filename, _conn); > > > if( sqlErr != SQLITE_OK ) { > > > return (false); > > > } > > > > > > > > > Here is my prepare code: > > > > > > sqlite3_stmt* sqlStmt; > > > const char* sqlTail = 0; > > > > > > if( sqlite3_prepare(m_conn, query, queryLength, , ) != > > > > > SQLITE_OK ) { > > > return (false); > > > } > > > > > > Here is my step code: > > > > > > bool loop = true; > > > while(loop) { > > > switch( sqlite3_step(rs) ) > > > { > > > case SQLITE_DONE: > > > loop = false; > > > break; > > > > > > case SQLITE_ROW: > > > // process row stuff here > > > break; > > > > > > case SQLITE_ERROR: > > > // handle... > > > loop = false; > > > break; > > > > > > case SQLITE_MISUSE: > > > // handle... > > > loop = false; > > > break; > > > } > > > } > > > > > > I've cut some unnecessary code from the snippets, but that's the > > important > > > stuff. If anyone has any idea what is going on here, please let me > > know. > > > > > > Josh > > > > > Your filename is not the correct pathname for your existing database and > > your program is creating a new, empty database. > > > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > > >
[sqlite] Debug Build Works, Release Doesn't
I'm using version 3.5.2. I built the static library using MSVC++ 6. The debug build works great. However, when I use the release build, the connect works, but every SQL statement I try to execute returns "SQL logic error or missing database." I know it's not a configuration problem between the debug and release builds of my program, because if I link the debug build of SQLite into the release build of my application, everything works great. I log the SQL statements before they are executed and they are fine. I check the name of the db file before connecting to it, and that's fine. I checked the build options between the Debug and Release builds of SQLite and other than the standard differences, nothing stands out. Anyone experience anything like this before? I should also mention that I was previously using version 3.3.8 with the exact same build settings and it worked fine.
Re: [sqlite] SQLite and Large Databases
I am using Windows Task Manager to determine memory usage. I minimize my application (which causes the task manager to show a more accurate measurement of memory used) and still the memory usage is enormous. On Nov 8, 2007 11:55 AM, Roger Binns <[EMAIL PROTECTED]> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > PokerAce wrote: > > Initially, the memory usage > > was outrageous (~ 500 mb for a 1.3 gb db), > > How are you measuring memory usage? You have to be very careful as most > operating system tools will report a number greater than the size of all > current mallocs() and depending on the pattern of malloc/free can report > something substantially larger. It doesn't mean the memory usage is > that large. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.6 (GNU/Linux) > > iD4DBQFHMz9wmOOfHg372QQRAtPXAJiW52di3U2JP6ywpE6MvB3e0Jr4AJ9B1bvj > kpBlioHvm8lesFP3S1LEfQ== > =zwuw > -END PGP SIGNATURE- > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] SQLite and Large Databases
"Are you certain it's sqlite RAM, and not your application?" Yes, we are also testing PostgreSQL and MySQL and the application memory with those stays < 20 mb. "Is your temp_store set to memory or file?" I have not changed this, so it's whatever the default is. "How are you performing your inserts (prepared statements)?" Not using prepared statements, just sending a batch of "INSERT INTO" statements. "How many rows are you inserting per batch?" Do you mean per transaction? If so, approximately 30 or so. The 500 mb memory usage was after 150k transactions. "What's your database page_size?" Whatever the default is. "Can you build your table indexes after you populate the data?" Preferably not. "Is this for a poker showdown database by any chance?" Nope. :-) It's for: http://preview.pokertracker3.com On Nov 8, 2007 11:59 AM, Joe Wilson <[EMAIL PROTECTED]> wrote: > --- PokerAce <[EMAIL PROTECTED]> wrote: > > I'm trying to see if SQLite is suitable for large databases ( > 1 gb, > > millions of rows in each of several tables). Initially, the memory > usage > > was outrageous (~ 500 mb for a 1.3 gb db), but I got that down to < 30 > mb by > > setting the cache size to 0 and setting a low soft heap limit. That > works > > when I'm reading from the database, but when I am inserting these rows, > the > > memory usage grows back into the ~500 mb range. My goal is to never > have > > the application use more than 100 mb of memory, preferably much less > than > > that. Does anyone have any suggestions? > > If your cache_size is 0 I'm not sure what's eating up 500M of RAM for > inserts. > > Some questions that might give you some ideas: > > Are you certain it's sqlite RAM, and not your application? > Is your temp_store set to memory or file? > How are you performing your inserts (prepared statements)? > How many rows are you inserting per batch? > What's your database page_size? > Can you build your table indexes after you populate the data? > > Is this for a poker showdown database by any chance? > http://games.cs.ualberta.ca/poker/IRC/ > > > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] SQLite and Large Databases
If you have to set that manually, it was not set. I am using version 3.5.2. I am doing tens of thousands of transactions, each without ~30 separate INSERTs. On Nov 8, 2007 11:40 AM, <[EMAIL PROTECTED]> wrote: > PokerAce <[EMAIL PROTECTED]> wrote: > > I'm trying to see if SQLite is suitable for large databases ( > 1 gb, > > millions of rows in each of several tables). Initially, the memory > usage > > was outrageous (~ 500 mb for a 1.3 gb db), but I got that down to < 30 > mb by > > setting the cache size to 0 and setting a low soft heap limit. That > works > > when I'm reading from the database, but when I am inserting these rows, > the > > memory usage grows back into the ~500 mb range. My goal is to never > have > > the application use more than 100 mb of memory, preferably much less > than > > that. Does anyone have any suggestions? > > > > Did you compile with SQLITE_ENABLE_MEMORY_MANAGEMENT=1? > The soft_heap_limit is a no-op if you did not. > > What version are you running. What SQL are you executing that > causes the memory usage to shoot up? > > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
[sqlite] SQLite and Large Databases
I'm trying to see if SQLite is suitable for large databases ( > 1 gb, millions of rows in each of several tables). Initially, the memory usage was outrageous (~ 500 mb for a 1.3 gb db), but I got that down to < 30 mb by setting the cache size to 0 and setting a low soft heap limit. That works when I'm reading from the database, but when I am inserting these rows, the memory usage grows back into the ~500 mb range. My goal is to never have the application use more than 100 mb of memory, preferably much less than that. Does anyone have any suggestions? Josh
Re: [sqlite] SQLite and Large Databases
Actually, I am. I abstract the database communication in my application, but I access each database using their native API. On Nov 8, 2007 12:44 PM, Joe Wilson <[EMAIL PROTECTED]> wrote: > --- PokerAce <[EMAIL PROTECTED]> wrote: > > "Are you certain it's sqlite RAM, and not your application?" > > > > Yes, we are also testing PostgreSQL and MySQL and the application memory > > with those stays < 20 mb. > > You're not using the sqlite3 API directly, are you? > If that's the case, I think your database driver is leaking memory. > > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >