Re: [sqlite] Left Join

[Igor Tandetnik]

"Tommy Anderson" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> Select * From ExcelMapValue
>
> OUTER JOIN   (SELECT ProjectId, InputId, DataValue, Formula
> FROM ProjectData
> WHERE  (ProjectId = @Id))  derivedtbl_1
>
> On ExcelMapValue.InputId =  derivedtbl_1.InputId

Try this:

Select * From ExcelMapValue e left join ProjectData p
on (e.InputId = p.InputId and p.ProjectId = @Id);

Igor Tandetnik 



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

Re: [sqlite] how do this stuff in sqlite

[Griggs, Donald]

Rachmat,

I also noted that one of your queries includes:
 time_to_sec(timediff(akhir1.End, awal1.Begin))

I think time conversion routines are part of each database vendor's
implementation -- and not part of the sql standard, so you'll need to
convert to the time routines provided in sqlite or write your own
functions.
 

"Rachmat Febfauza" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> i have query that don't work in sqlite but in mysql work and make good

> result.

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

Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b

[Sherief N. Farouk]

> The given code is correct. The lower-case string is a C# alias for the
> System.String class. System.Int64 is an opaque 64-bit pointer value.

Seems like the grasping deficiencies are on my side :D.

> The code so far is correct. What is missing though is the calling
> convention, which by default is cdecl, but .NET doesn't use that one
> by default for P/Invoke. It uses stdcall/winapi as the default calling
> convention. Change the CallingConvention in the DllImport line.
> 
> Additionally using CharSet=CharSet.Unicode adds an implicit W as per
> Win32 calling conventions to the function name. Since the
> sqlite3_open16
> doesn't have that you need to use ExactSpelling to prevent it from
> adding
> that W.

Off topic: God, that's ugly.

> Instead of rolling your own P/Invoke wrapper I'd suggest using one of
> the available .NET wrappers for SQLite.

Best idea so far!

- Sherief

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

Re: [sqlite] Loading database from memory buffer

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Brown, Daniel wrote:
> What will happen if I don't implement the locking functionality does
> that mean that SQLite would no longer be thread safe?

It means that concurrent access (from different sqlite3 pointers in the
same or different processes) will trash the database contents since
there won't be locking of the database itself.

If you need different sqlite3 pointers then you can use proxy locking.
Effectively this does the actual locking operations on a different file
than the database.  http://www.sqlite.org/cvstrac/wiki?p=ProxyLocking

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkkvE48ACgkQmOOfHg372QSX9ACgziGqbK4sUBBTTD19M4Xt+3pQ
0OYAoIzQN4IEZS4c03ZX8DKFpmi/vzR2
=W6co
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Left Join

[Tommy Anderson]

Hi, 

I have a left join problem with SQLite. 

this Select SELECT ProjectId, InputId, DataValue, Formula
FROM ProjectData
WHERE  (ProjectId = @Id) contains almost 15 000 rows.

this Table ExcelMapValue contains almost 5 000 rows. and contains these
columns InputId, sheetCode, rowIndex, ColumnIndex

When I run this, it takes at least 2 minutes or it says out of memory 

Select * From ExcelMapValue

OUTER JOIN   (SELECT ProjectId, InputId, DataValue, Formula
FROM ProjectData
WHERE  (ProjectId = @Id))  derivedtbl_1
  
On ExcelMapValue.InputId =  derivedtbl_1.InputId  


Anyone can help me ? I don't understand why it takes forever to run. In Sql
server it runs in less than a second.
-- 
View this message in context: 
http://www.nabble.com/Left-Join-tp20725943p20725943.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] Loading database from memory buffer

[Brown, Daniel]

What will happen if I don't implement the locking functionality does
that mean that SQLite would no longer be thread safe?

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns
Sent: Thursday, November 27, 2008 12:05 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Loading database from memory buffer

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Brown, Daniel wrote:
> However writing a new VFS would
> seem to be quite an time consuming solution, 

If you don't have to worry about implementing the locking functionality
(ie multi-threaded/process access) then doing your own VFS is very easy.
   You can just reuse most of the methods from the default VFS.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkku/V0ACgkQmOOfHg372QSQ0QCfeYqlQwCtfCQh2fvik9Qnq0yr
