Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Jens Alfke
> On Jul 21, 2017, at 1:01 PM, Keith Medcalf wrote: > > Just using a web browser has your machine executing god only knows what code > generated by god only knows who doing god only knows what to your computer. > Unless you have disabled that, of course. But that makes

Re: [sqlite] SQLite 3.20.0 postponed

2017-07-21 Thread petern
Nobody is storing pointers. Where do you get that idea? If struct pointer is passed through sqlite3_result_blob() with destructor callback disposition instead of SQLITE_TRANSIENT, the value received is a BLOB result pointed to the (void*) memory location of that exact supplied struct's memory.

Re: [sqlite] SQLite 3.20.0 postponed

2017-07-21 Thread Keith Medcalf
Pointers cannot be stored in the database. They are simply value objects that get passed around between bits and pieces -- such as passing a pointer from an SQL statement into an extension function or virtual table all within the same linkage unit (that is locally within a single process).

Re: [sqlite] Database version

2017-07-21 Thread Keith Medcalf
On Friday, 21 July, 2017 14:24, Igor Korot wrote: >I presume the same is true for the DB created with 3.0.0 and opened >with 3.20.0. >As long as I will use the standard SQL command and don't use any new >feature introduced in 3.20.0. >The only trouble is - I may not know in

Re: [sqlite] SQLite 3.20.0 postponed

2017-07-21 Thread Richard Hipp
On 7/21/17, petern wrote: > > How was adding to the BLOB API ruled out? It uses extra memory and CPU cycles, even on the vast majority of applications that do not use it. -- D. Richard Hipp d...@sqlite.org ___

Re: [sqlite] SQLite 3.20.0 postponed

2017-07-21 Thread petern
Indeed. Or Type(T) of long, or sqlite_int64, or whatever is most universal where porting other architectures is concerned. It would be a shame if the API had to be changed later because the integer type chosen couldn't hold a pointer. From there, in a closed compiled host C/C++ setting with the

Re: [sqlite] Database version

2017-07-21 Thread Igor Korot
Hi, Keith, On Fri, Jul 21, 2017 at 4:17 PM, Keith Medcalf wrote: >>The only trouble is - how do I know what version were used. >>My application may use some features that is available right now and >>I want to use them because they are great. >>But if I open the DB from the

Re: [sqlite] Database version

2017-07-21 Thread Keith Medcalf
>The only trouble is - how do I know what version were used. >My application may use some features that is available right now and >I want to use them because they are great. >But if I open the DB from the old version I will simply get an error. No, you are incorrect. If you open a DB file that

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Keith Medcalf
On Friday, 21 July, 2017 11:37, Jens Alfke wrote: > But anyone writing software that runs in a web server, > or that otherwise interacts with untrusted data, has to > pay attention to basic security practices. > And a fundamental one is that you don’t run code that >

Re: [sqlite] SQLite 3.20.0 postponed

2017-07-21 Thread Keith Medcalf
On Friday, 21 July, 2017 10:51, Richard Hipp wrote: > But sqlite3_result_pointer() and sqlite3_bind_pointer() are not > invoked from those languages. The _pointer() routines are invoked > from C, and C does easily support string literals that are static > strings. > A

Re: [sqlite] Database version

2017-07-21 Thread Jens Alfke
> On Jul 21, 2017, at 11:17 AM, Igor Korot wrote: > > The only trouble is - how do I know what version were used. > My application may use some features that is available right now and > I want to use them because they are great. > But if I open the DB from the old version I

Re: [sqlite] Database version

2017-07-21 Thread Richard Hipp
On 7/21/17, Peter Da Silva wrote: > I assume BEGIN, COMMIT, and ROLLBACK are safe too :) Yes, of course. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Database version

2017-07-21 Thread Peter Da Silva
I assume BEGIN, COMMIT, and ROLLBACK are safe too :) On 7/21/17, 1:08 PM, "sqlite-users on behalf of Richard Hipp" wrote: On 7/21/17, Simon Slavin wrote: > > The new features

Re: [sqlite] Database version

2017-07-21 Thread Igor Korot
Richard, et al, On Fri, Jul 21, 2017 at 2:08 PM, Richard Hipp wrote: > On 7/21/17, Simon Slavin wrote: >> >> The new features introduced by SQLite since it started using file format 3 >> all require explicit commands to use. Adding columns to existing

Re: [sqlite] Database version

2017-07-21 Thread Richard Hipp
On 7/21/17, Simon Slavin wrote: > > The new features introduced by SQLite since it started using file format 3 > all require explicit commands to use. Adding columns to existing tables, > AUTOVACUUM mode, WAL journals, DESC indexes, all require you to execute a > specific

