Re: [sqlite] Import feature requests

[Dan Bishop]

Simon Slavin wrote:
> On 14 Dec 2009, at 5:13am, Walter Dnes wrote:
>
>   
>>  The following might be options (compile time, config file, set manually;
>> I don't care), but they should be available...
>> 
>
> It might be worth writing a separate sqlite3 import facility which just reads 
> a .csv into a table.  It could have some command-line switches (options ?) or 
> the first time it comes across an ambiguous value it could stop and ask the 
> user what the user wants.
>   
http://www.mail-archive.com/sqlite-users@sqlite.org/msg46507.html

Feel free to modify the code as needed.

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

Re: [sqlite] Import feature requests

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Simon Slavin wrote:
> It might be worth writing a separate sqlite3 import facility which just reads 
> a .csv into a table.

I betcha did not know there are at least 4 different implementations of CSV
as a virtual table (2 in the wiki, one in the SQLite source, and one by our
prolific extension writer friend in Russia!)

They all have problems.  They also all work correctly on the data set the
authors wanted them to.  There are a huge number of issues with trying to do
this that will work in all cases for everyone.  (Encodings, locales,
quoting, delimiter merging, column affinity, value affinity etc).

Beyond simple needs the importer really needs to write some code.  Sometimes
they can do it with SQL (eg converting strings to null) but in other cases
it requires real code.  Every popular scripting language out there has
SQLite bindings so it is not that hard and it will at least ensure the
resulting data is as expected by the author.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAksl4qIACgkQmOOfHg372QSlLQCgppsDLOx2Zv2bVufHvycLLrci
NL8AmwSrtn13Iokg3G22sNyiBIMyrgiw
=TtuA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Import feature requests

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Walter Dnes wrote:
> 1) import with strict typing.  If I create a table with 3 numeric (real.
> integer, whatever) fields, then a CSV file containing...
> 
>2.345,  42,  27.7
> 
> should import as 3 numbers, not as 3 character strings

What makes you think it doesn't?

My test file is one line:  2.345,42,27.7
(Note no spaces etc)

sqlite> .mode csv
sqlite> create table foo(a real, b int, c double);
sqlite> .import t.csv foo
sqlite> select typeof(a), typeof(b), typeof(c) from foo;
real,integer,real

> 2) import adjacent commas in a CSV file as NULL, not as a zero-length
> string.  

null and zero length strings have *very* different semantics.

>a) if it can't read my mind, I need to be able to tell it what I want
> 
>b) why would I want a "zero-length string" to behave any differently
>   from NULL?

If you have to ask the question then you really don't understand the issue!

>c) why on earth would I want a "zero-length string" in an *INTEGER*
>   or *REAL* field???  That is a totally insane default.

It isn't a default and SQLite uses manifest typing.  The column types are
hints, *not* requirements.  You may not like this but IMHO it is by far one
of the best features.

Going back to point (a), this is what you do.

- - Import into a temporary table

- - Copy the data into your permanent table modifying it as needed:

INSERT INTO permtable SELECT a,b,c from temptable

If you want to force a to be real then replace it with cast(a as REAL).  If
you want to turn zero length strings into nulls then replace it with:

  CASE a WHEN '' THEN null ELSE a END

A longer example:

INSERT INTO permtable SELECT
   CASE a WHEN trim(a)='' THEN null ELSE cast(a as REAL) END,
   CASE b WHEN trim(b)='' THEN null ELSE cast(b as INTEGER) END,
   CASE c WHEN trim(c)='' THEN null ELSE cast(c as DOUBLE) END
 FROM temptable;

