[sqlite] Formatting system date and time in TCL

[Anirban Sarkar]

How do I format system date and time to dd-mm- and hh:mm:ss respectively?

Thanks for any response.
Anirban Sarkar

Re: [sqlite] Case-Insensitive Searches

[David Wheeler]

On Jan 28, 2005, at 6:03 PM, David Wheeler wrote:
On the other hand, I don't see why
collation info for a simple column reference can't be
made availabe through a view, since type info already
is available -- but I certainly could be missing
something.
It makes sense to me.
Bug report filed--many thanks!
  http://www.sqlite.org/cvstrac/tktview?tn=1088
Regards,
David

smime.p7s
Description: S/MIME cryptographic signature

[sqlite] Win CE support

[Krzysztof Kowalczyk]

If I was to provide patches for Windows CE support (and maintain win
ce code in the future):

a) would they get accepted (assuming the quality is up to sqlite's
quality) ? Basically I'm asking if it's worthwile for me to spend time
on doing that (it wouldn't if e.g. the patches wouldn't get integrated
into official sources no matter how good they are)

b) what's the best way to make it happen? open a bug and attach diff to it?

Krzysztof Kowalczyk | http://blog.kowalczyk.info

[sqlite] System date and time in Tcl

[Anirban Sarkar]

How do I pick the system date and time in TCL?
Any early help will be appreciated since it is very urgent.

Thanking in advance.

Your's sincerely,
Anirban Sarkar

Re: [sqlite] Join two select field together

[[EMAIL PROTECTED]]

Thank you. That answers my question.
Awesome Sqlite!
Darren Duncan wrote:
At 9:49 PM -0400 1/28/05, [EMAIL PROTECTED] wrote:
Hi All,
I wonder does Sqlite have feature such as join two string fields 
together.
e.g: Select FirstName + LastName From Username
Thank you,
Ming

The '+' is a numerical addition; its result is the sum of 2 numbers; 
that is not what you want.

What you want is '||', the string concatenation operator.
Also, all result fields that are calculations should use AS so that 
their names are reasonable.

Eg: SELECT firstname || ' ' || lastname AS name FROM username
-- Darren Duncan

Re: [sqlite] Case-Insensitive Searches

[David Wheeler]

On Jan 28, 2005, at 12:04 PM, Kurt Welgehausen wrote:
Yes, it seems to me that views should respect the configurations of
their underlying tables, including collations, triggers, and the like.
I'll submit a bug report.
It's not quite that simple. A view column may be an
expression, and inferring attributes for such a
column could be quite complicated. Triggers are
usually associated with updates, and view updatability
is a complex subject itself (SQLite views are not
updatable).
Oh, right. I was thinking of the triggers I apply to views, but then 
those act directly on the underlying tables, so *their* triggers should 
work just fine.

On the other hand, I don't see why
collation info for a simple column reference can't be
made availabe through a view, since type info already
is available -- but I certainly could be missing
something.
It makes sense to me.
   select * from va where lower(b) = 'this'
... Will that use the index, though?
No, not unless something has changed recently.
Gah, pity.
Regards,
David


smime.p7s
Description: S/MIME cryptographic signature

Re: [sqlite] Join two select field together

[Darren Duncan]

At 9:49 PM -0400 1/28/05, [EMAIL PROTECTED] wrote:
Hi All,
I wonder does Sqlite have feature such as join two string fields together.
e.g: Select FirstName + LastName From Username
Thank you,
Ming
The '+' is a numerical addition; its result is the sum of 2 numbers; 
that is not what you want.

What you want is '||', the string concatenation operator.
Also, all result fields that are calculations should use AS so that 
their names are reasonable.

Eg: SELECT firstname || ' ' || lastname AS name FROM username
-- Darren Duncan

[sqlite] Join two select field together

[[EMAIL PROTECTED]]

Hi All,
I wonder does Sqlite have feature such as join two string fields together.
e.g: Select FirstName + LastName From Username
Thank you,
Ming

Re: [sqlite] Case-Insensitive Searches

[Kurt Welgehausen]

> Yes, it seems to me that views should respect the configurations of 
> their underlying tables, including collations, triggers, and the like. 
> I'll submit a bug report.

