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

[bhaskarReddy]

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
function, the particular function will enter the record of values into that
table.

   Is there any C API like that in SQlite.


Regards,
Bhaskar
-- 
View this message in context: 
http://old.nabble.com/Need-a-sqlite-c-api-that-wrires-data-into-a-table.-tp33132538p33132538.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] simple like query optimization

[Durga D]

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/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%';
>
> processing on string operations on result set to get d, e, f, g and b.
>
> any alternative solution for this?
>
> Thanks in advance,
>  Durga.
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] simple like query optimization

[Durga D]

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%';

processing on string operations on result set to get d, e, f, g and b.

any alternative solution for this?

Thanks in advance,
Durga.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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. 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.

Bottom posting is the proper way to do it.  I don't really mind top or bottom 
post.  But the important thing is to trim anything you've quoted to just the 
useful bits.  We don't need to see every single post to the thread every time 
someone adds a new post.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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 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
>
>
>  Original Message 
> Subject: Re: [sqlite] Incompatible versions of SQLite on same system
> From: Simon Slavin 
> To: General Discussion of SQLite Database 
> Date: Thursday, January 12, 2012 03:59:02
>
>> 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 Richard's suggestion to get the
>>> latest-and-greatest DLL everywhere, but the DLLs for the two conflicting
>>> programs aren't even present.
>>>
>>> Joe, I assume your suggestion to "remove all System.Data.SQLite
>>> assemblies from the GAC and 'convert' them to be application-local" is
>>> directed at the software developers (like HP and Intuit), not at end-users
>>> (like me
>>>
>> Just to be clear, so is Richard's real suggestion, which is the
>> programmers should statically link to a SQLite library, or to include
>> SQLite source code in their applications and not use a library at all.
>>  SQLite is (deliberately designed to be) tiny.  Including it all in every
>> application which used it wouldn't use much disk space and would mean
>> problems like the one you reported would never happen: you could have ten
>> apps all expecting different versions of SQLite, and they could all run at
>> the same time with no installation or path problems.
>>
>> Unfortunately, as you noted, this is a decision which can be made only by
>> programmers, not users like you.
>>
>> Oh, and in case you didn't know, Doctor Richard Hipp is SQLite's creator.
>>  His advice about it is pretty good.
>>
>> Simon.
>>
>>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Joe Winograd]

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


 Original Message 
Subject: Re: [sqlite] Incompatible versions of SQLite on same system
From: Simon Slavin 
To: General Discussion of SQLite Database 
Date: Thursday, January 12, 2012 03:59:02

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 Richard's suggestion to get the latest-and-greatest DLL everywhere, but 
the DLLs for the two conflicting programs aren't even present.

Joe, I assume your suggestion to "remove all System.Data.SQLite assemblies from the 
GAC and 'convert' them to be application-local" is directed at the software 
developers (like HP and Intuit), not at end-users (like me

Just to be clear, so is Richard's real suggestion, which is the programmers 
should statically link to a SQLite library, or to include SQLite source code in 
their applications and not use a library at all.  SQLite is (deliberately 
designed to be) tiny.  Including it all in every application which used it 
wouldn't use much disk space and would mean problems like the one you reported 
would never happen: you could have ten apps all expecting different versions of 
SQLite, and they could all run at the same time with no installation or path 
problems.

Unfortunately, as you noted, this is a decision which can be made only by 
programmers, not users like you.

Oh, and in case you didn't know, Doctor Richard Hipp is SQLite's creator.  His 
advice about it is pretty good.

Simon.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT again

[Black, Michael (IS)]

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


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Tony Caras [tca...@yahoo.com]
Sent: Thursday, January 12, 2012 6:48 PM
To: General Discussion of SQLite Database
Subject: EXT :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 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 didn't exist then I could use the 
> trigger you have suggested.
>
>

two triggers?  one AFTER INSERT, one AFTER UPDATE.

jr.
___
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] UPSERT again

[Simon Slavin]

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 the replacement record, then the counter will get incremented twice 
> (once for the original insert and again for the insert associated with the 
> replace).

