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  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 
> To: SQLite mailing list  
> 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] Explanation

[Mike Eggleston]

On Fri, 28 Aug 2009, Marco Bambini might have said:

> Hello,
> 
> today I made some test on a project I wrote some years ago.
> I upgraded sqlite library from version 3.4.2 to version 3.6.17.
> What I am really unable to understand is the time difference required  
> to perform the same query using the exact same algorithm by the two  
> libraries.
> 
> SELECT * FROM table1
> where table1 has 1 million rows and 10 columns (its an 80MB db).
> 
> Version 3.4.2 takes about 5.06 seconds (average value) while version  
> 3.6.17 takes about 7.28 seconds (average value).
> Could be a slowdown in the library for the complexity added over the  
> years or does someone have another possible explanation?
> 
> Thanks.

Did you just relink your app or did you also migrate the data to a new
sqlite3 database? I think the migration command is:

echo '.dump' | sqlite3 $db | sqlite3 $dbnew

Maybe the internal database structure has changed?

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

Re: [sqlite] SELECT DISTINCT bug in SQLite 3.6.0?

[Mike Eggleston]

On Fri, 31 Jul 2009, Lennart Ramberg might have said:

> Hello,
> 
> I'm new to this list and what prompted me to sign up was a SELECT
> DISTINCT problem I experience in REALbasic (Linux), which has SQLite
> built-in.
> 
> REALbasic downgraded their latest version from SQLIte 3.6.3 to 3.6.0
> Now, 3.6.0 behaves differently than 3.3.6 and 3.6.3 in the following manner:
> (excuse me for using RB code, but I think it is readable)
> 
> dim rs as RecordSet
> rs=dbEta.SQLSelect("SELECT DISTINCT V.resanr,C.namn"_
>   +" FROM Voyages V,Category C WHERE C.kategorinr=V.kategorinr")
> 
> dim namnstr as string
> 
> namnstr=rs.Field("namn").StringValue   'I get a NilObjectException
> here in 3.6.0 ...
> namnstr=rs.Field("C.namn").StringValue   '... but not here.
> 
> namnstr=rs.Field("C.namn").StringValue   'I get a NilObjectException
> here in 3.3.6 and 3.6.3 ...
> namnstr=rs.Field("namn").StringValue   '... but not here.
> 
> There are workarounds, but is this a bug in SQLite 3.6.0?
> 
> Yes, at sqlite.org I read:
> "SQLite version 3.6.3 fixes a bug in SELECT DISTINCT that was
> introduced by the previous version."
> So that shouldn't be it, since it was introduced in 3.6.2, right?
> 
> Thanks
> Lennart Ramberg

Just a thought. Have you tried the same sql select statement in the
sqlite3 command line tool? Does the tool report the same different values?

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

Re: [sqlite] Advices to get max performance with SQLITE and BLOBS

[Mike Eggleston]

On Fri, 13 Mar 2009, Pierre Chatelier might have said:

> Hello,
> 
> I am using SQLITE to store and retrieve raw data blocks that are  
> basically ~300Ko. Each block has an int identifier, so that insert/ 
> select are easy. This is a very basic use : I do not use complex  
> queries. Only "INSERT/SELECT where index=..."
> 
> Now, I am thinking about performance, for writing a sequence of a few  
> hundreds 300k blocks, as fast as possible.
> Obviously, I use bind_blob(), blob_read() and blob_write() functions.  
> I have already tuned the PRAGMAs for journal/synchronous/page_size/ 
> cache, so that it's rather efficient.
> I do not DELETE any content and the whole database is dropped after  
> use: VACUUM is not important.
> 
> There are other ways to optimize, but I wonder if it is worth, or it  
> the gain would be only marginal regarding what I am doing.
> 1)recompile SQLite ? Which compile options would help in this case ?
> 2)using other memory allocators ? I am not sure that writing big data  
> blocks triggers many calls to malloc()
> 3)using compression ? zlib could help, but since my data does not  
> compress very well (Let's say an average 20% space can be saved per  
> block), I am not sure that the compression time will balance the  
> writing time.
> 
> Of course, I am only asking for advices regarding your experience,  
> there is certainly no exact answer, and it will always depend on my  
> data.
> 
> Regards,
> 
> Pierre Chatelier

