Re: [sqlite] Support EXTRACT SQL standard function

2012-02-11 Thread Kit 
2012/2/10 Willian Gustavo Veiga :
> SQLite is a great database to unit test (TDD) applications. You can run it
> in memory with your tests ...
>
> I've found a problem when I was unit testing my application. MySQL
> (production database) supports EXTRACT SQL standard function. SQLite don't
> support it. It would be great to have support in this standard.

http://www.sqlite.org/lang_datefunc.html

> MySQL examples:
>
> Input:
> SELECT EXTRACT(DAY FROM CURRENT_DATE)

mysql> SELECT DAY(CURRENT_DATE);
sqlite> SELECT strftime('%d',CURRENT_DATE);

> Input:
> SELECT EXTRACT(
> MONTH FROM CURRENT_DATE )

mysql> SELECT MONTH(CURRENT_DATE);
sqlite> SELECT strftime('%m',CURRENT_DATE);

> Input:
> SELECT EXTRACT( YEAR FROM CURRENT_DATE )

mysql> SELECT YEAR(CURRENT_DATE);
sqlite> SELECT strftime('%Y',CURRENT_DATE);

> Unfortunately, strftime isn't a solution. It's not a standard.

Function strftime is your solution. Write two models. One for MySQL,
one for SQLite. These databases are quite different and require
different SQL queries.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Support EXTRACT SQL standard function

2012-02-11 Thread Petite Abeille 

On Feb 10, 2012, at 4:00 PM, Willian Gustavo Veiga wrote:

> Unfortunately, strftime isn't a solution. It's not a standard.

Unfortunately, extract isn't supported. strtime is what you can use. 

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

Re: [sqlite] Support EXTRACT SQL standard function

2012-02-11 Thread Simon Slavin 

On 10 Feb 2012, at 3:00pm, Willian Gustavo Veiga wrote:

> I've found a problem when I was unit testing my application. MySQL 
> (production database) supports EXTRACT SQL standard function. SQLite don't 
> support it. It would be great to have support in this standard.

EXTRACT is /not/ a standard.  A couple of SQL products support it, and they're 
not even compatible with each-other.  SQLite has equivalent functions which you 
can find here:



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

[sqlite] Support EXTRACT SQL standard function

2012-02-11 Thread Willian Gustavo Veiga 
SQLite is a great database to unit test (TDD) applications. You can run 
it in memory with your tests ...


I've found a problem when I was unit testing my application. MySQL 
(production database) supports EXTRACT SQL standard function. SQLite 
don't support it. It would be great to have support in this standard.


MySQL examples:

Input:
SELECT EXTRACT(DAY FROM CURRENT_DATE)
Output:
EXTRACT(DAY FROM CURRENT_DATE)
10

Input:
SELECT EXTRACT(
MONTH FROM CURRENT_DATE )
Output:
EXTRACT(MONTH FROM CURRENT_DATE)
2

Input:
SELECT EXTRACT( YEAR
FROM CURRENT_DATE )
Output:
EXTRACT(YEAR FROM CURRENT_DATE)
2012

Unfortunately, strftime isn't a solution. It's not a standard.

Possible EXTRACT reference: 
http://oreilly.com/catalog/sqlnut/chapter/ch04.html#_Toc484170297


Sorry about my English and thank you very much.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] opening places.sqlite from Firefox (10.0.1) profile qAdmin Cannot perform on closed dataset

2012-02-11 Thread Max Vlasov 
On Sat, Feb 11, 2012 at 1:56 PM, Christoph Kukulies wrote:

