Re: [sqlite] reseting primary key
On Mon, 7 May 2007 11:28:57 -0400, Jonathan Kahn wrote: >Hey all, > I have tried different things such as reindex and vacuum on my primary key >auto inc field but I cannot reset it so that things start from one. I >deleted everything from my table but it still keeps the amount that was >there beforehand on the auto inc so anything new is appened to that number >so I used to have 7 records when I insert new it starts at 8, how can I >start it back from 1, its beginning to drive me a little crazy. You could try altering values in the sqlite_sequence table in your database. It works for me. Remember to delete all records in the data table to avoid problems with the auto increment column(s). Chris Peachment - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] I'm Starving for New User Information
You ask too many questions in one go. I will be surprised if anyone is willing to provide that much assistance all at once. It appears that you need some more fundamental knowledge about programming on GNU/Linux and maybe even about programming in C. Some of your questions are contradictory and suggest you have not done your own homework before asking others to do it for you. e.g. got a ".so" file. This is obviously a shared library ... and Is a C program that uses sqlite statically linked? You ask: >Once I've created a C program that uses sqlite can I administer its database >with sqlite3 or do I have to write utility programs to create a database, >tables, view table contents, etc? Why not try it and see for yourself? Others have done so and not all of them are smarter than you :-) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Getting a list of items on a column
On Sat, 5 May 2007 21:03:27 -0400, jose isaias cabrera wrote:
>Ok, I am going to push my luck. :-) Imagine the same data, what if I
>wanted to get the sum of the other two columns also? I know how to do it
>one at a time, so in this instance I would have to do 3 separate call using
>sum or total, but is there a way that I could do it on the same call and get
>the sum of columns c2 and c3, so that I would get something like this,
>a,11,19
>b,3,4
>c,6,9
>Yes, I know. I am pushing my luck. :-)
create table t1 (c1,c2,c3);
insert into t1 values ('a', 1, 2);
insert into t1 values ('b', 3, 4);
insert into t1 values ('c', 5, 6);
insert into t1 values ('a', 9, 8);
insert into t1 values ('a', 1, 9);
insert into t1 values ('c', 1, 3);
select c1, sum(c2), sum(c3) from t1 group by c1;
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Getting a list of items on a column
On Sat, 5 May 2007 19:58:19 -0400, jose isaias cabrera wrote: >Greetings! >imagine this data content, >c1, c1, c3 >a, 1, 2 >b, 3, 4 >c, 5, 6 >a, 9, 8 >a, 1, 9 >c, 1, 3 >I would like to get a list of all the items in c1. So, the return would be, >a, >b, >c >I know how to do it programmatically, but is there a way to get it right >from SQLite? >thanks, >josé select distinct c1 from tablename; - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] May I ask why the source distribution mechanism was changed starting with 3.3.14?
With the suggestion that the problem was a compiler bug in PellesC for Windows, I posted a message on their forum. One response suggested a couple of configuration changes and also said to wait a while because it took a long time to compile. So, I let the compiler continue after it had reported a timeout after 300 seconds. Surprise, surprise, it correctly handled the single source file and generated all necessary output components. The dll file was even smaller than the distributed image, but I can not say if that is just flagging a compile for minimum size rather than minimum processing speed. I'm happy to report that I have no reason to object to the change in preprocessed source. Thanks again Dr. Hipp for a great product. Regards, Chris Peachment - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re[2]: [sqlite] May I ask why the source distribution mechanism was changed starting with 3.3.14?
Hello Dr. Hipp:
I have previously reported compiler warnings to you issued
by the Pelles C MS-Windows compiler and you have repaired
them in the following release. This is the first time I have tried
to compile the single file sqlite3.c using the compiler version 3.50
and it reported some mismatches between prototypes and
subsequent declarations for the following functions. In all
cases, the prototype used a void parameter list and the
declaration used a K style empty parameter list.
line 9820: ThreadData *sqlite3ThreadData(void){
line 9833: const ThreadData *sqlite3ThreadDataReadOnly(void){
line 9844: void sqlite3ReleaseThreadData(void){
line 9876: int sqlite3MallocFailed(void){
line 16620: void sqlite3WinEnterMutex(void){
line 16637: void sqlite3WinLeaveMutex(void){
line 64683: int sqlite3_global_recover(void){
Changing the declarations as shown above eliminated
the warnings.
After clearing these warnings, I discovered that the Pelles C
compiler was unable to complete the compilation of sqlite3.c
and timed out after 300 seconds. So it appears that I need
to revert to separate source files that were available with
earlier versions of sqlite3.
Regards,
Chris Peachment
On Mon, 30 Apr 2007 21:50:44 -0400, Teg wrote:
>Hello drh,
>I'm happy with the new source release method. I was fine with the old
>way too. The new method is slightly more convenient for me when I
>upgrade.
>C
>Monday, April 30, 2007, 5:46:19 PM, you wrote:
>dhc> Martin Jenkins <[EMAIL PROTECTED]> wrote:
>>>
>>> As fas as I know, the dev team is Dr Hipp and Dan Kennedy (apologies if
>>> there's someone else and I missed you) and I agree, it is slightly odd
>>> for neither of them to reply.
>>>
>dhc> Why is it odd? The issue is not something that needs replying
>dhc> to. This is not a bug. There has been a request for a different
>dhc> kind of partially-compiled code distribution for the convenience
>dhc> of some users. We have observed the request. What more needs to
>dhc> be said?
>dhc> --
>dhc> D. Richard Hipp <[EMAIL PROTECTED]>
>dhc>
>-
>dhc> To unsubscribe, send email to [EMAIL PROTECTED]
>dhc>
>-
>--
>Best regards,
> Tegmailto:[EMAIL PROTECTED]
>-
>To unsubscribe, send email to [EMAIL PROTECTED]
>-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] how to cross compile sqlite 3
On Thu, 1 Feb 2007 10:41:25 +0100, anis chaaba wrote: >I'm cross compiling sqlite3 for arm and there's errors in the >configure and the makefile generated. >Do anyone of you cross compiled sqlite3? >thanks in advance Using the preprocessed source code in sqlite-source-3_3_12.zip lets you avoid the problems of configure checking the host computer attributes when it should specify the target computer attributes. I have compiled this source for both linux-arm and windows-pc without issues. Chris Peachment - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Abuse of the SQLite website
On Tue, 30 Jan 2007 12:58:01 +, [EMAIL PROTECTED] wrote: >Last night, a single user (or, at least, a single IP address) >in China that self-identified as running windows98 and >Mozilla 4.0 attempted to download sqlite-3.3.12.tar.gz >24980 times and sqlite-source-3_3_12.zip 25044 times >over about a 5 hour period, sucking up significant >bandwidth in the process. >I've seen this type of thing before and have on occasion >banned specific IP addresses from the website using > iptables -A INPUT -s -j DROP >But lately, there have been so many problems coming from >win98 and moz4 that I'm thinking of banning all traffic >that self-identifies as such in the User-Agent string of >the HTTP header. >Thoughts anyone? Are there less drastic measures that might >be taken to prevent this kind of abuse? No human could click fast enough and long enough to request (24980 + 25044) / (5 * 60) = 166.75 downloads per minute so it is probably safe to assume that a virus or spambot is making the requests. As such, the putative agent identifiers are likely faked and could be changed easily to report something more modern. This would circumvent your trap based on User-Agent. I think you need to place a general limit on requests from ANY ip address using some form of throttling in the web server. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Recovery tool ?
Alimentation is the French word and alimentacion is the Spanish word for "food" or "feed". In this case the poster is referring to an electrical power failure on the computer. On Tue, 08 Aug 2006 11:38:09 +, [EMAIL PROTECTED] wrote: >"zze-ContentLab MARTINEAU Y ext RD-SIRP-REN" <[EMAIL PROTECTED]> wrote: >> Hello, >> >> I had an alimentation interruption on my sqlite database running on >> linux. And now the data file seems to be corrupted. Is there any tool to >> cleanup this file ? Or is it necessary to restart from a blank new data >> file ? >> >What is an "alimentation interruption" and how might such a thing >corrupt the database? >-- >D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] create table command from listview in vb.net
Sorry, but I don't use proprietary programming languages
and can not provide assistance with Visual Basic.
On Tue, 11 Jul 2006 12:00:34 +0100, John Newby wrote:
>Hi, thanks for your speedy reply,
>Yes, you have understood my code correctly, this is the create table
>statement, and at the time of execution all column names are known to the
>form, I am just unsure as how to get them from the listview to place into a
>create table statement, I think I will need some sort of FOR loop to bring
>back each line that is in the listview but I am unsure as how to do this.
>This is my first time working with listviews and I've searched the internet
>and have 7 books from the library on vb.net but none explain how to get
>details from a listview, only how to populate it.
>I think I worded my original message wrong.
>Do you know of a way I could get the details from the listview?
>Many thanks for your help
>John
>On 11/07/06, C.Peachment <[EMAIL PROTECTED]> wrote:
>>
>> If understand your code correctly, this line creates a table with a
>> single column.
>>
>> dbConn.createTable("CREATE TABLE " & tblName & "(" & fldName & " "
>> &
>> fldAttribute & ")")
>>
>> You describe a user interface to be used to create tables with multiple
>> columns.
>>
>> You must do one of two things:
>>
>> 1. delay the create table statement execution until all columns
>> are
>> known and can be included inside the parentheses, or
>>
>> 2. perform the create for the first column and then use the alter
>> table
>> statement to append remaining columns, perhaps one at a time.
>>
>> ---
>>
>> On Tue, 11 Jul 2006 10:57:36 +0100, John Newby wrote:
>>
>> >Hi, I am creating a front-end to the sqlite DBMS using VB.Net 2002. I
>> have
>> >managed to get the name of the table, field names and types from user
>> input
>> >displayed into a listview but I can only get the Create table command to
>> >accept the last input values, so if the table has more than one
>> field(which
>> >nealry every table has) it simply ignores the previously entered fields.
>>
>> >How would I get it to create an SQL query with all the inputted field
>> >details?
>>
>> >I have inserted the code I have used so far.
>>
>> >Many thanks for your help.
>>
>> >This button opens up a new form for the user to input the field name and
>> >select the field type then brings them back and displays them in a
>> listview.
>>
>>
>> >Public Sub btnAddColumn_Click(ByVal sender As System.Object, ByVal e As
>> >System.EventArgs) Handles btnAddColumn.Click
>> >Dim frmAddColumns1 As New frmAddColumns()
>> >frmAddColumns1.ShowDialog(Me)
>> >fldName = frmAddColumns.ColumnNameTB.Text
>> >fldAttribute = frmAddColumns.ColumnTypeTB.SelectedItem
>> >'Create ListViewItem
>> >Dim item1 As New ListViewItem(fldName, 0)
>> >item1.SubItems.Add(fldAttribute)
>> >'Add the items to the ListView.
>> >listView1.Items.AddRange(New ListViewItem() {item1})
>> >Me.Controls.Add(listView1)
>> >End Sub
>>
>> >This button takes the input table name, field name and attributes and
>> >creates the SQL command to send to the database to create the table:
>>
>>
>> >Private Sub btnCreateTable_Click(ByVal sender As System.Object, ByVal e
>> As
>> >System.EventArgs) Handles btnCreateTable.Click
>> >Dim tblName As String
>> >tblName = txtTableName.Text.ToString()
>> >If Len(txtTableName.Text) < 1 Then
>> >MessageBox.Show("Please type a name for the table")
>> >ElseIf Len(txtTableName.Text) > 0 Then
>> >Try
>> >dbConn.openExistingDatabse("Data Source=" & getDBName() &
>> >";Version=3;New=False;Compress=True;")
>> >dbConn.createSQLCommand()
>> >dbConn.createTable("CREATE TABLE " & tblName & "(" & fldName & " " &
>> >fldAttribute & ")")
>> >MessageBox.Show("Table created successfully")
>> >Me.Close()
>> >Dim frmInsertData1 As frmInsertData = New frmInsertData()
>> >frmInsertData1.Show()
>> >Catch es As Exception
>> >MessageBox.Show(es.Message)
>> >End Try
>> >End If
>> >End Sub
>>
>>
>>
>>
>>
Re: [sqlite] create table command from listview in vb.net
If understand your code correctly, this line creates a table with a single
column.
dbConn.createTable("CREATE TABLE " & tblName & "(" & fldName & " " &
fldAttribute & ")")
You describe a user interface to be used to create tables with multiple columns.
You must do one of two things:
1. delay the create table statement execution until all columns are
known and can be included inside the parentheses, or
2. perform the create for the first column and then use the alter table
statement to append remaining columns, perhaps one at a time.
---
On Tue, 11 Jul 2006 10:57:36 +0100, John Newby wrote:
>Hi, I am creating a front-end to the sqlite DBMS using VB.Net 2002. I have
>managed to get the name of the table, field names and types from user input
>displayed into a listview but I can only get the Create table command to
>accept the last input values, so if the table has more than one field(which
>nealry every table has) it simply ignores the previously entered fields.
>How would I get it to create an SQL query with all the inputted field
>details?
>I have inserted the code I have used so far.
>Many thanks for your help.
>This button opens up a new form for the user to input the field name and
>select the field type then brings them back and displays them in a listview.
>Public Sub btnAddColumn_Click(ByVal sender As System.Object, ByVal e As
>System.EventArgs) Handles btnAddColumn.Click
>Dim frmAddColumns1 As New frmAddColumns()
>frmAddColumns1.ShowDialog(Me)
>fldName = frmAddColumns.ColumnNameTB.Text
>fldAttribute = frmAddColumns.ColumnTypeTB.SelectedItem
>'Create ListViewItem
>Dim item1 As New ListViewItem(fldName, 0)
>item1.SubItems.Add(fldAttribute)
>'Add the items to the ListView.
>listView1.Items.AddRange(New ListViewItem() {item1})
>Me.Controls.Add(listView1)
>End Sub
>This button takes the input table name, field name and attributes and
>creates the SQL command to send to the database to create the table:
>Private Sub btnCreateTable_Click(ByVal sender As System.Object, ByVal e As
>System.EventArgs) Handles btnCreateTable.Click
>Dim tblName As String
>tblName = txtTableName.Text.ToString()
>If Len(txtTableName.Text) < 1 Then
>MessageBox.Show("Please type a name for the table")
>ElseIf Len(txtTableName.Text) > 0 Then
>Try
>dbConn.openExistingDatabse("Data Source=" & getDBName() &
>";Version=3;New=False;Compress=True;")
>dbConn.createSQLCommand()
>dbConn.createTable("CREATE TABLE " & tblName & "(" & fldName & " " &
>fldAttribute & ")")
>MessageBox.Show("Table created successfully")
>Me.Close()
>Dim frmInsertData1 As frmInsertData = New frmInsertData()
>frmInsertData1.Show()
>Catch es As Exception
>MessageBox.Show(es.Message)
>End Try
>End If
>End Sub
Re: [sqlite] Group a set of events by day: howto??
The SQL query will do the grouping and present them in the correct sequence.
All programming languages require that you iterate over the set of retrieved
records. What you do with them as you retrieve them is up to you.
Code would look quite similar in 'C' and would have the same structure in any
other procedural language, so there is nothing special about PHP in this
example.
On Mon, 10 Jul 2006 13:42:15 +0200 (CEST), [EMAIL PROTECTED] wrote:
>Hello, and thanks for the reply.
>I dont' know PHP, but I can understand that you are suggesting to "manually"
>iterate the records to find the events, and group them by day. Is this right?
>"C.Peachment" <[EMAIL PROTECTED]> ha scritto: What is wrong with using:
>select EventID, EventDate, EventTime
> order by EventDate, EventTime
> group by EventDate;
>You have a separate display problem - you want
>to put up to three records on the same line. This is
>a language and application specific problem. In PHP,
>using the PDO module and producing output for a
>web page, it can be solved with something like:
>$SqlText = "select EventID, EventDate, EventTime " .
> " order by EventDate, EventTime " .
> " group by EventDate";
>$Stmt = $dbh->prepare($SqlText);
>$Stmt->execute();
>$Found = false;
>$PriorDate = 0;
>while ($Row = $Stmt->fetch(PDO::FETCH_OBJ)) {
> $Found = true;
> if ($Row->EventDate != $PriorDate) {
>if ($PriorDate != 0) {
> echo "
>\n";
>}
>echo "$Row->EventID, $Row->EventDate";
> }
> echo "$Row->EventTime";
> $PriorDate = $Row->EventDate;
>if ($Found) {
> echo "
>\n"; // close off last output statement
>$Stmt->closeCursor();
>On Mon, 10 Jul 2006 11:45:24 +0200 (CEST), [EMAIL PROTECTED] wrote:
>>Hello, everybody
>>I have the following problem: I have a table that contains "Events", with the
>>related date and time:
>>Fields:
>>EventID
>>EventDate
>>EventTime
>>I would like to "group" these records by day, returning all the "times" of
>>the events, like:
>>EventID, EventDate, EventTime1, EventTime2, EventTime3
>>(I can assume that no more than 3 events happen on the same day).
>>I did this query:
>>SELECT T1.EventDate, T1.EventTime AS Time1, T2.EventTime AS Time2
>>FROM Events AS T1 LEFT JOIN Events AS T2 ON T2.EventDate = T1.EventDate AND
>>T2.EventTime > T1.EventTime;
>>This query "works", but it has the following problems:
>>1. it returns several times the same "record" (can't understand why)
>>2. it takes about 30 seconds (!!) to run, and it consumes all the physical
>>memory of the system...
>>Any help on this?
>>Thanks in advance for any reply.
>>Kind regards
>>Marco
>> Chiacchiera con i tuoi amici in tempo reale!
>> http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com
> Chiacchiera con i tuoi amici in tempo reale!
> http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com
Re: [sqlite] Group a set of events by day: howto??
What is wrong with using:
select EventID, EventDate, EventTime
order by EventDate, EventTime
group by EventDate;
You have a separate display problem - you want
to put up to three records on the same line. This is
a language and application specific problem. In PHP,
using the PDO module and producing output for a
web page, it can be solved with something like:
$SqlText = "select EventID, EventDate, EventTime " .
" order by EventDate, EventTime " .
" group by EventDate";
$Stmt = $dbh->prepare($SqlText);
$Stmt->execute();
$Found = false;
$PriorDate = 0;
while ($Row = $Stmt->fetch(PDO::FETCH_OBJ)) {
$Found = true;
if ($Row->EventDate != $PriorDate) {
if ($PriorDate != 0) {
echo "\n";
}
echo "$Row->EventID, $Row->EventDate";
}
echo "$Row->EventTime";
$PriorDate = $Row->EventDate;
if ($Found) {
echo "\n";// close off last output statement
$Stmt->closeCursor();
On Mon, 10 Jul 2006 11:45:24 +0200 (CEST), [EMAIL PROTECTED] wrote:
>Hello, everybody
>I have the following problem: I have a table that contains "Events", with the
>related date and time:
>Fields:
>EventID
>EventDate
>EventTime
>I would like to "group" these records by day, returning all the "times" of the
>events, like:
>EventID, EventDate, EventTime1, EventTime2, EventTime3
>(I can assume that no more than 3 events happen on the same day).
>I did this query:
>SELECT T1.EventDate, T1.EventTime AS Time1, T2.EventTime AS Time2
>FROM Events AS T1 LEFT JOIN Events AS T2 ON T2.EventDate = T1.EventDate AND
>T2.EventTime > T1.EventTime;
>This query "works", but it has the following problems:
>1. it returns several times the same "record" (can't understand why)
>2. it takes about 30 seconds (!!) to run, and it consumes all the physical
>memory of the system...
>Any help on this?
>Thanks in advance for any reply.
>Kind regards
>Marco
> Chiacchiera con i tuoi amici in tempo reale!
> http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com
Re: [sqlite] Make a dll for .net
Penso que el primer sitio es mas hoy en dia. No necessita dar disculpa para tu ingles. No tengamos problemas entender. Vosotro facilidad con ingles es mejor que nuestro espanol. Disculpame, estudio el espanol desde dos anos y no puedo hablar/escribir facilmente. :-) On Sat, 8 Jul 2006 15:31:21 +0300, Veysel Harun Sahin wrote: >Hi, >Take a look at the following sites. >http://sqlite.phxsoftware.com/ >http://adodotnetsqlite.sourceforge.net/ >On 7/8/06, Robinson Andres Hernandez Salas <[EMAIL PROTECTED]> >wrote: >> >> Hi. >> >>First, i dont speak english so sorry bout my "weird" way to write my >> question >> and try to understandme please. >> >>Well, im new with sqlite, but i think this is the solution for that i >> was >> looking for. A lite dbsm :). >> >>I need to make a program in Visual Studio 2005, using Visual Basic. (Vb >> can >> use dlls writed in other lenguages like C) >> >>I downloaded the .dll zip for windows and i cant include the dll to my >> proyect .. its say "this dll is not for .net framework" or something like >> that. >> Then, i go to microsoft to look for how to include a Dll writed in C, i >> found a >> page that describe how to make a dll write in c, includeable in vb. >> >> So, my next step is recompile the dll with the sqlite source, i >> downloaded >> the sqlite source for windows. that who say "all what you need for compile >> with >> any ordinary compiler in win" (or something like that) >> >> Well, i need help with compiling this sources in visual studio 2005 >> and make >> that dll. OR if the orginal downloable dll works in visual studio and >> should run >> fine, someone can tell me how to run it. >> >>Short question. ¿ how i run the sqlite dll in visual studio ? >> >> i hope someone can help me, and sorry bout my english :) bye. >> >>Robinson H. >> >> >> >-- >Veysel Harun Sahin
Re: [sqlite] i have a few Qs - sqlite3
While this is good advice, I fear the content might be too much for our impatient petitioner. Learning to crawl before trying to walk or run can be a poorly understood fact of life for some novices. On Sat, 08 Jul 2006 11:23:03 +0100, Martin Jenkins wrote: > You might find it useful to >take a look at http://www.catb.org/~esr/faqs/smart-questions.html before >asking any more questions. Useful advice in there that you would do well >to digest. ;)
Re: [sqlite] Open sqb database in php?
You are using PHP syntax incorrectly.
You could use the following:
try {
$dbh = new PDO('sqlite:c:\Program Files\Kinetic\BaseStation\BaseStation.sqb');
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "";
die();
$Statement = $dbh->prepare("select * from location");
$Statement->execute();
$Array = $Statement->fetchAll(PDO::FETCH_OBJ);
$Statement->closeCursor();
foreach ($Array as $row) {
print_r($row);
On Fri, 7 Jul 2006 16:16:41 +0200, Albert van Bergen wrote:
>Thanks for the info about the PDO_SQLITE. Missed that completly.
>Now I can created an sqlite3 database and read from the database with the
>following code:
>try {
>$dbh = new PDO('sqlite:c:\Program
>Files\Kinetic\BaseStation\BaseStation.sqb');
> foreach ($dbh->query('SELECT * from location') as $row) {
> print_r($row);
> }
> $dbh = null;
>} catch (PDOException $e) {
> print "Error!: " . $e->getMessage() . "";
> die();
>}
>?>
>This only works for a database I made.
>The code above gives the following message: " Warning: Invalid argument
>supplied for foreach() in"
>when I try to read a table from the database I want to open.
>The database I want to open can be read with SQLite Admin and the table
>contains data.
>Can someone explain to me why the Warning is coming up?
>Albert
>- Original Message -
>From: "Firman Wandayandi" <[EMAIL PROTECTED]>
>To:
>Sent: Thursday, July 06, 2006 9:37 PM
>Subject: Re: [sqlite] Open sqb database in php?
>> On 7/6/06, Albert van Bergen <[EMAIL PROTECTED]> wrote:
>>> Hi All,
>>>
>>> I pretty new into sqlite.
>>>
>>> Is it possible to open a sqb database in php?
>>>
>>
>> Use PDO_SQLITE for sqlite2 and sqlite3 PHP5 only, or use SQLite
>> extension for sqlite2. SQLite extension already built-in in PHP5, for
>> PHP4 you must load the php_sqlite extension first.
>>
>>> When I use sqlite_open I do get the following warning:
>>>
>>> Warning: sqlite_open() [function.sqlite-open]: file is encrypted or is
>>> not a
>>> database in C:\Program Files\Apache Group\Apache2\htdocs\sql.php on line
>>> 2
>>> file is encrypted or is not a database
>>>
>>
>> I suspect if your file is sqlite database, but it's a sqlite file
>> format version 3 and you tried to open it with sqlite_open() which is
>> support only sqlite file format version3. You should use PDO_SQLITE
>> for open it. Also notes PDO currently support sqlite file format
>> version 3.2, no update yet. So be carefull with your database or your
>> data will lost.
>>
>> --
>> Firman Wandayandi
Re: [sqlite] Syntax Errors with various strings? & ...Ampersand
The solution I have adopted to both of these issues is to: 1. ensure all strings are enclosed by matching quotation marks, and 2. use the question mark substitution form of prepared statement with subsequent bind of parameters. This can only be done from a programming language and not the command line interface. On Wed, 05 Jul 2006 10:44:31 +0200, Roger wrote: >I have a company name as follows: >Chemistry & chemicals >I have plenty of those in my database which come with ampersands, now >when i do a query i get nothing. >How best can i write the query using a string with an ampersand as part >of it. >I am developing in a PHP/SQlite environment. On Wed, 5 Jul 2006 06:07:46 -0300, Gussimulator wrote: >Hi, When I have strings with "-", "!", "\" or similar characters I get a >syntax error.. Now, I thought this would happen with a few of this chars so I made 2 routines in my program, one that converts each of this chars into a flag string, which then, by the other routine can be reverted to the original characters to obtain the string in its original form, so I can later work with it by my side. >Now, I've found myself with syntax errors even on strings that didnt had any >strange characters, So.. I'm wondering, what can I do to prevent this? Does SQLite provide a "format" routine or is there anything I can do to prevent the syntax errors... like this ones? (since my data wont get into the db if theres an error, of course, thats why Im concerned). >I'm really worried about this, hence I subscribed on the list (first >message!). I hope someone can help me out on this one, thanks. >And, Indeed.. I'm quite a newbie on SQL but, I never thought I'd find myself >with this type of problem.
RE: [sqlite] real time gui updates
You could include a date/time field somewhere in the database that is queried on a regular basis by your display function. Depending on the update rate, you could poll every second, every minute, or every hour. Any change of value would justify a full data retrieval and screen refresh. The update process can lock the database using a transaction each time it performs an update, so the display program needs to be prepared for a denial of access for a few milliseconds. On Thu, 29 Jun 2006 19:50:09 +1000, Rob Menegon wrote: >Not sure whether I understand how this would occur. >The application is not doing or responsible for the updates to the database. >Its only function in life is to retrieve and display data. Updates, >modifications occur via another application, so I was incorrect in my >previous response to you - one user (app) doing updates and another >displaying data - independent processes. >-Original Message- >From: John Stanton [mailto:[EMAIL PROTECTED] >Sent: Thursday, 29 June 2006 2:51 PM >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] real time gui updates >In that case your application knows whenever the database is changed and can >call a refresh routine. >Rob Menegon wrote: >> No a single user/instance of the application reading from the database. >> >> >> >> -Original Message- >> From: John Stanton [mailto:[EMAIL PROTECTED] >> Sent: Wednesday, 28 June 2006 10:45 PM >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] real time gui updates >> >> Rob Menegon wrote: >> >>>I have an application that displays data retrieved from various SQLite >>>tables. As the data within the tables changes I want these changes >>>reflected in the application real time. >>> >>>Can someone advise on the best way of doing this? >>> >>>Rob Menegon >>> >>> >> >> Do you have multiple independent users? >>
Re: Re[2]: [sqlite] How to get column description from table using SQL
On Wed, 28 Jun 2006 12:40:52 +0600, blins wrote: >>> How to get column description from table using SQL >CP> To get table definition for '': >CP> select sql from sqlite_master where type='table' and name=''; >I need column definition. Sqlite does not require that columns be typed so they can hold anything. You need to read the table definition to see what the designer expects will be stored in each column.
Re: [sqlite] How to get column description from table using SQL
On Wed, 28 Jun 2006 10:29:47 +0600, blins wrote: > How to get column description from table using SQL To get table definition for '': select sql from sqlite_master where type='table' and name='';
Re: [sqlite] v3.2.1 and current differences!
You can create the database with the older Sqlite version, but any use with the new version must be preceded by the pragma, otherwise the new version changes the data format, making it unreadable by the old version thereafter. It got me confused when I started too. On Mon, 26 Jun 2006 21:28:59 +0200, Edwin Knoppert wrote: >>After opening the database file with a later library version and before >>doing anything else, issue the command: >Sorry, maybe i misunderstood. >The db is created with the latest release, v3.2.1 did not manage to read it. >Maybe the pragma helps anyway.. >- Original Message ----- >From: "C.Peachment" <[EMAIL PROTECTED]> >To: <sqlite-users@sqlite.org> >Sent: Monday, June 26, 2006 8:16 PM >Subject: Re: [sqlite] v3.2.1 and current differences! >> On Mon, 26 Jun 2006 20:03:35 +0200, Edwin Knoppert wrote: >> >>>Just wanted to warn you i can not read a newly created table created with >>>the current release and opening it in v3.2.1 (afaik) >> >>>Sorry, i removed the older dll, i overwrote it with the latest and read >>>the >>>table instantly. >>>Before i had 0 tables shown. >> >>>A simple query was used: >> >>>CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT ) >> >>>I believe i also tried first: >>>CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME ) >>>Sorry, i forgot. >> >>>I may assume only a major version will have a different format? >> >> >> After opening the database file with a later library version and >> before doing anything else, issue the command: >> >> PRAGMA legacy_file_format=ON; >> >> >> >>
Re: [sqlite] v3.2.1 and current differences!
On Mon, 26 Jun 2006 20:03:35 +0200, Edwin Knoppert wrote: >Just wanted to warn you i can not read a newly created table created with >the current release and opening it in v3.2.1 (afaik) >Sorry, i removed the older dll, i overwrote it with the latest and read the >table instantly. >Before i had 0 tables shown. >A simple query was used: >CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT ) >I believe i also tried first: >CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME ) >Sorry, i forgot. >I may assume only a major version will have a different format? After opening the database file with a later library version and before doing anything else, issue the command: PRAGMA legacy_file_format=ON;
RE: [sqlite] Sqlite crashes when i imort huge list
On Mon, 26 Jun 2006 11:20:59 +0100, Brandon, Nicholas (UK) wrote: >>The use of sequential numbers as the trailing part of the 'word' >>results in a continual rebalancing of the b-tree with each insertion. >Is that right considering it looks like you have not created an index on >the word column before inserting the data? Oops, you got that right :-) I was doing a lazy Sunday morning quick and dirty implementation and forgot to define the index. If you assume there had been a PRIMARY KEY phrase after the field definition then I think my comment would be valid.
Re: [sqlite] Need sql query help
On Sun, 25 Jun 2006 18:24:50 -0700 (PDT), onemind wrote:
>It took over 8 hours, so if anyone could tell me a text command that would
>do this same task of importing a txt file into a table through the sqlite3
>command line that would be great. It must be the gui slowing it down
>somehow.
Repeating details from my earlier post:
--
create table wordlist (word text);
select current_time;
begin transaction;
insert into wordlist values ("test0");
insert into wordlist values ("test1");
...
insert into wordlist values ("test18");
insert into wordlist values ("test19");
commit transaction;
select current_time;
select count(*) from wordlist;
--
It took 8 SECONDS, not 8 HOURS.
The above lines were taken directly from the
text file that I had created and then imported
into the sqlite3 command line utility by typing
the command:
.read word.lst
The only change for this email was to delete
the 199,996 intermediate text lines that would
just get in the way of this example.
The suggestion made to use a bitset is an
excellent one. Put an index on the bitset field.
The sql AND operator will find all combinations
very quickly.
Here is sample code to create the content of
the bitset field from the characters in each word.
If you wrap this into a small program that reads
the word list and creates the above insert
statements then you can also insert the bitset
value as a second field in each insert statement.
unsigned long int
GetBitSetOf (char * InWord)
int J, K;
unsigned long int BitSet; // must be at least 26 bits wide
K = strlen(InWord);
BitSet = 0;
for (J = 0; J < K; J++) {
BitSet |= 1 << (InWord[J] - 65);// assumes all letters are uppercase
only
}
return (BitSet);
} // GetBitSetOf
Re: [sqlite] Sqlite crashes when i imort huge list
There was a small error in my previous post.
The 'begin transaction;' line was missed when
I copied the text out of the script and editted
it to remove the code around the text.
On Sat, 24 Jun 2006 20:10:58 -0700 (PDT), onemind wrote:
>I am using the sqlite gui and click import table from csv. I select a txt
>file that contain over 200,000 words in a list. Sqlite works fine with a
>smaller list of 200-300 words but when i import my big list, it hangs for
>ages or completely crashes my computer.
>Does anyone know how i can import this list into a table successfully?
The following text file, "word.txt", was created by a small script:
create table wordlist (word text);
begin transaction;
select current_time;
insert into wordlist values ("test0");
insert into wordlist values ("test1");
...
insert into wordlist values ("test18");
insert into wordlist values ("test19");
commit transaction;
select current_time;
select count(*) from wordlist;
This text file was executed within the sqlite3 command line utility
by typing the command:
.read word.txt
The resulting screen output was
11:20:40
11:20:48
20
indicating the insertion of 200,000 words into a simple database
required 8 seconds of clock time on a 1.5 ghz AMD Sempron cpu
running MS-Windows XP.
The use of sequential numbers as the trailing part of the 'word'
results in a continual rebalancing of the b-tree with each insertion.
For performances reasons, this is probably the worst kind of data
to insert into a database. Random words inserted result in many
leaves that are partially filled and fewer rebalance acts.
Eight seconds to insert the lot is pretty good.
You could create a similar text file from the CSV file using your
editor.
Chris
Re: [sqlite] Sqlite crashes when i imort huge list
On Sat, 24 Jun 2006 20:10:58 -0700 (PDT), onemind wrote:
>I am using the sqlite gui and click import table from csv. I select a txt
>file that contain over 200,000 words in a list. Sqlite works fine with a
>smaller list of 200-300 words but when i import my big list, it hangs for
>ages or completely crashes my computer.
>Does anyone know how i can import this list into a table successfully?
The following text file was created by a small script:
create table wordlist (word text);
select current_time;
begin transaction;
insert into wordlist values ("test0");
insert into wordlist values ("test1");
...
insert into wordlist values ("test18");
insert into wordlist values ("test19");
commit transaction;
select current_time;
select count(*) from wordlist;
This text file was executed within the sqlite3 command line utility
by typing the command:
.read word.txt
The resulting screen output was
11:20:40
11:20:48
20
indicating the insertion of 200,000 words into a simple database
required 8 seconds of clock time on a 1.5 ghz AMD Sempron cpu
running MS-Windows XP.
The use of sequential numbers as the trailing part of the 'word'
results in a continual rebalancing of the b-tree with each insertion.
For performances reasons, this is probably the worst kind of data
to insert into a database. Random words inserted result in many
leaves that are partially filled and fewer rebalance acts.
Eight seconds to insert the lot is pretty good.
You could create a similar text file from the CSV file using your
editor.
Chris
Re: [sqlite] Preferred way to copy/flush new memory db to disk db ?
On Sun, 25 Jun 2006 01:58:10 -0700 (PDT), RohitPatel wrote: >Intial database will have about 30+ tables, very few records in each of >these tables, one or two indices on some tables. For such a small database, why not create it directly on disk? The time required should be just a one second or two. Remember to start the command sequence with "begn transaction" and finish with "commit transaction". Chris
Re: [sqlite] Re : [sqlite] Very blocking problem... please help me
On Thu, 22 Jun 2006 19:14:35 + (GMT), Fred a wrote: >But without this problem context, can i : >- open a (new) database >- insert some records >- (don t close db) >- select field >...? You can test this requirement using the sqlite3 command line utility. There is a tutorial on the wiki site: http://www.sqlite.org/quickstart.html and http://www.sqlite.org/sqlite.html Regards, Chris
Re: [sqlite] suggesiton needed for using SQL lite in a situation
On Thu, 22 Jun 2006 18:48:08 +0530, Puneet Goel wrote: >that there can be multiple incoming SMS/MMS for >different users at the same time. Also there can be multiple reads too all >at the same time. Frequency may be high considering there might be thousands >of users. >> >> >> Open/close of multiple databases could be time consuming. >> >> >> >> that's the point. >Anything on security point of view. Can any user send some malicious script >thru SMS which can corrupt the database. My main intention of using multiple >databases is that if some user send some malicious script he should be able >to corrupt only one DB and not others. Am i right in this assumption ? Since the content of the SMS/MMS message is not important for database purposes, you might consider storing the message as a plain file in a directory that is user specific. Then the database contains just fields like: - user identifier - current message status - date/time of receipt - date/time of retransmission - pathname for content file The operating system can write the message content into a hierarchical directory structure based on successive characters of the user's identifier (which must be unique anyway). The database is used for control purposes only; records are small and written quickly. Retrieval is also fast since it would be one or two field indexing. You might want to use Userd + MessageStatus as the key. Of course, similar functionality is possible just using the operating system's directory and file management, so why is the database important to you? Regards, Chris
Re: [sqlite] suggesiton needed for using SQL lite in a situation
On Thu, 22 Jun 2006 12:59:54 +0530, Puneet Goel wrote: >I am considering using SQL Lite for my purpose. For that i need some help in >decision making. I have an applivation which nneds to store muliple number >of messages for a user. Messages will grow over time. Also there can be >thousands of users. >Which approach would be better is i use SQL Lite (From developer ease, >Security etc point of view) >1) I make 1 DB having data for so many users. >2) I make seperate DB for each user This is a decision only you can make based on local knowledge and experience. Both approaches can work. Other developers have been working with datasets containing 10's of millions of records without difficulty. An important factor might be the frequency of message record insertion and/or deletion. A rate of several per minute should be no problem for a single database but several per second might cause unacceptable lockouts for readonly uses. How many writing processes will access the database "simultaneously"? Open/close of multiple databases could be time consuming.
Re: [sqlite] SQLiteSpy 1.5.4 released
On Wed, 21 Jun 2006 09:24:35 +0200, Ralf Junker wrote: >>1. SQLiteSpy is able to read and work with database files >>formatted by versions of Sqlite earlier than 3.3.6 but it also >>appears to change the database format rather than leave >>it as it was found. >> >>I use php version 5.1.4 including Sqlite version 3.2.8. >>There is a database format change in later versions of >>Sqlite that means php is NOT able to read these later >>versions. >SQLiteSpy does not normally change the database format, just as SQLite does >not. However, starting with SQLite 3.3.0, the file format changed slightly. This is no problem until you run the VACUUM command, which I suppose you did? VACUUM causes SQLite (and therefore SQLiteSpy as well) to rewrite the database and to update it to the latest file format. >There are two solutions to your problem: >1. Don't run VACUUM from SQLite 3.3.0 or later ;=) >2. Issue > PRAGMA legacy_file_format=ON; > before running VACUUM. This instructs SQLite to use the > pre 3.3.0 file format and maintain compatability with > your 3.2.8 PHP version. >Btw: The problem applies to all software using SQLite 3.3.0 or later, even to >the SQLite command-line application. It appears that VACUUM is not the only SQL command to cause this behaviour. I had done a number of INSERT and UPDATE commands before closing the database and attempting to use it with PHP. The VACUUM command was not used. For the moment, my situation is not unique since other users of PHP will face it too. The pragma legacy_file_format=on instruction should solve the problem. Any possibility that it could be set as a configuration parameter for SQLiteSpy to avoid the need to issue the pragma command each time SQLiteSpy is used? I have searched the documentation for this pragma but without success. Is this a case of "Star Wars : Use the source, Luke?" :-)
Re: [sqlite] SQLiteSpy 1.5.4 released
On Tue, 20 Jun 2006 15:34:21 +0200, Ralf Junker wrote: >SQLiteSpy 1.5.4 is released. Changes include: >* Updated to SQLite3 3.3.6. >* The schema tree view did not automatically reflect if a table was emptied or >initial records were being added. >* The default text encoding for newly generated database files is now UTF-8. >* Fixed a nasty bug which could cause the syntax highlighting to freeze the >application. >SQLiteSpy is a Freeware SQLite3 database manager for Win32. It is available >for download from http://www.yunqa.de/delphi/sqlitespy/. Your program looks quite nice and seems to do what is needed. Thanks for developing it. Here are a couple of points I have discovered after a few minutes of use: 1. SQLiteSpy is able to read and work with database files formatted by versions of Sqlite earlier than 3.3.6 but it also appears to change the database format rather than leave it as it was found. I use php version 5.1.4 including Sqlite version 3.2.8. There is a database format change in later versions of Sqlite that means php is NOT able to read these later versions. My intention is to use SQLiteSpy as a data administration tool outside of php but that is not possible until their versions are synchronised. 2. After executing some sql against a database, the result set is displayed. If the database is closed after that, the result set stays on the screen when it might be less confusing if it disappeared as the database was closed. Regards, Chris
Re: [sqlite] Avoiding duplicate record insertion
On Sun, 18 Jun 2006 00:23:58 -0400, David D Speck wrote:
>Please pardon what may be a rank newbie question, but I'm just starting
>with SQL and have not seen this addressed in the tutorials that I've
>studied.
>I have to compile a single name and address list from many small list
>files. It is likely that many of the names will be present in more than
>one input list, but I only want to have one copy of them in the master
>SQL table.
>What would the most elegant way be to insert a name and address entry
>into the main table ONLY if it is not already there? I could see doing
>a SELECT WHERE lname = new_lname AND fname = new_fname, AND street =
>new_street, etc, and then aborting the INSERT if the SELECT returns a
>match.
>I just wondered if there was a neater way to accomplish this?
You could declare each column in the table to be UNIQUE and let the
database report each duplicate. For example:
SQLite version 3.3.6
Enter ".help" for instructions
sqlite>create table test (
...>lname varchar(60) unique,
...>fname varchar(60) unique,
...>street varchar(60) unique);
sqlite>insert into test values ('a', 'b', 'c');
sqlite>insert into test values ('a', 'b', 'c');
SQL error: column street is not unique
sqlite>
In a program, you might want to catch the error and report it.
Re: [sqlite] Duplicate records
On Thu, 15 Jun 2006 20:12:41 +0100, Adam wrote: >Hi All, >I'm creating a database which will look a little like: >create table main (f1 TEXT, f2 TEXT, f3 TEXT, t1 TEXT, t2 TEXT, tn TEXT); >f1, f2 and f3 will always be present. Any or all of the ts might be NULL. >I'd like to prevent entries where all of the fields are the same being >created. How can I do this. I've tried experimenting with UNIQUE KEYs but >haven't been successful - perhaps because of the NULLs? >If this isn't possible my second best option would be a command which >removes existing duplicates. >Thanks a lot for any pointers, >Adam >-- >Adam Richardson >Carpe Diem Use an MD5 signature across the values of all fields and store that value in an indexed additional field. When the apparent duplicate is presented for storage, compute its MD5 signature and do preliminary query to check for existence of a record with the same signature. Reject the new record if the signature is found in an existing record.
Re: [sqlite] Avoiding Fragmentation of SQLite database file ???
On Wed, 14 Jun 2006 03:34:04 -0700 (PDT), RohitPatel wrote: >But like some other Databases, if SQLite does provide any way to set >auto-increment for database file by some fixed percentage (say 20%, 40% >etc.) such that everytime database file reaches some threashould size it >automatically add free space, that will reduce file defragmentation. >What are your thoughts on that ? One of Sqlite's strengths is its simplicity. Creeping featuritis plagues all software development and what you are suggesting fits nicely with that definition. The zero configuration principle is violated so that a very small percentage of applications can do something as part of the database module instead of leaving it to the operating system, running a cron task at 3.00 am. Regards, Chris
