Re: [sqlite] Does sqlite3 have variables like Oracle?

[Mike Eggleston]

Thanks. I didn't think of "CTEs" either. I need to read up on them. 

Mike

> On Sep 12, 2016, at 08:49, Dominique Devienne  wrote:
> 
>> On Sun, Sep 11, 2016 at 11:48 PM, mikeegg1  wrote:
>> 
>> I think Oracle (a long distant memory) has variables like @variable or
>> @@variable.
> 
> 
> Not really. Oracle SQL doesn't have variables per se.
> 
> But APIs to interact with Oracle SQL (OCI, JDBC, ODBC, etc...) can *bind*
> and *define*
> "host language" variables, just like SQLite's API can bind/define them too.
> 
> The Oracle command-line program (of choice), SQL*Plus, allows to bind
> variables [1] [3],
> which behind the scene means allocating some memory for the variable, and
> binding it as normal.
> But there's no direct support for defining. For this you need PL/SQL (see
> below).
> 
> Then PL/SQL, the server-side language which also supports variables, has
> special syntax to
> SELECT ... INTO [2], to define variables, and natively supports bind
> variables as well.
> 
> But again, in both cases it's the host program that implements the variable
> handling,
> not SQL itself. (define variables do need special support at the SQL level
> I guess, for the INTO syntax)
> 
> sqlite3.exe, the SQLite command line driver, doesn't support bind variables
> itself.
> It could be added (using [5] and co.), but that's just not the case.
> 
> In addition to what David mentioned (temp tables), you could also use CTEs
> [4],
> which is just a variation of the same temp table technique, albeit with a
> more "transient"
> temp table.
> 
> [1]
> https://oracle-base.com/articles/misc/literals-substitution-variables-and-bind-variables
> [2]
> https://oracle-base.com/articles/misc/introduction-to-plsql#using-sql-in-plsql
> [3] http://www.adp-gmbh.ch/ora/sqlplus/use_vars.html
> [4] https://www.sqlite.org/lang_with.html
> [5] https://www.sqlite.org/capi3ref.html#sqlite3_bind_parameter_count
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does sqlite3 have variables like Oracle?

[Mike Eggleston]

Duh. What a brilliant idea. Wish I had thought of it. :) Thanks. 

Mike

> On Sep 12, 2016, at 08:02, David Bicking <dbic...@yahoo.com> wrote:
> 
> Sqlite doesn't have variable.  While last row id is available other ways, a 
> trick to emulate a variable is to create a temp table with one field. You put 
> the value in to the that field. You can then cross join with the rest of your 
> table as need be, or do a sub-select to value a SET command.
> 
> David
>  From: mikeegg1 <mikee...@mac.com>
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> 
> Sent: Sunday, September 11, 2016 5:48 PM
> Subject: [sqlite] Does sqlite3 have variables like Oracle?
> 
> I think Oracle (a long distant memory) has variables like @variable or 
> @@variable where you can do something like '@variable = select rowid from 
> table where field = 4' and then later do 'insert into othertable (field2) 
> value(@variable)’. Does this make sense? I’m wanting to in the shell select 
> lastrowid then update a bunch of inserted rows in a different table with the 
> previously inserted lastrowid.
> 
> I don’t have an example at the moment of what I’m trying to do. I’m 
> generating a bunch of statements into a file that I will then ‘sqlite3 
> data.sqlite3 < data.sql’. I’m using the value of -14 (just a number) as a 
> place holder in a bunch of insert statements then at the end of each group I 
> do an update to the actual rowid.
> 
> Mike
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does sqlite3 have variables like Oracle?

[Dominique Devienne]

On Sun, Sep 11, 2016 at 11:48 PM, mikeegg1  wrote:

> I think Oracle (a long distant memory) has variables like @variable or
> @@variable.


Not really. Oracle SQL doesn't have variables per se.

But APIs to interact with Oracle SQL (OCI, JDBC, ODBC, etc...) can *bind*
and *define*
"host language" variables, just like SQLite's API can bind/define them too.

The Oracle command-line program (of choice), SQL*Plus, allows to bind
variables [1] [3],
which behind the scene means allocating some memory for the variable, and
binding it as normal.
But there's no direct support for defining. For this you need PL/SQL (see
below).

Then PL/SQL, the server-side language which also supports variables, has
special syntax to
SELECT ... INTO [2], to define variables, and natively supports bind
variables as well.

But again, in both cases it's the host program that implements the variable
handling,
not SQL itself. (define variables do need special support at the SQL level
I guess, for the INTO syntax)

sqlite3.exe, the SQLite command line driver, doesn't support bind variables
itself.
It could be added (using [5] and co.), but that's just not the case.

In addition to what David mentioned (temp tables), you could also use CTEs
[4],
which is just a variation of the same temp table technique, albeit with a
more "transient"
temp table.

[1]
https://oracle-base.com/articles/misc/literals-substitution-variables-and-bind-variables
[2]
https://oracle-base.com/articles/misc/introduction-to-plsql#using-sql-in-plsql
[3] http://www.adp-gmbh.ch/ora/sqlplus/use_vars.html
[4] https://www.sqlite.org/lang_with.html
[5] https://www.sqlite.org/capi3ref.html#sqlite3_bind_parameter_count
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does sqlite3 have variables like Oracle?

[David Bicking]

Sqlite doesn't have variable.  While last row id is available other ways, a 
trick to emulate a variable is to create a temp table with one field. You put 
the value in to the that field. You can then cross join with the rest of your 
table as need be, or do a sub-select to value a SET command.