This turns any amount of whitespace into null.  The cast function is also
more lenient - for example it will convert a number surrounded by white
space into a number - cast('   3.4' as real) - whereas column affinity
rules will not as doing so loses information (the space padding).  (Note
that cast won't even error on invalid input - cast('  3.4 hello' as real) -
returns the number 3.4.

Hint:  If you feel the need to get aggressive and abusive when posting then
you probably missed something!  If SQLite was useless, someone else would
have noticed by now.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAksl27gACgkQmOOfHg372QQl/QCfagur/5lU0pLbBjRpKe+jw1Wy
f+UAoKvODl2ki9yzSwjuhYu+4sDHIIjr
=JPnN
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Import feature requests

[Simon Slavin]

On 14 Dec 2009, at 5:13am, Walter Dnes wrote:

>  The following might be options (compile time, config file, set manually;
> I don't care), but they should be available...

It might be worth writing a separate sqlite3 import facility which just reads a 
.csv into a table.  It could have some command-line switches (options ?) or the 
first time it comes across an ambiguous value it could stop and ask the user 
what the user wants.

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

[sqlite] Import feature requests

[Walter Dnes]

  The following might be options (compile time, config file, set manually;
I don't care), but they should be available...

1) import with strict typing.  If I create a table with 3 numeric (real.
integer, whatever) fields, then a CSV file containing...

   2.345,  42,  27.7

should import as 3 numbers, not as 3 character strings

2) import adjacent commas in a CSV file as NULL, not as a zero-length
string.  As for the argument that SQLite can't read my mind...

   a) if it can't read my mind, I need to be able to tell it what I want

   b) why would I want a "zero-length string" to behave any differently
  from NULL?

   c) why on earth would I want a "zero-length string" in an *INTEGER*
  or *REAL* field???  That is a totally insane default.

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

Re: [sqlite] SQLite version 3.6.21

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Walter Dnes wrote:
> Possibly a stupid question from a relative newbie; shouldn't that be...
> 
> sqlite3 :memory: 'create table test(fieldname integer)'

integer is not a reserved word so it is perfectly valid as a column name,
but I suspect the text was just intended to illustrate the problem anyway.

However you can use reserved words too if you quote them:

  create table foo([create], "table");

Both forms of quoting work.  A common mistake is to use double quotes around
strings - they quote SQL identifiers but sometimes work for strings.

And just to blow your mind, SQLite supports zero length names too!

  create table ""("");

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkslxLkACgkQmOOfHg372QSuvQCfc7wZIPO554GIjKfP4xRqI1rv
TrUAnRtJAhQsIs2BNFEbuH6MiU68f216
=HnGy
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.6.21

[Simon Slavin]

On 14 Dec 2009, at 4:38am, Walter Dnes wrote:

> On Tue, Dec 08, 2009 at 02:41:23AM +0100, Andreas Schwab wrote
> 
>> It's still crashing due to undefined behaviour.
>> 
>> $ ./sqlite3 :memory: 'create table test(integer)'
>> Segmentation fault
> 
> Possibly a stupid question from a relative newbie; shouldn't that be...
> 
> sqlite3 :memory: 'create table test(fieldname integer)'

You're right in terms of SQL.  But a programmer would say that no program 
should cause a segmentation fault: it should instead spit out a useful error 
message which explains what went wrong.

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

Re: [sqlite] SQLite version 3.6.21

[Walter Dnes]

On Tue, Dec 08, 2009 at 02:41:23AM +0100, Andreas Schwab wrote

> It's still crashing due to undefined behaviour.
> 
> $ ./sqlite3 :memory: 'create table test(integer)'
> Segmentation fault

Possibly a stupid question from a relative newbie; shouldn't that be...

sqlite3 :memory: 'create table test(fieldname integer)'

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

Re: [sqlite] Using Parameters with views

[Cariotoglou Mike]

I thought of that, and the answer is NO.
actually, since my post, I did a little investigation :
it is not the view that is the problem, it is the parameter when used on a 
column that contains
the COUNT function. Weird, it seems like a genuine bug.

if you replace 

WHERE CNT LIKE :PARAM

in my original code, or in your example, it will work !

I tried this : (the subselect was originally the view)

select * from
(
select
*,(select count(*) from ITEM_ARTIST where ARTIST_id=artists.artist_id) CNT
from ARTISTS
) where cnt like :a

and it works.
changing the operator from "LIKE" to "=" breaks it.


-Original Message-
From: sqlite-users-boun...@sqlite.org on behalf of Darren Duncan
Sent: Mon 12/14/2009 2:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Using Parameters with views
 
Do bind parameters work for you if you replace the view with a subselect?

Does this work for you?

