[sqlite] Need a sqlite c api that wrires data into a table.

Hi friends, I check all the C APIs which are provided by sqlite. I can able to write data to a table, using sqlite3_exec() function. But i need a C API that will write data into table, i.e., sqlite3_exec(). Ex: if i give "database name", table name and values to that

Re: [sqlite] simple like query optimization

Here, a,b,d,e,f,u and v are variable length strings. delimiter '/ ' is constant. On Fri, Jan 13, 2012 at 12:22 PM, Durga D wrote: > Hi All, > > I have a table like this: > > h t0info( h is primary key, t0info is unique) > --- > > 1/a/d/u > 2

[sqlite] simple like query optimization

Hi All, I have a table like this: h t0info( h is primary key, t0info is unique) --- 1/a/d/u 2/a/e/u 3/a/f/u 4/a/g/v 5/a/b/c/d/e/f input is : /a output should be: d, e f, g and b I am doing this way: select t0info from t0 where t0info like '/a%';

Re: [sqlite] Incompatible versions of SQLite on same system

On 13 Jan 2012, at 1:46am, Joe Winograd wrote: > Thanks for the clarification. My pleasure. Triggers do generally work in the most useful way. Try coding it and see if it works for you. > Btw, is bottom-posting the standard in this group? As you can tell, I'm > rather fond of top-posting.

Re: [sqlite] Incompatible versions of SQLite on same system

gmail seems to really prefer to top-post. I mix and match because it doesn't bother me either way. On Thu, Jan 12, 2012 at 8:46 PM, Joe Winograd wrote: > Simon, > Thanks for the clarification. Btw, is bottom-posting the standard in this > group? As you can tell, I'm rather

Re: [sqlite] Incompatible versions of SQLite on same system

Simon, Thanks for the clarification. Btw, is bottom-posting the standard in this group? As you can tell, I'm rather fond of top-posting. Yes, I've ready many of the arguments why bottom-posting is better – I simply don't buy it. But I'll be happy to comply with group standards. Regards, Joe

Re: [sqlite] UPSERT again

Come to thihk of it just add another field which is set to the "old" value during the update. Then both fields are available AFTER INSERT. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems

Re: [sqlite] UPSERT again

On 13 Jan 2012, at 12:42am, Tony Caras wrote: > No, the insert I referred to actually is an INSERT OR REPLACE, but it doesn't > work because a REPLACE really is a DELETE the original and INSERT the new. > So in the example if the blocked field was true in the original record and > true in

Re: [sqlite] UPSERT again

What if you do a BEFORE INSERT and stick the values in a temporary table (use a matching rowid). Then you can just retrieve in in your AFTER trigger. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information

Re: [sqlite] UPSERT again

There aren't any true updates, all updates are done using the INSERT OR REPLACE. From: jr To: sqlite-users@sqlite.org Sent: Thursday, January 12, 2012 5:47 PM Subject: Re: [sqlite] UPSERT again On 13/01/12 00:17, Tony Caras

Re: [sqlite] UPSERT again

On 13/01/12 00:17, Tony Caras wrote: UPSERT (really means update the record if it exists otherwise insert the record) AFTER INSERT (I meant a trigger after an insert. In this case I have access to the new value but not the "old" values in the record.) If UPDATE would insert the record if it

Re: [sqlite] UPSERT again

No, the insert I referred to actually is an INSERT OR REPLACE, but it doesn't work because a REPLACE really is a DELETE the original and INSERT the new.  So in the example if the blocked field was true in the original record and true in the replacement record, then the counter will get

Re: [sqlite] UPSERT again

Does REPLACE do what you want? http://www.sqlite.org/lang_replace.html Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org

Re: [sqlite] UPSERT again

UPSERT (really means update the record if it exists otherwise insert the record) AFTER INSERT (I meant a trigger after an insert.  In this case I have access to the new value but not the "old" values in the record.) If UPDATE would insert the record if it didn't exist then I could use the

Re: [sqlite] UPSERT again

On 12 Jan 2012, at 11:20pm, Tony Caras wrote: > The problem is, that if I create AFTER INSERT then I don't know what the > original state of the blocked field was so I don't whether to increment or > decrement or leave the counter alone. I don't understand what you mean by 'UPSERT' or

[sqlite] UPSERT again

I know there have been discussions in the past about workarounds for UPSERT but I recently encountered another one and I haven't figure out a good workaround.  Any assistance would be greatly appreciated. I have a situation where I need to increment a counter in another table every time a

Re: [sqlite] suppress "database is locked"?

On Thu, Jan 12, 2012 at 4:52 PM, Hajo Locke wrote: > or just run ".load /usr/lib/sqlite3/pcre.so" with the do method of dbi? > The ".load" command (as with ALL of the dot-anything commands) are specific to the shell, and are not accessible via other clients. i think what you

Re: [sqlite] suppress "database is locked"?

On 12 Jan 2012, at 3:52pm, Hajo Locke wrote: > Hello, > >> shell command rather than writing a loop in perl. I'm happy your system >> mostly works so far, but I would still feel nervous using anything which >> depended on frequent use of shell execution in a server. Starting and >>

Re: [sqlite] suppress "database is locked"?

Hello, shell command rather than writing a loop in perl. I'm happy your system mostly works so far, but I would still feel nervous using anything which depended on frequent use of shell execution in a server. Starting and stopping shell sessions is extremely expensive in terms of processing

Re: [sqlite] Incompatible versions of SQLite on same system

Can't you just copy the DLL into the application directory? That just does what the app ought to do (if they don't already). Then you might have to turn off safe DLL mode to find the correct DLL unless you remove the system one.

Re: [sqlite] SQLite C - APIs to insert data in to a table.