Why do you not use the int converted to a hex (sprintf("%08x", id))
as a file name and just use the file system?

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

Re: [sqlite] import a CSV-File

[Mike Eggleston]

On Fri, 06 Mar 2009, Rich Shepard might have said:

> On Fri, 6 Mar 2009, Hans-Martin wrote:
> 
> > It seems that there is no way to get rid of the embedded CR/LF without parse
> > the complete output.
> 
>Use sed. That's what it's for.
> 
> Rich

Or tr(1) if it's a single character.

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

Re: [sqlite] Best GUI?

[Mike Eggleston]

On Thu, 26 Feb 2009, Nuno Magalh?es might have said:

> This is a bit of a religious question, but which are the mest/most
> popular *nix GUIs out there? I'm using a firefox plugin but not
> entirely happy with it. What do you use and why?

I prefer vi and sh.

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

Re: [sqlite] Query Optimization Help

[Mike Eggleston]

On Wed, 11 Feb 2009, inZania might have said:

> 
> Hello,
> 
> I have a query that is slowing down my application significantly; in some
> cases, it takes 20+ seconds (this is in a SQLite database in an iPhone app,
> which is why it is so slow - the iPhone doesn't have as much system
> resources).  If anybody could help me optimize this query, I'd appreciate it
> very much.
> 
> The situation is this: there is a table, "cards", which I am searching. 
> Each card has a card_id, name, text, etc.  There is also a table "card_tags"
> which has only the rows "card_id" and "tag", because a single card may have
> several tags.  The query I'm trying to execute is attempting to search the
> card's name, text, OR any of its tags for a specific search string.
> 
> Here's the query I've constructed that is operating slowly:
> SELECT DISTINCT cards.* FROM cards LEFT JOIN card_tags ON
> cards.card_id=card_tags.card_id WHERE (cards.name LIKE '%query%' OR
> cards.text LIKE '%query%' OR card_tags.tag LIKE '%query%')
> 
> Any help would be appreciated!

Would you post the relevant portions of your DML? That may help the
question be more clear.

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

Re: [sqlite] playing with sqlite3

[Mike Eggleston]

On Fri, 30 Jan 2009, D. Richard Hipp might have said:

> 
> On Jan 30, 2009, at 8:42 AM, Mike Eggleston wrote:
> 
> > This box is fedora core 5 with sqlite3 3.3.3.
> 
> Version 3.3.3 will be three years old tomorrow.  From this I'm  
> guessing you didn't compile SQLite yourself but are using whatever  
> happen to come with fedora core 5.  And there is no telling how they  
> compiled it.
> 
> I'd suggest you download the latest sqlite3 command-line shell from 
> http://www.sqlite.org/download.html 
>   - precompiled and ready to run, and try again using that.
> 
> FWIW, SQLite does not use mmap(), at least not directly.  (Maybe the  
> system malloc() is calling mmap().)  So I do not know what is causing  
> all of those mremap() calls you are seeing in strace.
> 
> D. Richard Hipp
> d...@hwaci.com

I had thought of that also, but wanted to wait to see if there is some
already known reason this is happening.

So, I pulled the source for sqlite-3.6.10.tar.gz, compiled it on the
fedora core 5 box, and ran the command again. The data loading is stopping
at exactly the same place. Short answer: no change.

What's next?

Wait. I ran the sqlite3 under script during lunch. I have the same
behavior. The script also captured the first line of output from killing
sqlite3. The output shows binary characters in an INSERT statement. The
bad line is (characters coverted):

