Re: [sqlite] Query executes in sqlite manager but not sqlite database in android

[big stone]

It seems Android use a pretty outdated SQlite motor : SQlite 3.7.1 = march
20th, 2012

http://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android

SQLite 3.7.11:

19-4.4-KitKat
18-4.3-Jelly Bean
17-4.2-Jelly Bean
16-4.1-Jelly Bean

SQLite 3.7.4:

15-4.0.3-Ice Cream Sandwich
14-4.0-Ice Cream Sandwich
13-3.2-Honeycomb
12-3.1-Honeycomb
11-3.0-Honeycomb

SQLite 3.6.22:

10-2.3.3-Gingerbread
9-2.3.1-Gingerbread
8-2.2-Froyo

SQLite 3.5.9:

7-2.1-Eclair
4-1.6-Donut
3-1.5-Cupcake
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query executes in sqlite manager but not sqlite database in android

[big stone]

Hi Kamulegs,

Your SQLiteManager includes a version of SQLite >=3.7.16 , and your android
application does not.

Indeed the syntax "  (b JOIN c ON b.id = c.id)" is only accepted without
this errror after 3.7.16.
==> If you can rewrite your syntax without these parenthesis (like below),
all should be fine.

*SELECT M._id as Meter_id, M.MeterNumber, R1.ReadingDate as ReadingDate,
R1.Reading AS CurrentReading, R2.ReadingDate AS PrevReadingDate, R2.Reading
AS PrevMeterReading, R2.Rate as Rate, R2._id as _id,R1.TenantMeter_id
FROM Meters AS M INNER JOIN TenantMeters ON M._id = TenantMeters.Meter_id
INNER JOIN MeterReading AS R1 INNER JOIN MeterReading AS R2 ON
R1.TenantMeter_id = R2.TenantMeter_id ON TenantMeters._id =
R1.TenantMeter_id
WHERE (((R2.ReadingDate)=(SELECT Max(R3.ReadingDate)
  FROM [MeterReading] AS R3
  WHERE (R3.TenantMeter_id = R1.TenantMeter_id)
AND (R3.ReadingDate < R1.ReadingDate)
))) OR (((R2.TenantMeter_id) Is Null))
*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting Error at line 16265 (v3.8.2)

[Simon Slavin]

On 24 Jan 2014, at 5:09pm, Hiew William  wrote:

> I’m getting error from sqlite3.c when i’m trying to run my application in 
> development……

Which error ?  In other words, what's the error message ?  And what API 
function is your application trying to execute when you get that error ?

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

Re: [sqlite] export table to csv

[Simon Slavin]

On 24 Jan 2014, at 6:24am, Frantisek Cerven  wrote:

> I want to ask you if is any way to call sqlite special commands
> programatically from c#.

These 'special commands' are not in SQLite at all.  They are features of the 
command-line shell application.  SQLite does not understand any command 
starting with a dot.