AeAAn07M/mos+yHS3QkSvaWzRCKjWuhj
=4OfS
-END PGP SIGNATURE-
___
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

Re: [sqlite] Loading database from memory buffer

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Brown, Daniel wrote:
> However writing a new VFS would
> seem to be quite an time consuming solution, 

If you don't have to worry about implementing the locking functionality
(ie multi-threaded/process access) then doing your own VFS is very easy.
   You can just reuse most of the methods from the default VFS.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkku/V0ACgkQmOOfHg372QSQ0QCfeYqlQwCtfCQh2fvik9Qnq0yr
AeAAn07M/mos+yHS3QkSvaWzRCKjWuhj
=4OfS
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b

[Michael Ruck]

The given code is correct. The lower-case string is a C# alias for the 
System.String class. System.Int64 is an opaque 64-bit pointer value.

The code so far is correct. What is missing though is the calling 
convention, which by default is cdecl, but .NET doesn't use that one 
by default for P/Invoke. It uses stdcall/winapi as the default calling
convention. Change the CallingConvention in the DllImport line.

Additionally using CharSet=CharSet.Unicode adds an implicit W as per
Win32 calling conventions to the function name. Since the sqlite3_open16
doesn't have that you need to use ExactSpelling to prevent it from adding
that W.

Instead of rolling your own P/Invoke wrapper I'd suggest using one of 
the available .NET wrappers for SQLite.

Mike

> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] Im Auftrag von 
> Sherief N. Farouk
> Gesendet: Donnerstag, 27. November 2008 18:26
> An: 'General Discussion of SQLite Database'
> Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> 
> > I am calling now this:
> > 
> > [DllImport("sqlite3.dll", CharSet = CharSet.Unicode)]
> > internal static extern System.Int64 sqlite3_open16(string 
> > filename, out IntPtr handle);
> > 
> > I now finds an entry point, but i returned to the previous error:
> > An attempt was made to read program in invalid format.
> > 
> > But now everything is 64bit.
> > 
> > Ti Ny
> > 
> 
> No offense, but I'm beginning to believe you don't fully 
> grasp what you're trying to do. First of all, if DW can't 
> find the entry point then it's not there. Second, I don't 
> think the CLR type corresponding to the return value of 
> sqlite3_open16 is System.Int64, and I'm not sure what string 
> is (CLR's string type is String, capital S). How about you 
> upload that DLL of your somewhere, send a link and I wouldn't 
> mind checking it for you.
> 
> - Sherief
> 
> ___
> 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

Re: [sqlite] Window functions

[Elefterios Stamatogiannakis]

I'm using sqlite's count function to emulate OLAP functionality. 
Unfortunately count function is not exposed in sqlite, nevertheless it 
eases the pain of not having analytics functions in sqlite (lead, lag, 
median etc).

lefteris


Alexey Pechnikov wrote:
> Hello!
> 
> В сообщении от Monday 24 November 2008 19:16:46 Constantine Vassil написал(а):
>> OLAP functionality includes the concept of a sliding *window* that moves
>> down
>> through the input rows as they are processed. Additional calculations can
>> be
>>
>> performed on the data in the window as it moves, allowing further analysis
>> in a
>> manner that is more efficient than using semantically equivalent self-join
>> queries, or correlated subqueries.
>>
>> I am thinking this functionality is possible to implement working directly
>> with
>> the B-Tree.
>>
>> Does anyone has better ideas?
> 
> I think application-level solutions (R language for example) is better. I'm 
> using safe tcl 
> interpreter with custom functions and do send input data row by row to it.
> 
> Best regards, Alexey.
> ___
> 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

Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b

[Sherief N. Farouk]

> I am calling now this:
> 
> [DllImport("sqlite3.dll", CharSet = CharSet.Unicode)]
> internal static extern System.Int64 sqlite3_open16(string
> filename, out IntPtr handle);
> 
> I now finds an entry point, but i returned to the previous error:
> An attempt was made to read program in invalid format.
> 
> But now everything is 64bit.
> 
> Ti Ny
> 

No offense, but I'm beginning to believe you don't fully grasp what you're
trying to do. First of all, if DW can't find the entry point then it's not
there. Second, I don't think the CLR type corresponding to the return value
of sqlite3_open16 is System.Int64, and I'm not sure what string is (CLR's
string type is String, capital S). How about you upload that DLL of your
somewhere, send a link and I wouldn't mind checking it for you.