INSERT OR REPLACE does correctly trigger the DELETE triggers if it deletes a 
row.  If you implement triggers on INSERT, DELETE and UPDATE everything will 
work correctly.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT again

[Black, Michael (IS)]

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 Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Tony Caras [tca...@yahoo.com]
Sent: Thursday, January 12, 2012 6:48 PM
To: General Discussion of SQLite Database
Subject: EXT :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 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 didn't exist then I could use the 
> trigger you have suggested.
>
>

two triggers?  one AFTER INSERT, one AFTER UPDATE.

jr.
___
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] UPSERT again

[Tony Caras]

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 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 didn't exist then I could use the 
> trigger you have suggested.
>
>

two triggers?  one AFTER INSERT, one AFTER UPDATE.

jr.
___
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] UPSERT again

[jr]

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 didn't exist then I could use the 
trigger you have suggested.




two triggers?  one AFTER INSERT, one AFTER UPDATE.

jr.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT again

[Tony Caras]

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 incremented twice (once for 
the original insert and again for the insert associated with the replace).




 From: "Black, Michael (IS)" 
To: Tony Caras ; General Discussion of SQLite Database 
 
Sent: Thursday, January 12, 2012 5:37 PM
Subject: 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 [sqlite-users-boun...@sqlite.org] on 
behalf of Tony Caras [tca...@yahoo.com]
Sent: Thursday, January 12, 2012 6:17 PM
To: General Discussion of SQLite Database
Subject: EXT :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 
trigger you have suggested.





 From: Simon Slavin 
To: Tony Caras ; General Discussion of SQLite Database 
 
Sent: Thursday, January 12, 2012 4:40 PM
Subject: 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 'create AFTER INSERT'.  If 
you're doing an INSERT then the field doesn't have an original state: it's a 
new row.  If you're doing an UPDATE a TRIGGER does indeed have access to the 
old value of a field changed
 by UPDATE:

http://www.sqlite.org/lang_createtrigger.html

So you can do

CREATE TRIGGER ... AFTER UPDATE ...
BEGIN
    UPDATE ... SET totalBlocked = totalBlocked - 1 WHERE old.blocked;
    UPDATE ... SET totalBlocked = totalBlocked + 1 WHERE new.blocked;
END;

Simon.
___
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] UPSERT again

[Black, Michael (IS)]

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 [sqlite-users-boun...@sqlite.org] on 
behalf of Tony Caras [tca...@yahoo.com]
Sent: Thursday, January 12, 2012 6:17 PM
To: General Discussion of SQLite Database
Subject: EXT :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 
trigger you have suggested.





 From: Simon Slavin 
To: Tony Caras ; General Discussion of SQLite Database 

Sent: Thursday, January 12, 2012 4:40 PM
Subject: 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 'create AFTER INSERT'.  If 
you're doing an INSERT then the field doesn't have an original state: it's a 
new row.  If you're doing an UPDATE a TRIGGER does indeed have access to the 
old value of a field changed by UPDATE:

http://www.sqlite.org/lang_createtrigger.html

So you can do

CREATE TRIGGER ... AFTER UPDATE ...
BEGIN
UPDATE ... SET totalBlocked = totalBlocked - 1 WHERE old.blocked;
UPDATE ... SET totalBlocked = totalBlocked + 1 WHERE new.blocked;
END;

Simon.
___
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] UPSERT again

[Tony Caras]

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 
trigger you have suggested.





 From: Simon Slavin 
To: Tony Caras ; General Discussion of SQLite Database 
 
Sent: Thursday, January 12, 2012 4:40 PM
Subject: 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 'create AFTER INSERT'.  If 
you're doing an INSERT then the field doesn't have an original state: it's a 
new row.  If you're doing an UPDATE a TRIGGER does indeed have access to the 
old value of a field changed by UPDATE:

http://www.sqlite.org/lang_createtrigger.html

So you can do

CREATE TRIGGER ... AFTER UPDATE ...
BEGIN
    UPDATE ... SET totalBlocked = totalBlocked - 1 WHERE old.blocked;
    UPDATE ... SET totalBlocked = totalBlocked + 1 WHERE new.blocked;