select * from (
   select t1.*,
   (select count(*) from song_artist t2 where t1.artist_id=t2.artist_id) as CNT
   from artists
) where CNT=:PARAM

-- Darren Duncan

Cariotoglou Mike wrote:
> I don't know if this has come up before, is so please point me to the
> right direction :)
> 
> I believe that using parameterized queries with views does not work
> as expected.
> 
> consider this (more or less self-explanatory) schema:
> 
> create table artists(artist_id)
> create table songs(song_id)
> create table song_artist(song_id,artist_id)
> 
> create view VARTISTS as
>  select t1.*,
>  (select count(*) from song_artist t2 where t1.artist_id=t2.artist_id)
> as CNT
>  from artists
>  
>  The sql may be a little off as I am typing this from scratch, but you
> get the
>  idea.
>  
>  now, if I do this:
>  
>  select * from VARTISTS where CNT=10
>  
>  it works.
>  
>  This, however, always returns an empty result set :
>  
>  select * from VARTISTS where CNT=:PARAM,
>  
>  for any value I bind to :PARAM.
>  
>  is this known/documented ?

___
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] Using Parameters with views

[Darren Duncan]

Do bind parameters work for you if you replace the view with a subselect?

Does this work for you?

select * from (
   select t1.*,
   (select count(*) from song_artist t2 where t1.artist_id=t2.artist_id) as CNT
   from artists
) where CNT=:PARAM

-- Darren Duncan

Cariotoglou Mike wrote:
> I don't know if this has come up before, is so please point me to the
> right direction :)
> 
> I believe that using parameterized queries with views does not work
> as expected.
> 
> consider this (more or less self-explanatory) schema:
> 
> create table artists(artist_id)
> create table songs(song_id)
> create table song_artist(song_id,artist_id)
> 
> create view VARTISTS as
>  select t1.*,
>  (select count(*) from song_artist t2 where t1.artist_id=t2.artist_id)
> as CNT
>  from artists
>  
>  The sql may be a little off as I am typing this from scratch, but you
> get the
>  idea.
>  
>  now, if I do this:
>  
>  select * from VARTISTS where CNT=10
>  
>  it works.
>  
>  This, however, always returns an empty result set :
>  
>  select * from VARTISTS where CNT=:PARAM,
>  
>  for any value I bind to :PARAM.
>  
>  is this known/documented ?

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

[sqlite] Using Parameters with views

[Cariotoglou Mike]

I don't know if this has come up before, is so please point me to the
right direction :)

I believe that using parameterized queries with views does not work
as expected.

consider this (more or less self-explanatory) schema:

create table artists(artist_id)
create table songs(song_id)
create table song_artist(song_id,artist_id)

create view VARTISTS as
 select t1.*,
 (select count(*) from song_artist t2 where t1.artist_id=t2.artist_id)
as CNT
 from artists
 
 The sql may be a little off as I am typing this from scratch, but you
get the
 idea.
 
 now, if I do this:
 
 select * from VARTISTS where CNT=10
 
 it works.
 
 This, however, always returns an empty result set :
 
 select * from VARTISTS where CNT=:PARAM,
 
 for any value I bind to :PARAM.
 
 is this known/documented ?
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique index on REAL values and equality check

[Alexey Pechnikov]

Hello!

On Monday 14 December 2009 01:35:46 Samuel Adam wrote:
> > It'is not the answer to my question. Why the unique index
> > can understand when REAL values are equal?
> 
> Because the unique index is ... not converting binary to decimal.

So a index compare stored binary values... Thanks.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique index on REAL values and equality check

[Samuel Adam]

On Sun, 13 Dec 2009 17:02:05 -0500, Alexey Pechnikov 
 wrote:

> Hello!
>
> On Monday 14 December 2009 00:24:42 Samuel Adam wrote:
>> Smells like a decimal/binary rounding error.
>
> It'is not the answer to my question. Why the unique _index_
> can understand when REAL values are equal?

Because the unique index is not rounding and not converting binary to 
decimal.

Going back to your original post, when you did this:

> select * from test;
> 2455179.34204073

…it gave you a rounded decimal approximation[†], which you then used here:

> select count(*) from test where save_date=2455179.34204073;
> 0