Re: [sqlite] Database version

2017-07-21 Thread Simon Slavin
On 21 Jul 2017, at 6:42pm, Warren Young wrote: > Now to complicate that, macOS also ships a /usr/bin/sqlite3, which happens to > be 3.16.0 in macOS 10.12, so my superficial question is, can I safely open > *and modify* the Lightroom catalog file with the macOS version of

Re: [sqlite] Database version

2017-07-21 Thread Warren Young
On Jul 21, 2017, at 10:11 AM, Jens Alfke wrote: > >> On Jul 21, 2017, at 8:25 AM, Igor Korot wrote: >> >> "Using SQLite library version X.Y.Z connecting to the DB version A.B.C" > > But why should your user care? As a developer I’ve been working with

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Jens Alfke
> On Jul 21, 2017, at 9:48 AM, John McKown wrote: > > ​And, just to interject a politically incorrect statement, any "programmer" > who does not use the safer interface is either __extremely__ ignorant, or > arrogantly stupid I wouldn’t put it that harshly, but I

Re: [sqlite] SQLite 3.20.0 postponed

2017-07-21 Thread petern
Yes. Good point. Before seeing Richard's last commits for pseudo-null pointer passing with static string type name, I proposed that the pseudo-null pointer API be extended with function signature forms which pass by value a user defined integer field for type information. After further

Re: [sqlite] Trigger firing order

2017-07-21 Thread Simon Slavin
On 21 Jul 2017, at 5:52pm, Simon Slavin wrote: > On 21 Jul 2017, at 4:27pm, Jean-Luc Hainaut > wrote: > >> Hence my modest proposal: wouldn't it be a nice idea to make this unofficial >> order a feature of SQLite (just like DB2)? This would

Re: [sqlite] Trigger firing order

2017-07-21 Thread Simon Slavin
On 21 Jul 2017, at 4:27pm, Jean-Luc Hainaut wrote: > Hence my modest proposal: wouldn't it be a nice idea to make this unofficial > order a feature of SQLite (just like DB2)? This would make the multiple > triggers of a kind much more useful as it currently are.

Re: [sqlite] SQLite 3.20.0 postponed

2017-07-21 Thread Richard Hipp
On 7/21/17, Gwendal Roué wrote: > > First, this strcmp() give a lot of work to languages that wrap SQLite and > lack support for "static strings". But sqlite3_result_pointer() and sqlite3_bind_pointer() are not invoked from those languages. The _pointer() routines are

Re: [sqlite] Database version

2017-07-21 Thread David Raymond
There's nothing in the file itself apart from the schema format number of 1-4. If you want to get a "version valid for" sort of thing you'd have to scan http://www.sqlite.org/changes.html for non-backwards compatible features, then scan the schema in sqlite_master for those. For example... -If

Re: [sqlite] Trigger firing order

2017-07-21 Thread petern
> a programming pattern that is missing in SQLite trigger body: "if (new.C1 <> old.C1) then ". It can be simulated for updates ("update ... where ... and new.C1 <> old.C1") but not for inserts ("insert" has no "where" clause). Maybe so. But, INSERT can accept data rows from a SELECT statement

Re: [sqlite] Database version

2017-07-21 Thread Jens Alfke
> On Jul 21, 2017, at 8:25 AM, Igor Korot wrote: > > In my "Help -> About..." I'd like to say something like: > > "Using SQLite library version X.Y.Z connecting to the DB version A.B.C" But why should your user care? As a developer I’ve been working with SQLite since 2004

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Jens Alfke
> On Jul 21, 2017, at 6:45 AM, Peter Da Silva > wrote: > > Have a look at prepared statements and statement parameters. Agreed. PLEASE, PLEASE, do not try to splice parameters into SQL strings! Any mistakes in this code leave you vulnerable to SQL Injection

Re: [sqlite] SQLite 3.20.0 postponed

2017-07-21 Thread Gwendal Roué
> Le 21 juil. 2017 à 17:55, Gwendal Roué a écrit : > > First, this strcmp() give a lot of work to languages that wrap SQLite and > lack support for "static strings". Building a global \0-terminated buffer > that never gets deallocated is not always that easy :-) For

Re: [sqlite] Database version

2017-07-21 Thread Igor Korot
On Fri, Jul 21, 2017 at 11:55 AM, Peter Da Silva wrote: > If SQLite3 can open the file at all, the first 16 characters will be "SQLite > format 3\000". Or "SQLite format 4\000" soon. Or "SQLite format 3\000" for an ancient db file. Thank you. > > On 7/21/17,