> I'm trying to open some Firefox files that are located in the profiles
> directory using Sqlite admin
> (http://sqliteadmin.orbmu2k.**de/ ).
>
> I'm getting an error message:
>
> "Cannot perform this operation on a closed dataset."
>
>
This admin is probably made with Delphi, this is a error message of BDE
(Borland Database Engine) that was there forever so you probably should
address this to the developers of the tool. I suppose that for some reason
it could not open the db, but didn't detect it so allowed database controls
and objects accept queries.

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

Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5

2012-02-11 Thread Black, Michael (IS) 


Here's exactly what I did.  You get a different result?



sqlite3 test1.db

create table grocery(a int,b int);

insert into grocery values(1,2);

insert into grocery values(2,3);

.quit



copy test1.db test2.db

copy test1.db test3.db



At this point I have:

02/11/2012  06:53 AM   228 dump.bat
02/11/2012  06:42 AM49 loop.bat
02/11/2012  06:48 AM 2,048 test1.db
02/11/2012  06:48 AM 2,048 test2.db
02/11/2012  06:48 AM 2,048 test3.db



loop test?.db



Then I have

02/11/2012  06:53 AM   228 dump.bat
02/11/2012  06:54 AM70 grocery.sql
02/11/2012  06:42 AM49 loop.bat
02/11/2012  06:54 AM 8 test1.csv
02/11/2012  06:48 AM 2,048 test1.db
02/11/2012  06:54 AM 8 test2.csv
02/11/2012  06:48 AM 2,048 test2.db
02/11/2012  06:54 AM 8 test3.csv
02/11/2012  06:48 AM 2,048 test3.db
   9 File(s)  6,515 bytes
   2 Dir(s)  201,627,729,920 bytes free

D:\SQLite\xx>more test1.csv
1,2
2,3



Using these two batch files

loop.bat

@for %%i in (%1) do @call dump %%i

dump.bat

@del /q %~n1.csv
@if not exist %1 echo No such file: %1
@echo .separator "," >grocery.sql
@echo .output %~n1.csv >>grocery.sql
@echo select * from grocery; >>grocery.sql
@echo .quit >>grocery.sql
@sqlite3 %1 mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Thursday, February 09, 2012 3:04 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5

Let's change them a touch...one too many @'s in loop.bat

loop.bat

@for %%i in (%1) do @call dump %%i

dump.bat

@echo .separator "," >grocery.sql
@echo .output %~n1.csv >>grocery.sql
@echo select * from grocery; >>grocery.sql
@echo .quit >>grocery.sql
@sqlite3 %1 grocery.sql
echo .output %~n1.csv >>grocery.sql
echo select * from grocery; >>grocery.sql echo .quit >>grocery.sql
sqlite3 %1 mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Thursday, February 09, 2012 1:39 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5


On 9 Feb 2012, at 6:28pm, Tim Leland wrote:

> That will work but we have to import the sqlite file into our
AS400/Iseries
> IBM (DB2)system. Easiest way is convert it to a .csv file for the import.
My
> last resort is just run a script on the PC side that will run sqlite3.exe
> shell and convert the sqlite file to a .csv but I want to avoid that step.
> If you know of a way for an as400 to import sqlite files that would be
> great!

Oh, you're that guy.  Okay, I'd forgotten your hosting side was on an AS400.

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
___
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] opening places.sqlite from Firefox (10.0.1) profile qAdmin Cannot perform on closed dataset

2012-02-11 Thread Richard Hipp 
On Sat, Feb 11, 2012 at 4:56 AM, Christoph Kukulies wrote:

> I'm trying to open some Firefox files that are located in the profiles
> directory using Sqlite admin
> (http://sqliteadmin.orbmu2k.**de/ ).
>
> I'm getting an error message:
>
> "Cannot perform this operation on a closed dataset."
>

That is not an error message that SQLite generates.  What program are you
trying to use to "open" the Firefox database files?



>
> I cannot see any security restrictions (Windows 7/64) is the OS.
>
> places.sqlpite has two other accompanying files, places.sqlite-shm and
> places.sqlite-wal,
> for what it's worth.
>
> Any clues?
>
> --
> Christoph
> __**_
> 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] opening places.sqlite from Firefox (10.0.1) profile qAdmin Cannot perform on closed dataset

2012-02-11 Thread Christoph Kukulies 
I'm trying to open some Firefox files that are located in the profiles 
directory using Sqlite admin

(http://sqliteadmin.orbmu2k.de/).

I'm getting an error message:

"Cannot perform this operation on a closed dataset."

I cannot see any security restrictions (Windows 7/64) is the OS.

places.sqlpite has two other accompanying files, places.sqlite-shm and 
places.sqlite-wal,

for what it's worth.

Any clues?

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

[sqlite] Compiling SQLite3 to run on Windows Mobile 6.5

2012-02-11 Thread Andrew Barnes 

>>Would this be able to run on windows mobile or windows ce?

I don't see why not but I've never used windows mobile or ce and I suppose it 
depends on the hardware too.

Maybe I've misunderstood what you are trying to do but it seems to me that 
somewhere in the process that gets your data
into your AS400 you should be able to put a program which reads the database 
and outputs a CSV file. 

It should not be a hard program to write and looks to me like a better option 
than trying to get the shell to do it.

Quite where in the process this best fits depends on how your hardware hangs 
together and how streamlined you want the
user experience to be.

Andy

PS Sorry if these aren't slotting into the threads correctly, I'm new here and 
trying to work out just how to do it.


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

Re: [sqlite] SQLite Random number generator

2012-02-11 Thread Kit 
2012/2/11 Igor Tandetnik :
> On 2/10/2012 10:52 PM, Rick Guizawa wrote:
>> Hi All, how do you generate a random number between two numbers in
>> your query using the random() function? Thank's.
> select random() % (:high  - :low) + :low;
> Igor Tandetnik

sqlite> select random()%(10-5)+5;
2

select abs(random()) % (:high  - :low) + :low;
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users