[sqlite] perl parameter binding by name

I'm trying to figure out how to bind parameters by name (:VVV or @VVV or $VVV ) using the DBI Perl module. I can't find any examples. All the bind_param methods look like they want a param num ($p_num). Maybe some %attr's need to be set? I want to be able to use SQL like this: $sql = qq/INSE

Re: [sqlite] double_quote(text_field)

Yes, that should work. Thanks!! Dave Watkinson wrote, On 2/22/2012 5:33 PM: Did you mean something like SELECT '"'||text_field||'"' FROM table; so that your column's data is quoted? ~~~ Dave Watkinson On Wed, Feb 22, 2012 at 6:26 PM, Igor Tandetnik

[sqlite] double_quote(text_field)

What is the easiest way to return a text field with double (") quotes? SELECT quote(text_field) FROM table; is close, but I'd rather have a SELECT double_quote(text_field) FROM table; Thanks!! ___ sqlite-users mailing list sqlite-users@sqlite.org htt

[sqlite] custom property db/editor

I need to develop a light-weight, custom property editor using SQLite, Perl, thttpd and HTML/CSS for building and editing recipes. I'm thinking of something like the classic "folder/tree/property sheet" interface and it will (obviously) run in a web browser I suppose I could use XML as well, b

Re: [sqlite] ALTER TABLE

Nico Williams wrote, On 2/6/2012 12:44 PM: On Mon, Feb 6, 2012 at 9:36 AM, Bill McCormick wrote: Is there no way to force columns added to a table with alter table to be added at certain column positions? Alternatively, if there is some way to save the data in an existing table; drop the

Re: [sqlite] ALTER TABLE

Gerry Snyder wrote, On 2/6/2012 9:48 AM: On 2/6/2012 8:36 AM, Bill McCormick wrote: Is there no way to force columns added to a table with alter table to be added at certain column positions? Alternatively, if there is some way to save the data in an existing table; drop the table; re-create

[sqlite] ALTER TABLE

Is there no way to force columns added to a table with alter table to be added at certain column positions? Alternatively, if there is some way to save the data in an existing table; drop the table; re-create the table with the desired schema; and then reload the data, this would be useful as

[sqlite] DBI->connect doesn't fail if no DB

In the following statement ... my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","") or croak $DBI::errstr; if $dbfile does not exist, it is created. I would like to croak if this happens. Is there some option for this? Thanks!! ___ sql

Re: [sqlite] FIRST/LAST function

Igor Tandetnik wrote, On 2/2/2012 10:50 AM: Petite Abeille wrote: On Feb 2, 2012, at 5:26 PM, Igor Tandetnik wrote: ORDER BY applies to groups, not to rows within each group (is this different with Oracle?) analytic functions works in term of the result set itself. Here is a simple example

Re: [sqlite] FIRST/LAST function

Mr. Puneet Kishor wrote, On 2/2/2012 9:57 AM: On Feb 2, 2012, at 9:46 AM, Petite Abeille wrote: On Feb 2, 2012, at 4:37 PM, Bill McCormick wrote: Does SQLite have FIRST and LAST aggregate function? No, sadly, SQLite doesn't support any analytic functions (aka window function) su

[sqlite] FIRST/LAST function

Does SQLite have FIRST and LAST aggregate function? I don't see it in the docs, but I thought I might ask anyway. Or maybe someone out there knows a slick way to do this: Anyway, it would be sort of like MIN & MAX, but would return the first and last values of the requested field over a grou

[sqlite] Perl script to output to file

From the sqlite command line interface, the .output command will output the result of a query to a file. How can I do this from a Perl script? I'm not finding any built-in facility to do this in the Perl module. I found this (http://members.toast.net/strycher/perl/example_dbi_sql.htm), but I

Re: [sqlite] PRAGMA journal_mode=WAL;

Larry Brasfield wrote, On 1/31/2012 11:36 AM: Bill McCormick wrote: > < PRAGMA journal_mode=WAL; ... So when I run an update command (to update some arbitrary record) from the sqlite3 command line, do I need to do something to do a checkpoint? By "checkpoint", I presu

Re: [sqlite] PRAGMA journal_mode=WAL;

Larry Brasfield wrote, On 1/31/2012 8:40 AM: < Either I have some incorrect version or don't understand how to use this: < < #include < < int main(int argc, char** argv) < { < sqlite3 *db; < < PRAGMA journal_mode=WAL; < < ... < < gcc [various errors] The PRAGMA is something you pas

Re: [sqlite] PRAGMA journal_mode=WAL;

Simon Slavin wrote, On 1/31/2012 8:34 AM: On 31 Jan 2012, at 2:32pm, Bill McCormick wrote: Where exactly to I put the PRAGMA journal_mode=WAL; line? You execute it as a SQL command, the same way you'd execute an INSERT command. You only need to do it once for the database file. The se

[sqlite] PRAGMA journal_mode=WAL;

Where exactly to I put the PRAGMA journal_mode=WAL; line? Either I have some incorrect version or don't understand how to use this: #include int main(int argc, char** argv) { sqlite3 *db; PRAGMA journal_mode=WAL; ... gcc -g -Wall -lrt -I.. -I../../include -I../ini-parse -o prodd pr

Re: [sqlite] date function

Bill McCormick wrote, On 1/27/2012 12:12 PM: I have a date stored in a numeric field like this: 2012-01-27 16:47:50. I want to update the date using the date function, like this: update table set TIME_STAMP = date('TIME_STAMP', '+ 2 years'); Reference: http://www.sqlite.or

[sqlite] date function

I have a date stored in a numeric field like this: 2012-01-27 16:47:50. I want to update the date using the date function, like this: update table set TIME_STAMP = date('TIME_STAMP', '+ 2 years'); Reference: http://www.sqlite.org/lang_datefunc.html I also tried this: update table set TIME_STAM

Re: [sqlite] SQLite and Perl

Darren Duncan wrote, On 1/27/2012 1:41 AM: Puneet Kishor wrote: On Jan 26, 2012, at 2:53 PM, Bill McCormick wrote: Puneet Kishor wrote, On 1/26/2012 12:02 PM: $ cpanm DBD::SQLite there is no step two Actually, that's not correct. In my case it took the following: Step 1: cpan DBI S

Re: [sqlite] SQLite and Perl

Puneet Kishor wrote, On 1/26/2012 3:01 PM: On Jan 26, 2012, at 2:53 PM, Bill McCormick wrote: Puneet Kishor wrote, On 1/26/2012 12:02 PM: On Jan 26, 2012, at 11:59 AM, Bill McCormick wrote: I need to get going with Perl& SQLite and I'm wondering what I need to get off CPAN, if

Re: [sqlite] SQLite and Perl

Puneet Kishor wrote, On 1/26/2012 12:02 PM: On Jan 26, 2012, at 11:59 AM, Bill McCormick wrote: I need to get going with Perl& SQLite and I'm wondering what I need to get off CPAN, if anything. $ cpanm DBD::SQLite there is no step two Actually, that's not correct. In m

[sqlite] SQLite and Perl

I need to get going with Perl & SQLite and I'm wondering what I need to get off CPAN, if anything. Here's what I think I have now: root@fiberio:/etc/cron.daily# locate perl | grep SQLite /usr/share/perl/5.10.1/CPANPLUS/Internals/Source/SQLite /usr/share/perl/5.10.1/CPANPLUS/Internals/Source/SQL

Re: [sqlite] sqlite3_bind_text destructor

Stephan Beal wrote, On 1/25/2012 11:36 AM: On Wed, Jan 25, 2012 at 6:33 PM, Bill McCormickwrote: http://www.sqlite.org/**capi3ref.html#sqlite3_bind_**blob Ha! That's the full description that left me with my questions in the first place!!

Re: [sqlite] sqlite3_bind_text destructor

Stephan Beal wrote, On 1/25/2012 11:01 AM: On Wed, Jan 25, 2012 at 5:45 PM, Bill McCormickwrote: 2. What will be void* argument be to the destructor? 3. What void* should the destructor return? int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); It doesn't return