- Sherief

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

Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b

[Ti Ny]

I am calling now this:

[DllImport("sqlite3.dll", CharSet = CharSet.Unicode)]
internal static extern System.Int64 sqlite3_open16(string filename, out 
IntPtr handle); 

I now finds an entry point, but i returned to the previous error:
An attempt was made to read program in invalid format.

But now everything is 64bit.

Ti Ny

> From: [EMAIL PROTECTED]
> To: sqlite-users@sqlite.org
> Date: Thu, 27 Nov 2008 16:37:13 +0100
> Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> 
> 
> New version of DW fixed that. The entry point is not present. All is built in 
> 64bit but there seems to ne no entry point, no functions etc. Everything 
> fails to load.
> I compile sqlite3.c
> 
> Ti Ny
> 
> > Date: Wed, 26 Nov 2008 16:54:56 -0500
> > From: [EMAIL PROTECTED]
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > 
> > On Wed, Nov 26, 2008 at 11:23 AM, Ti Ny <[EMAIL PROTECTED]> wrote:
> > > Dependency walker fails to load that DLL. Also it reports mess between 
> > > dependencies (x86 vs. x64), don't understand why when it
> > > has been compiled as target x64.
> > 
> > If it fails to load, where is this "mess" at?
> > -- 
> > Joel Lucsy
> > "The dinosaurs became extinct because they didn't have a space
> > program." -- Larry Niven
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> _
> Explore the seven wonders of the world
> http://search.msn.com/results.aspx?q=7+wonders+world=en-US=QBRE
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_
Explore the seven wonders of the world
http://search.msn.com/results.aspx?q=7+wonders+world=en-US=QBRE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b

[Ti Ny]

New version of DW fixed that. The entry point is not present. All is built in 
64bit but there seems to ne no entry point, no functions etc. Everything fails 
to load.
I compile sqlite3.c

Ti Ny

> Date: Wed, 26 Nov 2008 16:54:56 -0500
> From: [EMAIL PROTECTED]
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> 
> On Wed, Nov 26, 2008 at 11:23 AM, Ti Ny <[EMAIL PROTECTED]> wrote:
> > Dependency walker fails to load that DLL. Also it reports mess between 
> > dependencies (x86 vs. x64), don't understand why when it
> > has been compiled as target x64.
> 
> If it fails to load, where is this "mess" at?
> -- 
> Joel Lucsy
> "The dinosaurs became extinct because they didn't have a space
> program." -- Larry Niven
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_
Explore the seven wonders of the world
http://search.msn.com/results.aspx?q=7+wonders+world=en-US=QBRE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database file size

[Jens Miltner]

Am 27.11.2008 um 09:12 schrieb Simon Bulman:

> I have been playing around with SQLite to use as an alternative to  
> one of
> our proprietary file formats used to read large amounts of data. Our
> proprietary format performs very badly i.e. takes a long time to  
> load some
> data; as expected SQLite is lighting quick in comparison - great!
>
> One considerable stumbling block is the footprint (size) of the  
> database
> file on disk. It turns out that SQLite is roughly 7x larger than our
> proprietary format - this is prohibitive. The data is pretty simple  
> really,
> 2 tables
>
> Table 1
>
> BIGINT (index),  VARCHAR(30), VARCHAR(10)
>
>
> Table 2
>
> BIGINT (index), FLOAT
>
>
> For a particular data set Table1 has 1165 rows and Table 2 has 323  
> rows,
> however typically Table 2 becomes bigger for larger models. The size  
> on disk
> of this file is 11.8 Mb (compared to 1.7 Mb for our proprietary  
> format). I
> have noticed that if I drop the indexes the size drops dramatically -
> however the query performance suffers to an unacceptable level.
>
> For a larger model the DB footprint is 2.2 Gb compared to 267 Mb for  
> the
> proprietary format.
>
> Does anybody have any comments on this? Are there any configuration  
> options
> or ideas I could use to reduce the footprint of the db file?


