Re: [sqlite] Inmemory database in sqlite

[Alessandro Marzocchi]

Today, random-access memory takes the form of integrated circuits
. RAM is normally
associated with volatile  types
of memory (such as DRAM  memory modules
), where stored information is lost if
the power is removed, although many efforts have been made to develop
non-volatile RAM chips.

Source: Wikipedia
Il 17/ott/2014 11:48 "Prakash Premkumar"  ha
scritto:

> Hi,
> Does in memory database in sqlite have journal files associated with it ?
> If there's a system failure before an in memory database is backed up ?
> Will there be data loss ? Or Can you kindly tell me how sqlite handles this
> ?
>
>
> Thanks
> Prakash
> ___
> 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] Search query alternatives.

[GB]

Michael,

a Guy named Joe Celko elaborated about trees and hierarchies in SQL a 
few years ago. Have a look here: 
http://www.amazon.com/Hierarchies-Smarties-Edition-Kaufmann-Management/dp/0123877334/ref=dp_ob_title_bk



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

Re: [sqlite] Inmemory database in sqlite

[Igor Tandetnik]

On 10/17/2014 1:00 AM, Prakash Premkumar wrote:

I mean , I have written INSERT statements in to the inmemory database and a
system failure occurs before I dump the data to a file using the SQLite
Backup API, will there be a data loss ?


Of course.


There is no assurance of durablity
for in memory databases right ?


None, naturally. That would defeat the whole purpose - the database 
would no longer be in-memory.

--
Igor Tandetnik

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

Re: [sqlite] Inmemory database in sqlite

[Prakash Premkumar]

Thanks for your reply Igor,

I mean , I have written INSERT statements in to the inmemory database and a
system failure occurs before I dump the data to a file using the SQLite
Backup API, will there be a data loss ? There is no assurance of durablity
for in memory databases right ?

Thanks
Prakash

On Fri, Oct 17, 2014 at 10:27 AM, Igor Tandetnik  wrote:

> On 10/17/2014 12:48 AM, Prakash Premkumar wrote:
>
>> Does in memory database in sqlite have journal files associated with it ?
>>
>
> No. The journal is in memory, too.
>
>  If there's a system failure before an in memory database is backed up ?
>>
>
> What do you mean, backed up? Backed up where and by whom?
> --
> Igor Tandetnik
>
> ___
> 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] Inmemory database in sqlite

[Igor Tandetnik]

On 10/17/2014 12:48 AM, Prakash Premkumar wrote:

Does in memory database in sqlite have journal files associated with it ?


No. The journal is in memory, too.


If there's a system failure before an in memory database is backed up ?


What do you mean, backed up? Backed up where and by whom?
--
Igor Tandetnik

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

[sqlite] Inmemory database in sqlite

[Prakash Premkumar]

Hi,
Does in memory database in sqlite have journal files associated with it ?
If there's a system failure before an in memory database is backed up ?
Will there be data loss ? Or Can you kindly tell me how sqlite handles this
?


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

Re: [sqlite] Getting an unexpected result

[jose isaias cabrera]

"Richard Hipp" wrote...

On Thu, Oct 16, 2014 at 7:16 PM, Keith Medcalf  
wrote:




Although the parser does not care how you format your query, human
readability is improved by using nice formatting.



Indeed, the error would have likely been spotted much faster had the query
been reformatted for readability.

That should perhaps be the first rule of trouble-shooting a query:  First
reformat the SQL so that the spacing and indentation help to elucidate the
intent of the query, not obscure it.


Thank you both.


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

[sqlite] specified store provider cannot be found in the configuration, or is not valid exception

[Ben Lam]

Hi,

Really appreciate any help. I get the exception 'The specified store provider 
cannot be found in the configuration, or is not valid.' on this line in the 
code:

using (var handheldEntities = new HandheldDatabaseOnDesktopEntities())

My environment: VS2010 SP1, WIN 8.1 PRO

Snippet from app.config:



  
  
  

  
  


  
  

  


I am using the Free version of SPAMfighter.
SPAMfighter has removed 805 of my spam emails to date.

Do you have a slow PC? Try 
a free scan!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting an unexpected result

[Richard Hipp]

On Thu, Oct 16, 2014 at 7:16 PM, Keith Medcalf  wrote:

>
> Although the parser does not care how you format your query, human
> readability is improved by using nice formatting.
>

Indeed, the error would have likely been spotted much faster had the query
been reformatted for readability.

That should perhaps be the first rule of trouble-shooting a query:  First
reformat the SQL so that the spacing and indentation help to elucidate the
intent of the query, not obscure it.


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

Re: [sqlite] Getting an unexpected result

[Keith Medcalf]

You said:

  select id, ProjID, PClass, PSubClass, bdate, lang, wDir
