Re: [sqlite] Question on Blobs
Dennis Cote wrote: > If you are using the command line sqlite3 program rather than the > library, then all your input must be text that can appear on the command > line or be redirected from stdin. Handling binary data this way will be > difficult. Thanks Dennis, You cleared up allot for me. I think for my (simple) purposes, it's more trouble than it's worth. Plus, I wouldn't be able to search for a word or phrase without lots of trouble decoding first. Mostly, I would just like to save the formating of text when I save a record, the view it again in an editor (as plain text). Some data I am encrypting with gpg, but I am saving that as files outside the database. Fred ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to recover from database corruption? (and why does it happen?)
En/na Luca Olivetti ha escrit: > Hello, > I'm using sqlite 3.3.8 under linux (mandriva 2007.1). [...] > 1) is sqlite suitable when you have multiple threads accessing the same > database? Or should I delegate the access in a single thread and > serialize the queries from the various threads? > 2) is sqlite suitable when you access the database from multiple programs? > 3) why did the first error (rowid missing, wrong # of entries) occur? Is it possible that the problem is due to the fact that apparently 3.3.8 didn't --enable-threadsafe by default (and neither did the distro provided package)? Bye -- Luca Olivetti Wetron Automatización S.A. http://www.wetron.es/ Tel. +34 93 5883004 Fax +34 93 5883007 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Retrieve Rownumber in sqlite
In SQL Server2005, Row_number() function is used to retrieve the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. Which is very useful when implementing paging through a large number records in Table. Is there any function available in SQLite similar to this. Thanks -Kalyani ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How To concatenate two fields in one
Alessio Forconi wrote: > > What I would like to achieve is the same as writing in SQL Server > > SELECT IDStudent, Name + " - " + Surname AS Nominative FROM Students > You need to use the SQL standard concatenation operator. SELECT IDStudent, Name || " - " || Surname AS Nominative FROM Students HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
[EMAIL PROTECTED] wrote: > > How can I debug this issu ? > You will have to show some of the code you are having problems with before anyone here can help you with this. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimization Question for SQLite Experts
Mark Gilbert wrote:
>
> In fact we stumbled across the solution, and I am amazed we didnt
> think of it earlier, and no-one suggested it. Basically our LEAVES
> table doesn't have an Index !!
>
> As soon as we added an index, the process sped up by 17000% :-)
>
> However, I have some questions about this.
>
> 1) What happens when you add records to a table, since the index was built ?
>
They will be added to the index at the same time they are added to the
table.
> 2) Can we trigger a 're-index' without closing the database ?
>
No need. See above.
> 3) In our tests we added the Index manually with an application to
> modify the DB, however, in our own app, we can't get the index to be
> added - any ideas:
>
>
> I am trying to add an index to my database, but the following code is
> failing in CREATE INDEX with error 14 (unable to open Database file).
>
>
> rc = sqlite3_open(OurDataBaseName, );
> if (rc)
> {
> fprintf(stderr, "sqlite3_open returned %d\n",rc);
> return -1;
> }
> rc = sqlite3_exec(db, "CREATE INDEX leaves_index on LEAVES (leafID)",
> 0, 0, );
>
>
>
> the OPEN worked fine, so why is CREATE INDEX returning this error ?
> -
>
I suspect the error is due to the fact the index already exists. It only
needs to be created once, just like the table. You probably have an
error somewhere else that is displaying an incorrect error message.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How To concatenate two fields in one
SQLite uses '||' as the concatenation operator (which is correct, MSSQL is really wrong to accept '+' and not use '||'). HTH, Sam On Fri, Feb 29, 2008 at 11:52 AM, Alessio Forconi <[EMAIL PROTECTED]> wrote: > > What I would like to achieve is the same as writing in SQL Server > > SELECT IDStudent, Name + " - " + Surname AS Nominative FROM Students > > -- - We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer. Position is in the Washington D.C. metro area. Contact [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimization Question for SQLite Experts
shouldn't leafID be the primary key of your LEAVES table and thus already indexed? What does your create table statement look like? I'd expect CREATE TABLE Leaves (LeafID INTEGER PRIMARY KEY AUTOINCREMENT, ... other columns ... ) As far as the create index failing, no idea there, sorry.. Sam On Fri, Feb 29, 2008 at 12:23 PM, Mark Gilbert <[EMAIL PROTECTED]> wrote: > Hey Samuel > > Thanks for your ideas. > > In fact we stumbled across the solution, and I am amazed we didnt > think of it earlier, and no-one suggested it. Basically our LEAVES > table doesn't have an Index !! > > -- - We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer. Position is in the Washington D.C. metro area. Contact [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How To concatenate two fields in one
On Fri, Feb 29, 2008 at 11:52 AM, Alessio Forconi <[EMAIL PROTECTED]> wrote:
> Hello everyone,
>
> SELECT IDStudent, Name + " - " + Surname AS Nominative FROM Students
+ is addition. You want ||. Also, you're using double-quotes (") when
you should be using single-quotes (').
SELECT IDStudent, Name || '.' || Surname
--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How To concatenate two fields in one
Alessio Forconi <[EMAIL PROTECTED]> wrote: > What I would like to achieve is the same as writing in SQL Server > > SELECT IDStudent, Name + " - " + Surname AS Nominative FROM Students SELECT IDStudent, Name || ' - ' || Surname AS Nominative FROM Students Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
On Fri, Feb 29, 2008 at 11:01 AM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I'm working on a program using sqlite library, but I've got an issue > that I can't solve. > Suddenly, my program don't update the tables > I > don't understand whats matter because, if I write SQL instructions > using Sqlite3 client, UPDATE works fine, and I haven't any ERROR CODE. > sqlite3_exec function return SQLITE_OK, but tables aren't updated. > > How > can I debug this issu ? > > Pierlugi Could you provide some examples of the UPDATE statements you're using? -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How To concatenate two fields in one
Use the standard SQL || operator for concatenate. The + is not SQL and specific to SQl Server. Alessio Forconi wrote: > Hello everyone, > > This is my first message. > > I have a table called Students: > > IDStudent char(10) PRIMARY KEY, NOT NULL > Name char (30) NOT NULL > Surname char(30) NOT NULL > > > What I would like to achieve is the same as writing in SQL Server > > SELECT IDStudent, Name + " - " + Surname AS Nominative FROM Students > > > In SQL Server the results is: > IDStudentNominative > MICA-00Michele - Calzolani > > Unfortunately in SQLite I get this: > DStudentNominative > MICA-00 0.0 > > I do not understand where the error is, someone can help me? > > I apologize for my bad English > > > > ___ > 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] CREATE INDEX problem - answer
I have solved my problem below
I am trying to add an index to my database, but the following code is
failing in CREATE INDEX with error 14 (unable to open Database file).
rc = sqlite3_open(OurDataBaseName, );
if (rc)
{
fprintf(stderr, "sqlite3_open returned %d\n",rc);
return -1;
}
rc = sqlite3_exec(db, "CREATE INDEX leaves_index on LEAVES (leafID)",
0, 0, );
the OPEN worked fine, so why is CREATE INDEX returning this error ?
-
I had just updated to the SQLite 3.5.6 amalgamation and I was opening
a database created with 3.4.1. That was giving me this error.
So, I just tried downgrading the SQLite amalgamation to 3.4.1 and
suddenly the CREATE INDEX is working.
Anyone have an explanation for this ?
Thanks
Mark
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite 3.5.6 and readline
Hello, I am trying to compile sqlite in a /custom/directory and keep the ability to use arrows to get previously entered commands in the sqlite3 executable. I read the wiki about that topic http://www.sqlite.org/cvstrac/wiki?p=ReadLine but I am still failing. It is advised to find the variable READLINE_FLAGS and LIBREADLINE in the Makefile, but I can only find: READLINE_LIBS I am not sure what I should enter there as a value and whether that would be enough to solve my problem Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimization Question for SQLite Experts
Hey Samuel
Thanks for your ideas.
In fact we stumbled across the solution, and I am amazed we didnt
think of it earlier, and no-one suggested it. Basically our LEAVES
table doesn't have an Index !!
As soon as we added an index, the process sped up by 17000% :-)
However, I have some questions about this.
1) What happens when you add records to a table, since the index was built ?
2) Can we trigger a 're-index' without closing the database ?
3) In our tests we added the Index manually with an application to
modify the DB, however, in our own app, we can't get the index to be
added - any ideas:
I am trying to add an index to my database, but the following code is
failing in CREATE INDEX with error 14 (unable to open Database file).
rc = sqlite3_open(OurDataBaseName, );
if (rc)
{
fprintf(stderr, "sqlite3_open returned %d\n",rc);
return -1;
}
rc = sqlite3_exec(db, "CREATE INDEX leaves_index on LEAVES (leafID)",
0, 0, );
the OPEN worked fine, so why is CREATE INDEX returning this error ?
-
Thanks again
Mark.
>
>Message: 2
>Date: Thu, 28 Feb 2008 21:25:35 -0500
>From: "Samuel Neff" <[EMAIL PROTECTED]>
>Subject: Re: [sqlite] Optimization Question for SQLite Experts
>To: "General Discussion of SQLite Database"
>
>Here's two suggestions. First the simple suggestion is instead of this..
>
>for (z=0;z{
>sqlite3_get_table("select * from LEAVES where leafID =
>theTwig[z].childLeafID")
>// we end up performing this query 200 times, once for each of the
>parent twigs.
>}
>
>try this
>
>select * from LEAVDES where leafID IN ( list of leaves ...)
>
>and then sort out which records belong to which in code. That way you're
>executing far less individual queries. That's a simple optimization that
>should help a bit, but you still need some levels of recursion to identify
>all the twigs.
>
>Another option is to load all of the IDs and references into a structure in
>memory that allows you to perform efficient hierarchical searches. Use this
>structure for the recursive search to identify the id's of all the twigs and
>leaves, and then you can issue one sql statement to get all twigs and one to
>get all leaves. Much faster and with 15000 leaves should have plenty of
>space to hold the data in memory, especially if you're only storing ids and
>references.
>
>HTH,
>
>Sam
>
>
>On Thu, Feb 28, 2008 at 7:00 PM, Mark Gilbert <[EMAIL PROTECTED]> wrote:
>
>> Folks.
>>
>> Looking for some advice from hardened SQliters...
>>
>> ...
>
>For each twig we have to find all the leaves. The Leaves table has
>> maybe 15000 records and we have a query where we search the Leaves
>> table once for each twig we find. In our part of the tree there
>> might be 200 twigs, and so we end up searching the leaves DB 200
>> times, to build up the list of leaves attached to the twigs
>>
>> ...in pseudocode:
>> for (z=0;z> {
>> sqlite3_get_table("select * from LEAVES where leafID =
>> theTwig[z].childLeafID")
>> // we end up performing this query 200 times, once for each of the
>> parent twigs.
>> }
>> ...
>>
> > Thanks for any experience you can share..
>>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] newB question; c++ and sqlite3; how2 check if word exist in the table
vl.pavlov wrote: > > i wander how 2 check (efficiently) if some word (string, in c++) exist in > the sqlite3 table > Assuming this is the same database as your last question. You have the table: create table words (word text primary key, number integer); Then this query will work: select exists (select * from words where word = :word); HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How To concatenate two fields in one
Hello everyone, This is my first message. I have a table called Students: IDStudent char(10) PRIMARY KEY, NOT NULL Name char (30) NOT NULL Surname char(30) NOT NULL What I would like to achieve is the same as writing in SQL Server SELECT IDStudent, Name + " - " + Surname AS Nominative FROM Students In SQL Server the results is: IDStudentNominative MICA-00Michele - Calzolani Unfortunately in SQLite I get this: DStudentNominative MICA-00 0.0 I do not understand where the error is, someone can help me? I apologize for my bad English ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimizing an insert/update
Michael Miller wrote: > I apologize if this is a double-post; I just got approved for the mailing > list, and I can't find the older message in the archives, so I'm reposting > it. > > I have a table with two columns, the first with a string and the second with > an integer. > > > Given a set of input strings, I want to perform this operation 50,000+ times > preferably in a single transaction: "If the string doesn't exist in the > table, create a new row with the string in the first column and 1 in the > second column. If the string does exist in the table, increment the second > column by 1" > > > What I am doing now is using the UPDATE statement, and checking (via C#) the > number of rows affected, and if this is zero, running an insert statement. > Doing this 50,000 times, without an encapsulating transaction, is an > expensive operation. > > > Is there any way to encapsulate this into SQL so that no outside interaction > in C# is needed to perform this operation, and so I can put 50,000 of these > into a transaction and commit all at once? > > > Thanks, > > Mike > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > See this thread from last week http://article.gmane.org/gmane.comp.db.sqlite.general/35829/match=easy+question+concerning+c%2b%2b+sqlite3 This exact issue was discussed. You can prepare the query once, and execute it 50K times in a transaction to quickly enter all your words. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Join Syntax Questions
Mitchell Vincent wrote: > I could swear I've done this type of thing before and am sure I'm > overlooking something simple. > > Is this correct syntax? > > SELECT im.invoice_date as invoice_date,im.pay_by as > due_date,im.invoice_id as invoice_id, im.invoice_number as > invoice_number,im.invoice_date as created,im.status as status, > im.next_invoice as next_invoice, im.tax as tax,im.tax2 as > tax2,im.subtotal as subtotal,im.total as total,im.balance_due as > balance_due, im.customer_number as customer_number, > im.customer_name as customer_name FROM invoice_master as im LEFT JOIN > ( SELECT coalesce(sum(payment_applied), 0.00) as total_paid,invoice_id > as theiid FROM payments WHERE void='f' AND > created <= 1204243199) the_payments on im.invoice_id = > the_payments.theiid WHERE im.invoice_date between 1201478400 And > 1204243199 AND im.status != 'Forwarded' > GROUP BY im.invoice_id ORDER BY im.balance_due > DESC,im.invoice_date,im.total DESC,im.customer_name > > With or without the join I get the exact same result set. I don't even > see null results for the columns that are supposed to be pulled in > from the join. I have a habit of mixing SQLite and PostgreSQL syntax, > have I done it again? > The above statement is basically unreadable. After it is formatted for human consumption it becomes: SELECT im.invoice_date as invoice_date, im.pay_by as due_date, im.invoice_id as invoice_id, im.invoice_number as invoice_number, im.invoice_date as created, im.status as status, im.next_invoice as next_invoice, im.tax as tax, im.tax2 as tax2, im.subtotal as subtotal, im.total as total, im.balance_due as balance_due, im.customer_number as customer_number, im.customer_name as customer_name FROM invoice_master as im LEFT JOIN ( SELECT coalesce(sum(payment_applied), 0.00) as total_paid, invoice_id as theiid FROM payments WHERE void='f' AND created <= 1204243199) the_payments on im.invoice_id = the_payments.theiid WHERE im.invoice_date between 1201478400 And 1204243199 AND im.status != 'Forwarded' GROUP BY im.invoice_id ORDER BY im.balance_due DESC, im.invoice_date, im.total DESC, im.customer_name Where you can easily see that the only columns you are selecting are the from the im table (invoice_master). The columns from the the_payments sub-query are not selected, so they are not displayed. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Update fail without ERRORS
I'm working on a program using sqlite library, but I've got an issue that I can't solve. Suddenly, my program don't update the tables I don't understand whats matter because, if I write SQL instructions using Sqlite3 client, UPDATE works fine, and I haven't any ERROR CODE. sqlite3_exec function return SQLITE_OK, but tables aren't updated. How can I debug this issu ? Pierlugi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] export to SQL insert statements with column names
[EMAIL PROTECTED] wrote: > Is there a way to export/dump SQLite data into INSERT statements which > also have column names? > No. You would have to build a custom version and modify the source of the dump command in shell.c. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Blobs
Fred J. Stephens wrote: > Thanks John & Dennis; > Looks like I am getting ahead of myself here. I'm just doing a simple > PIM app as a BASH script that uses SQLite to store data. Probably I > can't do this in a script as you could in C. > > I find the formating of the text from a file is not saved if I read it > and insert it into a table, so I was hoping there was a way to save the > binary file and thus preserve all the formatting. Also it would let me > save images etc. in the database. > > When I get back to learning Python, maybe one of the SQLite wrappers > will help me do this more easily. My present program is really just a > way to get started until I move to Python. Also, it is good practice in > BASH scripting, SQL and basic usage of SQLite. > Fred, If you are using the command line sqlite3 program rather than the library, then all your input must be text that can appear on the command line or be redirected from stdin. Handling binary data this way will be difficult. They only idea I have is to use another command to encode the binary file as pure text, say by converting each byte into a two character hex encoding of that byte, and then using .import to load the encoded text file into a table. From there other SQL commands can move the data to other tables etc. To recover the file your bash scripts would have to export the encoded file using .output and a select statement, then run another command to decode the file back to its binary representation. The encode/decode programs would be quite easy to write in C or Python, or they may already exist. One disadvantage of this scheme is that the encoded file will be twice the size of the original. This could be mitigated for text files by compressing them before encoding, and decompressing them after decoding, since text files typically compress very well. This wouldn't work (i.e. won't stop the file doubling) for files that hold compressed data such as jpeg image data. Any of the command line file compressors, like gzip and bzip, should work fine. Note the encoding scheme must not split a file into multiple lines (i.e the encoded format can't have any embedded linefeeds), since the .import command would split the file into multiple records. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] undefined symbol: sqlite3_prepare_v2
Joanne Pham wrote: > The question is how to check the sqlite version from this > library(libsql3.so.0.8.6. > Joanne, Have your application execute the following SQL statement and then look at the result. select sqlite_version(); This will return the versionif the library used to execute the query. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested calls to prepare/step/prepare
On Fri, Feb 29, 2008 at 11:50:32AM +0700, Dan scratched on the wall:
>
> On Feb 29, 2008, at 5:21 AM, Ken wrote:
>
> > I wanted to find out if the following is allowed in Sqlite.
> >
> >sqlite3_prepare_v2
> > while ( ) {
> >sqlite3_step
> > sqlite3_prepare_v2 --- I;m getting a segv here.
> > while ( ) {
> >sqlite3_step
> > }
> > }
> >
> >
> > So my questing is, does sqlite allow a prepare to be started while
> > a prior prepared statement is still open?
>
> This is supposed to be Ok. If you can supply an example program
> to demonstrate the crash that would be very helpful.
I've pre-prepared the statements and then just executed them in a
nested fashon. That only works if you know exactly what statements
you need, but I've found this is often the case.
e.g.:
sqlite3_prepare_v2( ...stmt1... );
sqlite3_prepare_v2( ...stmt2... );
sqlite3_bind...( ...stmt1... );
while( sqlite3_step( ...stmt1...) ) {
// extract data from step above
sqlite3_bind...( ...stmt2... );
sqlite3_step( ...stmt2... ); // or loop, if required
sqlite3_reset( ...stmt2... );
sqlite3_clear_bindings( ...stmt2... );
}
This brings up a somewhat related question I was wondering about. Is
there any documentation on how many "in-flight" statements a single
database handle can have open? For example, how many prepared
statements I can have somewhere between their first sqlite3_step()
call and sqlite3_reset(). Also, are there any limits on how those
statements interact? Will the inner statement be run in its own
transaction? Does the outer statement need to be a read-only
statement?
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"'People who live in bamboo houses should not throw pandas.' Jesus said that."
- "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimizing an insert/update
Michael Miller wrote: > I apologize if this is a double-post; I just got approved for the mailing > list, and I can't find the older message in the archives, so I'm reposting > it. > > I have a table with two columns, the first with a string and the second with > an integer. > > > Given a set of input strings, I want to perform this operation 50,000+ times > preferably in a single transaction: "If the string doesn't exist in the > table, create a new row with the string in the first column and 1 in the > second column. If the string does exist in the table, increment the second > column by 1" > > > What I am doing now is using the UPDATE statement, and checking (via C#) the > number of rows affected, and if this is zero, running an insert statement. > Doing this 50,000 times, without an encapsulating transaction, is an > expensive operation. INSERT OR REPLACE > > > Is there any way to encapsulate this into SQL so that no outside interaction > in C# is needed to perform this operation, and so I can put 50,000 of these > into a transaction and commit all at once? > > > Thanks, > > Mike > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- Cyril SCETBON ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unable to Open Database File when creating Index
Folks.
I am trying to add an index to my database, but the following code is
failing in CREATE INDEX with error 14 (unable to open Database file).
rc = sqlite3_open(OurDataBaseName, );
if (rc)
{
fprintf(stderr, "sqlite3_open returned %d\n",rc);
return -1;
}
rc = sqlite3_exec(db, "CREATE INDEX objindex on object (objectID)",
0, 0, );
the OPEN worked fine, so why is CREATE INDEX returning this error ?
Thanks for any advice.
Cheers
Mark
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] installation of sqlite in linux
I have downloaded tclsqlite-3.5.6.so.gz(with tcl binding , i need tcl).I dont know how to install.plz help. -- View this message in context: http://www.nabble.com/installation-of-sqlite-in-linux-tp15759216p15759216.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimizing an insert/update
Michael Miller wrote: > "If the string doesn't exist in the table, create a new row with > the string in the first column and 1 in the second column. If > the string does exist in the table, increment the second column > by 1" One way you can do it is to insert everything into a temporary table which doesn't require the string to be unique, and then do a select sum(count),string from temp group by string; create table mystrings (string text primary key, count integer); create table mytemp (string text, count integer); .import mydata.txt mytemp insert into mystrings (string, count) select string, sum(count) from mytemp group by string; As a complement to the "insert or replace" it would be nice to have "insert or accumulate" for this kind of a job. However, that would go outside of the SQL standard. -- Lars Aronsson ([EMAIL PROTECTED]) Aronsson Datateknik - http://aronsson.se ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] newB question; c++ and sqlite3; how2 check if word exist in the table
hi again, i wander how 2 check (efficiently) if some word (string, in c++) exist in the sqlite3 table any suggestions ? -- View this message in context: http://www.nabble.com/newB-question--c%2B%2B-and-sqlite3--how2-check-if-word-exist-in-the-table-tp15758100p15758100.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] export to SQL insert statements with column names
Hi,
Is there a way to export/dump SQLite data into INSERT statements which
also have column names?
As of now, a sqlite dump looks like this
INSERT INTO "ric_tb_language" VALUES('ENG','English');
INSERT INTO "ric_tb_language" VALUES('SPN','Spanish');
INSERT INTO "ric_tb_language" VALUES('GER','German');
Is it possible to have it like below(column names included)?
INSERT INTO "ric_tb_language" (ID, language) VALUES('ENG','English');
INSERT INTO "ric_tb_language" (ID, language) VALUES('SPN','Spanish');
INSERT INTO "ric_tb_language" (ID, language) VALUES('GER','German' );
Regards
Shibu Narayanan
Consultant, PrimeSourcing Division, Investment Banking Group
Tel.Office: 91-80-2208-6270 or 91-80-6659-6270
e-mail: [EMAIL PROTECTED]
The answer is 42.
-bin/mailman/listinfo/sqlite-users
DISCLAIMER:
This message contains privileged and confidential information and is intended
only for an individual named. If you are not the intended recipient, you should
not disseminate, distribute, store, print, copy or deliver this message. Please
notify the sender immediately by e-mail if you have received this e-mail by
mistake and delete this e-mail from your system. E-mail transmission cannot be
guaranteed to be secure or error-free as information could be intercepted,
corrupted, lost, destroyed, arrive late or incomplete or contain viruses. The
sender, therefore, does not accept liability for any errors or omissions in
the contents of this message which arise as a result of e-mail transmission. If
verification is required, please request a hard-copy version.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