By contrast, this used the actual floating-point value, not the rounded 
value:

> insert into test select * from test;
> Error: constraint failed

As I said, take the result of your SELECT * programmatically and use 
parameter binding for your SELECT count()… WHERE.  If it still returns 0, 
then it means my answer to your correction is incorrect[‡].  But I doubt it 
will return 0.

For further information, do web searches regarding “decimal floating point” 
and “binary decimal conversion”.  This kind of error is quite common, and is 
one of the computing industry’s dirtiest open secrets.  Contemplate this: 
If you put a penny into a binary float, you do not get a penny back out. 
Binary floats are manifestly unsuitable for most applications found in 
day-to-day human life; but programmers don’t know this, so they keep using 
binary floats incorrectly, and most people don’t care, so platforms support 
of decimal floats is not universal as it should be.

Samuel Adam

[†] Again, I haven’t done the math to verify that this is your problem.  It’s 
impossible to take that step without extracting the 8-byte floating point 
value stored in your database.  But this has “decimal conversion error” 
written all over it.  Or perhaps another floating point round error, with 
the same end results:  = seems to fail.  Decimal conversion looks like the 
most probable culprit here.

[‡] See above.
 

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

Re: [sqlite] SQLite with HTA(vbscript) Date Format Problem

[Olaf Schmidt]

"Çaðlar Orhan"  schrieb im
Newsbeitrag
news:677fef480912130642u5ed971b2r2c9cbec17771e...@mail.gmail.com...
> I am using SQLite for my little HTA application with
> vbscript.
> Everything is ok but in my sql query date format gets
> wrong records. SQLite uses YY-MM-DD
Normally it should be: -MM-DD

> i am querying with DD-MM-YY what should i do?
You are aware of the VBScript Format(...) function?
Just get your input right, before you feed it to the
SQLite-engine in your concatenated SQL-command-
string...

Dim D
D = CDate(Now) 'ensure a Variant-DateType within 'D'

MsgBox Format(D, "'\-mm\-dd'")
will give you the content of the Date-Variable as:
'-MM-DD'

and if your SQLite-Table really contains the dates in YY-MM-DD,
then:
MsgBox Format(D, "'yy\-mm\-dd'")
will give you:
'YY-MM-DD'
appropriately (in both cases already including the quote-signs).

Then your SQL-string-construct could look like this for example:
(assuming D1 and D2 represent Date-Variables and define an interval).
SQL = "Select * From Table Where DCol Between " & _
 Format(D1, "'yy\-mm\-dd'") & " AND " & _
 Format(D2, "'yy\-mm\-dd'")

Or define a Const for the above shown Format-Def-
Stringliteral for the second Parameter of the Format-Function.

Nonetheless better to use a command-object for that task, in
case your current COM-wrapper has built-in support for that.

Olaf Schmidt



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

Re: [sqlite] Unique index on REAL values and equality check

[Alexey Pechnikov]

Hello!

On Monday 14 December 2009 01:14:25 Doug Currie wrote:
> The problem is that floating point values (in SQLite shell) do not have 
> write-read idempotency

Please read my message again. The index work correct but why?

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique index on REAL values and equality check

[Doug Currie]

On Dec 13, 2009, at 3:16 PM, Alexey Pechnikov wrote:

> As we can see, the unique index can check equlity of REAL values 
> but the "=" operator can not. it's fantastic I think :-)

The problem is not the "=" operator...

sqlite> create table test (save_date REAL unique);
sqlite> insert into test values (julianday('now'));
sqlite> select rowid,* from test;
1|2455179.42227787
sqlite> insert into test select * from test;
SQL error: column save_date is not unique
sqlite> select count(*) from test where save_date=2455179.42227787;
0
sqlite> select count(*) from test where save_date in (select save_date from 
test where rowid=1);
1
sqlite> 

The problem is that floating point values (in SQLite shell) do not have 
write-read idempotency. I have moaned about that on this mailing list for 
years! ;-)
http://www.mail-archive.com/sqlite-users@sqlite.org/msg09529.html

e

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

Re: [sqlite] Unique index on REAL values and equality check

[Alexey Pechnikov]

Hello!