END;

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT again

[Simon Slavin]

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 'create AFTER INSERT'.  If 
you're doing an INSERT then the field doesn't have an original state: it's a 
new row.  If you're doing an UPDATE a TRIGGER does indeed have access to the 
old value of a field changed by UPDATE:

http://www.sqlite.org/lang_createtrigger.html

So you can do

CREATE TRIGGER ... AFTER UPDATE ...
BEGIN
UPDATE ... SET totalBlocked = totalBlocked - 1 WHERE old.blocked;
UPDATE ... SET totalBlocked = totalBlocked + 1 WHERE new.blocked;
END;

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPSERT again

[Tony Caras]

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 record is added or specific field is modified to a certain value.  For 
example same my record is message and it has a field called blocked.  The 
record can be inserted with blocked field set to true or false.  The fields in 
the  record may get changed/updated many times but if the blocked field gets 
changed to true I need to increment a counter (conversely if it goes from true 
to false i need to decrement the counter).

I can create a trigger that will increment and decrement the counter but the 
record changes are currently done doing INSERT OR REPLACE so record will get 
added if it doesn't exist.  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.  If I just 
increment when blocked=true then multiple inserts with the same value will 
cause my counter to increment multiple times for the same record. This isn't 
what I need.


Does anyone have any suggestions?  I tried some of the UPSERT type of 
workarounds but so far they haven't worked for me.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Stephan Beal]

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
want is load_extension:

http://sqlite.org/lang_corefunc.html

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 
>> stopping shell sessions is extremely expensive in terms of processing time 
>> and memory.  I recommend you look into either writing proper perl code, or 
>> making use of a language which can use the proper SQLite API.
> 
> i will think about it.
> is there somewhere a dbi example for loading pcre-extension before executing 
> sql?
> i dont find any helpful pages.
> or just run ".load /usr/lib/sqlite3/pcre.so" with the do method of dbi?

The dot commands (like '.load') are interpreted by the command-line tool.  
They're not understood by the SQLite library.  So whatever API you use needs to 
have its own command for loading SQLite extensions.  You might take a look at 
the perl DBI library and see if it has a way to load extensions.

On the other hand, it looks like all that extension does is implement some 
regex stuff, and you might be able to do what you want to do with SQLite's 
"LIKE" operator.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Hajo Locke]

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 time 
and memory.  I recommend you look into either writing proper perl code, or 
making use of a language which can use the proper SQLite API.


i will think about it.
is there somewhere a dbi example for loading pcre-extension before executing 
sql?

i dont find any helpful pages.
or just run ".load /usr/lib/sqlite3/pcre.so" with the do method of dbi?

Thanks,
Hajo

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Black, Michael (IS)]

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.

http://msdn.microsoft.com/en-us/library/windows/desktop/ms682586(v=vs.85).aspx#standard_search_order_for_desktop_applications





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Thursday, January 12, 2012 3:59 AM
To: General Discussion of SQLite Database
Subject: EXT :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 Richard's suggestion to get the latest-and-greatest DLL 
> everywhere, but the DLLs for the two conflicting programs aren't even present.
>
> Joe, I assume your suggestion to "remove all System.Data.SQLite assemblies 
> from the GAC and 'convert' them to be application-local" is directed at the 
> software developers (like HP and Intuit), not at end-users (like me

Just to be clear, so is Richard's real suggestion, which is the programmers 
should statically link to a SQLite library, or to include SQLite source code in 
their applications and not use a library at all.  SQLite is (deliberately 
designed to be) tiny.  Including it all in every application which used it 
wouldn't use much disk space and would mean problems like the one you reported 
would never happen: you could have ten apps all expecting different versions of 
SQLite, and they could all run at the same time with no installation or path 
problems.

Unfortunately, as you noted, this is a decision which can be made only by 
programmers, not users like you.

Oh, and in case you didn't know, Doctor Richard Hipp is SQLite's creator.  His 
advice about it is pretty good.

Simon.
___
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] SQLite C - APIs to insert data in to a table.