I don't think you'll be able to make SQLite as efficient (regarding  
storage size) as a custom file format, because it has to have some  
overhead for indexes, etc.

However, one thing that comes to mind is the way string data is stored:
If you're concerned about disk space an your string data is mostly  
ASCII, make sure your strings are stored as UTF-8 - for ASCII string  
data, this will save you one byte per character in the string data  
storage.
To enforce UTF-8 string storage, execute "PRAGMA encoding='UTF-8'" as  
the first command when creating the database (before you create and  
tables).
You can query the format using "PRAGMA encoding" - UTF-16 encodings  
will store two bytes / character, regardless of the actual characters...

Note that this doesn't mean your database size will shrink to half the  
size - it merely means you'll be able to fit more rows onto a single  
page, thus eventually you should see a decrease in file size when  
comparing UTF-16 vs. UTF-8 databases.

BTW: are you aware that SQLite database won't shrink by themselves?  
You'll have to vacuum them to reclaim unused space (see 
)

HTH,


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

[sqlite] Database file size

[Simon Bulman]

Morning,

 

I have been playing around with SQLite to use as an alternative to one of
our proprietary file formats used to read large amounts of data. Our
proprietary format performs very badly i.e. takes a long time to load some
data; as expected SQLite is lighting quick in comparison - great!

 

One considerable stumbling block is the footprint (size) of the database
file on disk. It turns out that SQLite is roughly 7x larger than our
proprietary format - this is prohibitive. The data is pretty simple really,
2 tables

 

Table 1

BIGINT (index),  VARCHAR(30), VARCHAR(10)

 

Table 2

BIGINT (index), FLOAT

 

For a particular data set Table1 has 1165 rows and Table 2 has 323 rows,
however typically Table 2 becomes bigger for larger models. The size on disk
of this file is 11.8 Mb (compared to 1.7 Mb for our proprietary format). I
have noticed that if I drop the indexes the size drops dramatically -
however the query performance suffers to an unacceptable level.

 

For a larger model the DB footprint is 2.2 Gb compared to 267 Mb for the
proprietary format.

 

Does anybody have any comments on this? Are there any configuration options
or ideas I could use to reduce the footprint of the db file?

 

Many thanks,

Simon

 

 

--

Simon Bulman

Petrel Reservoir Engineering Architect

Schlumberger

Lambourn Court, Wyndyke Furlong,

Abingdon Business Park, Abingdon,

Oxfordshire, OX14 1UJ, UK

Tel: +44 (0)1235 543 401

 

Registered Name: Schlumberger Oilfield UK PLC

Registered Office: 8th Floor, South Quay Plaza 2, 183 Marsh Wall, London.
E14 9SH

Registered in England No. 4157867

 

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

Re: [sqlite] Syntax for sql 'insert' for text with comma

[Martin.Engelschalk]

Hi,

text constants have to be set between single quotes:

insert into sometable values ( 5 , 'text' )

If the text contains a single quote, double it:

insert into sometable values ( 5 , 'Don''t do this' )


Martin

Mauricio schrieb:
> Hi,
>
> I would like to
>
> insert into sometable values ( someid , text ) ;
>
> but 'text' contains a few commas, and then it
> would look like
>
> (...) values ( someid , text , text2 , text3 ) ;
>
> Is there a syntax to do that properly, maybe
> like  C string constants ("text,\"text\",text\n")?
>
> Thanks,
> Maurício
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   

-- 

* Codeswift GmbH *
Traunstr. 30
A-5026 Salzburg-Aigen
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 12120 / 204645
[EMAIL PROTECTED]
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

[sqlite] Syntax for sql 'insert' for text with comma

[Mauricio]

Hi,

I would like to

insert into sometable values ( someid , text ) ;

but 'text' contains a few commas, and then it
would look like

(...) values ( someid , text , text2 , text3 ) ;

Is there a syntax to do that properly, maybe
like  C string constants ("text,\"text\",text\n")?

Thanks,
Maurício

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