On Monday 14 December 2009 00:24:42 Samuel Adam wrote:
> Smells like a decimal/binary rounding error.

It'is not the answer to my question. Why the unique _index_
can understand when REAL values are equal?

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique index on REAL values and equality check

[Samuel Adam]

On Sun, 13 Dec 2009 15:16:08 -0500, Alexey Pechnikov 
 wrote:

> Hello!
>
> I have some frustration about
>
> create table test (
>   save_date REAL unique
> );
> insert into test values (julianday('now'));
> select * from test;
> 2455179.34204073
> insert into test select * from test;
> Error: constraint failed
> select count(*) from test where save_date=2455179.34204073;
> 0
>
> As we can see, the unique index can check equlity of REAL values
> but the "=" operator can not. it's fantastic I think :-)

Smells like a decimal/binary rounding error.

Try it from C (or whatever), feeding back *exactly* the result of your 
SELECT as a bound value.  I would be quite surprised if = didn’t work. 
Although I haven’t done the math here, experience suggests that you are 
dealing with two values which are actually unequal on the bit level.

SQLite really needs a DECIMAL type (e.g. based on IEEE 754-2008 BCD) so 
these questions can be answered “you’re using the wrong type” rather than 
“the database can’t count like a normal human”.  BCD floats still have 
rounding issues common to all floats, but let’s take one thing at a time; on 
a different but related note, things like this should never exist:

http://www.sqlite.org/faq.html#q16

I am aware that platform support is awful, of course.

Samuel Adam
 

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

Re: [sqlite] Sqlite and php

[Simon Slavin]

Frank,

I think you might get things done faster if you take a step back from your own 
application and try to get a simpler PHP application working.  You need to 
learn how the PDO system works and also what it takes to get Apache to store 
data on your computer.

Test it command by command: make something to create a blank database, run your 
program, then use the Finder to see if the file appeared.  Once it does, delete 
the file.

Add a command to you PHP application to try to create a blank table and see if 
the table appeared.  Once it does, delete the file.

Then try to put one record in it and see whether the record appeared.  Once it 
does, delete the file.

Each time you add a new command in your PHP program, use the sqlite3 
command-line utility to look inside the database file and check to see if the 
command had the right result.

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

Re: [sqlite] Sqlite and php

[Simon Slavin]

On 13 Dec 2009, at 7:15pm, FrankLane wrote:

> Well, I have such a file that I created, because with out it, I would get an
> error that reads:
> 
>  SQLSTATE[HY000] [14] unable to open database file
> 
> So I created a junk.sqlite file and I got
> 
>  SQLSTATE[HY000] [10] disk I/O error
> 
> so I changed permissions using  chmod 777 junk.sqlite  and that got rid of
> the error, and I continued on. After running the present program (which
> gives the error on fetchAll) the junk.sqlite file contains a single
> character - "S". This also happens if junk.sqlite is in the same directory
> as the php file. 

Then although you are not getting an error from your INSERT commands, they are 
not working correctly since if they were Apache would have created that file.  
You have two problems:

The database file must be created by SQLite, not you.  Even a blank database 
file has certain special things in the file.  You cannot just create a file 
with no contents and hope SQLite can use it.  You can either use your PHP 
program to create the database file or create it by using the sqlite3 
command-line tool.

Permissions have to be set so that Apache (not you yourself as a user) can read 
and write the database file.

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

[sqlite] Unique index on REAL values and equality check

[Alexey Pechnikov]

Hello!

I have some frustration about

create table test (
save_date REAL unique
);
insert into test values (julianday('now'));
select * from test;
2455179.34204073
insert into test select * from test;
Error: constraint failed
select count(*) from test where save_date=2455179.34204073;
0

As we can see, the unique index can check equlity of REAL values 
but the "=" operator can not. it's fantastic I think :-)

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and php

[FrankLane]

Hmmm

Well, I have such a file that I created, because with out it, I would get an
error that reads:

  SQLSTATE[HY000] [14] unable to open database file

So I created a junk.sqlite file and I got

  SQLSTATE[HY000] [10] disk I/O error

so I changed permissions using  chmod 777 junk.sqlite  and that got rid of
the error, and I continued on. After running the present program (which
gives the error on fetchAll) the junk.sqlite file contains a single
character - "S". This also happens if junk.sqlite is in the same directory
as the php file. 