> E.g. I need something like this in code not in command line:
> 
> sqlite> .mode csv
> sqlite> .output test.csv
> sqlite> select * from tbl1;
> sqlite> .output stdout
> 
> If not, what is the quicker way to export table programatically? Now I`m
> doing it with DbDataReader, but it is slow.

I'm afraid that the way to do that quickly is to write some C# code which does 
it.  A slower way to do it would be to have C# issue an operating system 
command that makes the shell tool do it.

> Ps: There is not any support user forum??

This is it !

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

[sqlite] Query executes in sqlite manager but not sqlite database in android

[Kamulegs]

Hello Community

Hope to get some pointers here because i have a hit a snag!

I have 4 tables.

Meters:_id, SerialNumber
Tenants: _id, FirstName.
TenantsMeters: _id,Tenant_id,Meter_id, (basically junction table for linking
many to many relationship btn tenants and meters)

MeterReading: _id, ReadingDate, Reading, TenantMeter_id(FK for TenantMeters
table)

This query works in sqlite manager 
*SELECT M._id as Meter_id, M.MeterNumber, R1.ReadingDate as ReadingDate,
R1.Reading AS CurrentReading, R2.ReadingDate AS PrevReadingDate, R2.Reading
AS PrevMeterReading, R2.Rate as Rate, R2._id as _id,R1.TenantMeter_id
FROM (Meters AS M INNER JOIN TenantMeters ON M._id = TenantMeters.Meter_id)
INNER JOIN (MeterReading AS R1 INNER JOIN MeterReading AS R2 ON
R1.TenantMeter_id = R2.TenantMeter_id) ON TenantMeters._id =
R1.TenantMeter_id
WHERE (((R2.ReadingDate)=(SELECT Max(R3.ReadingDate) 
  FROM [MeterReading] AS R3   
  WHERE (R3.TenantMeter_id = R1.TenantMeter_id)   
AND (R3.ReadingDate < R1.ReadingDate) 
))) OR (((R2.TenantMeter_id) Is Null))
*

But fails in sqlite database in android with the error "no such
column:R1.ReadingDate"

It fails in android app and also fails when i connect to the android sqlite
db from command prompt .

What can be the likely cause?

Any pointers please. 

Ronald



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Query-executes-in-sqlite-manager-but-not-sqlite-database-in-android-tp73466.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] SELECTing from another SELECT

[jose isaias cabrera]

Igor Tandetnik wrote...

On 1/23/2014 2:26 PM, St. B. wrote:

SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a');

will probably fill the bill.

If I where to run your query, I would do a
select A.* from A inner join B on A.a = b.ProjId where b.ptask='a'


Careful - this will produce a different result if B has multiple rows with 
the same ptask and projid. May or may not be a concern in the OP's case.


What would be the correct inner join syntax to provide the same output as 
the original select?  I like that shorter syntax.  Will any of these will be 
faster?  Thanks so much.


josé 


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

Re: [sqlite] SELECTing from another SELECT

[jose isaias cabrera]

"Igor Tandetnik" wrote on Friday, January 24, 2014 9:48 AM...



On 1/24/2014 9:28 AM, jose isaias cabrera wrote:


Igor Tandetnik wrote...

On 1/23/2014 2:26 PM, St. B. wrote:

SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask =
'a');

will probably fill the bill.

If I where to run your query, I would do a
select A.* from A inner join B on A.a = b.ProjId where b.ptask='a'


Careful - this will produce a different result if B has multiple rows
with the same ptask and projid. May or may not be a concern in the
OP's case.


What would be the correct inner join syntax to provide the same output as
the original select?  I like that shorter syntax.  Will any of these
will be
faster?  Thanks so much.


"SELECT DISTINCT ..." perhaps. But that would kill any alleged performance 
improvement that a join may have over IN (if any; personally, I'm not sure 
it's necessarily true that the join would perform better in the first 
place).


You are right. Your original IN command is much faster.  It instantly 
responds, while the INNER JOIN takes a few second before responding. 
Thanks. 


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

[sqlite] Getting Error at line 16265 (v3.8.2)

[Hiew William]

Help,


not sure if this is the right place to post, I’m getting error from sqlite3.c 
when i’m trying to run my application in development……

I’m developing using vs 2012 for Windows Store App, running for winrt and 
desktop. Installed “SQLite for Windows Runtime and SQLite-net 1.0.7 nugget 
package. never have this problem before… just added 50 records and it dies.



 




Please help urgent ….. need to release soon 




Sent from Windows Mail___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECTing from another SELECT

[jose isaias cabrera]

St. B. wrote...


SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a');

will probably fill the bill.

If I where to run your query, I would do a
select A.* from A inner join B on A.a = b.ProjId where b.ptask='a'

the join may optimize better than the in (select ...)


Thanks.  I will give this a try also.  As we say in the Spanish language: 
Muchas gracias.


josé 


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

Re: [sqlite] csv export

[Frantisek Cerven]

can I run sql shell also on windows CE and windows Mobile?
could you please give me little example how to create virual table to file
export it and to pc? I can find it. Can I also delete it from file explorer
without problems?
Many thanks.


On Fri, Jan 24, 2014 at 9:10 AM, Hick Gunter  wrote:

> You can fork off a child process, run the sqlite shell in that and pipe
> your commands to it.
>
> Or you could write a CSV virtual table and execute something like "insert
> into  select ..."
>
> -Ursprüngliche Nachricht-
> Von: Frantisek Cerven [mailto:feri...@gmail.com]
> Gesendet: Freitag, 24. Jänner 2014 07:45
> An: sqlite-users@sqlite.org
> Betreff: [sqlite] csv export
>
> Hello,
>
> I want to ask you if is any way to call sqlite special commands
> programatically from c#.
> E.g. I need something like this in code not in command line:
>
> sqlite> .mode csv
> sqlite> .output test.csv
> sqlite> select * from tbl1;
> sqlite> .output stdout
>
> If not, what is the quicker way to export table programatically? Now I`m
> doing it with DbDataReader, but it is slow.
>
> Ps: There is not any support user forum??
>
> Thank you,
> Fratnisek
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> --
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> Klitschgasse 2 – 4, A - 1130 Vienna, Austria
> FN 157284 a, HG Wien
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This e-mail is confidential and may well also be legally privileged. If
> you have received it in error, you are on notice as to its status and
> accordingly please notify us immediately by reply e-mail and then delete
> this message from your system. Please do not copy it or use it for any
> purposes, or disclose its contents to any person as to do so could be a
> breach of confidence. Thank you for your cooperation.
> ___
> 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] export table to csv