[sqlite] sqlite3_bind_text destructor

The documentation I see for the sqlite3_bind_text 5th argument (a destructor used to dispose of the BLOB or string after SQLite has finished with it) isn't clear: 1. I assume the "string" that we're talking about here is the 3rd argument (const char*) 2. What will be void* argument be to the

Re: [sqlite] Bin parameters by name - generic function

me given a unix timestamp 1092941466. SELECT datetime(1092941466, 'unixepoch'); --David Garfield On Tue, Jan 17, 2012 at 19:32, Bill McCormick wrote: I'm trying to write a function with a sig like this: int BindParameter(sqlite3_stmt* stmt, int sqlType, const char* pname, c

[sqlite] Bin parameters by name - generic function

I'm trying to write a function with a sig like this: int BindParameter(sqlite3_stmt* stmt, int sqlType, const char* pname, char* value); Somewhere before the call(s) to BindParameter, I'll have a string that looks like this: (FOO=some text value, BAR=3.141, ZIP=45, TIMESTAMP1=01/17/12 17

[sqlite] sqlite3_close() necessary?

What happens if my application fails to call sqlite3_close() before it exits? Does it leave the db in some unknown state or open to corruption? If I have a daemon type program, will I need to catch signals that try to close/kill the program and call sqlite3_close() before exiting? Thanks!! __

Re: [sqlite] Some pieces of the puzzle are coming together

Pavel Ivanov wrote, On 1/17/2012 9:42 AM: I attempted all of the above and the call stacks do not change. That leads me to one of two conclusions: 1. The call stacks are inaccurate 2. There is some side effect which is causing malloc to be called inside of a procedure which should not be call

Re: [sqlite] sql/tcl script

Simon Slavin wrote, On 1/16/2012 1:45 PM: On 16 Jan 2012, at 7:34pm, Bill McCormick wrote: Looks like pipes work as well (cat yoursql.sql|sqlite3 yourdb.db3). Also, can I safely delete the database file (yourdb.db3, using your example) if I just want to start again? It appears that I can

Re: [sqlite] sql/tcl script

Oliver Peters wrote, On 1/16/2012 1:02 PM: Am 16.01.2012 19:59, schrieb Bill McCormick: James Pearson wrote, On 1/16/2012 12:28 PM: If it's just for setting up databases, tables, etc, why not just use a sql script? Throw all your database object creation sql into a file and then execute

Re: [sqlite] sql/tcl script

Stephan Beal wrote, On 1/16/2012 12:15 PM: On Mon, Jan 16, 2012 at 7:12 PM, Bill McCormickwrote: Is tcl the only scripting interface for SQLite? I'm just wondering what the options are. There are few scripting languages which don't have an sqlite3 binding. Just to name a few: - Perl - PHP -

Re: [sqlite] sql/tcl script

James Pearson wrote, On 1/16/2012 12:28 PM: If it's just for setting up databases, tables, etc, why not just use a sql script? Throw all your database object creation sql into a file and then execute that file from within sqlite using the ".read FILE" command. I think this is what I'm lookin

[sqlite] sql/tcl script

Is tcl the only scripting interface for SQLite? I'm just wondering what the options are. I'm trying to write a script to setup my database on new systems (create the db, add tables, etc.), but I don't have much experience using Tcl. My fist attempt is not going so well and I'm not finding hel

Re: [sqlite] makefile for c

Tim Streater wrote, On 1/15/2012 3:00 PM: On 15 Jan 2012 at 20:44, Bill McCormick wrote: What is the problem with the shared lib stuff? Thanks!! Black, Michael (IS) wrote, On 1/15/2012 2:27 PM: A simple one -- and please compile sqlite3.c into your program and make everybody happy. Forget

Re: [sqlite] makefile for c

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Bill McCormick [wpmccorm...@gmail.com] Sent: Sunday, January 15, 2012 2:23 PM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] makefile for c I'm looking for an example c program makefile for compi

[sqlite] makefile for c

I'm looking for an example c program makefile for compiling and linking in the SQLite lib to gcc compiled programs. I'm not sure which lib to include between libsqlite.so.0 and libsqlite3.so.0 and what options I should pass to gcc. Where is this documented? Thanks!! _