From LSOpenJobs
   Where cust = 'PIPA'
 AND fromLang = 'EN-CA'
 AND (lang = 'DE-DE' OR lang = 'PT-BR')
 AND PSubClass LIKE '%-Trans'
  OR PSubClass Like '%-Valid'
 AND (PClass = 'Language' OR PClass = 'Technical')
 AND (bdate BETWEEN '2014-10-01' AND '2014-10-01')
GROUP BY id;

did you perchance mean:

  select id, ProjID, PClass, PSubClass, bdate, lang, wDir
From LSOpenJobs
   Where cust = 'PIPA'
 AND fromLang = 'EN-CA'
 AND (lang = 'DE-DE' OR lang = 'PT-BR')
 AND (PSubClass LIKE '%-Trans' OR PSubClass Like '%-Valid')
 AND (PClass = 'Language' OR PClass = 'Technical')
 AND (bdate BETWEEN '2014-10-01' AND '2014-10-01')
GROUP BY id;

Although the parser does not care how you format your query, human readability 
is improved by using nice formatting.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of jose isaias cabrera
>Sent: Thursday, 16 October, 2014 12:46
>To: General Discussion of SQLite Database
>Subject: [sqlite] Getting an unexpected result
>
>
>Greetings!
>
>I am trying to create a report with this query,
>
>select id, ProjID, PClass, PSubClass, bdate, lang, wDir
>From LSOpenJobs Where
>cust = 'PIPA' AND
>fromLang = 'EN-CA' AND
>(lang = 'DE-DE' OR lang = 'PT-BR') AND
>PSubClass LIKE '%-Trans' OR PSubClass Like '%-Valid' AND
>(PClass = 'Language' OR PClass = 'Technical') AND
>(bdate BETWEEN '2014-10-01' AND '2014-10-01')
>GROUP BY id;
>
>I am getting a bunch of records, where I should not get any.  Where is
>failing is the bdate.  All records are before the bdate.  Any thoughts on
>why?  Thanks.
>
>josé
>
>___
>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] group_concat query performance

[Joe Mistachkin]

Kraijenbrink - FixHet - Systeembeheer wrote:
> 
> Sorry for the delay, I had to solve an server problem first. This is the
> sql query I've been testing with
>

Thanks for the query.  It's difficult to track down performance issues with
System.Data.SQLite without seeing the C# (or VB.NET) example code as there
are a variety of ways to query and process data using it.

Is there any chance we could see the code that is using System.Data.SQLite?

--
Joe Mistachkin

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

Re: [sqlite] Please fix the EBCDIC support

[k]

Hi all,
this is my first reply-to post to this mailing list (using gmane nttp 
interface) so I hope this post passes moderation ok and is correctly 
threaded and not duplicated...).


Regarding the inability to use databases created on EBCDIC systems on 
'normal' systems, my initial thoughts are that sqlite should at least 
create databases with the magic number 'SQLite format 3' correctly - ie 
the magic number should be encoded in the source as a hex literal rather 
than a string.


Regarding codepage conversions, how does this normally work? As Teg said 
(or alluded to), should it not be the application's responsibly to do 
the codepage conversion and pass to the sqlite engine the text in the 
appropriate Unicode encoding (UTF-8/UTF-16) as required. I'm not sure 
though, if there is a distinction here between using the bind functions 
of the API vs supplying literal text in the SQL. ** The documentation on 
the C API does not say a lot about how encodings are  handled for text 
(bound or in the SQL) **. I would expect in any case, if the application 
is locale aware it should respect the settings of the respective LC_* 
environment variables and perform the necessary conversions before 
calling the sqlite functions??


Regarding the specific case of performing EBCDIC<->ASCII conversions on 
zOS, this is (IMHO) not the approach to take, since (outside of the unix 
subsystem of zOS) any EBCDIC codepage can be in use, and as per the 
above, a locale aware application should take care of this, rather than 
leaving it to the sqlite engine.


All the above of course would (again IMHO) be moot for blobs, where 
everything would be stored as is.


On the subject of support for sqlite on zOS, has anyone investigated the 
possibility on making the command line interface, or the engine itself 
(via a custom vfs) support the MVS (record orientated) filesystem?


Please share your thoughts,
Thanks in advance,
k

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

Re: [sqlite] Search query alternatives.

[Michael Falconer]

Thanks Eduardo,

a most interesting link.

On 17 October 2014 05:41, Eduardo Morras  wrote:

> On Thu, 16 Oct 2014 09:05:51 +1100
> Michael Falconer  wrote:
>
> > Hi all,
> >
> > first off I must start with an apology. I know I'm sort of doing the
> > wrong thing here as this question is NOT related to sqlite. It is a
> > general SQL question but I ask it here because I have great respect
> > for the answers and discussions I have seen on this forum over many
> > years. I rarely post myself as there are always several contributors
> > who beat me to the answer and often their response is far better than
> > mine would have been. I'm not a code leper, I don't need the actual
> > SQL just the method really, though a short code example would be well
> > received for illustration.
> >
> > Any thoughts? Suggestions? Missiles? Good approach, bad approach, or
> > completely off the grid? I do use sqlite quite a bit, but not on this
> > particular project.
>
> I point you to sqlite closure extension. It may shows you some ideas for
> tree implementation and parent/child relations under sql/sqlite.
>
> http://www.sqlite.org/src/artifact/636024302cde41b2bf0c542f81c40c624cfb7012
> http://www.sqlite.org/src/finfo?name=ext/misc/closure.c
>
> HTH
>
> >
> > --
> > Regards,
> >  Michael.j.Falconer.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ---   ---
> Eduardo Morras 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting an unexpected result

[jose isaias cabrera]

"jose isaias cabrera" wrote...

Greetings!

I am trying to create a report with this query,

select id, ProjID, PClass, PSubClass, bdate, lang, wDir
From LSOpenJobs Where
cust = 'PIPA' AND
fromLang = 'EN-CA' AND
(lang = 'DE-DE' OR lang = 'PT-BR') AND
PSubClass LIKE '%-Trans' OR PSubClass Like '%-Valid' AND
(PClass = 'Language' OR PClass = 'Technical') AND
(bdate BETWEEN '2014-10-01' AND '2014-10-01')
GROUP BY id;

I am getting a bunch of records, where I should not get any.  Where is 
failing is the bdate.  All records are before the bdate.  Any thoughts on 
why?  Thanks.


Never mind.  My apologies for the bandwidth waste.  I needed one more set of 
().


(PSubClass LIKE '%-Trans' OR PSubClass Like '%-Valid') AND

Igor probably telepathically told me the answer.  He is that good. :-)

jic

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

[sqlite] Getting an unexpected result

[jose isaias cabrera]

Greetings!

I am trying to create a report with this query,

select id, ProjID, PClass, PSubClass, bdate, lang, wDir

From LSOpenJobs Where

cust = 'PIPA' AND
fromLang = 'EN-CA' AND
(lang = 'DE-DE' OR lang = 'PT-BR') AND
PSubClass LIKE '%-Trans' OR PSubClass Like '%-Valid' AND
(PClass = 'Language' OR PClass = 'Technical') AND
(bdate BETWEEN '2014-10-01' AND '2014-10-01')
GROUP BY id;

I am getting a bunch of records, where I should not get any.  Where is 
failing is the bdate.  All records are before the bdate.  Any thoughts on 
why?  Thanks.


josé

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

Re: [sqlite] Search query alternatives.

[Eduardo Morras]

On Thu, 16 Oct 2014 09:05:51 +1100
Michael Falconer  wrote:

> Hi all,
> 
> first off I must start with an apology. I know I'm sort of doing the
> wrong thing here as this question is NOT related to sqlite. It is a
> general SQL question but I ask it here because I have great respect
> for the answers and discussions I have seen on this forum over many
> years. I rarely post myself as there are always several contributors
> who beat me to the answer and often their response is far better than
> mine would have been. I'm not a code leper, I don't need the actual
> SQL just the method really, though a short code example would be well
> received for illustration.
>
> Any thoughts? Suggestions? Missiles? Good approach, bad approach, or
> completely off the grid? I do use sqlite quite a bit, but not on this
> particular project.

I point you to sqlite closure extension. It may shows you some ideas for tree 
implementation and parent/child relations under sql/sqlite.

http://www.sqlite.org/src/artifact/636024302cde41b2bf0c542f81c40c624cfb7012
http://www.sqlite.org/src/finfo?name=ext/misc/closure.c

HTH
 
> 
> -- 
> Regards,
>  Michael.j.Falconer.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] WHERE expression with operators from text functions?

[Tom Holden]

I think you are right. The FTS MATCH looks like it should function the way
I want.

Thanks, Richard! I will now learn how to use FTS...

Tom

On Thu, Oct 16, 2014 at 12:20 PM, Richard Hipp  wrote:

> On Thu, Oct 16, 2014 at 12:03 PM, Tom Holden  wrote:
>
> > Coming up with a subject was a struggle and maybe that indicates an
> > impossibility. Searching the archive was equally fruitless.
> >
>
> Perhaps what you really want is Full Text Search.
> http://www.sqlite.org/fts3.html
>
>
> >
> > What I am trying to do is to build a SELECT with a compound WHERE  using
> > one or more run-time parameters. Sort of like transforming an input
> phrase
> > such as:
> > "string1+string2+string3..."
> > INTO
> > WHERE
> > [Value] LIKE '%string1%'
> > AND
> > [Value] LIKE '%string2%'
> > AND
> > [Value] LIKE '%string3%'
> > AND...
> >
> > This brute force method works:
> > WHERE
> > [Value] LIKE '%'||$SearchString_ONE||'%'
> > AND
> > [Value] LIKE '%'||$SearchString_TWO||'%'
> > ...
> > but requires every parameter to be acted on (filled in or made blank).
> >
> > I can build a statement that produces a desirable looking expression but
> > cannot evaluate it as such with WHERE:
> >
> > SELECT '[Value] LIKE '||'''%'||REPLACE($SearchString, '+', '%'''||' AND
> > [Value] LIKE '||'''%')||'%'''
> > produces
> > [Value] LIKE '%string1%' AND [Value] LIKE '%string2%' AND [Value] LIKE
> > '%string3%'
> >
> > but
> >
> > WHERE (above SELECT...) evaluates to FALSE
> >
> > I need a way to convert the text result to an expression that WHERE
> > evaluates as an expression.
> >
> > Any possibility to do this within SQLite?
> >
> > Tom
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] WHERE expression with operators from text functions?

[Richard Hipp]

On Thu, Oct 16, 2014 at 12:03 PM, Tom Holden  wrote:

> Coming up with a subject was a struggle and maybe that indicates an
> impossibility. Searching the archive was equally fruitless.
>

Perhaps what you really want is Full Text Search.
http://www.sqlite.org/fts3.html


>
> What I am trying to do is to build a SELECT with a compound WHERE  using
> one or more run-time parameters. Sort of like transforming an input phrase
> such as:
> "string1+string2+string3..."
> INTO
> WHERE
> [Value] LIKE '%string1%'
> AND
> [Value] LIKE '%string2%'
> AND
> [Value] LIKE '%string3%'
> AND...
>
> This brute force method works:
> WHERE
> [Value] LIKE '%'||$SearchString_ONE||'%'
> AND
> [Value] LIKE '%'||$SearchString_TWO||'%'
> ...
> but requires every parameter to be acted on (filled in or made blank).
>
> I can build a statement that produces a desirable looking expression but
> cannot evaluate it as such with WHERE:
>
> SELECT '[Value] LIKE '||'''%'||REPLACE($SearchString, '+', '%'''||' AND
> [Value] LIKE '||'''%')||'%'''
> produces
> [Value] LIKE '%string1%' AND [Value] LIKE '%string2%' AND [Value] LIKE
> '%string3%'
>
> but
>
> WHERE (above SELECT...) evaluates to FALSE
>
> I need a way to convert the text result to an expression that WHERE
> evaluates as an expression.
>
> Any possibility to do this within SQLite?
>
> Tom
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] WHERE expression with operators from text functions?

[Tom Holden]

Coming up with a subject was a struggle and maybe that indicates an
impossibility. Searching the archive was equally fruitless.

What I am trying to do is to build a SELECT with a compound WHERE  using
one or more run-time parameters. Sort of like transforming an input phrase
such as:
"string1+string2+string3..."
INTO
WHERE
[Value] LIKE '%string1%'
AND
[Value] LIKE '%string2%'
AND
[Value] LIKE '%string3%'
AND...

This brute force method works:
WHERE
[Value] LIKE '%'||$SearchString_ONE||'%'
AND
[Value] LIKE '%'||$SearchString_TWO||'%'
...
but requires every parameter to be acted on (filled in or made blank).

I can build a statement that produces a desirable looking expression but
cannot evaluate it as such with WHERE:

SELECT '[Value] LIKE '||'''%'||REPLACE($SearchString, '+', '%'''||' AND
[Value] LIKE '||'''%')||'%'''
produces
[Value] LIKE '%string1%' AND [Value] LIKE '%string2%' AND [Value] LIKE
'%string3%'

but

WHERE (above SELECT...) evaluates to FALSE

I need a way to convert the text result to an expression that WHERE
evaluates as an expression.

Any possibility to do this within SQLite?

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

Re: [sqlite] Please fix the EBCDIC support

[Teg]

Hello John,

I  feed  everything  to Sqlite in UTF-8.  If it's coming from Windows,
that  means  I have to do a UTF-16 to UTF-8 conversion.  I know Sqlite
has UTF-16 support but, I want things to be consistent across all OS's
I  work  with.  The  less  I have to think about things like this, the
better. 

C


Thursday, October 16, 2014, 10:49:52 AM, you wrote:

JM> On Thu, Oct 16, 2014 at 9:09 AM, Richard Hipp  wrote:
>> On Thu, Oct 16, 2014 at 9:53 AM, John McKown 
>> wrote:
>>
>>> On Wed, Sep 24, 2014 at 9:46 PM, Richard Hipp  wrote:
>>> > Please try the latest version of SQLite on trunk to see if that works
>>> > better.  Specifically, apply the patch at
>>> >
>>> > http://www.sqlite.org/src/vpatch?from=b2c89ef49cd1=ef30e0352b3d
>>> >
>>> > --
>>> > D. Richard Hipp
>>> > d...@sqlite.org
>>>
>>> Unfortunately, the ASCII vs. EBCDIC issued _does_ make it
>>> impossible to share a single SQLite data base file between z/OS and
>>> other ASCII-based SQLite systems.
>>
>>
>> If you store content in EBCDIC, it is retrieved in EBCDIC and if you store
>> content in ASCII it is retrieved in ASCII, regardless of which platform you
>> do the storing and retrieving on.  I wonder if this is the right approach.
>> Perhaps the zOS patch should be amended to simply transform EBCDIC->ASCII
>> on input and ASCII->EBCDIC on output.
>>

JM> Hum, I'll need to look more closely at the code to see where this
JM> would need to be implemented. As I indicated previously, making it
JM> work on z/OS was so easy that I didn't need to really look closely at
JM> the code itself. I basically compiled and it worked. And this was my
JM> first attempt to port something to the z/OS environment, so I was not
JM> very knowledgeable about it. I am somewhat more knowledgeable today
JM> due to some other porting work that was somewhat more involved. I
JM> guess that instead of saying ASCII<->EBCDIC, I need to say more
JM> exactly ISO8859-1<->IBM-1047 since there are many "ASCII' and "EBCDIC"
JM> code pages. Or does SQLite use UTF-8 internally? Hum, something else
JM> to look into. I am not really that familiar with SQLite's internals.
JM> Thanks for the thoughts. I appreciate your help. If I have more
JM> questions, I guess that I would go over to the sqlite-dev forum.

>>
>>
>>> I haven't looked closely enough at
>>> the code to see if the big-endian (z/OS) vs. little-endian (Intel)
>>> storage of integers would also be a problem.
>>>
>>>
>> We do cross-platform database portability tests between x86, sparc, and
>> PPC, to verify that big-endian vs little-endian is not a factor.

JM> Thanks for telling me that. I won't worry about it again.

>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users






-- 
 Tegmailto:t...@djii.com

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

Re: [sqlite] Please fix the EBCDIC support

[John McKown]

On Thu, Oct 16, 2014 at 9:09 AM, Richard Hipp  wrote:
> On Thu, Oct 16, 2014 at 9:53 AM, John McKown 
> wrote:
>
>> On Wed, Sep 24, 2014 at 9:46 PM, Richard Hipp  wrote:
>> > Please try the latest version of SQLite on trunk to see if that works
>> > better.  Specifically, apply the patch at
>> >
>> > http://www.sqlite.org/src/vpatch?from=b2c89ef49cd1=ef30e0352b3d
>> >
>> > --
>> > D. Richard Hipp
>> > d...@sqlite.org
>>
>> Unfortunately, the ASCII vs. EBCDIC issued _does_ make it
>> impossible to share a single SQLite data base file between z/OS and
>> other ASCII-based SQLite systems.
>
>
> If you store content in EBCDIC, it is retrieved in EBCDIC and if you store
> content in ASCII it is retrieved in ASCII, regardless of which platform you
> do the storing and retrieving on.  I wonder if this is the right approach.
> Perhaps the zOS patch should be amended to simply transform EBCDIC->ASCII
> on input and ASCII->EBCDIC on output.
>

Hum, I'll need to look more closely at the code to see where this
would need to be implemented. As I indicated previously, making it
work on z/OS was so easy that I didn't need to really look closely at
the code itself. I basically compiled and it worked. And this was my
first attempt to port something to the z/OS environment, so I was not
very knowledgeable about it. I am somewhat more knowledgeable today
due to some other porting work that was somewhat more involved. I
guess that instead of saying ASCII<->EBCDIC, I need to say more
exactly ISO8859-1<->IBM-1047 since there are many "ASCII' and "EBCDIC"
code pages. Or does SQLite use UTF-8 internally? Hum, something else
to look into. I am not really that familiar with SQLite's internals.
Thanks for the thoughts. I appreciate your help. If I have more
questions, I guess that I would go over to the sqlite-dev forum.

>
>
>> I haven't looked closely enough at
>> the code to see if the big-endian (z/OS) vs. little-endian (Intel)
>> storage of integers would also be a problem.
>>
>>
> We do cross-platform database portability tests between x86, sparc, and
> PPC, to verify that big-endian vs little-endian is not a factor.

Thanks for telling me that. I won't worry about it again.

>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
The temperature of the aqueous content of an unremittingly ogled
culinary vessel will not achieve 100 degrees on the Celsius scale.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please fix the EBCDIC support

[Richard Hipp]

On Thu, Oct 16, 2014 at 9:53 AM, John McKown 
wrote:

> On Wed, Sep 24, 2014 at 9:46 PM, Richard Hipp  wrote:
> > Please try the latest version of SQLite on trunk to see if that works
> > better.  Specifically, apply the patch at
> >
> > http://www.sqlite.org/src/vpatch?from=b2c89ef49cd1=ef30e0352b3d
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
>
> Unfortunately, the ASCII vs. EBCDIC issued _does_ make it
> impossible to share a single SQLite data base file between z/OS and
> other ASCII-based SQLite systems.


If you store content in EBCDIC, it is retrieved in EBCDIC and if you store
content in ASCII it is retrieved in ASCII, regardless of which platform you
do the storing and retrieving on.  I wonder if this is the right approach.
Perhaps the zOS patch should be amended to simply transform EBCDIC->ASCII
on input and ASCII->EBCDIC on output.



> I haven't looked closely enough at
> the code to see if the big-endian (z/OS) vs. little-endian (Intel)
> storage of integers would also be a problem.
>
>
We do cross-platform database portability tests between x86, sparc, and
PPC, to verify that big-endian vs little-endian is not a factor.

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

Re: [sqlite] Please fix the EBCDIC support

[John McKown]

On Wed, Sep 24, 2014 at 9:46 PM, Richard Hipp  wrote:
> Please try the latest version of SQLite on trunk to see if that works
> better.  Specifically, apply the patch at
>
> http://www.sqlite.org/src/vpatch?from=b2c89ef49cd1=ef30e0352b3d
>
> --
> D. Richard Hipp
> d...@sqlite.org

Dr. Hipp,

I did the port of SQLite to z/OS mentioned by the OP. Sorry it took me
so long to test the above fix out. I downloaded the latest 3.8.7
amalgamation source and applied that patch to it (actually I looked at
what it did and hand edited the files myself). This did indeed fix the
compile problem. I hope to be able to do some testing and get your
excellent code available to other z/OS users as soon as possible. I
don't really have a date in mind.

I will also mention that the z/OS xlc compiler has a "problem" with
the ./configure script. On z/OS 2.1, the script detects that the
fpurge() function exists. But this function did not exist in earlier
versions of z/OS. So if, like me, you want to target a prior release
(like targeting a lower level kernel that the one you're running on),
./configure sets the HAVE_POSIX_FALLOCATE and so the code precompiles
to use fpurge(), but the compiler itself complains that the code is
invalid, due to targeting the back level z/OS versions. I have found
an easy work around. I simply put the phrase
"-DHAVE_POSIX_FALLOCATE=0" in the CFLAGS used by ./configure. This
allows a clean compile which does _not_ use the fpurge().

Again my thanks for your excellent work. I did not really have to do
_anything_, code wise, to get your program to run on z/OS in an EBCDIC
environment. Unfortunately, the ASCII vs. EBCDIC issued _does_ make it
impossible to share a single SQLite data base file between z/OS and
other ASCII-based SQLite systems. I haven't looked closely enough at
the code to see if the big-endian (z/OS) vs. little-endian (Intel)
storage of integers would also be a problem.

-- 
The temperature of the aqueous content of an unremittingly ogled
culinary vessel will not achieve 100 degrees on the Celsius scale.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Granularity of Locks in sqlite

[Prakash Premkumar]

Hi,

>From what I understand  from reading the followig doc:
http://www.sqlite.org/lockingv3.html

sqlite supports only file level locking.Is there any attempts to improve
the granularity of locking to table level or row level ?

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

Re: [sqlite] UPDATE Help

[Shantanu Namjoshi]

Thank you Simon. Will do. 
Shantanu

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Tuesday, October 14, 2014 11:27 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] UPDATE Help

On 15 Oct 2014, at 1:10am, Shantanu Namjoshi 
 wrote:

> ALTER TABLE dailydelete ADD COLUMN SMB varchar(11);

If you find yourself doing things like this your schema is messed up.  SMB is 
obviously data, not a column.  Redesign your table so that HML, SMB, UMD and Rf 
are values in a column, not the names of columns.

By the way, SQLite doesn't have a varchar type.  Your data will be interpreted 
as TEXT, and will not be truncated to 11 characters.  It would be better just 
to declare the column as TEXT.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://cp.mcafee.com/d/5fHCNEedEI8IIIcK6zCVKVJAsCVssqekjr8VdOXP3NJAsCVssqemjoUsej79II6WgRkhHgD8546WixZasTqI8tW3pIwTikfFjCXlx3LgrdzH6zDQ6m7-LO8VxMsDvHTbFIL6zDCkjhOy-UVORQr8FGTKVOEuvkzaT0QSyrhdTWVEVs73C4jhOO-rKr01Q5zVj_w0ajRTHU0xsxa62qKMM-l9OwXna2NYF-nrFYq6W2NYF-nrFYq5O5mUm-waEb7OD_00jrbBNNI5-Aq83iTgmfBfPiWq80C5wUg18Qq80LVWNJdwTvf_-a2
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE Help

[Shantanu Namjoshi]

Thank you sir. Sometimes my brain freezes are unbelievable!

Shantanu


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Tuesday, October 14, 2014 11:27 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] UPDATE Help

On Tue, Oct 14, 2014 at 8:10 PM, Shantanu Namjoshi <
shantanu.namjo...@business.uconn.edu> wrote:

>
> UPDATE dailydelete
> SET SMB = (SELECT dailyfactors.SMB FROM dailyfactors WHERE
> dailydelete.data = dailyfactors.Date),
> SET Rf = (SELECT dailyfactors.Rf FROM dailyfactors WHERE dailydelete.data
> = dailyfactors.Date)
>
> WHERE EXISTS (select * from dailyfactors WHERE dailydelete.data =
> dailyfactors.Date);
>
>
> I get the following error:
> Error: near "SET": syntax error
>

On the syntax diagram 
(http://cp.mcafee.com/d/2DRPoQ76Qm4mmm6hO8UTsSOejsKed7a9JAsCVtVxUSOejsKed7b9Ise79zASm3t8qG8REjA2y3t9g-BerJm4eZ1ISgrFa7QFPtGMxTEdCT_d7fEcIfZvAQTS4kuLsKCO-DP2bPXPDbnjIyyHtNfBgY-F6lK1FJ4SCrLRPhOUe7c8CzBBYTsS02Eb7OD_00sfS14lrmgSvY3zt1o-k_bJQ-d2V2Hsbvg5k5zVj_w09JAQsTjdwLQzh0qmW2NYF-qnjh04MI72096zh05_fmdFI6OETA)
 the SET
keyword only occurs once.


--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://cp.mcafee.com/d/5fHCNEgdEI8IIIczAhNKVJAsCVssqekjr8VdOXP3NJAsCVssqemjoUsej79II6WgRkhHgD8546WixZasTqI8tW3pIwTikfFjCXlx3LgrdL-qevgpovW_9FLI8EZuVtdBZfC4nDTDemKDp55mXyvaxVZicHs3jqpJcTvHCzBMseohd7bbVKVI07gmfBf-00FfnuLw25O4Eo9GX33VkDa3JsEb7ODVtKDNErEb7ODVtKDNEn8lrxrW0GwIvavY01dICzCWpI5-Aq83iTgmfBfPiWq80C5wUg18Qq80LVWNJdwSbE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unable to prepare a statement

[Sam Carleton]

Yes, that was the case.  The app has two databases, a system wide DB and
the active data DB.  The system points to the active data DB but wasn't
configured to point to the active data.  Live and learn:)

Pax vobiscum,
Sam Carleton

On Wed, Oct 15, 2014 at 6:01 AM, Dan Kennedy  wrote:

> On 10/15/2014 07:19 AM, Sam Carleton wrote:
>
>> When I use the SQLite Manager, I am able to run this query just fine:
>>
>>  UPDATE EventNode
>> SET IsActive = 1
>>   WHERE EventNodeId IN (SELECT w.EventNodeId
>>   FROM EventNode as w, EventNode as m on
>> m.objectId = 'a09f0f8a-a37c-44c2-846f-16a59b1c34c1'
>>   WHERE w.lft BETWEEN m.lft AND m.rgt )
>>
>> But when I try to prepare the same statement to be used with my C++ code:
>>
>>  const char * updateString =
>>  "UPDATE EventNode "
>> "SET IsActive = @isActive "
>>   "WHERE EventNodeId IN (SELECT w.EventNodeId "
>>   "FROM EventNode AS w, EventNode AS m ON
>> m.objectId = @objectId "
>>  "WHERE w.lft BETWEEN m.lft AND m.rgt)";
>>
>> I get an error where sqlite3_errmsg() returns: no such table: EventNode
>>
>> Now the code that is opening the DB is in a base class which is used other
>> places to access the EventNode table, so I am a bit mystified as to what
>> exactly is going on.  Any thoughts?
>>
>
> Perhaps it's opening a different database file.
>
> ___
> 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] sqlite Query Optimizer

[Simon Slavin]

On 16 Oct 2014, at 11:07am, Prakash Premkumar  wrote:

> Are there any projects where , sqlite optimizer has been extended to add
> System R

The optimizer currently built into SQLite does the same job as what you're 
thinking of as "System R".  It is not possible to use both together.

You should be aware that the System R paper is now 40 years old.  We have got 
considerably better and learned considerably more about databases since System 
R was devised.  My guess is that if you replaced the current SQLite 
optimization system with System R SQLite would get worse, not better.

> or other algorithms ?

I will let the developers answer that.

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

Re: [sqlite] sqlite Query Optimizer

[Prakash Premkumar]

Thanks a lot Simon.

Are there any projects where , sqlite optimizer has been extended to add
System R or other algorithms ?
I browsed through the github repository and could not find any.

Thanks a lot for your time.

Regards
Prakash

On Thu, Oct 16, 2014 at 12:49 PM, Simon Slavin  wrote:

>
> On 16 Oct 2014, at 7:50am, Prakash Premkumar 
> wrote:
>
> > Does sqlite implement the pointers in the System R Algorithm ?
>
> SQLite does not implement the System R Algorithm, so no, it doesn't
> implement the pointers from System R.  It implements the algorithms
> described in the two references just cited.  Those algorithms have some
> things in common with System R but are not the same as it.
>
> > Like
> > assigning selectivity factors for predicates in where clause?
>
> SQLite does something equivalent to this.  Read about the ANALYZE command
> which is documented on the SQLite web site, and examine the type of data it
> puts in the tables it creates.  The ANALYZE command is discussed in the two
> references just cited.
>
> You've asked some detailed questions on this list which could be answered
> by reading the documentation available to on the SQLite web site.  It might
> be worth trying to get your answers from the web site first, and if you
> still want to post, citing the pages you've already read so we don't point
> them out to you in our own answers.
>
> Simon.
> ___
> 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] group_concat query performance

[Kraijenbrink - FixHet - Systeembeheer]

Hi Joe,

Sorry for the delay, I had to solve an server problem first. This is the sql 
query I've been testing with

SELECT 
GROUP_CONCAT(Parent.fkintFolderID,'\') FilePath 
FROM tblFolderNestedSets Node
,tblFolderNestedSets Parent
WHERE 
Node.intLeft 
BETWEEN 
Parent.intLeft AND Parent.intRight 
AND 
Parent.fkintSessionID = Node.fkintSessionID 
AND 
Node.fkintSessionID =  1817 
AND 
Node.fkintFolderID  = 1937926;

And this is the table:

CREATE TABLE tblFolderNestedSets (
  pkintFolderNestedSetID  integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  fkintSessionID  integer NOT NULL,
  fkintFolderID   integer NOT NULL,
  intLeft integer,
  intRightinteger
);

It runs perfectly with the C++ test sample and very slow on ADO.Net SQLite 
library. I don't know why.

Thank you for your time and effort.

With regards,

Peter


>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
>On Behalf Of Joe Mistachkin
>Sent: maandag 13 oktober 2014 20:31
>To: 'General Discussion of SQLite Database'
>Subject: Re: [sqlite] group_concat query performance
>
>
>Kraijenbrink - FixHet - Systeembeheer wrote:
>>
>> 1. "SQLitePerfTest - C++ " runs very fast. 50.000 queries in 8 or 9
>seconds;
>>
>> 2. "SQLitePerfTest - VB.net  runs very fast. 50.000 queries in 7 or 8
>seconds.
>>(Without the GROUP_CONCAT function that is;)
>> 
>> 3. "SQLitePerfTest - VB.net  which runs slow, 50.000 queries in 5 or 6
>minutes;  
>> 
>
>Are you able to share the query and the schema of the database involved?
>
>If you have sample code, that might reveal important details as well.
>
>--
>Joe Mistachkin
>
>___
>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] sqlite Query Optimizer

[Simon Slavin]

On 16 Oct 2014, at 7:50am, Prakash Premkumar  wrote:

> Does sqlite implement the pointers in the System R Algorithm ?

SQLite does not implement the System R Algorithm, so no, it doesn't implement 
the pointers from System R.  It implements the algorithms described in the two 
references just cited.  Those algorithms have some things in common with System 
R but are not the same as it.

> Like
> assigning selectivity factors for predicates in where clause?

SQLite does something equivalent to this.  Read about the ANALYZE command which 
is documented on the SQLite web site, and examine the type of data it puts in 
the tables it creates.  The ANALYZE command is discussed in the two references 
just cited.

You've asked some detailed questions on this list which could be answered by 
reading the documentation available to on the SQLite web site.  It might be 
worth trying to get your answers from the web site first, and if you still want 
to post, citing the pages you've already read so we don't point them out to you 
in our own answers.

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

Re: [sqlite] sqlite Query Optimizer

[Prakash Premkumar]

Thanks for your reply Simon.
I have read through those links.

Does sqlite implement the pointers in the System R Algorithm ? Like
assigning selectivity factors for predicates in where clause?
Link to System R Algorithm:
https://www.cs.duke.edu/courses/spring03/cps216/papers/selinger-etal-1979.pdf

Thanks a lot for your time.

Regards
Prakash

On Wed, Oct 15, 2014 at 5:36 PM, Simon Slavin  wrote:

>
> On 15 Oct 2014, at 12:54pm, Prakash Premkumar 
> wrote:
>
> > I'm trying to understand the sqlite select query optimizer. It works by
> > assigning costs to each relation in FROM clause.
>
> That is only a little bit of how it works.  Have you read these ?
>
> 
> 
>
> Simon.
> ___
> 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