[Frantisek Cerven]

Hello,

I want to ask you if is any way to call sqlite special commands
programatically from c#.
E.g. I need something like this in code not in command line:

sqlite> .mode csv
sqlite> .output test.csv
sqlite> select * from tbl1;
sqlite> .output stdout

If not, what is the quicker way to export table programatically? Now I`m
doing it with DbDataReader, but it is slow.

Ps: There is not any support user forum??

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

Re: [sqlite] "Common Table Expression"

[Keith Medcalf]

>remark 2:
>-
>I'm using Keith buildMSVC.cmd file to compile (that I'm using without
>knowing what it does exactly)
>
>I have one more compile error than with 21th version.
>"c1 : fatal error C1083: Impossible d'ouvrir le fichier source :
>'CSVImport.c' "
>
>==> Maybe it's normal.

Yes.  If you delete file containing the source code then that code cannot be 
compiled.  

CSVImport.c is code written by Michael D. Black which implements a stand-alone 
CSV import utility, just as there are other source files (other than just the 
amalgamation) which implement a number of add-in features if you enable them 
with

BuildMSVC ext

the default build (without ext) contains the base engine only without any 
extensions.





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

Re: [sqlite] "Common Table Expression"

[Petite Abeille]

On Jan 25, 2014, at 6:05 AM, Keith Medcalf  wrote:

> Read the docs.  It explains how recursive CTEs are computed and how UNION ALL 
> vs UNION work in CTEs.


Hmmm… perhaps… doing is believing… so will experiment once the next SQLite 
release is officially out.

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

Re: [sqlite] "Common Table Expression"

[Petite Abeille]

On Jan 25, 2014, at 2:37 AM, James K. Lowden  wrote:

> Funny, we find ourselves on the opposite side of the compexity question
> this time. 

Ehehehe… yes… the irony is duly noted :)

But, ok, then, let welcome our new VALUES overlord. May it have a long and 
prosperous carrier! 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[Elefterios Stamatogiannakis]

On 25/01/14 18:41, James K. Lowden wrote:

On Sat, 25 Jan 2014 11:56:32 +0200
Elefterios Stamatogiannakis  wrote:


Wouldn't it be better instead of creating a new concept "row
constructor", to use the existing row constructors, also known as
virtual tables?


Perhaps.  I didn't make up the term; it exists in various other DBMSs,
sometimes called table value constructor.  I just wanted to make clear
that it's not new, and opens the potential for (I would say) better
queries, because they can be expressed in terms of tuples instead of
just scalars.



Yes i see what you mean and i agree. Furthermore, and to carry on a 
little more that train of thought, i hope that tuples will become 
possible as return values from SQLite's user defined functions.


If ever that becomes the case, then i suggest to go for "tuple 
generators" instead of materialized tuples as the return primitive. 
Materialized tuples as a primitive, have a lot of nasty side effects in 
a DB engine.


l.

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

Re: [sqlite] "Common Table Expression"

[James K. Lowden]

On Sat, 25 Jan 2014 11:56:32 +0200
Elefterios Stamatogiannakis  wrote:

> Wouldn't it be better instead of creating a new concept "row 
> constructor", to use the existing row constructors, also known as 
> virtual tables?

Perhaps.  I didn't make up the term; it exists in various other DBMSs,
sometimes called table value constructor.  I just wanted to make clear
that it's not new, and opens the potential for (I would say) better
queries, because they can be expressed in terms of tuples instead of
just scalars.  

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

Re: [sqlite] "Common Table Expression"

[Elefterios Stamatogiannakis]

On 25/01/14 03:37, James K. Lowden wrote:

On Fri, 24 Jan 2014 23:51:11 +0100
Petite Abeille  wrote:


It's exactly the same as "SELECT ?", but a little bit easier to
write. (It behaves like with INSERT, but is now available in every
place where a SELECT would be allowed.)


Hmmm? seems rather pointless to me.

select 1 as value /* from thin air */ union all
select 2 as value /* from thin air */
? etc ?

Seems to be good enough. No point in hijacking a totally unrelated
construct. I would drop such complication if I had a say. There is
already a perfectly fine construct to conjure constants out of thin
air: select.


VALUES is a row constructor.  You should be able to do

VALUES ( ( 'a', 'b', 'c' ) , ('d', 'e', 'f') )
as ( A, B, C ) as T



Wouldn't it be better instead of creating a new concept "row 
constructor", to use the existing row constructors, also known as 
virtual tables?


If we had the option of using virtual tables without first creating 
them, and we also were able to have them at the front of the query 
(automatically prepending a select * from ...) then VALUES above, could 
be a virtual table [*].


So think of a virtual table named VALUES that gets as parameters the 
values that you want it to emit. Then you can select from it, insert 
from it and so on.


l.

[*] In addition if we permitted queries as parameters in virtual tables, 
it would also enable virtual table composition.


An example of how this is done in madIS is below (XMLPARSE and FILE are 
virtual tables):


XMLPARSE FILE 'xmldata.xml.gz';

or expanded:

select * from (XMLPARSE select * from FILE('xmldata.xml'));



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

Re: [sqlite] "Common Table Expression"

[big stone]

hi again,

With the version sqlite-amalgamation-201401242258   of this night.

 I found my error so timings are :

timing with medium sudoku example  :
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
2 nested with = 3.06 sec (3.32 sec previous beta of 21rst) 8% faster
1 nested with = 1.7 sec   (1.7 sec previous beta of 21rst) 0%
3 nested with = 2.65 sec (2.5 previous beta of 21rst) 5% faster
(1 nested with which could be 3 nested with) = 1.05 sec (1.09 sec
previous beta of 21rst) 3% faster

remark1 :
-
On my non-cte workload, it 24th  version seems quicker by  3% faster than 21rst)

==> It's curious, were there speed improvement commits since beta 2 ?


remark 2:
-
I'm using Keith buildMSVC.cmd file to compile (that I'm using without
knowing what it does exactly)

I have one more compile error than with 21th version.
"c1 : fatal error C1083: Impossible d'ouvrir le fichier source : 'CSVImport.c' "

==> Maybe it's normal.

*** full compile report ***
>buildMSVC.cmd
CompileOptions=/nologo /O2 /GLFAy /fp:precise
Creating library file: SQLite3.dll
sqlite3.c
   Création de la bibliothèque SQLite3.lib et de l'objet SQLite3.exp
Génération de code en cours
Fin de la génération du code
Creating executable;   CSVImport.exe
CSVImport.c
c1 : fatal error C1083: Impossible d'ouvrir le fichier source : 'CSVImport.c' :
No such file or directory
Creating executable:   SQLite3d.exe
shell.c
Génération de code en cours
Fin de la génération du code
Creating executable:   SQLite3s.exe
shell3x.c
shell3x.c(146240) : warning C4005: 'ArraySize' : redéfinition de macro
shell3x.c(8545) : voir la définition précédente de 'ArraySize'
Génération de code en cours
Fin de la génération du code
Copying Build to DIST
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users