Re: [sqlite] SQLite 3.20.0 postponed

2017-07-21 Thread Gwendal Roué
> Le 17 juil. 2017 à 20:54, Richard Hipp a écrit : > > The 3.20.0 release will be delayed. Some concerns came up over the > new sqlite3_value_pointer() interface. Interface chagnes were made > over the weekend. But there are still concerns. So the decision has > been made

Re: [sqlite] Database version

2017-07-21 Thread Peter Da Silva
If SQLite3 can open the file at all, the first 16 characters will be "SQLite format 3\000". On 7/21/17, 10:46 AM, "sqlite-users on behalf of Igor Korot" wrote: Hi, Peter et al, On Fri, Jul 21, 2017 at

Re: [sqlite] Database version

2017-07-21 Thread Igor Korot
Hi, Peter et al, On Fri, Jul 21, 2017 at 11:35 AM, Peter Da Silva wrote: > The problem is that SQLITE_VERSION_NUMBER is not “the database version”, it’s > something like “the last version of SQLite that committed a transaction”. > > The database version number is

Re: [sqlite] Database version

2017-07-21 Thread Peter Da Silva
The problem is that SQLITE_VERSION_NUMBER is not “the database version”, it’s something like “the last version of SQLite that committed a transaction”. The database version number is “3”. ___ sqlite-users mailing list

Re: [sqlite] Database version

2017-07-21 Thread Igor Korot
Hi, Bob, On Fri, Jul 21, 2017 at 11:27 AM, Bob Friesenhahn wrote: > On Fri, 21 Jul 2017, Igor Korot wrote: >> >> >> In my "Help -> About..." I'd like to say something like: >> >> "Using SQLite library version X.Y.Z connecting to the DB version A.B.C" > > > Is this a

[sqlite] Trigger firing order

2017-07-21 Thread Jean-Luc Hainaut
Hello all, My question concerns the order (deterministic or not) in which triggers of the same kind (same table, same event, same position) fire. My application is a temporal database in which each table stores the history of one attribute of a set of entities. A view collects the last

Re: [sqlite] Database version

2017-07-21 Thread Bob Friesenhahn
On Fri, 21 Jul 2017, Igor Korot wrote: In my "Help -> About..." I'd like to say something like: "Using SQLite library version X.Y.Z connecting to the DB version A.B.C" Is this a sensible expectation? Several programs may access the database at once, and all could be using a different

Re: [sqlite] Database version

2017-07-21 Thread Igor Korot
Hi, Andy, On Fri, Jul 21, 2017 at 11:19 AM, Andy Ling wrote: >>Let's say I made some database files 2 years ago. >>Now I want the current SQLite code to open them and performs some queries >>from the C interface. > > I would ask why do you care? Sqlite will read old

Re: [sqlite] Database version

2017-07-21 Thread Peter Da Silva
On 7/21/17, 10:14 AM, "sqlite-users on behalf of Igor Korot" wrote: > This is also stored at offset 96 in the db file: > > https://www.sqlite.org/fileformat.html Is this the number I'm after?

Re: [sqlite] Database version

2017-07-21 Thread Andy Ling
>Let's say I made some database files 2 years ago. >Now I want the current SQLite code to open them and performs some queries >from the C interface. I would ask why do you care? Sqlite will read old databases without any problem. What you may be interested in is what schema version and that's up

Re: [sqlite] Database version

2017-07-21 Thread Simon Slavin
On 21 Jul 2017, at 4:14pm, Igor Korot wrote: > > Let's say I made some database files 2 years ago. > Now I want the current SQLite code to open them and performs some queries > from the C interface. SQLite file format hasn’t changed in over 10 years. Your current code and

Re: [sqlite] Database version

2017-07-21 Thread Igor Korot
Hi, Peter, On Fri, Jul 21, 2017 at 11:06 AM, Peter Da Silva wrote: > https://www.sqlite.org/c3ref/c_source_id.html Those are for the library. But... Let's say I made some database files 2 years ago. Now I want the current SQLite code to open them and performs

Re: [sqlite] Database version

2017-07-21 Thread Peter Da Silva
https://www.sqlite.org/c3ref/c_source_id.html This is also stored at offset 96 in the db file: https://www.sqlite.org/fileformat.html I don’t think there’s a pragma for extracting it from within SQLite code. On 7/21/17, 9:58 AM, "sqlite-users on behalf of Igor Korot"

[sqlite] Database version