(oops deleted the line, a few ^P, ^G, and ^0 characters)
(found another)
INSERT INTO File VALUES 
(3555,55862,1093685304,1817326624,1799833888,1092633120,'A A Bey BAA Y BGmuHb 
BFi8Ob BGfR','I A A C^V+I2SKHGKJVuCfW9BcGPWLQ\0\0^C\0g^A 
ø~^~G§\0^G;^C\0~J\0\0\0ã\r\0\05Ú\0\09P0A 4Rj IGk B A A A');

In vi I finally used :g/[^A-^Z]/d to remove all binary characters.

After removing that line I was able to load the entire file without
errors. Thanks for the help.

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

Re: [sqlite] playing with sqlite3

[Mike Eggleston]

On Fri, 30 Jan 2009, John Machin might have said:

> On 30/01/2009 2:27 AM, Mike Eggleston wrote:
> > On Thu, 29 Jan 2009, Thomas Briggs might have said:
> > 
> >>When you say the load "stops", what do you mean?  Does the sqlite3
> >> process end?  Does it sit there doing nothing?
> >>
> >>The first thing I would do is look at line 55035 of the source file
> >> and see if there's something weird about it.
> >>
> >>Also, have you done a line count on the file so you know exactly
> >> how many rows it should load?
> >>
> >>-T
> >>
> >> On Wed, Jan 28, 2009 at 5:33 PM, Mike Eggleston <mikee...@me.com> wrote:
> >>> Hi,
> >>>
> >>> I'm curious how sqlite3 may perform for some of my applications that
> >>> really don't need things like MySQL or larger. I am using bacula
> >>> (http://www.bacula.org) at work so I dumped the bacula data from MySQL
> >>> (mysqldump bacula > bacula.sql), wrote a perl script to massage the data,
> >>> and now I'm trying to load that data into a sqlite3 file.
> >>>
> >>> I don't see any errors on stdout, but the loading of rows stops after
> >>> 55034 rows (file size is 6338560 bytes). I know in MySQL this table
> >>> has rows.
> >>>
> >>> Where can I look and what might be the error that the rows are not
> >>> loading?
> >>>
> >>> Mike
> >>>
> >>> Fedora Core 5
> >>> sqlite3 3.3.3
> > 
> > After deleting the three rows in my previous message (that has not yet
> > made it through moderation), the load now stops at 6337536 bytes when
> > using the command 'time sqlite3 x.db < x.sql ; date'. Still no messages
> > nor errors from sqlite to stdout nor stderr from the above command.
> > 
> > What to try next?
> 
> Have you looked at the stoppage points in your file(s) with a hex 
> editor? Any non-printable non-ASCII characters other than newline ('\n')?

This box is fedora core 5 with sqlite3 3.3.3.

> [If you were on Windows I'd bet you had Ctrl-Z aka '\x1a' aka CPMEOF 
> bytes in there]

[Right, this is not windows.]

> You could also try answering Thomas Briggs's questions:
> 
> (1) When you say the load "stops", what do you mean?  Does the sqlite3
>   process end?  Does it sit there doing nothing?

I start sqlite3, several tables are created, data for the largest table
(File) starts loading, after 55000+ rows the loading stops, no further
rows are loaded nor are the rest of the tables later in the x.sql file
created.

> (2) Also, have you done a line count on the file so you know exactly how 
> many rows it should load?

[mi...@zurich tmp]$ grep -ci 'insert into file ' x.sql
6093439
[mi...@zurich tmp]$ mysql --user=root --exec='select count(*) from File' bacula
+--+
| count(*) |
+--+
|  2289331 | 
+--+

My test with sqlite3 so far is only loading 55034 rows of the 6093439
in the x.sql file.

> Also while you are getting the line count from wc, get the character 
> count and compare it with the file size from ls.

Below.

> And another thought, bit of a long shot, try running it without the 
> "time" and "; date".
> 
> Oh, and try running it with only the 3 lines that you cut out plus a 
> couple more on the end. If that reproduces the problem, then at least 
> you don't have to wait around while experimenting. The next experiment 
> would be to try to reproduce the problem with a non-confidential set of 
> 5 or so lines so that you could post it here for scrutiny.
> 
> And double-check the SQL syntax in the 3 lines that you cut out.

I did.

> HTH,
> John

$ ls -l
total 2012088
drwxr-xr-x 20 geDomain Users   4096 Sep 17  2007 acads
-rw-r--r--  1 mikee Domain Users  944217701 Jan 28 14:21 bacula.sql
-rw-r--r--  1 mikee Domain Users969 Jan 28 15:22 x.pl
-rw-r--r--  1 mikee Domain Users 1114109293 Jan 29 08:52 x.sql
$ cp x.sql y.sql
$ ls -l
total 3101160
drwxr-xr-x 20 geDomain Users   4096 Sep 17  2007 acads
-rw-r--r--  1 mikee Domain Users  944217701 Jan 28 14:21 bacula.sql
-rw-r--r--  1 mikee Domain Users969 Jan 28 15:22 x.pl
-rw-r--r--  1 mikee Domain Users 1114109293 Jan 29 08:52 x.sql
-rw-r--r--  1 mikee Domain Users 1114109293 Jan 30 06:59 y.sql
$ df -k .
Filesystem   1K-blocks  Used Available Use% Mounted on
/dev/mapper/rootvg-datalv
 1887255336 1780873492  12026644 100% /opt/data
$ wc x.sql
   9459865  138924803 1114109293 x.sql
$ 

Running the command:

$ strace -o /opt/data/tmp/sqlite3.strace sqlite3 x.db < x.sql

The strace output whe

Re: [sqlite] playing with sqlite3

[Mike Eggleston]

On Thu, 29 Jan 2009, Thomas Briggs might have said:

>When you say the load "stops", what do you mean?  Does the sqlite3
> process end?  Does it sit there doing nothing?
> 
>The first thing I would do is look at line 55035 of the source file
> and see if there's something weird about it.
> 
>Also, have you done a line count on the file so you know exactly
> how many rows it should load?
> 
>-T
> 
> On Wed, Jan 28, 2009 at 5:33 PM, Mike Eggleston <mikee...@me.com> wrote:
> > Hi,
> >
> > I'm curious how sqlite3 may perform for some of my applications that
> > really don't need things like MySQL or larger. I am using bacula
> > (http://www.bacula.org) at work so I dumped the bacula data from MySQL
> > (mysqldump bacula > bacula.sql), wrote a perl script to massage the data,
> > and now I'm trying to load that data into a sqlite3 file.
> >
> > I don't see any errors on stdout, but the loading of rows stops after
> > 55034 rows (file size is 6338560 bytes). I know in MySQL this table
> > has rows.
> >
> > Where can I look and what might be the error that the rows are not
> > loading?
> >
> > Mike
> >
> > Fedora Core 5
> > sqlite3 3.3.3

After deleting the three rows in my previous message (that has not yet
made it through moderation), the load now stops at 6337536 bytes when
using the command 'time sqlite3 x.db < x.sql ; date'. Still no messages
nor errors from sqlite to stdout nor stderr from the above command.

What to try next?

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

Re: [sqlite] playing with sqlite3

[Mike Eggleston]

On Thu, 29 Jan 2009, Thomas Briggs might have said:

>When you say the load "stops", what do you mean?  Does the sqlite3
> process end?  Does it sit there doing nothing?
> 
>The first thing I would do is look at line 55035 of the source file
> and see if there's something weird about it.
> 
>Also, have you done a line count on the file so you know exactly
> how many rows it should load?
> 
>-T
> 
> On Wed, Jan 28, 2009 at 5:33 PM, Mike Eggleston <mikee...@me.com> wrote:
> > Hi,
> >
> > I'm curious how sqlite3 may perform for some of my applications that
> > really don't need things like MySQL or larger. I am using bacula
> > (http://www.bacula.org) at work so I dumped the bacula data from MySQL
> > (mysqldump bacula > bacula.sql), wrote a perl script to massage the data,
> > and now I'm trying to load that data into a sqlite3 file.
> >
> > I don't see any errors on stdout, but the loading of rows stops after
> > 55034 rows (file size is 6338560 bytes). I know in MySQL this table
> > has rows.
> >
> > Where can I look and what might be the error that the rows are not
> > loading?
> >
> > Mike
> >
> > Fedora Core 5
> > sqlite3 3.3.3

Duh, I should have tried that first. I've removed three lines around line
55035 and will try the inserts again. Inspecting the file of lines I see
no difference other than the values for the columns. The File table should
have 2671022 rows (that count is from mysql and from last night's backup,
so the count is bound to be off some, but sqlite3 should be close).

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

[sqlite] playing with sqlite3

[Mike Eggleston]

Hi,

I'm curious how sqlite3 may perform for some of my applications that
really don't need things like MySQL or larger. I am using bacula
(http://www.bacula.org) at work so I dumped the bacula data from MySQL
(mysqldump bacula > bacula.sql), wrote a perl script to massage the data,
and now I'm trying to load that data into a sqlite3 file.

I don't see any errors on stdout, but the loading of rows stops after
55034 rows (file size is 6338560 bytes). I know in MySQL this table
has rows.

Where can I look and what might be the error that the rows are not
loading?

Mike

Fedora Core 5
sqlite3 3.3.3
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users