[Vivien Malerba]

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 sqlite3_exec(), is there any other APIs which help inserting
> data into tables.
>
>
>
You can also have a look at the Libgda library, see http://www.gnome-db.org

Vivien

PS: to be completely honest I have to tell that I'm Libgda's maintainer
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Named parameters and spaces.

[David Garfield]

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 only
so that the SQL command and the API user can be matched up, particularly
when one or more value need to be reused.

--David Garfield

On Wed, Jan 11, 2012 at 19:01, Steven Michalske wrote:

> On Tue, Jan 10, 2012 at 4:11 PM, Simon Slavin 
> wrote:
> >
> > On 10 Jan 2012, at 11:57pm, Steven Michalske wrote:
> >
> >> Some day they might have a field that is named with the underscore as
> >> a second column; not saying it makes sense.  We find all kinds on the
> >> internet :-)
> >
> > Well some day they might have a field that is named with a space, too.
>  There's nothing in SQLite to forbid it.  The real solution is to use named
> parameters the way they were designed, not try to match the name of the
> parameter with the name of the field.
>
> This statements is not quite true.
> I want to auto generate the fields based on csv files that may or may
> not have spaces in them.
> This makes the named parameter match the column names in the CSV file.
>  I used the python csv DictReader object that creates a key value
> mapping for column name and value.  i wanted to not have to manipulate
> the field names and such.
> ___
> 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] suppress "database is locked"?

[Simon Slavin]

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 
>> execute the shell tool in it.
>> 
>> http://mailliststock.wordpress.com/2007/03/01/sqlite-examples-with-bash-perl-and-python/
> 
> hmm, this is true
> in this situation i cannot use any dbi or something.
> just basic-perl and sqlite binary...

You seem to have a situation where you have many concurrent users, each doing 
SELECT and none updating the database file.  You report you're getting this 
error

> "Error: database is locked"

You can use the

.timeout

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 time and memory.  I 
recommend you look into either writing proper perl code, or making use of a 
language which can use the proper SQLite API.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Hajo Locke]

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.


http://mailliststock.wordpress.com/2007/03/01/sqlite-examples-with-bash-perl-and-python/


hmm, this is true
in this situation i cannot use any dbi or something.
just basic-perl and sqlite binary...

Thanks,
Hajo 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 trying to put the commands straight into it.
> 
> this works but is there no other way?
> i use regexp-statement 4 times in script. it is possible that this script is 
> running by a lot of users at the same time.
> every time creating a temp-file,filling with data, deleting etc. seems to me 
> to be very expensive and i want to avoid this.

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.

http://mailliststock.wordpress.com/2007/03/01/sqlite-examples-with-bash-perl-and-python/

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Hajo Locke]

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 there no other way?
i use regexp-statement 4 times in script. it is possible that this script is 
running by a lot of users at the same time.
every time creating a temp-file,filling with data, deleting etc. seems to me 
to be very expensive and i want to avoid this. :(


Thanks,
Hajo 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 other APIs which help inserting
> data into tables.

http://www.sqlite.org/quickstart.html

http://www.sqlite.org/cintro.html

http://www.sqlite.org/c3ref/funclist.html

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[bhaskarReddy]

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 tables.

 
Regards,
Bhaskar Reddy.
-- 
View this message in context: 
http://old.nabble.com/SQLite--C---APIs-to-insert-data-in-to-a-table.-tp33127130p33127130.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] suppress "database is locked"?

[Hajo Locke]

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";
$result=`$command`;

so i can count return-code and have error-message in $result

but my problem is now how to do this with sql-statements using multiple 
lines with EOT


$command="/usr/bin/sqlite3 mydb.db 

Re: [sqlite] primary key autoincrement updation problemincaserefereced

[Durga D]

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 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 everyone attaching their problem files rather than
> doing their own investigations.  However, you could try something like
>
> SELECT * FROM sqlite_master WHERE name='t0'
>
> and see which CREATE command you get back.
>
> Simon.
>
> ___
> 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] table names on the fly