Frank


Simon Slavin-3 wrote:
> 
> 
> On 13 Dec 2009, at 5:45pm, FrankLane wrote:
> 
>> Hi Simon - I don't know how to access the database created by my php
>> program,
> 
> The database is whatever file you specified when you created the database:
> 
> try { $dbHandle = new PDO('sqlite:'."/Users/me/junk.sqlite"); }
> 
> So your database file is the file
> 
> /Users/me/junk.sqlite
> 
> But are you certain that this file exists ?  Please check to see it exists
> and use the sqlite3 commend-line tool to see whether the right data exists
> in it.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Sqlite-and-php-tp26754013p26768798.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

[sqlite] sqlite files and locking on Lustre filesystems.

[George Hartzell]

My computing environment at work is bringing a new cluster online, in
addition to some local storage there will be shared access to SAN
style storage using the Lustre filesystem.

My reading about Lustre tells me that it supports POSIX semantics,
including flock and lockf (although they must be explicitly enabled).

That suggests to me that sqlite databases can be safely read/written.
Is that correct?

Does anyone have any direct experiences they can share with sqlite and
Lustre?

Thanks,

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

Re: [sqlite] Sqlite and php

[Simon Slavin]

On 13 Dec 2009, at 5:45pm, FrankLane wrote:

> Hi Simon - I don't know how to access the database created by my php program,

The database is whatever file you specified when you created the database:

try { $dbHandle = new PDO('sqlite:'."/Users/me/junk.sqlite"); }

So your database file is the file

/Users/me/junk.sqlite

But are you certain that this file exists ?  Please check to see it exists and 
use the sqlite3 commend-line tool to see whether the right data exists in it.

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

Re: [sqlite] BUG: The sqlite3 shell does not call setlocale

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Alexey Pechnikov wrote:
> The locale of CSV file may be defined in virtualtable arguments like to
> create virtual table test using VirtualText 
> ('/srv/projects/work/billing/export/ats.csv','utf8','','.',',',',');
> 
> But _default_ may be system locale. How to do filesystem access in a 
> virtualtable without locale 
> information? 

The best I can see is to call setlocale yourself, saving the prior locale
that it returns, then using localeconv/nl_langinfo to get the info you need
and then calling setlocale again with the original locale returned by your
setlocale call.

This will be fine for single threaded programs but for multi-threaded
programs will have temporarily changed the locale for a short while.  To be
100% correct in that case I'd fork and then let the child process gather
locale information to return to the parent over a pipe.  (A lot of work for
something so simple!)

On Linux you can also parse files in /usr/share/i18n/locales.  Heck you
could probably just compile the info into your code.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkslM/kACgkQmOOfHg372QT8ZgCfTLb/1zCLQZORm6vYcrAklrWm
/wIAn1FmC3mmKaYMBsCmL6Oq+GSbRw2d
=hUCe
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and php

[FrankLane]

Hi Simon - I don't know how to access the database created by my php program,
but I did put an echo after the definition of the command used by exec:

$i = 0;
while ($i <= 5)
{

  $valuesSeparatedByCommas = preg_replace('/\t/', ',', $data2[$i]);
  $sqlcmd = 'INSERT INTO tab (i0,i1,i2,i3,i4,t0,ID0,t1,t2,t3,t4,s,l,date,d)
VALUES ('.$valuesSeparatedByCommas.')'; 
  echo $sqlcmd."";

  $dbHandle->exec($sqlcmd);
  $i++;
}

The output look good. I followed this by

try {
$sth = $dbHandle->prepare("select * from tab where i0=2");
} catch (PDOException $PDOError) {
echo "Syntax Error: ".$PDOError->getMessage();
} 

and I obtained no messages. I then removed those statements and replaced
them by:

try {
$sth = $dbHandle->query("select * from tab where i0=2");
} catch (PDOException $PDOError) {
echo "Syntax Error from query: ".$PDOError->getMessage();
}
try {
$resultInArray = $sth->fetchAll();
} catch (PDOException $PDOError) {
echo "Syntax Error from fetchAll: ".$PDOError->getMessage();
}
echo "returned: ".print_r($result,true); 