2017-07-21 Thread Igor Korot
Hi, ALL, Is there a way to know the version of the .db file I am using? I'd like to issue some kind of SELECT statement to get it. Looks like there is an interface to get the library version, but I don't see anything for a db file. Thank you. ___

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Peter Da Silva
Using a straight PHP-level substitution like that performs the substitution before the SQL parser sees it. It’s also super dangerous if you’re not absolutely sure there’s no path for an untrusted agent to inject the name you’re selecting on. https://xkcd.com/327/ On 7/21/17, 3:42 AM,

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Peter Da Silva
Have a look at prepared statements and statement parameters. Also, if you’re quoting strings you should use single quotes rather than double quotes. Double quotes just override the tokenizer, the result is still untyped and technically an identifier (the tokenizer actually marks it as TK_ID)

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Tim Streater
On 21 Jul 2017, at 11:14, Rowan Worth wrote: > On 21 July 2017 at 17:50, Tim Streater wrote: > >>$sql = "UPDATE Movies SET name = '$newname' where id=$newid"; >> >> Personally I don't like forcing PHP to scan strings so I tend to use >> concatentation,

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Rowan Worth
On 21 July 2017 at 17:50, Tim Streater wrote: >$sql = "UPDATE Movies SET name = '$newname' where id=$newid"; > > Personally I don't like forcing PHP to scan strings so I tend to use > concatentation, rewriting the last of these as: > >$sql = 'UPDATE Movies SET name

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Tim Streater
On 21 Jul 2017 at 10:04, Edmondo Borasio wrote: > Hi Tim. > > It *almost* works.. > >$DbItemNameTest = "new name"; >$hIdTest = "1"; > >$db->exec ('UPDATE Anagrafica SET name = \'' . $DbItemNameTest . > '\' WHERE hospital_ID="1"'); //IT WORKS >

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Tim Streater
On 21 Jul 2017 at 09:58, Simon Slavin wrote: > On 21 Jul 2017, at 7:33am, Edmondo Borasio wrote: > >> $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"'); > > Those are the wrong quotes. SQLite requires single quotes around literal >

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Edmondo Borasio
Hi Tim. It *almost* works.. $DbItemNameTest = "new name"; $hIdTest = "1"; $db->exec ('UPDATE Anagrafica SET name = \'' . $DbItemNameTest . '\' WHERE hospital_ID="1"'); //IT WORKS $db->exec ('UPDATE Anagrafica SET name = \'' . $DbItemNameTest . '\' WHERE hospital_ID=' .

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Simon Slavin
On 21 Jul 2017, at 7:33am, Edmondo Borasio wrote: > $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"'); Those are the wrong quotes. SQLite requires single quotes around literal strings, and expects no quote around numbers. And PHP doesn’t care which

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Edmondo Borasio
Hi and thanks for your email. I am using PHP with SQLite on an Apache server. That statement was taken from some advice I got from a forum. I wasn't aware it was MySQL. I am new to SQLite and this is my first database. *"Table/column names cannot use parameters. You have to put it directly*

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Tim Streater
On 21 Jul 2017 at 07:33, Edmondo Borasio wrote: > I am updating a record of a SQLite database as follows: > > $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"'); > > but instead of using name and ID I want to use some variables, $NewItemName > and $hId. > >

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Clemens Ladisch
Edmondo Borasio wrote: > $stmt->bind_param($p_name,$bind_value); This looks like PHP's MySQL driver. Which DB are you actually using? Anyway, I recommend you start with the examples from the manual, e.g., : $stmt = $db->prepare('SELECT

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Edmondo Borasio
Hi Thanks but it doesn't work. It's weird because the following works: $db->exec('UPDATE Anagrafica SET name = "new name" WHERE hID="1"'); //WORKS But this doesn't: $p_name ="new name"; $bind_value = "1"; $stmt = $conn->prepare('UPDATE Anagrafica SET name=? WHERE hID=?');

Re: [sqlite] A Descending Auto Increment Key

2017-07-21 Thread cindy estepp
i have a file could you look at it and explain it to me please thank you On Thu, Jul 20, 2017 at 8:47 AM, Don V Nielsen wrote: > On the heel of this is a grammatical error: "declared type "INTEGER" > includes an "PRIMARY KEY DESC" clause". It should read "includes a >

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread nomad
On Fri Jul 21, 2017 at 06:33:55AM +, Edmondo Borasio wrote: > I am updating a record of a SQLite database as follows: > > $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"'); > > but instead of using name and ID I want to use some variables, $NewItemName > and $hId. > > Entering