On 12 January 2012 12:22, bhaskarReddy wrote: > > Hi Friends, > > I am using SQLite. This is the first time. And i am creating, > accessing and retrieving data from the table. > > I am using sqlite3_exec() function to insert and retrieve data. > Instead using

Re: [sqlite] Named parameters and spaces.

The named parameters need not match the column names, and the column names are probably what you want to match the CSV file. As such, you could make P1 throught P25 to be the named parameters for your 25 columns, and insert them into columns with the real name. The named parameters are there

Re: [sqlite] suppress "database is locked"?

On 12 Jan 2012, at 12:32pm, Hajo Locke wrote: > Hello, > >> Using the shell tool is itself a very 'expensive' way to manipulate a SQLite >> database. If you're concerned about concurrent users, write proper software >> directly in perl to do it, rather than using perl to create a shell and

Re: [sqlite] suppress "database is locked"?

Hello, Using the shell tool is itself a very 'expensive' way to manipulate a SQLite database. If you're concerned about concurrent users, write proper software directly in perl to do it, rather than using perl to create a shell and execute the shell tool in it.

Re: [sqlite] suppress "database is locked"?

On 12 Jan 2012, at 12:19pm, Hajo Locke wrote: >> On 12 Jan 2012, at 11:16am, Hajo Locke wrote: >> >>> but my problem is now how to do this with sql-statements using multiple >>> lines with EOT >> >> Make a file with the commands in, and pipe that file to the command-line >> tool instead of

Re: [sqlite] suppress "database is locked"?

On 12 Jan 2012, at 11:16am, Hajo Locke wrote: but my problem is now how to do this with sql-statements using multiple lines with EOT Make a file with the commands in, and pipe that file to the command-line tool instead of trying to put the commands straight into it. this works but is

Re: [sqlite] SQLite C - APIs to insert data in to a table.

On 12 Jan 2012, at 11:22am, bhaskarReddy wrote: > I am using SQLite. This is the first time. And i am creating, > accessing and retrieving data from the table. > > I am using sqlite3_exec() function to insert and retrieve data. > Instead using sqlite3_exec(), is there any

Re: [sqlite] suppress "database is locked"?

On 12 Jan 2012, at 11:16am, Hajo Locke wrote: > but my problem is now how to do this with sql-statements using multiple lines > with EOT Make a file with the commands in, and pipe that file to the command-line tool instead of trying to put the commands straight into it. Simon.

[sqlite] SQLite C - APIs to insert data in to a table.

Hi Friends, I am using SQLite. This is the first time. And i am creating, accessing and retrieving data from the table. I am using sqlite3_exec() function to insert and retrieve data. Instead using sqlite3_exec(), is there any other APIs which help inserting data into

Re: [sqlite] suppress "database is locked"?

Hello, You can redirect stderr to /dev/null and you won't see any error messages then. Also you can redirect it to some file and analyze it later for some kinds of errors you want to process. i did it now this way: $command="/usr/bin/sqlite3 mydb.db \"select foo from bar;\" 2>&1";

Re: [sqlite] primary key autoincrement updation problemincaserefereced

Fixed it. It allows duplicates when stmt reset(sqlite3_reset) missed after sqlite3_step(..) in transaction. Thank you. On Thu, Jan 12, 2012 at 4:27 PM, Simon Slavin wrote: > > On 12 Jan 2012, at 10:52am, Durga D wrote: > > > I have attached sample db. I tried to

Re: [sqlite] table names on the fly

Alternatively in tcl : dbcmd eval { create table mytable ( aaa integer, text) } dbcmd eval { insert into mytable (aaa,bbb) values (1, '') } dbcmd eval "select * from mytable" loopvar { # loopvar(*) contains the column names, loopvar(aaa) contains 1, loopvar(bbb) contains

Re: [sqlite] primary key autoincrement updation problemincaserefereced

On 12 Jan 2012, at 10:52am, Durga D wrote: > I have attached sample db. I tried to simulate with test application. > But, I could not. > >Please find the attached db, which has only one table. You can't attach things to messages to this list, because we don't want the list flooded with

Re: [sqlite] primary key autoincrement updation problemincaserefereced

On 12 Jan 2012, at 10:14am, Durga D wrote: > create table if not exists t0 ( > h integer primary key autoincrement, > t0info text, unique(t0info)); > > t0 table allows duplicate t0info column, when in transaction. > > how to avoid duplicates for t0info column. If your table was

Re: [sqlite] primary key autoincrement updation problemincaserefereced

Thank you. create table if not exists t0 ( h integer primary key autoincrement, t0info text, unique(t0info)); t0 table allows duplicate t0info column, when in transaction. how to avoid duplicates for t0info column. Thanks in advance. Durga. On Thu, Jan 12, 2012 at 3:37 PM,

Re: [sqlite] primary key autoincrement updation problemincaserefereced

On 12 Jan 2012, at 6:05am, Durga D wrote: >Insertion time, I am able to get the rowid from > sqlite3_last_insert_rowid(); > > It's working fine. > > Now, t0info record already exists, that time , I should not update > with latest rowid. I should fetch the corresponding h

Re: [sqlite] Incompatible versions of SQLite on same system

On 12 Jan 2012, at 6:30am, Joe Winograd wrote: > Thanks to both of you for your responses. I'm back to wondering how SQLite > can be effective in the PC world with so many different programs using many > different versions of SQLite. Since all versions are backward compatible, I > was liking

[sqlite] formatting sqlite_master differently

Hi, no question here, just wanted to share a little discovery :) Since sqlite_master is a general table, one can format and order things differently. I used to use simple Select * from sqlite_master ... for querying everyting related to structure, but the following query can show the things