Re: [sqlite] import / insert 120k records
That makes good sense - if my task were repetitive I would take that route - I opted to write a quick and dirty PHP script just to handle this one-off case and it worked like a charm. I can share the script as a jumping off point for anyone who is interested. *Ben Marchbanks* John Stanton wrote: > Write a simple program.. > > As I recall our programs which do this use the Expat parser and obey a > protocol where the XML DTD represents the database tables and columns so that > SQL can be generated directly from the XML. It makes for a handy way to > transport a database, or a part of a database in a universal format. All > that is needed is the program to scan the database and generate XML and one > to receive the XML and transform it into SQL statements. > > > Ben Marchbanks wrote: > >> Whats the best way to import a large number of records ? >> 120K+ currently stored as an XML file. >> >> >> > > ___ > 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
[sqlite] import / insert 120k records
Whats the best way to import a large number of records ? 120K+ currently stored as an XML file. -- *Ben Marchbanks* ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Import xml into SQLite
I am looking for a nice GUI for admin of SQLite database - on Mac OS. I tried FireFox SQLmanager with no luck - any other suggestions ? I need an easy way to import large XML into a new table. -- *Ben Marchbanks* ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Feasibility of SQLite as multiuser (readonly)
I realize the SQLite is designed for single client only but is it feasible at all to use a SQLite DB for multiple users in a workgroup as a readonly data source ? -- *Ben Marchbanks* ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiuser Readonly Database
I have a user application written in Abobe AIR that utilizes SQLite - its a modular application in that users can download sets of files and each set has its own SQLite db as an index for performing searches etc Originally this was designed to be standalone client application but the project now has a new requirement that enterprise users be able to run the app and access the same documents (and SQLite db) over the network simultaneously. Files ad SQLite DB is for readonly. Is this viable and if so what are some of the caveats ? -- *Ben Marchbanks* ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Parenthetical LIKE query ??
SELECT * FROM `pagesText` WHERE pageText LIKE ( "%muffler%" , "%clamp%" ) Is there a nice way to write a query using parenthetical LIKE ? Queries like this can get quite long otherwise Any suggestions ? *Ben Marchbanks* www.magazooms.com <http://www.magazooms.com/> Signature Email: b...@magazooms.com <mailto:b...@magazooms.com> Phone: (864) 284.9918 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] REGEXP
I am confused. Is REGEXP enabled in SQLite or does there have to be a regexp custom function created ? -- *Ben Marchbanks* www.magazooms.com <http://www.magazooms.com/> Signature Email: b...@magazooms.com <mailto:b...@magazooms.com> Phone: (864) 284.9918 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adobe AIR SQLite - error 3132
I believe I found the problem. I attempted to update a date field 2008.12.07 which was being interpretted as a number - apparently AIR version of SQLite enforces data types more stringently. *Ben Marchbanks* www.magazooms.com <http://www.magazooms.com/> Signature Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> Phone: (864) 284.9918 Ben Marchbanks wrote: > I am getting an exception # 3132 as I try to update a record in my AIR > runtime application. > > This error only occurs on my WinXP test machine and not on my iMac > running OS 10.5.5 ( my development machine). > > Any resource for getting full list of error codes ? > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Adobe AIR SQLite - error 3132
I am getting an exception # 3132 as I try to update a record in my AIR runtime application. This error only occurs on my WinXP test machine and not on my iMac running OS 10.5.5 ( my development machine). Any resource for getting full list of error codes ? -- *Ben Marchbanks* www.magazooms.com <http://www.magazooms.com/> Signature Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> Phone: (864) 284.9918 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adobe AIR Version Issue(?)
My project essentially creates a backup of selected records from mySQL in SQLite. If I don't completely delete the previous backup (SQLite database file) I cannot create a new backup. In otherwords the database is locked and I cannot drop tables etc Simple work-around for me was to delete the db file and start fresh. If you are using AIR besure to "clean project" to move your sqlite database to the bin-debug folder. I had the same issue just today - I realized my updated SQLite table was not being used by the debugger. *Ben Marchbanks* www.magazooms.com <http://www.magazooms.com/> Signature Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> Phone: (864) 284.9918 DMcQ wrote: > Hi Ben, > > Would you mind explaining what you mean by " delete the previous sqlite > backup file"? > > I'm running into a 'no table found' error (when I can see it's there using > SQLite Data Browser app), but I don't know of any backup file that's > created. > > Any hints greatly appreciated. > > Daniel > > > Ben Marchbanks-2 wrote: > >> Yes I did resolve it using a large hammer - >> >> I just delete the previous sqlite backup file using ulink since for my >> purposes its a total >> backup anyway. >> >> It would be good to know how to do incremental changes as an option - >> let me know if you find a solution. >> >> One thing I did notice is that the file permission were set to >> -rwx-rwx-r-x instead of drwx-rwx--r-x as >> I would expect using chmod 0775. >> >> I think that PHP-PDO is a clumsy implementation. >> >> *Ben Marchbanks* >> >> www.magazooms.com <http://www.magazooms.com/> >> Signature >> Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> >> Phone: (864) 284.9918 >> >> >> phillipm wrote: >> >>> tiggyboo wrote: >>> >>> >>>> I created and populated a table via sqlite3 on a Mac OSX machine, and >>>> have >>>> no problem working with it from the sqlite3 prompt. However, when I >>>> try >>>> to access the sole table (named cftable) in this database via a very >>>> simple Adobe AIR application I'm working on, I get this error: >>>> >>>> Error #3115: SQL Error.', details:'no such table: cftable' >>>> >>>> My question - would this be typical of a version conflict for sqlite, >>>> i.e., is the latest release of AIR perhaps using an embedded sqlite that >>>> is not compatible with what I have (3.1.3) on my machine? I have yet >>>> to >>>> determine which version is embedded in AIR. >>>> >>>> Thanks, >>>> Al >>>> >>>> >>>> >>> Just wondering if you resolved this issue - I'm experiencing the same >>> situation. Very simple project, small database in the project folder? >>> >>> Any help appreciated, thanks >>> >>> >>> Phil >>> >>> >> ___ >> 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
Re: [sqlite] Adobe AIR sqlite - access to sqlite_master
ignore this post - found the error of my ways ! *Ben Marchbanks* www.magazooms.com <http://www.magazooms.com/> Signature Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> Phone: (864) 284.9918 Ben Marchbanks wrote: > I am having trouble getting a table description from a SQLite 3.4.2 database > that was originally created on Linux system using PHP-PDO. > > I migrated this DB to an Adobe AIR 1.1 application where I understand > the AIR implement is somehow different. > > I am trying to use the basic call to describe the table. > > select table_name from sqlite_master where type='table' >> throws ERROR > 3115. > > Using the build-in methods from AIR to get table schema also fails. > (Same method works fine on DB > create with AIR) > > _sqlConnection.loadSchema(); > var schemaResult:SQLSchemaResult = _sqlConnection.getSchemaResult(); > var mytableSchema = schemaResult.tables > > trace(mytableSchema) // = "undefined" > > Any ideas on a workaround ? > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Adobe AIR sqlite - access to sqlite_master
I am having trouble getting a table description from a SQLite 3.4.2 database that was originally created on Linux system using PHP-PDO. I migrated this DB to an Adobe AIR 1.1 application where I understand the AIR implement is somehow different. I am trying to use the basic call to describe the table. select table_name from sqlite_master where type='table' >> throws ERROR 3115. Using the build-in methods from AIR to get table schema also fails. (Same method works fine on DB create with AIR) _sqlConnection.loadSchema(); var schemaResult:SQLSchemaResult = _sqlConnection.getSchemaResult(); var mytableSchema = schemaResult.tables trace(mytableSchema) // = "undefined" Any ideas on a workaround ? -- *Ben Marchbanks* www.magazooms.com <http://www.magazooms.com/> Signature Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> Phone: (864) 284.9918 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adobe AIR Version Issue(?)
If you haven't already downloaded it checkout Christian Coenreats SQLite Admin written in AIR. http://coenraets.org/blog/2008/02/sqlite-admin-for-air-10/ Its a big help for me as my SQLite dbs are created on a Linux server (a select backup from mySQL) and then I embed them into my AIR project on mac OSX. SQLite Admin helps verify schema and test queries etc... *Ben Marchbanks* www.magazooms.com <http://www.magazooms.com/> Signature Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> Phone: (864) 284.9918 Ben Marchbanks wrote: > Yes I did resolve it using a large hammer - > > I just delete the previous sqlite backup file using ulink since for my > purposes its a total > backup anyway. > > It would be good to know how to do incremental changes as an option - > let me know if you find a solution. > > One thing I did notice is that the file permission were set to > -rwx-rwx-r-x instead of drwx-rwx--r-x as > I would expect using chmod 0775. > > I think that PHP-PDO is a clumsy implementation. > > *Ben Marchbanks* > > www.magazooms.com <http://www.magazooms.com/> > Signature > Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > Phone: (864) 284.9918 > > > phillipm wrote: > >> tiggyboo wrote: >> >> >>> I created and populated a table via sqlite3 on a Mac OSX machine, and have >>> no problem working with it from the sqlite3 prompt. However, when I try >>> to access the sole table (named cftable) in this database via a very >>> simple Adobe AIR application I'm working on, I get this error: >>> >>> Error #3115: SQL Error.', details:'no such table: cftable' >>> >>> My question - would this be typical of a version conflict for sqlite, >>> i.e., is the latest release of AIR perhaps using an embedded sqlite that >>> is not compatible with what I have (3.1.3) on my machine? I have yet to >>> determine which version is embedded in AIR. >>> >>> Thanks, >>> Al >>> >>> >>> >> Just wondering if you resolved this issue - I'm experiencing the same >> situation. Very simple project, small database in the project folder? >> >> Any help appreciated, thanks >> >> >> Phil >> >> > ___ > 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
Re: [sqlite] Adobe AIR Version Issue(?)
Yes I did resolve it using a large hammer - I just delete the previous sqlite backup file using ulink since for my purposes its a total backup anyway. It would be good to know how to do incremental changes as an option - let me know if you find a solution. One thing I did notice is that the file permission were set to -rwx-rwx-r-x instead of drwx-rwx--r-x as I would expect using chmod 0775. I think that PHP-PDO is a clumsy implementation. *Ben Marchbanks* www.magazooms.com <http://www.magazooms.com/> Signature Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> Phone: (864) 284.9918 phillipm wrote: > > tiggyboo wrote: > >> I created and populated a table via sqlite3 on a Mac OSX machine, and have >> no problem working with it from the sqlite3 prompt. However, when I try >> to access the sole table (named cftable) in this database via a very >> simple Adobe AIR application I'm working on, I get this error: >> >> Error #3115: SQL Error.', details:'no such table: cftable' >> >> My question - would this be typical of a version conflict for sqlite, >> i.e., is the latest release of AIR perhaps using an embedded sqlite that >> is not compatible with what I have (3.1.3) on my machine? I have yet to >> determine which version is embedded in AIR. >> >> Thanks, >> Al >> >> > > Just wondering if you resolved this issue - I'm experiencing the same > situation. Very simple project, small database in the project folder? > > Any help appreciated, thanks > > > Phil > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PHP created sqlite permissions
I use PDF-PDO to create and populate sqlite tables as a backup - each time the backup process is run I get a table is locked error. Deleting the sqlite db allows the process to run without error. After the db is created I chmod the file to "0775"; I notice the result is ... -rwxrwxr-x 1 apache apache 9411584 2008-11-21 09:23 mzdb.sqlite Could it be that the reason for the "table lock" is due to file permissions? If not what could cause the table to remain locked after being populated ? -- *Ben Marchbanks* www.magazooms.com <http://www.magazooms.com/> Signature Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> Phone: (864) 284.9918 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Escaped values throws error
This is my first foree into PDO-SQLite and I saw samples using binding, I am using prepare() though without binding. So you think that had I used binding my escape issue would not be an issue ? *Ben Marchbanks* www.magazooms.com <http://www.magazooms.com/> Signature Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> Phone: (864) 284.9918 Eric Minbiole wrote: >> Now I remember why I wasn't using single quotes in SQLite statements - >> >> because escaped single quoted values like >> >> 'Billy\'s bike' >> >> cause it to fail. Again I think its related to PHP-PDO SQLite >> implementation. >> > > Have you considered using prepared statements and bound parameters? > That way, you never have to worry about character escaping, or SQL > injection problems. > > ~Eric > ___ > 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
Re: [sqlite] Escaped values throws error
Now I remember why I wasn't using single quotes in SQLite statements -
because escaped single quoted values like
'Billy\'s bike'
cause it to fail. Again I think its related to PHP-PDO SQLite
implementation.....
*Ben Marchbanks*
www.magazooms.com <http://www.magazooms.com/>
Signature
Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
Phone: (864) 284.9918
Ben Marchbanks wrote:
> Hmmm - thanks for straightening me out guys - somehow I thought that
> SQLite used double quotes
>
> *Ben Marchbanks*
>
> www.magazooms.com <http://www.magazooms.com/>
> Signature
> Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
> Phone: (864) 284.9918
>
>
> John Stanton wrote:
>
>> An SQL literal uses single quotes.
>>
>> Ben Marchbanks wrote:
>>
>>
>>> Abridged sample of the insert statement that produces an error when
>>> trying to insert escaped text.
>>>
>>>
>>> INSERT INTO pageWords (word) VALUES (" \"nitch\" ");
>>>
>>> SQLSTATE[HY000]: General error: 1 near "nitch": syntax error'
>>>
>>>
>>> Using
>>>
>>> PHP Version 5.2.4
>>> PDO SQLite - version 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.2
>>> 2007/03/23 14:30:00 wez Exp $
>>> SQLite 3.4.2
>>>
>>>
>>>
>> ___
>> 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
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Escaped values throws error
Hmmm - thanks for straightening me out guys - somehow I thought that
SQLite used double quotes
*Ben Marchbanks*
www.magazooms.com <http://www.magazooms.com/>
Signature
Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
Phone: (864) 284.9918
John Stanton wrote:
> An SQL literal uses single quotes.
>
> Ben Marchbanks wrote:
>
>> Abridged sample of the insert statement that produces an error when
>> trying to insert escaped text.
>>
>>
>> INSERT INTO pageWords (word) VALUES (" \"nitch\" ");
>>
>> SQLSTATE[HY000]: General error: 1 near "nitch": syntax error'
>>
>>
>> Using
>>
>> PHP Version 5.2.4
>> PDO SQLite - version 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.2
>> 2007/03/23 14:30:00 wez Exp $
>> SQLite 3.4.2
>>
>>
>
> ___
> 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
Re: [sqlite] Escaped values throws error
Thanks - I did find a solution since I was not successful using
sqlite_escape_string.
I think that the SQLite implementation in PHP via PDO is the culprit.
I wound up using double escape - str_replace("\"","\"\"",$value);
*Ben Marchbanks*
www.magazooms.com <http://www.magazooms.com/>
Signature
Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
Phone: (864) 284.9918
P Kishor wrote:
> On 11/16/08, Ben Marchbanks <[EMAIL PROTECTED]> wrote:
>
>> Abridged sample of the insert statement that produces an error when
>> trying to insert escaped text.
>>
>>
>> INSERT INTO pageWords (word) VALUES (" \"nitch\" ");
>>
>
> SQL text strings are delimited by single quotes. If your really want
> to insert "nitch" (including the double quotes), try ... VALUES
> ('"nitch"');
>
> Alternatively, check out the ESCAPE clause in SQLite docs.
>
>
>
>> SQLSTATE[HY000]: General error: 1 near "nitch": syntax error'
>>
>>
>> Using
>>
>> PHP Version 5.2.4
>> PDO SQLite - version 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.2
>> 2007/03/23 14:30:00 wez Exp $
>> SQLite 3.4.2
>>
>> --
>> *Ben Marchbanks*
>>
>> www.magazooms.com <http://www.magazooms.com/>
>> Signature
>> Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
>> Phone: (864) 284.9918
>> ___
>> 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
[sqlite] Escaped values throws error
Abridged sample of the insert statement that produces an error when
trying to insert escaped text.
INSERT INTO pageWords (word) VALUES (" \"nitch\" ");
SQLSTATE[HY000]: General error: 1 near "nitch": syntax error'
Using
PHP Version 5.2.4
PDO SQLite - version 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.2
2007/03/23 14:30:00 wez Exp $
SQLite 3.4.2
--
*Ben Marchbanks*
www.magazooms.com <http://www.magazooms.com/>
Signature
Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
Phone: (864) 284.9918
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database Locked
I am writing a php script to back up mysql to sqlite(3) via PDO My process is : -- Create SQLite database connection Loop several tables Describe mysql table Drop sqlite table if exists, Create matching sqlite table query mysql table for recordset. insert recordset into sqlite table End Loop Kill SQLite connection This process works fine if the database is a new file otherwise I get an error : SQLSTATE[HY000]: General error: 6 database table is locked; What am I missing here - perhaps the tables are left open ? *Ben Marchbanks* www.magazooms.com <http://www.magazooms.com/> Signature Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> Phone: (864) 284.9918 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