and I obtained the following error on the fetchAll() line:

Fatal error: Call to a member function fetchAll() on a non-object in …

Frank
-- 
View this message in context: 
http://old.nabble.com/Sqlite-and-php-tp26754013p26768088.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] BUG: The sqlite3 shell does not call setlocale

[Jay A. Kreibich]

On Sun, Dec 13, 2009 at 01:47:43PM +0300, Alexey Pechnikov scratched on the 
wall:
> Hello!
> 
> On Sunday 13 December 2009 02:09:48 Roger Binns wrote:
> > Alexey Pechnikov wrote:
> > > This is standart for all i18n applications.
> > 
> > The SQLite shell is not an i18n application, and this is deliberate.  It is
> > a developer tool.  That is why for example it always uses a dot for a
> > decimal point and not a comma even if that is what the locale does.  The
> > output is always the same wherever it is used.  (Same thing applies for 
> > input.)
> 
> The libsqlite is locale-independent but sqlite3 shell linked with 
> readline and it's locale-dependent. 

  That's a user choice.  It does not link with readline by default.

  (Well, the UNIX autoconf version does, but the raw source has it off
  by default.)

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite with HTA(vbscript) Date Format Problem

[Çağlar Orhan]

I am using SQLite for my little HTA application with vbscript. Everything is
ok but in my sql query date format gets wrong records. SQLite uses YY-MM-DD
i am querying with DD-MM-YY what should i do?
Thanks
Caglar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and php

[Simon Slavin]

On 13 Dec 2009, at 10:18am, FrankLane wrote:

> After the loop that inserts the data
> into the databease,

I would like to be sure that your INSERT commands work correctly.  Once you 
have executed the code that puts data in the database, please open the same 
database in the command-line tool (sqlite3) and check to see that the contents 
of your table look vaguely right.

> I'm using the following code to try to execute an sqlite
> commmand:
> 
> $sth = $dbHandle->prepare("select * from tab where i0=2");
> $sth->execute();
> 
> and I get the following error:
> 
> Fatal error: Call to a member function execute() on a non-object in
> /Library/WebServer/Documents/test3.php on line 41

I agree that this does look like it may be a bug in PHP.  However, please check 
to see whether your call to -->prepare is working properly.  For instance 
replace your '-->prepare' with

try { 
$sth = $dbHandle->prepare("select * from tab where i0=2");
} catch (PDOException $PDOError) { 
echo "Syntax Error: ".$PDOError->getMessage();
}

Actually I am surprised that ->query is not working for you.  Instead of 
->prepare and ->execute can you try

try { 
$sth = $dbHandle->query("select * from tab where i0=2");
} catch (PDOException $PDOError) { 
echo "Syntax Error from query: ".$PDOError->getMessage();
}
try { 
$resultInArray = $sth->fetchAll();
} catch (PDOException $PDOError) { 
echo "Syntax Error from fetchAll: ".$PDOError->getMessage();
}
echo "returned: ".print_r($result,true);

and tell me if you get errors back ?

Warning: There may be bugs in the above code.  It's off the top of my head.

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

Re: [sqlite] Problems with loosing data

[Simon Slavin]

On 13 Dec 2009, at 11:04am, Marek Staniewski wrote:

> We are using some application, which uses sqlite over the network.
> Sometimes I observed that last entered data into application is lost. 

What programming language are you calling the SQLite function library from ?
What operating system is the computer that runs that program running ?

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

[sqlite] Problems with loosing data

[Marek Staniewski]

We are using some application, which uses sqlite over the network.
Sometimes I observed that last entered data into application is lost. 

I have impression that it can be done due to the fact that one of a
workstation is using offline files. 

Assume that a workstation on which offline files are installed are
called "X":

Let say on Monday we entered some data on one of the workstation. On
this day also workstation X was used with our sqlite application.

Then on Tuesday were again add some data as well as on Wednesday (but
without using X workstation). 

 

On Wednesday evening I used X workstation (connected to the network) and
observed that data entered on Tuesday and Wednesday are missing. I saw
also that a data base file has date of Monday.  Therefore I tired to use
all workstations in the network and found that this is the same problem.


 

