Re: [sqlite] CREATE TABLE
Are you execduting several sqlite3_prapare and sqlite3_step statements? The prepare steps through the statements returning a pointer to the start of the next one as one is compiled. Ryan M. Lederman wrote: I'm using sqlite3, built with Microsoft's CL compiler, 8.0 on a Windows Mobile platform. I have a problem wherein I issue several CREATE TABLE commands concatenated together, semi-colon delimited. It appears, from the feedback from my customers, that one or more table(s) fail to get created, but the database engine reports no failures when executing the CREATE TABLE statement. I know this because later queries will fail with messages such as "no such table: foo" Has anyone ever seen this before? Am I doing something wrong, or is this a known bug? Any help would be greatly appreciated. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re[2]: [sqlite] CREATE TABLE
In fact, it uses to be N seperate execs and I consolidated it in an
effort to fix the problem. It seems like under certain circumstances,
my code fails to create a table but does not report it as an error.
// strTmp is filled with other CREATE TABLE commands before this,
strTmp.Format(L"CREATE TABLE %s ("
L"%s int," // Ticket ID
L"%s int," // Group ID
L"%s int," // Field ID
L"%s text," // Value
L"PRIMARY KEY (%s, %s, %s)"
L");",
TDB_CUSTOMFIELDDATATABLE, TDB_COL_TICKETID,
TDB_COL_GROUPID, TDB_COL_FIELDID, TDB_COL_VALUE,
TDB_COL_TICKETID, TDB_COL_GROUPID, TDB_COL_FIELDID);
strSQL += strTmp;
// It is all executed here
if (!ExecuteSQLStatements(strSQL.GetBuffer(), , NULL, false))
{
CEString strMsg;
strMsg.Format(L"Unable to create database! Error: %d, '%s'",
sql.GetErrorCode(), sql.GetErrorMsg());
MessageBox(g_hWnd, strMsg.GetBuffer(), L"Error", MB_OK | MB_ICONSTOP);
return false;
}
// This is ExecuteSQLStatements:
bool KayakoMobile::ExecuteSQLStatements(const wchar_t *pwszSQL,
kce::SQLite *pSQLite,
SQLiteRowHandler *pHandler /*=
NULL*/, bool bContinueOnFail /*= true*/)
{
bool r = false;
if (pSQLite && pSQLite->IsOpen())
{
if (pwszSQL && *pwszSQL)
{
kce::SQLiteStatement *pStmt = new kce::SQLiteStatement;
if (!pStmt)
{
#ifdef DEBUG
HandleAllocFailure(L"ExecuteSQLStatements", sizeof(SQLiteStatement));
#else
HandleAllocFailure(sizeof(SQLiteStatement));
#endif
return false;
}
else
{
int nCode = SQLITE_OK;
int nRetry= 0;
const wchar_t *pLeftOver = NULL;
while (SQLITE_OK == nCode || (SQLITE_SCHEMA == nCode &&
((++nRetry) < 2)))
{
if (!*pwszSQL)
{
break;
}
nCode = pSQLite->Prepare(pwszSQL, , -1, );
// Check to see if this prepare succeeded.
if (SQLITE_OK != nCode)
{
pStmt->Finalize();
if (SQLITE_ERROR != nCode)
{
// Could be busy, so we'll retry
if (pStmt)
{
continue;
}
}
else
{
// There was an error parsing this statement; let's skip
to the next one.
#ifdef DEBUG_DB
kce::odsf(L"ExecuteSQLStatements() : SQLite::Prepare()
Failed! Err = {#%d, '%s'} SQL: '%s'\n", nCode,
pSQLite->GetErrorMsg(), pwszSQL);
#endif // !DEBUG_DB
const wchar_t *pwszToken = wcsstr(pwszSQL, L";");
if (pwszToken && *pwszToken + 1)
{
// We want to print the statement that failed for observation
kce::CEString strFailed;
size_t nSizeFailed = (pwszToken - pwszSQL);
wchar_t *pwszBuf =
strFailed.GetMutableBuffer(nSizeFailed + 1);
#ifdef DEBUG_DB
if (pwszBuf)
{
wcsncpy(pwszBuf, pwszSQL, nSizeFailed);
odsf(L"%s\n", pwszBuf);
}
#endif
if (!bContinueOnFail)
{
r = false;
break;
}
pwszSQL = pwszToken + 1;
nCode = SQLITE_OK;
continue;
}
else
{
// No more statements; bail.
continue;
}
}
}
if (!pStmt)
{
// This happens for a comment or whitespace
pwszSQL = pLeftOver;
continue;
}
// Now step to execute each statement
bool bFailed = false;
int nCode= SQLITE_ERROR;
while (pStmt->Step(bFailed, ))
{
if (pHandler)
{
pHandler->OnRowReady(pStmt);
}
}
r = !bFailed;
pStmt->Reset();
if (r)
{
nCode = SQLITE_OK;
nRetry = 0;
pwszSQL = pLeftOver;
while (iswspace(pwszSQL[0]))
{
pwszSQL++;
}
}
else
{
pStmt->Finalize();
pStmt->Set(NULL);
}
#ifdef DEBUG_DB
if (bFailed)
{
kce::odsf(L"ExecuteSQLStatements() :
SQLiteStatement::Step() Failed! Err {#%d, '%s'}\n", nCode,
pSQLite->GetErrorMsg());
}
#endif
}
delete pStmt;
}
}
}
return r;
}
On 7/6/07, Teg <[EMAIL PROTECTED]> wrote:
Hello Ryan,
Why not make N distinct Execs to create the tables and see if it works?
Seems like simplification is the way to trouble-shoot this.
Friday, July 6, 2007, 7:08:09 PM, you wrote:
RML> James,
RML> Nope. And
Re[2]: [sqlite] CREATE TABLE
Hello Ryan, Why not make N distinct Execs to create the tables and see if it works? Seems like simplification is the way to trouble-shoot this. Friday, July 6, 2007, 7:08:09 PM, you wrote: RML> James, RML> Nope. And actually, it's always a different table that fails to get RML> created. I know that (usually) all the tables get created RML> successfully. It seems plausible that it's a low-memory condition RML> that causes it, seeing as this code is running on PDAs with very very RML> little memory. RML> I will post my code here if necessary. RML> On 7/6/07, James Dennett <[EMAIL PROTECTED]> wrote: >> > -Original Message- >> > From: Ryan M. Lederman [mailto:[EMAIL PROTECTED] >> > Sent: Friday, July 06, 2007 3:34 PM >> > To: sqlite-users@sqlite.org >> > Subject: [sqlite] CREATE TABLE >> > >> > I'm using sqlite3, built with Microsoft's CL compiler, 8.0 on a >> > Windows Mobile platform. >> > >> > I have a problem wherein I issue several CREATE TABLE commands >> > concatenated together, semi-colon delimited. It appears, from the >> > feedback from my customers, that one or more table(s) fail to get >> > created, but the database engine reports no failures when executing >> > the CREATE TABLE statement. I know this because later queries will >> > fail with messages such as "no such table: foo" >> > >> > Has anyone ever seen this before? Am I doing something wrong, or is >> > this a known bug? Any help would be greatly appreciated. >> >> Without seeing your code, it's hard to say, but it seems plausible that >> you didn't actually execute all of the statements. Is only the first >> table getting created? >> >> -- James >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> >> -- Best regards, Tegmailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] CREATE TABLE
James, Nope. And actually, it's always a different table that fails to get created. I know that (usually) all the tables get created successfully. It seems plausible that it's a low-memory condition that causes it, seeing as this code is running on PDAs with very very little memory. I will post my code here if necessary. On 7/6/07, James Dennett <[EMAIL PROTECTED]> wrote: > -Original Message- > From: Ryan M. Lederman [mailto:[EMAIL PROTECTED] > Sent: Friday, July 06, 2007 3:34 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] CREATE TABLE > > I'm using sqlite3, built with Microsoft's CL compiler, 8.0 on a > Windows Mobile platform. > > I have a problem wherein I issue several CREATE TABLE commands > concatenated together, semi-colon delimited. It appears, from the > feedback from my customers, that one or more table(s) fail to get > created, but the database engine reports no failures when executing > the CREATE TABLE statement. I know this because later queries will > fail with messages such as "no such table: foo" > > Has anyone ever seen this before? Am I doing something wrong, or is > this a known bug? Any help would be greatly appreciated. Without seeing your code, it's hard to say, but it seems plausible that you didn't actually execute all of the statements. Is only the first table getting created? -- James - To unsubscribe, send email to [EMAIL PROTECTED] - -- Ryan M. Lederman - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] CREATE TABLE
> -Original Message- > From: Ryan M. Lederman [mailto:[EMAIL PROTECTED] > Sent: Friday, July 06, 2007 3:34 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] CREATE TABLE > > I'm using sqlite3, built with Microsoft's CL compiler, 8.0 on a > Windows Mobile platform. > > I have a problem wherein I issue several CREATE TABLE commands > concatenated together, semi-colon delimited. It appears, from the > feedback from my customers, that one or more table(s) fail to get > created, but the database engine reports no failures when executing > the CREATE TABLE statement. I know this because later queries will > fail with messages such as "no such table: foo" > > Has anyone ever seen this before? Am I doing something wrong, or is > this a known bug? Any help would be greatly appreciated. Without seeing your code, it's hard to say, but it seems plausible that you didn't actually execute all of the statements. Is only the first table getting created? -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Using SQlite with .NET
There are a few .NET wrappers for SQLite. I would suggest System.Data.SQLite available here: http://sqlite.phxsoftware.com/ And for ADO.NET 2.0 development use version 1.0.43. For LINQ stuff use 2.0.35. Also wrapper-specific questions will probably get quicker responses in their dedicated forums. http://sqlite.phxsoftware.com/forums/default.aspx I haven't used the feature but I'm pretty sure that provider adds support for viewing/editing SQLite within Visual Studio 2005. HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Ahmed Sulaiman [mailto:[EMAIL PROTECTED] Sent: Friday, July 06, 2007 2:02 PM To: sqlite-users@sqlite.org Subject: [sqlite] Using SQlite with .NET Hi, I am trying to evaluate the use of SQLite with .NET in out project. I have downloaded the ADO.NET 2 adaptor for SQLite from source forge. But I was not sure which version do I need to download (if any) from the download page to get myself started. Could you please give me some direction, links would be great. Also, is there a database viewer/updater to populate tables with some test date in SQLite? Regards Ahmed - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Using SQlite with .NET
Hi, I am trying to evaluate the use of SQLite with .NET in out project. I have downloaded the ADO.NET 2 adaptor for SQLite from source forge. But I was not sure which version do I need to download (if any) from the download page to get myself started. Could you please give me some direction, links would be great. Also, is there a database viewer/updater to populate tables with some test date in SQLite? Regards Ahmed
Re: [sqlite] Views
On Fri, 2007-07-06 at 08:42 -0700, Mike Johnston wrote: > n Fri, 2007-07-06 at 07:18 -0700, Mike Johnston wrote: > > I have to join three tables to retrieve bits of data from each. I'm > wondering if I use a view are there any performance issues vs. issuing > the complete 3 table join query in code. > > > > Also, is it quicker to have sqlite do joins on primary keys between > tables instead of doing three separate single table queries on the > primary key in code? > > >The short answers are probably not and no. > >But it depends on the exact view you're defining. If it's just a join, > >no GROUP BY or ORDER BY or aggregate functions, it should be fine. > > >Dan. > > They are very simple select statements retrieving exactly one row. > > On the second point, are you saying that doing a select with a three table > join is no quicker than having three groups of sqlite_prepare, sqlite_step > sequences for single table, single row lookups? That just seems a little > counter-intuitive (at least to me). Sorry, I think I read the question the wrong way around. It probably is a bit faster to use a join query because you call the parser less. The point I meant to make was that the I/O operations on the file-system/database-cache are the same either way. Dan. > Mike > > - > Choose the right car based on your needs. Check out Yahoo! Autos new Car > Finder tool. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] FTS and upgrades
Right, but what I'm asking is: If version 1.0 of my program uses Sqlite with *only* fts1, and then I upgrade users to version 2.0 of my program which uses *only* fts3, can my version 2.0 code (which is using sqlite that only has fts3) safely delete the original fts1 table created by version 1.0 without crashing the program? Thanks again! On 7/5/07, Dan Kennedy <[EMAIL PROTECTED]> wrote: On Thu, 2007-07-05 at 21:08 -0700, Dave Brown wrote: > I am considering using FTS for a new feature. My question is: > > - If I use fts1 (or fts2) now, and then a new version of fts comes out which > is not backwards compatible, will I at least be able to upgrade users by > simply deleting the old FTS table and re-building it using the new FTS > system? > > In other words, my users will have a database file built with fts1, then my > new upgraded code (which contains sqlite with fts3, for example) will try to > upgrade them by deleting the fts1 table, and creating a new table. > > Will the delete of the old table at least be guaranteed to work? fts1 and fts2 are separate extensions that happen to do similar things. You can load them both at the same time and have a single database connection access both fts1 and fts2 tables. The same should be true with fts3. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Views
--- Mike Johnston <[EMAIL PROTECTED]> wrote: > I have to join three tables to retrieve bits of data from each. I'm wondering > if I use a view > are there any performance issues vs. issuing the complete 3 table join query > in code. As long as your VIEW/subquery does not make use of UNION/EXCEPT/INTERSECT, then it's usually the same speed. I would not recommend complex querying of views/subquery of unions of selects with large result sets within SQLite. The select will run much faster and use far less memory if you manually expand these queries to not make use of the VIEW. SQLite will not perform this optimization for you. -- slower SELECT * FROM ( SELECT a, b FROM t1 WHERE b!=7 UNION ALL SELECT x, sum(y) FROM t2 WHERE x<9 GROUP BY x HAVING sum(y)>7 ) WHERE a>b; -- faster SELECT * FROM ( SELECT a, b FROM t1 WHERE b!=7 AND a>b UNION ALL SELECT x, sum(y) FROM t2 WHERE x<9 GROUP BY x HAVING sum(y)>7 AND x>sum(y) ); Bored stiff? Loosen up... Download and play hundreds of games for free on Yahoo! Games. http://games.yahoo.com/games/front - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Views
n Fri, 2007-07-06 at 07:18 -0700, Mike Johnston wrote: > I have to join three tables to retrieve bits of data from each. I'm wondering if I use a view are there any performance issues vs. issuing the complete 3 table join query in code. > > Also, is it quicker to have sqlite do joins on primary keys between tables instead of doing three separate single table queries on the primary key in code? >The short answers are probably not and no. >But it depends on the exact view you're defining. If it's just a join, >no GROUP BY or ORDER BY or aggregate functions, it should be fine. >Dan. They are very simple select statements retrieving exactly one row. On the second point, are you saying that doing a select with a three table join is no quicker than having three groups of sqlite_prepare, sqlite_step sequences for single table, single row lookups? That just seems a little counter-intuitive (at least to me). Mike - Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool.
Re: [sqlite] Views
On Fri, 2007-07-06 at 07:18 -0700, Mike Johnston wrote: > I have to join three tables to retrieve bits of data from each. I'm wondering > if I use a view are there any performance issues vs. issuing the complete 3 > table join query in code. > > Also, is it quicker to have sqlite do joins on primary keys between tables > instead of doing three separate single table queries on the primary key in > code? The short answers are probably not and no. But it depends on the exact view you're defining. If it's just a join, no GROUP BY or ORDER BY or aggregate functions, it should be fine. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Is it necessary to clear bindings in a Prepared Statement
J Jayavasanthan <[EMAIL PROTECTED]> wrote: Is it necessary to clear bindings in a Prepared Statement, before reusing it to insert or update or select from the database again, No. In fact, sometimes it is useful that sqlite3_reset preserves bindings, e.g. if one needs to insert several rows that differ only in a small number of fields. I recently found out, sqlite3_reset doesn't reset the bound parameters in a prepare statement, and sqlite3_clear_bindings need to be used, It can be used, but it doesn't _need_ to be used. You can simply bind the same parameter again, which replaces the old binding with the new one. If sqlite3_clear_bindings is not done and I repeatedly use it to insert into a table few binary values, will this affect the memory allocations and usage by sqlite and is there any possibility of ending up with a corrupt database due to the above mis-interpretation, No. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Is it necessary to clear bindings in a Prepared Statement
Hi, Is it necessary to clear bindings in a Prepared Statement, before reusing it to insert or update or select from the database again, I recently found out, sqlite3_reset doesn't reset the bound parameters in a prepare statement, and sqlite3_clear_bindings need to be used, If sqlite3_clear_bindings is not done and I repeatedly use it to insert into a table few binary values, will this affect the memory allocations and usage by sqlite and is there any possibility of ending up with a corrupt database due to the above mis-interpretation, Please clarify, Urs, Jayavasanthan J
[sqlite] Views
I have to join three tables to retrieve bits of data from each. I'm wondering if I use a view are there any performance issues vs. issuing the complete 3 table join query in code. Also, is it quicker to have sqlite do joins on primary keys between tables instead of doing three separate single table queries on the primary key in code? Thanks Mike - Park yourself in front of a world of choices in alternative vehicles. Visit the Yahoo! Auto Green Center.
Re: [sqlite] Using loadable extension with Qt
Many thanks for the suggestion. It solved my problem. - Alex
[sqlite] Suggestions Add/Remove columns from Table
I've been reading up a bit and I understand SQLite has limited support for ALTER TABLE columns can be added but not dropped. Some suggestions are to create a temp table and copy the data to it drop the old table create the new one and then select the data back. But there appears to be more to this. What I decided to do so far is: Lets say the table I am working with is TEMP1 BEGIN; CREATE TEMP TABLE TEMP1_TEMP as SELECT * FROM TEMP1; DROP TEMP1; CREATE TEMP1 ( NEW FIELDS); Now at this point select all the fields that still exist after the alteration INSERT INTO TEMP1 (FIELD1, FIELD2) SELECT FIELD1, FIELD2 from TEMP1_TEMP But the problem is it appears that sqlite automatically drops associated triggers and indexes. You can probably query the index schema by doing a select from sqlite_master When the indexes are determined do a PRAGMA index_info(idx_temp1..) for instance to get the associated fields. If those still exist, recreate the index using the same schema. Maybe the same can be done with the trigger. If the table has a sequence then it appears this is taken care of automatically in the select? Any foreign key constraints or references to the table in other triggers should be ok as long as you don't delete any primary key columns? As the select back and forth from the temp keeps the values of the primary keys as long as they remain? Im trying to figure out if I am missing something, or maybe exactly what others have done. Thanks in advance.
Re: [sqlite] sqlite3_step
On Thu, 2007-07-05 at 18:26 -0300, Bruno S. Oliveira wrote: > Hi all, > > I'm having problems with the sqlite3_step function. > I have a table with only one record and then I execute the > sqlite3_step twice (with some processing in between). In the first run > of the function everything goes fine, but, in the second one, it > returns SQLITE_ROW even with only one record in the table and with no > modifications in the SQL statement. > Is this the correct behaviour of that function? Does anyone knows why > this happens? > > I'm using the sqlite3_prepare before executing the sqlite3_step. Could be a few things. If you post the problematic code somebody will be able to help you. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] FTS and upgrades
On Thu, 2007-07-05 at 21:08 -0700, Dave Brown wrote: > I am considering using FTS for a new feature. My question is: > > - If I use fts1 (or fts2) now, and then a new version of fts comes out which > is not backwards compatible, will I at least be able to upgrade users by > simply deleting the old FTS table and re-building it using the new FTS > system? > > In other words, my users will have a database file built with fts1, then my > new upgraded code (which contains sqlite with fts3, for example) will try to > upgrade them by deleting the fts1 table, and creating a new table. > > Will the delete of the old table at least be guaranteed to work? fts1 and fts2 are separate extensions that happen to do similar things. You can load them both at the same time and have a single database connection access both fts1 and fts2 tables. The same should be true with fts3. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
