Re: [sqlite] SQLite ODBC Driver - relative path
Hello. On 17/03/2010 04:16, Shen Nan wrote: I used SQLite ODBC Driver to display data in Excel, and everything works fine. Except in the connection String, I have to enter an absolute path, which is very troublesome every time I move the file. I wonder is there any way to specify relative path instead in the connection String? You can do two things to (probably) fix this. 1. You can use a symbolic link to the moved database file. Use an absolute path to a location that should never change, and in that location, put a symbolic link to the database file; to create one, try the sysinternal utility here: http://technet.microsoft.com/en-us/sysinternals/bb896768.aspx 2. If you move the database file with the Excel file, you can recreate the ODBC connection string in VBA every time you open the file by parsing the Excel document's path name and replacing the relative part. Guy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange File Import Error
Arr, let's count. Column 1: 117172 Column 2: Engineered Structures Inc. Column 3: Brockway Center Column 4: 10875 SW Herman Rd Column 5: Tualatin Column 6: 97062-8033 Column 7: Washington Column 8: NWR Column 9: 45.3834 Column 10: -122.7882 Column 11: 1542 Column 12: Nonresidential Construct NEC Column 13: Gen12c(Agent) Column 14: Stormwater; NPDES Construction More Than 1 Acre Disturbed Ground Column 15: Issued By Agent Column 16: Minor Column 17: STM Column 18: Legal Contact Column 19: Engineered Structures Inc. Column 20: Gary Column 21: Ross Column 22: 15940 SW 72nd Ave Column 23: Portland Column 24: OR Column 25: 97224-7936 Column 26: 503-968-6639 26 columns. I guess the SQLite command line parser can count. Guy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite to Access
Well, Pighin, Ryan wrote: Hi All - We have a new utility in our environment using SQLite and I was wondering if there was a way to dump all the database into Access so we can create reports on the databases? Why not attach the SQLite database into Access and create your reports? That way changes in the database will be reflected in the reports without having to dump it again. Use an SQLite ODBC driver (for example, the one at www.ch-werner.de/sqliteodbc/ ) and use the linked tables command to attach the SQLite database tables in. Guy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Resources for newbies.
Robert Simpson wrote: Nothing stands out ... is your callback being called at all? What's it look like? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Nelson Sent: Saturday, August 02, 2008 2:00 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Resources for newbies. here's the create code: if( sqlite3_exec( m_pdbData, create table Event( Key TEXT[unique], DateTime DATE, Event blob, MotionData1 blob, MotionData2 blob, MotionData3 blob, MotionData4 blob, MotionData5 blob, MotionData6 blob, MotionData7 blob, MotionData8 blob, MotionData9 blob, MotionData10 blob, MotionData11 blob, MotionData12 blob, MotionData13 blob, MotionData14 blob, MotionData15 blob, MotionData16 blob, PRIMARY KEY (Key) );, Callback, this, lpszErr ) != 0 ) (have also used TEXT instead of blob) here I add a record: strSql.Format( insert or replace into Event values( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' );, strKey, szSQLDateTime, strArgs + /t + szDateTime, m_strMotionData[0], m_strMotionData[1], m_strMotionData[2], m_strMotionData[3], m_strMotionData[4], m_strMotionData[5], m_strMotionData[6], m_strMotionData[7], m_strMotionData[8], m_strMotionData[9], m_strMotionData[10], m_strMotionData[11], m_strMotionData[12], m_strMotionData[13], m_strMotionData[14], m_strMotionData[15] ); sqlite3_open( m_strFile, m_pdbData ); sqlite3_exec( m_pdbData, strSql, Callback, this, lpszErr ); sqlite3_close( m_pdbData ); (note m_strMotionData are strings -- small values work, and around 64 chars each the following query fails) here's the query: sqlite3_open( m_strFile, m_pdbData ); sqlite3_exec( m_pdbData, select * from Event , Callback, this, lpszErr ); sqlite3_close( m_pdbData ); err msg: SQL logic error or bad database What's the size of strSql? If it's limited to 2K characters (for example, if it's an MFC CString, which as a limit of 2K for the Format function), you can get failures such as you describe. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite website
[EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] wrote:
James Darpinian [EMAIL PROTECTED] wrote:
I decided to try turning the page into a pure CSS layout with no
tables. The result is available at
http://www.cs.hmc.edu/~jdarpini/sqlite.html I tested it in IE6; 3
hopefully unobtrusive hacks were needed for it to render decently.
When you click on one of the links on the menu bar, afterwards
the font color is almost identical to the background color
so you can no longer read the text. Can you suggest a fix
for this problem?
I should have said that this problem is in IE6 only
Add to the style sheet:
.toolbar A:visited {
COLOR: blue;
}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Trouble compiling with MSVC++ 6.0
Andreas Kupries wrote: Ticket #2457 reports difficulty building SQLite version 3.4.0 using MSVC++ 6.0. This appears to be a bug in MSVC++. See the comments on the ticket for details: http://www.sqlite.org/cvstrac/tktview?tn=2457 I do not own MSVC++ (and have no intention of acquiring a copy) so I am unable to reproduce. On the other hand, Version 3.4.0 has been out for a week and there have been no other reports of problems, so I am somewhat suspicious that this problem is specific to the specific installation. Can anybody shed any light on ticket #2457? Can anybody else reproduce the problem or suggest a work-around? Error in MSVC 6.0 -- the user who wrote the ticket hasn't installed the latest service pack of VC6. http://support.microsoft.com/kb/890892 The article sounds like a very likely match (large macros). Just retrieved and installed VC6 SP6, rebooted the machine ... The compiler now reports a different build number, higher than before, so I guess really did not have that ServicePack before ... Compiling the original sources (not the ones I messed with) ... I however still run into the same internal compiler error, in the same line of code. The problem is actually linked to the optimizations in the release code. If you compile it without optimizations (for example, in Debug mode), it compiles successfully. I made some tests, and you can compile all the rest of the files (if you don't use the amalgamation file) with release flags; and you can even compile btree.c if you do the following: - Select btree.c - Right-click the file in the FileView and select 'Settings' from the menu - Select the 'C/C++' tab on the right - Select 'Customize' in the Optimizations box - Mark all the check boxes in the list, EXCEPT for: Assume Aliasing Across Function Calls* Favor Small Code - Select the Online _inline option in the inline functions box - Click OK Now it should compile (my VC6 with SP6 compiler reads Version 12.00.8804). As this is just an optimization that fails, the code should work. * The actual problem is with this option. It can probably be turned of specifically for this file using a VC6 macro - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Locked database
Hello. At 13:37 2/25/2007 +, you wrote: Came across a situation where it was impossible to delete or rename a SQLite db file even after the application (Excel) that had locked the database was closed. It wasn't me, so I don't have very exact information, but there was a statement to create a table and to insert data. For some reason this couldn't be completed and there was a db file and the corresponding journal file. I thought that it should be that closing the app that initiated the db connection should always release the handle to the db and allow deletes of renames of the file. If this is not so then should I maybe write the db handle to a safe place, like an .ini file, so I could use it later to close the db. Also would there be a way to release this db lock without a reboot of the PC? I have had a lot of experience with Excel crashing the UI and leaving some Excel application running in the background. Open the task manager (Ctrl+Shift+Esc) and check to see if you have any Excel leftovers in the Processes tab (NOT in the Applications tab!). If you do, just select them and use the End Process button to close them. The DB file should be freed. Guy - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Still Excel crash when running sqlite_get_table
Hmmm... At 13:38 2/7/2007 +, you wrote: Still having a problem when selecting data from one particular table with the VB wrapper dll SQLite3VB.dll. It is only a small table and I just can't see why there would be a problem. Would anybody be willing to have a look at this table? The zipped database file is only 15 Kb. Nothing attached... can you post it somewhere for download? Guy - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Still Excel crash when running sqlite_get_table
At 17:04 2/7/2007 +, you wrote: Not sure now the zip file has come through to this forum. I can see it, but I also got a message that it wasn't allowed. Didn't get through the first time, did get through the second time. I tried my version of the VBSqlite3 DLL and it works, although I didn't try it with Excel but with VB6. I did make some changes to my DLL code - if you want to compile that, I can post them. The database itself seems OK. Guy - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How do I know sqlite_get_table is finished
At 07:44 2/2/2007 +, you wrote: Sure, here it is: snip Looking at the C code published at http://www.tannertech.net/sqlite3vb/index.htm , it seems very risky - there are a lot of places it can fail to allocate memory (for example), and the use of some variables looks like it could randomly crash at any time... but I didn't test it, just looked at it. As far as I can understand the problem, it happens around this line: arr = GetFromDB(strSQL, lRows, strError, lDBHandle, strDB) Are you seeing a crash on the actual call to sqlite_get_table or only after it (when you try to use the results)? If you compiled the SQLite3VB.dll on your own, I can probably make some suggestions about fixing the C code of sqlite_get_table implementation, and maybe even some improvments (for example, a boolean flag to allow you to request the results array without the column headers, which I remember you asking about), and returning the number of rows immediately instead of in another function (which is somewhat dangerous), etc.). You should also probably consider using some kind of wrapper around sqlite3_exec in addition to sqlite3_get_table so you will be able to run commands (like pragma) without going through sqlite_get_table. Guy - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How do I know sqlite_get_table is finished
Hello.
At 14:32 2/2/2007 +, you wrote:
lReturnedRows is one of the function arguments, so that is fine.
I have made some progress though and that is that this problem only occurs
with this particular table, called SQL. It is a table that logs all the SQL
statements that run in my app. When I instead make this for example
sqlite_master there is no problem ever.
No idea though why this table would cause a problem. Could it be that
one of the items in that table is a reserved word?
CREATE TABLE 'SQL'
([STATEMENT_COUNT] INTEGER,
[DB] TEXT,
[QUERY_TIME] TEXT,
[QUERY_LENGTH] REAL,
[QUERY] TEXT)
Any other ideas what could be wrong with this table?
It is possible that the result set is too large to fit in the memory
constraints you have, so when one of the memory allocations in the
sqlite_get_table fails, the application crashes.
Can you run the same statement from the sqlite3 command line application
and see if it returns the expected results? and if it's very long?
In any case, I've worked a bit on the function, and here's the result:
VBSQL.h -
#include windows.h
#include stdio.h
#include io.h
#include oleauto.h
#include wtypes.h
#include sqlite3.h
SAFEARRAY * __stdcall sqlite_get_table(sqlite3 * , const char *, BSTR * ,
long*, VARIANT);
BSTR __stdcall sqlite_libversion(void);
int __stdcall sqlite_exec(sqlite3*, const char*, BSTR*);
VBSQL.c -
#include vbsqlite.h
#define MEMORY_ERRORMemory allocation error
BOOL FillVariantFromString(VARIANT* pVariant, const char* pString)
{
int nLen;
LPOLESTR pOLEString;
VariantClear(pVariant);
nLen = MultiByteToWideChar( CP_ACP, 0, pString, -1, NULL, 0);
if (nLen == 0)
return FALSE;
nLen++;
pOLEString = CoTaskMemAlloc(nLen * sizeof(WCHAR));
if (pOLEString == NULL)
return FALSE;
if (MultiByteToWideChar( CP_ACP, 0, pString, -1, pOLEString, nLen)
== 0)
{
CoTaskMemFree(pOLEString);
return FALSE;
}
pVariant-bstrVal = SysAllocString(pOLEString);
V_VT(pVariant) = VT_BSTR;
CoTaskMemFree(pOLEString);
return TRUE;
}
SAFEARRAY * __stdcall sqlite_get_table(
sqlite3 *db, /* The database on which the SQL executes */
const char *zSql, /* The SQL to be executed */
BSTR *ErrMsg, /* Write error messages here */
long* pNumberOfRows, /* Number of rows in the returned array (ALWAYS
without the headers!) */
VARIANT varIncludeHeaders /* TRUE to include headers, FALSE not to */
) /* Return the SAFEARRAY */
{
// Temp result fields
char **pSQL_Results;
char *pErrMessage= 0;
int nNumberOfColumns;
int nResult;
SAFEARRAY* pResult = NULL;
nResult = sqlite3_get_table(db, zSql, pSQL_Results,
pNumberOfRows, nNumberOfColumns, pErrMessage);
if (nResult == SQLITE_OK)
{
SAFEARRAYBOUND SA_Bounds[2];
sqlite3_free(pErrMessage);
if (nNumberOfColumns == 0)
{
sqlite3_free_table(pSQL_Results);
// Return an empty array - to make sure nothing
happens:
SA_Bounds[0].cElements = 0;
SA_Bounds[0].lLbound = 0;
pResult = SafeArrayCreate(VT_VARIANT, 1, SA_Bounds);
if (pResult == NULL)
{
*ErrMsg =
SysAllocStringByteLen(MEMORY_ERROR,strlen(MEMORY_ERROR) );
return NULL; // Don't know what this will
do. NEVER USE AN ARRAY when an error was returned!
}
}
else
{
//We have a resultset so transform this into a
SAFEARRAY
// Create SAFEARRAY
//SAFEARRAY FAR* resultp = NULL;
BSTR bstrTemporyStringHolder = NULL;
VARIANT tmpVariant;
BSTR bstr1 = NULL;
HRESULT hr;
LPOLESTR pTempWideDataHolder = NULL;
int intCurrentRow ; // Tempory counter for looping
long indices[] = {0,0};
VariantInit(tmpVariant);
// Set up array bounds
SA_Bounds[0].cElements = *pNumberOfRows + 1;
SA_Bounds[0].lLbound = 0;
SA_Bounds[1].cElements = nNumberOfColumns;
SA_Bounds[1].lLbound = 0;
//Create array
pResult = SafeArrayCreate(VT_VARIANT, 2, SA_Bounds);
if (pResult == NULL)
{
*pNumberOfRows = 0;
RE: [sqlite] How do I know sqlite_get_table is finished
Well... At 23:17 2/1/2007 +, you wrote: Thanks, yes, I somehow didn't think my explanation made sense and in fact I just had another Excel crash, caused by the same call to sqlite_get_table. I just can't understand why this is happening. There is a valid connection, there is a valid SQL, the DB file is there and working otherwise fine, etc. The wrapper works otherwise 100% perfect and I only have the problem in this particular procedure. I guess there must be a VBA bug then, but I just can't find it. Is it possible that you are using the array returned by the function after you close the database, and that the VB wrapper frees the data when the database is closed? If that is the case, you will probably get a crash as you are accessing a memory that was already freed. Guy - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] VACUUM question
At 14:56 09/02/2005 -0500, Luc wrote: My application does a VACUUM everytime it is launched. This slows down the application a little (it's a plugin for Outlook so it freezes Outlook for a second or two at most, but users could find that annoying). So I was wondering if my app has a sqlite3* pointer to the database and another app does a VACUUM on the same database, will the pointer still be valid? I know that VACUUM actually deletes the database file and replaces it with another one so that's why I need to know if it's safe to do so. My point is that I could get the manager app to do the VACUUM a couple of times during the day so that loading time of the other app (the plugin) would be faster. Why don't you VACUUM on exit? Outlook takes its time closing as it is... and another second or two closing is usually much less noticeable then making the user wait for the application to launch. Guy
Re: [sqlite] index not used?
Hi. At 15:41 12/12/2004 +0100, you wrote: I've a pretty big table (between 800.000 and 900.000 rows) with the following schema: create table relations (rel_id_from integer, rel_id_to integer, id_old integer, id_new integer, valid_from integer, valid_to integer); create index idx_relations_idx0 on relations (valid_from, valid_to, rel_id_from, rel_id_to); ... Normally, a select-statement would look like this (where 1 is the current day): select * from relations where valid_from 1 and valid_to 1 and rel_id_from = 78 and rel_id_to = 9120; This kind of statement is slow (takes between 3 and 4 seconds). It seems that sqlite is doing a full table-scan. For testing purposes, I've executed the following statement using fixed (and existing) values for valid_from and valid_to: select * from relations where valid_from = 9003 and valid_to = 43020 and rel_id_from = 78 and rel_id_to = 9120; This statement executed within a few milliseconds but is of course of no use. I'd suggest reading the 2004 International PHP Conference SQLite slideshow (see the new section of the SQLite site, or just go to http://www.sqlite.org/php2004/page-001.html); specifically page 49 and on (http://www.sqlite.org/php2004/page-049.html). The discussion about the index structure is an eye-opener. Following this, I think you can try to reverse your index and your query to get much better results, at least if you search rel_id_from and rel_id_to as your test query does: create index idx_relations_idx0 on relations (rel_id_from, rel_id_to, valid_from, valid_to); select * from relations where rel_id_from = 78 and rel_id_to = 9120 and valid_from 1 and valid_to 1; As noted in that page, the inequality should be in the right-most columns whenever you can. Guy
Re: [sqlite] FW: SQL error: no such column: State
At 12:51 03/12/2004 -0600, you wrote: When The Table is created in code with the following statements cmd.CommandText = CREATE TABLE BillingNumbers(id int default 0 not null, _ clecID int default 0 not null, _ ilecID int default 0 not null, _ BillingNumber varchar(20) default not null, _ State varchar(20) default not null, _ Resale int default 0 not null, _ UNEP int default 0 not null, _ PctDiscount decimal(8,2) default 0 not null) ... This Insert statement returns the error SQL error: no such column: State INSERT INTO BillingNumbers (id, clecID, ilecID, BillingNumber, State, Resale, UNEP, PctDiscount) VALUES (15, 2, 0, '318Q802095', 'LA', 1, 0, 0) I'd suggest using a few more times; in VB, when you put inside a string, it only leaves one . So the BillingNumber and State columns should be defined thus: ... BillingNumber varchar(20) default not null, _ State varchar(20) default not null, _ ... In your code, the State column is not defined; the default string for BillingNumber, OTOH, is defined as not null, State varchar(20) default Guy
Re: [sqlite] Extracting values from callback
Well...
At 22:23 11/5/2004 +0100, you wrote:
I've been trying at this for a good few hours now, I'm using sqlite3 and
the quick start code at the website.
By using a global char *buffer[5][220];
and then doing buffer[i][counter]=argv[i]; I thought I would be able to
extract the rows in my table but it does not work. When the program has
exited the callback buffer[i][0] equals buffer[i][1] and so on. For some
reason all of them will have the values of the last callback execution.
What you do not seem to understand is that the strings passed to the
callback function are created (and freed) by SQLite code. You can't keep
pointers to them outside of the callback.
For example, you could print out the values using a code like this:
[snip]
int counter=0;
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
int k;
for(k=0;kargc;k++)
{
cout i argv[k] \t;
}
cout \n;
return 0;
}
rc = sqlite3_exec(db, SELECT * FROM gg, callback, 0, zErrMsg);
[/snip]
Or, you could keep the values in string; something like:
[snip]
int counter=0;
std::string values[5][220];
int rows;
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
int k;
rows=argc;
for(k=0;kargc;k++)
{
values[k][counter]= argv[k];
}
counter++;
return 0;
}
rc = sqlite3_exec(db, SELECT * FROM gg, callback, 0, zErrMsg);
int i,p;
for(i=0;icounter;i++)
{
for(p=0;prows;p++)
{
cout i values[p][i].c_str() \t;
}
cout endl;
}
[/snip]
Of course, you can use malloc() and free() to create char* instances
instead of std::string...
Guy
Re: [sqlite] column def vs. constraint at end of create table stmt
At 18:57 11/2/2004 -0500, you wrote: if I have a column def statement for every field and specify unique and primary constraints in each column def, what is the reason the constraint that goes on the very end of the create table would be needed [, constraint]* column-def ::= name [type] [[CONSTRAINT name] column-constraint]* sql-command ::= CREATE [TEMP | TEMPORARY] TABLE table-name ( column-def [, column-def]* [, constraint]* ) Some database users like to have constraints for more then one column at a time; for example, creating a unique constraint for two columns together. As constraint is defined thus: constraint ::= PRIMARY KEY ( column-list ) [ conflict-clause ] | UNIQUE ( column-list ) [ conflict-clause ] | CHECK ( expr ) [ conflict-clause ] You can see that the only way to create a two-column constraint is to use this syntax (unless you want to define an index, that is). Guy
[sqlite] Building SQLite in MSVC 6
Hello. First I'd like to compliment you on a very nice product; I've been adding database support to an open source project and SQLite is of immense help to us. I've been building SQLite as a library and as a DLL for Windows using Visual C++ 6 from sources of version 3.0.8. I'd be happy to publish or provide you with a .dsp (project file) for both types of compilation, as I couldn't seem to find them anywhere on the site. I've gotten quite a few warnings while compiling; one of the warnings is severe IMHO - the function access() used in shell.c has no prototype (an #include io.h is needed there somewhere). There are lots of other warnings that can probably be removed. I was wandering if you would be fixing those warnings, or if you'd like me to try and fix them myself and send you the results. Guy