Do you think this can be due to offline files other may be some other
problem?

 

If yes then is there any chance to retrieve somewhere some newer file?
How it can be that older data file replaces newer file?  

 

 

Marek Staniewski

AMSORT Sp. z o.o. (previous name DERCO Sp. z o.o.)

 

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

Re: [sqlite] BUG: The sqlite3 shell does not call setlocale

[Alexey Pechnikov]

Hello!

On Sunday 13 December 2009 02:09:48 Roger Binns wrote:
> Alexey Pechnikov wrote:
> > This is standart for all i18n applications.
> 
> The SQLite shell is not an i18n application, and this is deliberate.  It is
> a developer tool.  That is why for example it always uses a dot for a
> decimal point and not a comma even if that is what the locale does.  The
> output is always the same wherever it is used.  (Same thing applies for 
> input.)

The libsqlite is locale-independent but sqlite3 shell linked with readline and 
it's locale-dependent. 

> > The SQLite virtualtables can
> > perform access to filesystem, read/write scv files and other - how they can 
> > to
> > determine the current locale?
> 
> That is indeed trickier.  But even your example is hard. If I am sitting in
> Canada and get a German CSV file, which locale applies?  

The locale of CSV file may be defined in virtualtable arguments like to
create virtual table test using VirtualText 
('/srv/projects/work/billing/export/ats.csv','utf8','','.',',',',');

But _default_ may be system locale. How to do filesystem access in a 
virtualtable without locale 
information? 

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and php

[FrankLane]

Hi - The $valuesSeparatedByCommas worked fine, but I am still having problems
executing a command on the database - After the loop that inserts the data
into the databease, I'm using the following code to try to execute an sqlite
commmand:

$sth = $dbHandle->prepare("select * from tab where i0=2");
$sth->execute();

and I get the following error:

Fatal error: Call to a member function execute() on a non-object in
/Library/WebServer/Documents/test3.php on line 41

After googling this error, I am worried that it is not a code problem. For
example:

http://www.dotproject.net/vbulletin/showthread.php?t=5667

I am running php 5.3.0 on Mac OS X 10.6.1 (Snow Leopard). Could it be a
problem with my version of php? 

Frank


Simon Slavin-3 wrote:
> 
> 
> On 13 Dec 2009, at 5:27am, FrankLane wrote:
> 
>>  $data3 = preg_split('/\t/',$data2[$i]);  // tab delimited record data
>> into
>> array
>> 
>>  $sqlcmd = 'INSERT INTO tab (i0) VALUES ('.$data3[0].')';
> 
> Good so far.  Now you should know that a normal SQL command to insert a
> record would look something like this:
> 
> INSERT INTO tab(i0,i1,i2,i3,i4) VALUES (1,2,3,4,5)
> 
> where the five values listed go into the five variables listed.  So you
> need to turn the array currently in $data3 into a list of variables.  One
> way to do it is this:
> 
> $valuesSeparatedByCommas = preg_replace('/\t/', ',', $data2[$i]);
> $sqlcmd = 'INSERT INTO tab(i0,i1,i2,i3,i4) VALUES
> ('.$valuesSeparatedByComma.')';
> 
> another way to do it is this:
> 
> $arrayOfValues = explode('/\t/',$data2[$i]);  // tab delimited record data
> into array
> $valuesSeparatedByCommas = join(',', $arrayOfValues);
> $sqlcmd = 'INSERT INTO tab(i0,i1,i2,i3,i4) VALUES
> ('.$valuesSeparatedByComma.')';
> 
> 
> 
>> Looking in the php manual, there is no "query" function, but a lot of
>> other
>> xxx_query functions. Is this a simple problem"
> 
> 
> I think you are best off using ->fetchAll for now.  See
> 
> http://php.net/manual/en/pdostatement.fetchall.php
> 
> and scroll down to see some examples.  This should allow you to prove that
> the database system is working and give you confidence to try some of the
> other functions.  You will probably end up using either ->fetch() or
> ->fetchObject().
> 
> (Warning: I haven't actually tried any of the above code.)
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Sqlite-and-php-tp26754013p26764843.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