It's not quite that simple. A view column may be an
expression, and inferring attributes for such a
column could be quite complicated. Triggers are
usually associated with updates, and view updatability
is a complex subject itself (SQLite views are not
updatable). On the other hand, I don't see why
collation info for a simple column reference can't be
made availabe through a view, since type info already
is available -- but I certainly could be missing
something.

> >select * from va where lower(b) = 'this'
>
> ... Will that use the index, though?

No, not unless something has changed recently.

Regards

Re: [sqlite] Case-Insensitive Searches

[David Wheeler]

On Jan 28, 2005, at 10:53 AM, Kurt Welgehausen wrote:
David,
No, you can't specify a collation when creating a
view (http://www.sqlite.org/lang_createview.html).
I don't know whether is was intentional that simple
columns in a view don't get collation info from the
base table, but if you think it's a bug, you can
submit a bug ticket; meanwhile, there are work-
arounds.
Yes, it seems to me that views should respect the configurations of 
their underlying tables, including collations, triggers, and the like. 
I'll submit a bug report.

For sorting, you can specify a collation
   ... order by b collate nocase
If all you need is caseless comparison, you can use
   select * from va where lower(b) = 'this'
That's what I needed, thanks. Will that use the index, though?
If you need a more complicated comparison, you'll
probably have to write a comparison function:
   select * from va where mycomp(b, x) = 0
Thanks for the help.
Regards,
David

Re: [sqlite] Case-Insensitive Searches

[Kurt Welgehausen]

David,

No, you can't specify a collation when creating a
view (http://www.sqlite.org/lang_createview.html).
I don't know whether is was intentional that simple
columns in a view don't get collation info from the
base table, but if you think it's a bug, you can
submit a bug ticket; meanwhile, there are work-
arounds.

For sorting, you can specify a collation

   ... order by b collate nocase

If all you need is caseless comparison, you can use

   select * from va where lower(b) = 'this'

If you need a more complicated comparison, you'll
probably have to write a comparison function:

   select * from va where mycomp(b, x) = 0

Regards

[sqlite] Windows Shares again... (SQLITE 2.8)

[Christian Kienle]

Hi all,
I have a small windows network here. On every computer I install a 
client which should access a sqlite database (the same one).

The sqlite database is located in on a server and the clients connect 
to this server through a windows share. My app tries to open the 
database which is located on the server. But it doens't work.

I use Qt/C++ for this. Qt provides a SQLITE driver which uses (default) 
the sqlite libs 2.8. And I dont want to change that. I know that SQLITE 
3.x is much better but I have to stick with the current version.

So what to do? I am not able to receive an error message with Qt/C++ - 
well I am able but the last error message is empty.
In general it should be possible to do it via a windows share (I asked 
this in a previous post) but well - here it doesn't work :(

So my question: Are there known problems when doing that? Are there 
workarounds?

I am not "allowed" to install a real database server because of several 
reasons.

Thanks in advanced.

Best Regards, Christian Kienle (QtForum.org)

Re: [sqlite] Case-Insensitive Searches

[David Wheeler]

On Jan 26, 2005, at 8:56 AM, David Wheeler wrote:
sqlite> create table a(b text COLLATE nocase);
sqlite> insert into a values ('this');
sqlite> insert into a values ('This');
sqlite> insert into a values ('THIS');
sqlite> select * from a where b = 'this';
this
This
THIS
sqlite>
Hrm, I'f finding that the collation isn't respected by views:
sqlite> select * from va where b = 'this';
b
--
this
How do I get views to respect the collation?
I realized that I left out creation of the view. It was
sqlite> create view va as select b from a;
Do I need a COLLATE expression in the view declaration, too? I couldn't 
get it to work without getting a syntax error:

sqlite> create view va as select b COLLATE nocase from a;
SQL error: near "COLLATE": syntax error
Any help would be greatly appreciated.
Regards,
David

Re: [sqlite] Printing ?

[Will Leshner]

On Fri, 28 Jan 2005 08:46:35 -0500, Richard <[EMAIL PROTECTED]> wrote:
> So, one would need some kind of Report Software,
> to interface wth SQlite?

On-Target Reports supports SQLite:

Re: [sqlite] Printing ?

[Richard]

So, one would need some kind of Report Software,
to interface wth SQlite?
Rick
Clay Dowling wrote:
Richard said:
 

Had a question on printing search results,
to a network printer?
what command would I use for this?
   

lpr would be favorite, but SQLite itself doesn't have any printing
mechanism.  If you dump output to a file in columns you could send that
file to a printer, or you could dump as HTML, view in a browser and send
that to a printer.
Clay Dowling
 

Re: [sqlite] Printing ?

[Clay Dowling]

Richard said:
> Had a question on printing search results,
> to a network printer?
>
> what command would I use for this?

lpr would be favorite, but SQLite itself doesn't have any printing
mechanism.  If you dump output to a file in columns you could send that
file to a printer, or you could dump as HTML, view in a browser and send
that to a printer.

Clay Dowling
-- 
Lazarus Notes from Lazarus Internet Development
http://www.lazarusid.com/notes/
Articles, Reviews and Commentary on web development

RE: [sqlite] Unable to open database file

[Drew, Stephen]

Thanks Clay,

It's definitely Starteam - I used FileMonitor from SysInternals to
identify it.

My database is running on a removable drive, which may be the cause I
guess. If it gets temporarily disconnected or something (or this is
usually displayed on the screen). 

You are right in the last paragraph, that as Starteam is not even
interested in the file, it is unlikely to be the cause, although
empirical evidence suggests otherwise.

I shall keep investigating. Thanks for the help.

Regards,
Steve

-Original Message-
From: Clay Dowling [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 27, 2005 9:42 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Unable to open database file


Drew, Stephen said:
> Yet Starteam (version control system) is still interfering with the 
> file
> - causing unexpected errors while the application is running.  Is this

> because the locking in Windows is below-par in comparison with the 
> Unix locking?  The thing is that Starteam is not even interested in 
> the file, only the directory (I guess it is scanning the file to check

> it's not been added to version control).

First, verify that it's really StarTeam touching the process and fouling
it up.  You might also want to make sure that you're only using
timestamps and not md5 sums to verify file integrity.  Trust me, as a
fellow StarTeam victim, that using md5 sums in StarTeam is not a
reliable way to determine file changes (having nothing to do with md5
and everything to do with StarTeam).

Second, check if this thing is running on a local or network drive. 
SQLite isn't recommended for network drives, and StarTeam most assuredly
isn't suitable for network drives.

The truth is that StarTeam probably isn't causing the problem, since it
isn't interested in writing to the file.  If it is causing the problem,
consider one of the free source control systems.  I've found CVS at
least to be a lot less problematic than StarTeam.

Clay
--
Lazarus Notes from Lazarus Internet Development
http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web
development

Re: [sqlite] Expr error in TCL

[lawrence.chitty]

Hi Anirban

I recently suggested to you 2 very good resources for these sort of questions. 
I really recommend you use them as you will find answers much more forthcomming.

The problem you have is a common problem where numbers with leading 0's are 
interpreted as octal numbers. To solve this, the number can be reformatted to 
remove the 0's . The method recommended on the Tcl'ers Wiki is to use the scan 
command.

e.g.

scan $prv_rd %d prv_rd
set calc [expr $prv_rd+2]

Lawrence

> 
> From: "Anirban Sarkar" <[EMAIL PROTECTED]>
> Date: 2005/01/28 Fri AM 08:25:54 GMT
> To: 
> Subject: [sqlite] Expr error in TCL
> 
> The $prv_rd variable stores backend sqlite data (varchar data type). The data 
> is 874. In order to perform any mathematical operation on this data, I 
> use :
> 
> set calc [expr $prv_rd+2]
> 
> The above expression gives me the following error message :
> 
> Error : expected integer but got "874" ( looks like invalid octal number )
> 
> How do I get rid of this error?
> Any early help will be appreciated.
> 
> Thanks.
> Anirban Sarkar
> 

-
Email provided by http://www.ntlhome.com/

[sqlite] Expr error in TCL

[Anirban Sarkar]

The $prv_rd variable stores backend sqlite data (varchar data type). The data 
is 874. In order to perform any mathematical operation on this data, I use :

set calc [expr $prv_rd+2]

The above expression gives me the following error message :

Error : expected integer but got "874" ( looks like invalid octal number )

How do I get rid of this error?
Any early help will be appreciated.

Thanks.
Anirban Sarkar