[John Gillespie]

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 ""
}

On 11 January 2012 20:57, Igor Tandetnik  wrote:

> On 1/11/2012 3:53 PM, inq1ltd wrote:
>
>> Can someone tell me how to get the column names
>> contained in a table on the fly.
>>
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] primary key autoincrement updation problemincaserefereced

[Simon Slavin]

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 everyone attaching their problem files rather than doing 
their own investigations.  However, you could try something like

SELECT * FROM sqlite_master WHERE name='t0'

and see which CREATE command you get back.

Simon.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] primary key autoincrement updation problemincaserefereced

[Simon Slavin]

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 created with that command, then it should not allow duplicate 
t0info columns.  Did you perhaps create it first without the 'unique' 
requirement, and now your 'CREATE TABLE IF NOT EXISTS' command is doing nothing 
?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] primary key autoincrement updation problemincaserefereced

[Durga D]

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, Simon Slavin  wrote:

>
> 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 value, update in t1
> > table. Here, fetching is the problem:
>
> Yes, this is a problem.  If the way your program works may cause it to try
> to insert a t0 row which already exists, you do need at least two SQLite
> commands.  The two common structures are ...
>
> create table if not exists t0 (
>h integer primary key autoincrement,
>t0info text);
>
> First option:
>
> INSERT OR IGNORE INTO t0 ...
> SELECT h FROM t0 WHERE ...
>
> Second option:
>
> SELECT h FROM t0 WHERE ...
>
> Then see whether you got 0 or 1 row back.  If you got 1 for back, the row
> already exists and you can use that 'h' value.  If not, you do the INSERT,
> then use sqlite3_last_insert_rowid().
>
>
>
>
> One option is good if your application structure makes 'IF' commands
> difficult.  The other is good if executing SQLite calls slows it down
> unacceptably.
>
> Simon.
> ___
> 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] primary key autoincrement updation problemincaserefereced

[Simon Slavin]

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 value, update in t1
> table. Here, fetching is the problem:

Yes, this is a problem.  If the way your program works may cause it to try to 
insert a t0 row which already exists, you do need at least two SQLite commands. 
 The two common structures are ...

create table if not exists t0 (
h integer primary key autoincrement,
t0info text);

First option:

INSERT OR IGNORE INTO t0 ...
SELECT h FROM t0 WHERE ...

Second option:

SELECT h FROM t0 WHERE ...

Then see whether you got 0 or 1 row back.  If you got 1 for back, the row 
already exists and you can use that 'h' value.  If not, you do the INSERT, then 
use sqlite3_last_insert_rowid().




One option is good if your application structure makes 'IF' commands difficult. 
 The other is good if executing SQLite calls slows it down unacceptably.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 Richard's suggestion to get the latest-and-greatest DLL 
> everywhere, but the DLLs for the two conflicting programs aren't even present.
> 
> Joe, I assume your suggestion to "remove all System.Data.SQLite assemblies 
> from the GAC and 'convert' them to be application-local" is directed at the 
> software developers (like HP and Intuit), not at end-users (like me

Just to be clear, so is Richard's real suggestion, which is the programmers 
should statically link to a SQLite library, or to include SQLite source code in 
their applications and not use a library at all.  SQLite is (deliberately 
designed to be) tiny.  Including it all in every application which used it 
wouldn't use much disk space and would mean problems like the one you reported 
would never happen: you could have ten apps all expecting different versions of 
SQLite, and they could all run at the same time with no installation or path 
problems.

Unfortunately, as you noted, this is a decision which can be made only by 
programmers, not users like you.

Oh, and in case you didn't know, Doctor Richard Hipp is SQLite's creator.  His 
advice about it is pretty good.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] formatting sqlite_master differently

[Max Vlasov]

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 more organized (indention for indexes and triggers)

SELECT type, (case when type='table' then name else '   '||name end) as
name, tbl_name, sql
FROM
  sqlite_master
ORDER BY
  tbl_name, case when type='table' then '0' else type end

Maybe others can share some other tricks with sqlite_master here

Thanks,

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users