David
  From: mikeegg1 <mikee...@mac.com>
 To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> 
 Sent: Sunday, September 11, 2016 5:48 PM
 Subject: [sqlite] Does sqlite3 have variables like Oracle?
   
I think Oracle (a long distant memory) has variables like @variable or 
@@variable where you can do something like '@variable = select rowid from table 
where field = 4' and then later do 'insert into othertable (field2) 
value(@variable)’. Does this make sense? I’m wanting to in the shell select 
lastrowid then update a bunch of inserted rows in a different table with the 
previously inserted lastrowid.

I don’t have an example at the moment of what I’m trying to do. I’m generating 
a bunch of statements into a file that I will then ‘sqlite3 data.sqlite3 < 
data.sql’. I’m using the value of -14 (just a number) as a place holder in a 
bunch of insert statements then at the end of each group I do an update to the 
actual rowid.

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


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

Re: [sqlite] Does sqlite3 have variables like Oracle?

[Darren Duncan]

You can also access that value within your Perl code, in a DBMS-agnostic 
fashion, with the appropriate DBI routine:


http://search.cpan.org/dist/DBI/DBI.pm#last_insert_id

-- Darren Duncan

On 2016-09-11 2:59 PM, mikeegg1 wrote:

I forgot I could use it inside the shell. Thanks.


On Sep 11, 2016, at 16:58, Simon Slavin  wrote:
On 11 Sep 2016, at 10:53pm, mikeegg1  wrote:


Thanks. I thought not. I’m doing this from PERL on a Mac and don’t know if I 
can fully access last_row_id().


It's not a C function, it's a function you can use inside SQL commands.  There 
shouldn't be a problem with it.

INSERT INTO myTable VALUES ('Hastings',17);
UPDATE anotherTable SET theRowId = last_row_id() WHERE placeName = 'Hastings';


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

Re: [sqlite] Does sqlite3 have variables like Oracle?

[mikeegg1]

I forgot I could use it inside the shell. Thanks.

Mike

> On Sep 11, 2016, at 16:58, Simon Slavin  wrote:
> 
> 
> On 11 Sep 2016, at 10:53pm, mikeegg1  wrote:
> 
>> Thanks. I thought not. I’m doing this from PERL on a Mac and don’t know if I 
>> can fully access last_row_id().
> 
> It's not a C function, it's a function you can use inside SQL commands.  
> There shouldn't be a problem with it.
> 
> INSERT INTO myTable VALUES ('Hastings',17);
> UPDATE anotherTable SET theRowId = last_row_id() WHERE placeName = 'Hastings';
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Does sqlite3 have variables like Oracle?

[Simon Slavin]

On 11 Sep 2016, at 10:53pm, mikeegg1  wrote:

> Thanks. I thought not. I’m doing this from PERL on a Mac and don’t know if I 
> can fully access last_row_id().

It's not a C function, it's a function you can use inside SQL commands.  There 
shouldn't be a problem with it.

INSERT INTO myTable VALUES ('Hastings',17);
UPDATE anotherTable SET theRowId = last_row_id() WHERE placeName = 'Hastings';

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

Re: [sqlite] Does sqlite3 have variables like Oracle?

[mikeegg1]

Thanks. I thought not. I’m doing this from PERL on a Mac and don’t know if I 
can fully access last_row_id(). :)

Mike

> On Sep 11, 2016, at 16:52, Simon Slavin  wrote:
> 
> 
> On 11 Sep 2016, at 10:48pm, mikeegg1  wrote:
> 
>> I think Oracle (a long distant memory) has variables like @variable or 
>> @@variable where you can do something like '@variable = select rowid from 
>> table where field = 4' and then later do 'insert into othertable (field2) 
>> value(@variable)’. Does this make sense?
> 
> Yes.  I understand how variables could be useful.  But SQLite doesn't have 
> variables the user can assign.
> 
>> I’m wanting to in the shell select lastrowid then update a bunch of inserted 
>> rows in a different table with the previously inserted lastrowid.
> 
> See the function "last_insert_rowid()" on the page
> 
> 
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Does sqlite3 have variables like Oracle?

[Simon Slavin]

On 11 Sep 2016, at 10:48pm, mikeegg1  wrote:

> I think Oracle (a long distant memory) has variables like @variable or 
> @@variable where you can do something like '@variable = select rowid from 
> table where field = 4' and then later do 'insert into othertable (field2) 
> value(@variable)’. Does this make sense?

Yes.  I understand how variables could be useful.  But SQLite doesn't have 
variables the user can assign.

> I’m wanting to in the shell select lastrowid then update a bunch of inserted 
> rows in a different table with the previously inserted lastrowid.

See the function "last_insert_rowid()" on the page



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

[sqlite] Does sqlite3 have variables like Oracle?

[mikeegg1]

I think Oracle (a long distant memory) has variables like @variable or 
@@variable where you can do something like '@variable = select rowid from table 
where field = 4' and then later do 'insert into othertable (field2) 
value(@variable)’. Does this make sense? I’m wanting to in the shell select 
lastrowid then update a bunch of inserted rows in a different table with the 
previously inserted lastrowid.

I don’t have an example at the moment of what I’m trying to do. I’m generating 
a bunch of statements into a file that I will then ‘sqlite3 data.sqlite3 < 
data.sql’. I’m using the value of -14 (just a number) as a place holder in a 
bunch of insert statements then at the end of each group I do an update to the 
actual rowid.

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