Re: [sqlite] image upload to db trouble
> //echo $images;
>$db = new PDO('sqlite: database.sqlite3');
>
>$con = sqlite_open('sqlite: database.sqlite3');
> if (!$con)
> {
> die('Could not connect: ' . mysql_error());
> }
why you first make a new db opject and then open an connection to the
database? new PDO sqlite already open an connection .. as i am right.
and why mysql_error() ?
>
> $db->query('sqlite: database.sqlite3', $con);
>
> $db->query ("INSERT INTO images (id, images, info, url)
> VALUES ('$i', '$images', '$info', '$url')");
>
> $db->query("DELETE FROM images WHERE id='$i'");
> }
AS i know, if you want to put images in your db you need blob columns and
these are supported in sqlite3. or i understand something wrong?
Artur Reilin
sqlite.yuedream.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] APSW 3.6.20-r1 released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 APSW is a wrapper around SQLite that provides all SQLite API functionality in Python. It also has an interactive shell so you don't have to use Python. The home page is http://code.google.com/p/apsw/ which includes a changelog. New in this release is the ability to also access CouchDB. This is implemented by providing a SQLite virtual table - a mechanism SQLite provides to let external code provide the contents of a table and actions upon it. http://couchdb.apache.org/ These are some example use cases: * Loading data (including CSV) into CouchDB or out of CouchDB * Copying data between SQL and CouchDB (bidirectional) * Being able to do joins between data in SQLite and CouchDB * Using other SQLite extensions such as the full text search engine and the multi-dimensional spatial engine (rtree) CouchDB specific information: http://apsw.googlecode.com/svn/publish/couchdb.html The interactive shell: http://apsw.googlecode.com/svn/publish/shell.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkr8/bEACgkQmOOfHg372QQXBgCeOIMXBVXTbYJuNfG+QqJMEu1E f50AoM0hRyuJ6yCaIfs8XKZDtFClq6Ey =7Ehv -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
Simon Slavin wrote: > On 13 Nov 2009, at 3:30am, Roger Binns wrote: > > >> Simon Slavin wrote: >> >>> Integers in all languages I'm aware of are not stored as mantissa/exponent, >>> they're stored as bits with complete precision. >>> >> There is one huge exception I found out the hard way recently: Javascript >> stores all 'integers' as floating point and so Javascript integers lose >> precision. For example if you try to use 9223372036854775807 in Javascript >> it will keep coming back as 9223372036854776000. >> > > JavaScript doesn't have an integer type, just a number type: > > var myVariable = 42 > document.writeln ( "myVariable is a " + typeof myVariable ) Microsoft Excel has a similar problem. I ran into it back when I was working in a credit union and tried to import a CSV file containing credit card numbers. Wouldn't have noticed except that credit card numbers are 16 digits long and double only has 15 digits of precision. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Converting .dbf to SQLite
Using R might actually be a convenient way to do it all in essentially one step, and technically batch scriptable. You'd need the RSQlite add on package, I think dbf reading is built in. Alex dave lilley wrote: > 2009/11/12 Rich Shepard > >> On Thu, 12 Nov 2009, dave lilley wrote: >> >>> Not trying to be silly here but why not write a wee program that reads in >>> the dbf file and for each row read in write the data into an sql file? >> Because I'd have to research the format of the .dbf file and I'd probably >> be re-inventing the wheel. >> > > No I mean you use a programming language to read the DBF datafile and write > out to your new database. > > And as someone else has suggested you use OOo spreadsheet to connect to the > DBF file and then write it out to a CVS file so you can import into your new > DB. > > if your not confidant with programming then i strongly suggest you take this > option as you then only have to import the CVS data into SQLlite. > > Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Simon Slavin wrote: > JavaScript doesn't have an integer type, just a number type: You are agreeing with me :-) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkr86D8ACgkQmOOfHg372QQFMQCgogWhSWfZzSpfILfUDhWJqjK1 w8EAoJosqghW1lBnhE3iZ4soWhpY5xFG =rJHI -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
On 13 Nov 2009, at 3:30am, Roger Binns wrote: > Simon Slavin wrote: >> Integers in all languages I'm aware of are not stored as mantissa/exponent, >> they're stored as bits with complete precision. > > There is one huge exception I found out the hard way recently: Javascript > stores all 'integers' as floating point and so Javascript integers lose > precision. For example if you try to use 9223372036854775807 in Javascript > it will keep coming back as 9223372036854776000. JavaScript doesn't have an integer type, just a number type: var myVariable = 42 document.writeln ( "myVariable is a " + typeof myVariable ) Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Simon Slavin wrote: > Integers in all languages I'm aware of are not stored as mantissa/exponent, > they're stored as bits with complete precision. There is one huge exception I found out the hard way recently: Javascript stores all 'integers' as floating point and so Javascript integers lose precision. For example if you try to use 9223372036854775807 in Javascript it will keep coming back as 9223372036854776000. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkr80sIACgkQmOOfHg372QTLaACfZW8LyMa9vg3RUXuw+95L5PlS 390AmwU27YpaoP6Qf2hOd+hnPpYoJLl/ =hEhD -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
On 13 Nov 2009, at 12:34am, Nicolas Williams wrote: > On Thu, Nov 12, 2009 at 11:40:23PM +, Simon Slavin wrote: >> There's still some possibility for confusion, however: how many places >> of decimals do you use for each currency ? As far as I know, no >> currently traded currency uses more than two digits of precision. > ^^^ > They use integer math to avoid floating point rounding issues, but > logically those integers are still real (or at least rational) numbers, > and so we can speak of base, mantissa and exponent. The precision > required is pretty large, much more than two digits. Integers in all languages I'm aware of are not stored as mantissa/exponent, they're stored as bits with complete precision. You can say you need a particular number of bits, but you'll never lose the last bit (the 1s) just because your numbers have got too big. You'll get an overflow error instead. By 'two digits of precision' I was referring to cents for US dollars, pence for pounds sterling, etc.. Some currencies have no fractional part (e.g. Yen). And I was sure there were ... ah, here we are: http://www.worldatlas.com/aatlas/infopage/currency.htm Okay, so do not hardwire your code for two decimal places because you won't handle Kuwaiti dinar correctly. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will sqlite flush the change to the disk when page cache is full?
pierr wrote: > I have a impression that sqlite will flush the change to the disk when the > page cache is full even if the "COMMIT" command has not been issued during > the transction. Yes. > If this is true, will this violate the isolation properity > of a transction No. > as other parallel transction would be able the see the > uncommitted change. Other transactions will be locked out while a writing transaction is in progress. For more details, see http://www.sqlite.org/atomiccommit.html http://www.sqlite.org/lockingv3.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Will sqlite flush the change to the disk when page cache is full?
Hi, I have a impression that sqlite will flush the change to the disk when the page cache is full even if the "COMMIT" command has not been issued during the transction. If this is true, will this violate the isolation properity of a transction as other parallel transction would be able the see the uncommitted change. However, IMO, this won't impact the Atomic properity as we still be able to rollback if the transction would fail after the flush but before the "COMMIT" of the transction as the rollback journey is there. -- View this message in context: http://old.nabble.com/Will-sqlite-flush-the-change-to-the-disk-when-page-cache-is-full--tp26328716p26328716.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] Output in currency format
On Thu, Nov 12, 2009 at 11:40:23PM +, Simon Slavin wrote: > There's still some possibility for confusion, however: how many places > of decimals do you use for each currency ? As far as I know, no > currently traded currency uses more than two digits of precision. ^^^ They use integer math to avoid floating point rounding issues, but logically those integers are still real (or at least rational) numbers, and so we can speak of base, mantissa and exponent. The precision required is pretty large, much more than two digits. Consider U.S. dollars, where we need to express from some fraction of pennies to tens of trillions of dollars. That's at least 16 digits of precision. You need to use larger than 32-bit integers for this, meaning, in practice, 64-bit integers. Besides, 100 years ago 16 digits of precision for counting money would probably have seemed farfeteched, so use 64-bit integers, which gets you a bit less than 20 digits of precision. (Hmmm, 19 digits looks a bit small now!) Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
On 12 Nov 2009, at 7:17pm, Peter Haworth wrote: > Just to be sure I get my calculations correct, the suggestion is that > all currency amounts should be stored in the database as whole numbers > in fields of type INTEGER. Calculations would be done using the whole > numbers and I'll need some routines to convert between the database > format and the display format. Do I have that right? Exactly. I'm familiar with lots of big pieces of bank software and that's how they do it. There's still some possibility for confusion, however: how many places of decimals do you use for each currency ? As far as I know, no currently traded currency uses more than two digits of precision. Some systems use two digits for dollars (because that's the actual smallest unit you can trade in it). But some use four digits for every currency because it makes the library routines simpler, betrays rounding problems in your code, and allows for figures to be rounded later. It's one of the unsettled problems of financial software. Since you probably won't have to deal with multi-currency rounding problems I'd recommend you stick to two digits unless advised not to by someone who knows exactly what you're doing. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] image upload to db trouble
i took that part out and still nothign writes to the db
Pavel Ivanov-2 wrote:
>
> Why should they show up in database if you're deleting it right after
> inserting?
>
> Pavel
>
> On Thu, Nov 12, 2009 at 2:50 PM, perkinsdp
> wrote:
>>
>> hey guys i have a script where im taking images and uploading to an
>> upload
>> file on my server ( this part works fine in my code ) i also want it to
>> put
>> the images into my sqlite3 database which has the table images. Im new to
>> this and cant seem to figure out why they wont show up in the db.
>>
>> >
>> $success = 0;
>> $fail = 0;
>> $uploaddir = '../uploads/';
>> for ($i=0;$i<5;$i++)
>> {
>> if($_FILES['userfile']['name'][$i])
>> {
>> $uploadfile = $uploaddir . basename($_FILES['userfile']['name'][$i]);
>> $ext = strtolower(substr($uploadfile,strlen($uploadfile)-3,3));
>> if (preg_match("/(jpg|gif|png|bmp)/",$ext))
>> {
>> if (move_uploaded_file($_FILES['userfile']['tmp_name'][$i],
>> $uploadfile))
>> {
>>
>> $success++;
>> $info= $_POST[desc.$i];
>> $images=$uploadfile;
>> $url=$_POST[textfield.$i];
>> //echo $info;
>>
>> //echo $images;
>> $db = new PDO('sqlite: database.sqlite3');
>>
>> $con = sqlite_open('sqlite: database.sqlite3');
>> if (!$con)
>> {
>> die('Could not connect: ' . mysql_error());
>> }
>>
>> $db->query('sqlite: database.sqlite3', $con);
>>
>> $db->query ("INSERT INTO images (id, images, info, url)
>> VALUES ('$i', '$images', '$info', '$url')");
>>
>> $db->query("DELETE FROM images WHERE id='$i'");
>> }
>>
>> else
>> {
>> echo "Error Uploading the file. Retry after sometime.\n";
>> $fail++;
>> }
>> }
>> else
>> {
>> $fail++;
>> }
>> }
>> }
>> echo " Number of files Uploaded:".$success;
>> echo " Number of files Failed:".$fail;
>> ?>
>> --
>> View this message in context:
>> http://old.nabble.com/image-upload-to-db-trouble-tp26325178p26325178.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-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/image-upload-to-db-trouble-tp26325178p26325889.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] image upload to db trouble
im using a db file, I have one sqlite3 file that i use and edit with sqlite
manager on firefox, the only code i have is what i posted, im sorry if im
misunderstanding but im completely new at using this as of now.
Keith Roberts-4 wrote:
>
> On Thu, 12 Nov 2009, perkinsdp wrote:
>
>> To: sqlite-users@sqlite.org
>> From: perkinsdp
>> Subject: [sqlite] image upload to db trouble
>>
>>
>> hey guys i have a script where im taking images and uploading to an
>> upload
>> file on my server ( this part works fine in my code ) i also want it to
>> put
>> the images into my sqlite3 database which has the table images. Im new to
>> this and cant seem to figure out why they wont show up in the db.
>>
>> >
>> $success = 0;
>> $fail = 0;
>> $uploaddir = '../uploads/';
>> for ($i=0;$i<5;$i++)
>> {
>> if($_FILES['userfile']['name'][$i])
>> {
>>$uploadfile = $uploaddir . basename($_FILES['userfile']['name'][$i]);
>>$ext = strtolower(substr($uploadfile,strlen($uploadfile)-3,3));
>>if (preg_match("/(jpg|gif|png|bmp)/",$ext))
>>{
>> if (move_uploaded_file($_FILES['userfile']['tmp_name'][$i],
>> $uploadfile))
>> {
>>
>> $success++;
>>$info= $_POST[desc.$i];
>>$images=$uploadfile;
>>$url=$_POST[textfield.$i];
>>//echo $info;
>>
>> //echo $images;
>> $db = new PDO('sqlite: database.sqlite3');
>>
>> $con = sqlite_open('sqlite: database.sqlite3');
>> if (!$con)
>> {
>> die('Could not connect: ' . mysql_error());
>> }
>>
>>$db->query('sqlite: database.sqlite3', $con);
>>
>>$db->query ("INSERT INTO images (id, images, info, url)
>>VALUES ('$i', '$images', '$info', '$url')");
>>
>>$db->query("DELETE FROM images WHERE id='$i'");
>> }
>>
>> else
>> {
>> echo "Error Uploading the file. Retry after sometime.\n";
>> $fail++;
>> }
>>}
>>else
>>{
>> $fail++;
>>}
>> }
>> }
>> echo " Number of files Uploaded:".$success;
>> echo " Number of files Failed:".$fail;
>> ?>
>> --
>
> Do you have the create statemnt for your DB please?
>
> Kind Regards,
>
> Keith Roberts
>
> -
> Websites:
> http://www.php-debuggers.net
> http://www.karsites.net
> http://www.raised-from-the-dead.org.uk
>
> All email addresses are challenge-response protected with
> TMDA [http://tmda.net]
> -
> ___
> 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/image-upload-to-db-trouble-tp26325178p26325679.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] image upload to db trouble
On Thu, 12 Nov 2009, perkinsdp wrote:
> To: sqlite-users@sqlite.org
> From: perkinsdp
> Subject: [sqlite] image upload to db trouble
>
>
> hey guys i have a script where im taking images and uploading to an upload
> file on my server ( this part works fine in my code ) i also want it to put
> the images into my sqlite3 database which has the table images. Im new to
> this and cant seem to figure out why they wont show up in the db.
>
>
> $success = 0;
> $fail = 0;
> $uploaddir = '../uploads/';
> for ($i=0;$i<5;$i++)
> {
> if($_FILES['userfile']['name'][$i])
> {
>$uploadfile = $uploaddir . basename($_FILES['userfile']['name'][$i]);
>$ext = strtolower(substr($uploadfile,strlen($uploadfile)-3,3));
>if (preg_match("/(jpg|gif|png|bmp)/",$ext))
>{
> if (move_uploaded_file($_FILES['userfile']['tmp_name'][$i],
> $uploadfile))
> {
>
> $success++;
> $info= $_POST[desc.$i];
> $images=$uploadfile;
> $url=$_POST[textfield.$i];
> //echo $info;
>
> //echo $images;
>$db = new PDO('sqlite: database.sqlite3');
>
>$con = sqlite_open('sqlite: database.sqlite3');
> if (!$con)
> {
> die('Could not connect: ' . mysql_error());
> }
>
> $db->query('sqlite: database.sqlite3', $con);
>
> $db->query ("INSERT INTO images (id, images, info, url)
> VALUES ('$i', '$images', '$info', '$url')");
>
> $db->query("DELETE FROM images WHERE id='$i'");
> }
>
> else
> {
> echo "Error Uploading the file. Retry after sometime.\n";
> $fail++;
> }
>}
>else
>{
> $fail++;
>}
> }
> }
> echo " Number of files Uploaded:".$success;
> echo " Number of files Failed:".$fail;
> ?>
> --
Do you have the create statemnt for your DB please?
Kind Regards,
Keith Roberts
-
Websites:
http://www.php-debuggers.net
http://www.karsites.net
http://www.raised-from-the-dead.org.uk
All email addresses are challenge-response protected with
TMDA [http://tmda.net]
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] image upload to db trouble
Why should they show up in database if you're deleting it right after inserting?
Pavel
On Thu, Nov 12, 2009 at 2:50 PM, perkinsdp
wrote:
>
> hey guys i have a script where im taking images and uploading to an upload
> file on my server ( this part works fine in my code ) i also want it to put
> the images into my sqlite3 database which has the table images. Im new to
> this and cant seem to figure out why they wont show up in the db.
>
>
> $success = 0;
> $fail = 0;
> $uploaddir = '../uploads/';
> for ($i=0;$i<5;$i++)
> {
> if($_FILES['userfile']['name'][$i])
> {
> $uploadfile = $uploaddir . basename($_FILES['userfile']['name'][$i]);
> $ext = strtolower(substr($uploadfile,strlen($uploadfile)-3,3));
> if (preg_match("/(jpg|gif|png|bmp)/",$ext))
> {
> if (move_uploaded_file($_FILES['userfile']['tmp_name'][$i],
> $uploadfile))
> {
>
> $success++;
> $info= $_POST[desc.$i];
> $images=$uploadfile;
> $url=$_POST[textfield.$i];
> //echo $info;
>
> //echo $images;
> $db = new PDO('sqlite: database.sqlite3');
>
> $con = sqlite_open('sqlite: database.sqlite3');
> if (!$con)
> {
> die('Could not connect: ' . mysql_error());
> }
>
> $db->query('sqlite: database.sqlite3', $con);
>
> $db->query ("INSERT INTO images (id, images, info, url)
> VALUES ('$i', '$images', '$info', '$url')");
>
> $db->query("DELETE FROM images WHERE id='$i'");
> }
>
> else
> {
> echo "Error Uploading the file. Retry after sometime.\n";
> $fail++;
> }
> }
> else
> {
> $fail++;
> }
> }
> }
> echo " Number of files Uploaded:".$success;
> echo " Number of files Failed:".$fail;
> ?>
> --
> View this message in context:
> http://old.nabble.com/image-upload-to-db-trouble-tp26325178p26325178.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
That has always been my most effective last resort when attempting to do business math with many databases and development environments. > Thanks for all the advice on this. Just to be clear, I wasn't > referring to the accuracy of calculations when I compared the sqlite > date/time formatting capabilites to the lack of similar functionality > for currency, just the fact that there is a precedent for sqlite > providing output formatting capability for some types of data. > > Just to be sure I get my calculations correct, the suggestion is that > all currency amounts should be stored in the database as whole numbers > in fields of type INTEGER. Calculations would be done using the whole > numbers and I'll need some routines to convert between the database > format and the display format. Do I have that right? > > Pete Haworth > > > > > > > > > ___ > 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] image upload to db trouble
hey guys i have a script where im taking images and uploading to an upload
file on my server ( this part works fine in my code ) i also want it to put
the images into my sqlite3 database which has the table images. Im new to
this and cant seem to figure out why they wont show up in the db.
query('sqlite: database.sqlite3', $con);
$db->query ("INSERT INTO images (id, images, info, url)
VALUES ('$i', '$images', '$info', '$url')");
$db->query("DELETE FROM images WHERE id='$i'");
}
else
{
echo "Error Uploading the file. Retry after sometime.\n";
$fail++;
}
}
else
{
$fail++;
}
}
}
echo " Number of files Uploaded:".$success;
echo " Number of files Failed:".$fail;
?>
--
View this message in context:
http://old.nabble.com/image-upload-to-db-trouble-tp26325178p26325178.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] Output in currency format
Thanks for all the advice on this. Just to be clear, I wasn't referring to the accuracy of calculations when I compared the sqlite date/time formatting capabilites to the lack of similar functionality for currency, just the fact that there is a precedent for sqlite providing output formatting capability for some types of data. Just to be sure I get my calculations correct, the suggestion is that all currency amounts should be stored in the database as whole numbers in fields of type INTEGER. Calculations would be done using the whole numbers and I'll need some routines to convert between the database format and the display format. Do I have that right? Pete Haworth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 for Mac OSX 10.5
Thanks for all the info. I believe the problem lies within Revolution since I'm pretty sure it includes its own private library of the sqlite code. I've reported it to them and hopefully they will fix it. I understand the reasons for applications having their own copy of the code like this but think there's equally good reasons why they should use a common library installed on the computer, maybe as an option. Right now, I have to rely on Revolution updating their code to solve my problems whereas with a common library approach, I could download and compile as several people have suggested and not have to rely on Revolution. Pete Haworth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: Content filtered message notification
Begin forwarded message: > From: sqlite-users-boun...@sqlite.org > Date: November 12, 2009 1:55:07 PM EST > To: sqlite-users-ow...@sqlite.org > Subject: Content filtered message notification > > The attached message matched the sqlite-users mailing list's content > filtering rules and was prevented from being forwarded on to the list > membership. You are receiving the only remaining copy of the > discarded message. > > > From: Marien Zwart > Date: November 12, 2009 1:55:01 PM EST > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Fwd: failing attempts at sending "Feature > request: PRAGMA maximum_file_format" > > > On Thu, 2009-11-12 at 13:37 -0500, D. Richard Hipp wrote: >> On Nov 12, 2009, at 1:30 PM, D. Richard Hipp wrote: To write more future-proof code I would like to have something like PRAGMA default_file_format or PRAGMA maximum_file_format that lets me explicitly specify the highest file format version SQLite will use for new databases. Does this sound like something worth adding? >> >> Not to me. >> >> Why don't you just use the default (file format 1)? It works great >> with every version of SQLite back to 3.0.0. Do you have some unusual >> requirement for descending indices or something? > > I figured the newer format would not have been added if it was not > normally better than format 1 in size and/or performance, and would > like > to take advantage of this without having to worry about backwards > compatibility (or at least not much). I must admit I have not tried to > measure the performance or size difference: I'm just assuming there is > one, and with this feature I could take advantage of it with very > little > cost. > > -- > Marien. > > D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database is locked
Hello, Yesterday, we recognized that we had two concurrent SQL Server
threads reading and writing to the same sqlite database. Furthermore,the reader
thread was not releasing it's lock. So, now we release the lock by commiting
the transaction. As a result, we no longer get the SQLite database is locked
message. Thank you
Retval = Keys->Execute("BEGIN EXCLUSIVE");
sprintf(Command,"SELECT [Key], [RowId], [DupeGroup] "
"FROM [Keys] WHERE [Cluster]=\"%*.*s\"",BlockSize,BlockSize,_Key);
Keys->Prepare(Command);
while (Keys->Step()==SQLITE_ROW) {
Keys->ColumnText(0,TestKey);
if ((rc=CompareKeys(TestKey,_Key,0))!=0) {
ErrorMask|=rc;
if (DupeCount>=DedupeBlockSize)
IncreaseDedupeBlocks();
RowIds[DupeCount]=Keys->ColumnInt(1);
DupeGroups[DupeCount]=Keys->ColumnInt(2);
}
}
Retval = Keys->Execute("COMMIT");
_
Bing brings you maps, menus, and reviews organized in one place.
http://www.bing.com/search?q=restaurants&form=MFESRP&publ=WLHMTAG&crea=TEXT_MFESRP_Local_MapsMenu_Resturants_1x1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: failing attempts at sending "Feature request: PRAGMA maximum_file_format"
On Nov 12, 2009, at 1:30 PM, D. Richard Hipp wrote: >> >> To write more future-proof code I would like to have something like >> PRAGMA default_file_format or PRAGMA maximum_file_format that lets me >> explicitly specify the highest file format version SQLite will use >> for >> new databases. >> >> Does this sound like something worth adding? Not to me. Why don't you just use the default (file format 1)? It works great with every version of SQLite back to 3.0.0. Do you have some unusual requirement for descending indices or something? D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: failing attempts at sending "Feature request: PRAGMA maximum_file_format"
Begin forwarded message: > From: Marien Zwart > Date: November 12, 2009 1:26:11 PM EST > To: sqlite-users-ow...@sqlite.org > Subject: failing attempts at sending "Feature request: PRAGMA > maximum_file_format" > > Hi, > > I'm really sorry to bother you, but I've now attempted to send the > following to the list twice and apparently it is eaten somewhere (some > spam filter?). This was about a week ago now, so I'm assuming the mail > isn't simply waiting in the moderation queue (it should not be > ending up > there anyway, because I'm subscribed and receiving mail from the list > just fine). There is probably something wrong with my mail setup, > but I > cannot figure out what. If you could have a look at this message's > headers or whatever logs the mailing list software keeps that would be > very much appreciated. > > This was the text from the message I'm trying to send: > > SQLite currently supports two slightly different ways of setting the > file format used for new databases: PRAGMA legacy_file_format at run > time and defining SQLITE_DEFAULT_FILE_FORMAT at compile time. The > latter > lets you set the default format created to any of the supported > formats > (currently just 1 or 4, according to the documentation). The former > lets > you pick either the most compatible or the most recent format (so > currently also 1 or 4). > > To write more future-proof code I would like to have something like > PRAGMA default_file_format or PRAGMA maximum_file_format that lets me > explicitly specify the highest file format version SQLite will use for > new databases. > > My reasoning is that while with versions of SQLite that currently > exist > I can accept PRAGMA legacy_file_format=0 (I can safely assume SQLite > 3.3 > or higher is available) if my code gets used with some future > version of > SQLite that introduces a new file format I will start creating > databases > that are not backwards compatible without warning. So I would like > to be > able to use something like PRAGMA default_file_format=4 instead, which > would cause this future version of SQLite to create databases that can > be read by SQLite 3.3 and up. (I am not sure what to do when the > requested default_file_format is higher than the highest supported > version. It may be reasonable to handle this as equivalent to PRAGMA > legacy_file_format=0 instead of treating it as an error.) > > Does this sound like something worth adding? If it does I could try to > cook up a patch, although I would probably need some pointers for > that, > not being familiar with the SQLite code. > > Thanks in advance! > > -- > Marien. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Disk activity on Linux
We're seeing a lot more disk activity than expected on Linux when using sqlite3. We've run this same series of test on windows and the disk IO is much lower, which is the opposite of what I really expected. Below is my scenario and perhaps someone can point out what I can do to fix this problem. We are using sqlite version 3.5.7 . Shared cache is enabled using at program startup time: sqlite3_enable_shared_cache(1); We create a new database every hour. This database has 2 indexes and a ton of columns. The maximum size of each row is about 3,400 bytes. A lot of that is UTF8 strings. The database was actually pre-created on a windows machine and is installed with the product. The stored database template file is then copied to the real database instead of actually "creating" it every hour. The database is opened in read/write mode and we issue the following pragma's after opening: PRAGMA read_uncommitted=ON PRAGMA synchronous=OFF I also call: sqlite3_busy_timeout( db,TM_DB_BUSY_TIMEOUT); // 30,000 is the value of the busy timeout. We are then attempting to insert about 278 rows per second over the course of an hour. The batch size is 8,192 rows or every 5 minutes, whichever comes first. In this case we are committing the transaction approximately every 30-40 seconds. The database is grows from it's initial creation size of about 11,000 bytes to about 1.5 gigabytes in the course of the hour. As the database size increases we are seeing significant increases in the CPU IOWait times that eventually slow down the insertion thread enough that our reception queue fills up and we have to throw out data to prevent a queue overflow. The disk we are using is 2 disks in Raid 0 configuration. On windows we had a similar problem but we fixed it by issuing the PRAGMA synchronous=OFF to speed up the disk io. A corrupt database is a problem but performance is very important and we can tolerate a potential database corrupt in low chance scenarios. I've tried everything I can think of to reduce the IOWait times and am at a loss. I've lowered the batch size and even tried the pragma PRAGMA journal_mode=MEMORY but that didn't seem to have any effect that I could determine. If I watch the database directory as the test is run the Database file and the journal file are being written to constantly. Anyone have any ideas? This problem is limiting the scale of our product significantly and I need to find a solution to it. We are pretty much hitting a wall around 277 records per second and we need to be more around 500-1000 records per second if possible. Bret Patterson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on Windows XP with ADO.Net - batching updates + inserts in a single transaction - optimal transaction size
It depends on what else you want to do with database and how important for you to be safe against power outages and OS crashes. If it's not important and you're working with database from the single process and single connection you may never commit a transaction opened at the beginning of the application. Pavel On Thu, Nov 12, 2009 at 12:08 AM, Jon wrote: > For performance reasons, I want to batch my SQLite insert/replace > operations in a single SQL transaction that I send to my local SQLite > database running on Windows XP. I am also using the ADO.Net assembly > to interact with my SQLite database. > > Does anyone have any insight into how large my transaction batches > should be? For example, have up to 300 insert/replace operations in a > single transaction? up to 1000 insert/replace operations in a single > transaction? etc. > > ___ > 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] collation bug?
Sorry, I forgot to say that this method is just defined as static:
static int xCompare (void* v, int iLen1, const void* str1, int iLen2,
const void* str2);
In my opinion the important question is:
does the return value have a specific meaning according with its
numeric value or returning positive/negative/zero value it's enough?
Citando "Jay A. Kreibich" :
>
> On Thu, Nov 12, 2009 at 05:06:20PM +0100, galea...@korg.it scratched
> on the wall:
>
>> int MyClass::xCompare (void* v, int iLen1, const void* str1, int
>> iLen2, const void* str2)
>> {
>
>
>> and I registered it:
>> sqlite3_create_collation(mpDB,"MYCOLLATE", SQLITE_UTF8, NULL,
>> MyClass::xCompare);
>
> You can't do that.
>
> C++ puts a silent "this" pointer in as the first argument, so you
> cannot use a non-static class method as a callback.
>
> You must create a static method, like this:
>
> static int MyClass:xCompareCallback( void *v,
> int iLen1, const void *str1, int iLen2, const void* str2)
> {
> MyClass *ptr = v;
> return v->xCompare(NULL, iLen1, str1, iLen2, str2);
> }
>
> And register like this:
>
> sqlite3_create_collation(mpDB, "MYCOLLATE", SQLITE_UTF8,
> PtrToClassInstance, MyClass::xCompareCallback);
>
>
> The "PtrToClassInstance" must be a pointer to the class instance you
> want called. If you're registering this inside your C++ class, you
> can use "this".
>
>-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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] collation bug?
On Thu, Nov 12, 2009 at 05:06:20PM +0100, galea...@korg.it scratched on the
wall:
> int MyClass::xCompare (void* v, int iLen1, const void* str1, int
> iLen2, const void* str2)
> {
> and I registered it:
> sqlite3_create_collation(mpDB,?MYCOLLATE", SQLITE_UTF8, NULL,
> MyClass::xCompare);
You can't do that.
C++ puts a silent "this" pointer in as the first argument, so you
cannot use a non-static class method as a callback.
You must create a static method, like this:
static int MyClass:xCompareCallback( void *v,
int iLen1, const void *str1, int iLen2, const void* str2)
{
MyClass *ptr = v;
return v->xCompare(NULL, iLen1, str1, iLen2, str2);
}
And register like this:
sqlite3_create_collation(mpDB, "MYCOLLATE", SQLITE_UTF8,
PtrToClassInstance, MyClass::xCompareCallback);
The "PtrToClassInstance" must be a pointer to the class instance you
want called. If you're registering this inside your C++ class, you
can use "this".
-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
Re: [sqlite] ogr2ogr
On Thu, 12 Nov 2009, stormtrooper wrote: > There is a command line tool (ogr2ogr) that runs on linux or windows that > converts dbf to sqlite, csv, xml, etc. It is primarily a converter for > geographic data but supports tabular data as well. OGR is part of fwtools. Keith, Thank you. I'm getting back to using GRASS for terrain analyese and hydrological modeling after a number of years and have not used ogr2ogr. It's installed here. I've read the man page and will now look for syntax examples. It's just the tool I need. Thank you, Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] collation bug?
Hi everybody,
I found a strange behaviour of SQLite (3.6.19) when it relies on a
custom collation during a SELECT execution. So let me explain the
matter from the beginning:
I've got the following simple table:
CREATE TABLE Genre (
idINTEGER NOT NULL UNIQUE,
name VARCHAR(20) NOT NULL UNIQUE COLLATE MYCOLLATE,
image_idINT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_Genre FOREIGN KEY (image_id)
REFERENCES Image (id)
ON DELETE SET NULL
ON UPDATE CASCADE);
-- Indeces
CREATE INDEX Genre_name_idx ON Genre(name);
and I fill it as follows:
INSERT INTO Genre VALUES(0,'',0);
INSERT INTO Genre VALUES(1,'Blues',0);
INSERT INTO Genre VALUES(2,'Classic Rock',0);
INSERT INTO Genre VALUES(3,'Country',0);
INSERT INTO Genre VALUES(4,'Dance',0);
INSERT INTO Genre VALUES(5,'Disco',0);
INSERT INTO Genre VALUES(6,'Funk',0);
INSERT INTO Genre VALUES(7,'Grunge',0);
INSERT INTO Genre VALUES(8,'Hip-Hop',0);
INSERT INTO Genre VALUES(9,'Jazz',0);
...
Then, when I try to execute:
SELECT id FROM Genre WHERE name = 'Jazz'
it doesn't yield any results. On the other hand, when I execute the
following statement:
INSERT INTO Genre(name,image_id) VALUES('Jazz',0)
SQLite correctly returns this errors:
SQLITE_CONSTRAINT[19]: constraint failed
SQLITE_CONSTRAINT[19]: column name is not unique
So my first thought was: my collation method has some bugs! So I used
the NOCASE bult-in collate:
name VARCHAR(20) NOT NULL UNIQUE COLLATE NOCASE
and it works fine. The next step was to try again MYCOLLATE and
compare all results of MYCOLLATE method with the NOCASE ones. To do
that I write the following piece of code:
int MyClass::xCompare (void* v, int iLen1, const void* str1, int
iLen2, const void* str2)
{
//MYCOLLATE
std::wstring s1;
std::wstring s2;
CStringConverter::FromUTF8toUCS2 ((const BYTE *) str1,iLen1, s1);
CStringConverter::FromUTF8toUCS2 ((const BYTE *) str2,iLen2, s2);
int iRes = CCollationStringComparer::Compare(s1.length(),
s1.c_str(), s2.length(), s2.c_str());
//NOCASE
int r = sqlite3StrNICmp(
(const char *)str1, (const char *)str2, (iLen1 0) && (r > 0);
assert(bNegative || bZero || bPositive);
return iRes;
}
and I registered it:
sqlite3_create_collation(mpDB,MYCOLLATE", SQLITE_UTF8, NULL,
MyClass::xCompare);
As you can see the assert condition ensures me that the two methods
yield the same results.
That's the strange behaviour: no assert is ever triggered even if the
SELECT statement keeps yielding no result; but if I change
return iRes; with
return r;
it works fine! I point out that CCollationStringComparer::Compare
always returns 1,0,-1 whereas sqlite3StrNICmp returns different
values (but always with the same sign!).
Any ideas about what's happening? Could it be a SQLite bug?
Cheers
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ogr2ogr
Rich, There is a command line tool (ogr2ogr) that runs on linux or windows that converts dbf to sqlite, csv, xml, etc. It is primarily a converter for geographic data but supports tabular data as well. OGR is part of fwtools. http://fwtools.maptools.org/ http://www.gdal.org/ogr/ - supported formats and OGR SQL are worthy reads. Keith -- View this message in context: http://old.nabble.com/Converting-.dbf-to-SQLite-tp26311756p26320465.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] Converting .dbf to SQLite
Hello! On Thursday 12 November 2009 07:30:28 Rich Shepard wrote: >Perhaps. I learned today that only the Winduhs version of OO.o can import > .mdb files; the linux version cannot. Try to read by Linux ODBC and save to SQLite. 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] sqlite3_analyzer with 3.6.19 distribution
On Nov 12, 2009, at 4:36 PM, Artur Reilin wrote: > I also wonder, why the analyzer isn't working. I doesn't work for me. In what respect is it malfunctioning? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite on Windows XP with ADO.Net - batching updates + inserts in a single transaction - optimal transaction size
For performance reasons, I want to batch my SQLite insert/replace operations in a single SQL transaction that I send to my local SQLite database running on Windows XP. I am also using the ADO.Net assembly to interact with my SQLite database. Does anyone have any insight into how large my transaction batches should be? For example, have up to 300 insert/replace operations in a single transaction? up to 1000 insert/replace operations in a single transaction? etc. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_analyzer with 3.6.19 distribution
I also wonder, why the analyzer isn't working. I doesn't work for me. > > On Nov 12, 2009, at 3:01 PM, Jens Miltner wrote: > >> >> Am 11.11.2009 um 17:45 schrieb D. Richard Hipp: >> >>> >>> On Nov 11, 2009, at 11:24 AM, Jens Miltner wrote: Is sqlite3_analyzer supposed to work in 3.6.19? >>> >>> >>> No. sqlite3_analyzer has been busted for a long time. But the 3.6.0 >>> version of sqlite3_analyzer works just fine, even on databases >>> created >>> using 3.6.19. >> >> Thanks, that helps... >> >> Just out of curiosity: is sqlite3_analyzer just broken and nobody got >> around to fix it, or is it being deprecated? > > Plans are to fix it at some point in the future. It's not urgent though > as there is no reason the 3.6.0 version will stop working. > > Dan. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > Artur Reilin sqlite.yuedream.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_analyzer with 3.6.19 distribution
On Nov 12, 2009, at 3:01 PM, Jens Miltner wrote: > > Am 11.11.2009 um 17:45 schrieb D. Richard Hipp: > >> >> On Nov 11, 2009, at 11:24 AM, Jens Miltner wrote: >>> >>> Is sqlite3_analyzer supposed to work in 3.6.19? >>> >> >> >> No. sqlite3_analyzer has been busted for a long time. But the 3.6.0 >> version of sqlite3_analyzer works just fine, even on databases >> created >> using 3.6.19. > > Thanks, that helps... > > Just out of curiosity: is sqlite3_analyzer just broken and nobody got > around to fix it, or is it being deprecated? Plans are to fix it at some point in the future. It's not urgent though as there is no reason the 3.6.0 version will stop working. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_analyzer with 3.6.19 distribution
Am 11.11.2009 um 17:45 schrieb D. Richard Hipp: > > On Nov 11, 2009, at 11:24 AM, Jens Miltner wrote: >> >> Is sqlite3_analyzer supposed to work in 3.6.19? >> > > > No. sqlite3_analyzer has been busted for a long time. But the 3.6.0 > version of sqlite3_analyzer works just fine, even on databases created > using 3.6.19. Thanks, that helps... Just out of curiosity: is sqlite3_analyzer just broken and nobody got around to fix it, or is it being deprecated? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
