[sqlite] Something to think about: Saving Select ID Lists
One thing that I really miss form another database is the ability to reuse select statements. I haven't programmed but I believe this is sort of possible using C, this is a command language version maybe? e.g. CREATE TABLE IF NOT EXISTS Transactions (ID Integer PRIMARY KEY AutoIncrement, DataID Integer, DateTime Real, Comment Text COLLATE IUNICODE) This has lots of entries (INSERTS, DELETES, UPDATES) CREATE TABLE IF NOT EXISTS Data (ID Integer PRIMARY KEY AutoIncrement, Data1 Text, Data2 Integer, LastTransaction Real) This doesn't have many entries (comparatively) Select * FROM Transactions Where DateTime Between x and y Saving Unique DataID to 1 GetList 1 Select * From Data Using 1 Select Data2 From Data Where Data2=Z Using 1 The "Saving Unique DataID to 1" saves a list of ID's that can be used like part of an IN(1) on another table. The unique qualifier restricts the list to only 1 unique ID where it is in multiple rows. If I remember correctly the lists where saved as UsernameList1, UsernameList2 etc and cleared when disconnected logged out. Not only could you reuse process intensive ID lists, many complicated joins etc can be eliminated. Select * FROM Transactions Where DateTime Between x and y Saving Unique DataID to 1 (500 rows) Select * From Data Where Data2=Z Using 1 (150 rows) Saving ID to 2 (unique not used as the ID is unique already) Select * From AnotherTable Where Data7<>B Using 2 (25 rows returned) If you need information from the other tables you can do the join statement here with only 25 IDs. I hope this makes sense ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert / Update images using MS VBScript
Thanks for the reply.
However I am restricted to VBscript rather than visual basic.
I will try to convert the syntax but I'm not confident that all the
functions will be available in VBscript.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Lauri Ojansivu
Sent: Saturday, 31 May 2008 9:40 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Insert / Update images using MS VBScript
2008/5/31 MoDementia <[EMAIL PROTECTED]>:
> I have spent most of the day searching for examples in VBscript to add /
> update an image into a database without luck.
>
> If anyone has a snippet of code they could share I would be most grateful.
>
> I have either an image as an object in the script and or a physical file
> location i.e. "C:\image.jpg"
>
> None of the examples I looked at even came close to helping me understand
> what I need to do :(
Hi,
in following VB code image file (or any other binary file) is read
from disk to string, uuencoded, and can then be inserted into
database.
Another option is try to figure out dhSQLite http://www.thecommon.net/2.html
.
- Lauri
Sub test_image_read_write()
Dim path As String, filename As String
Dim t1 As String, t2 As String
Dim sql As String
path = "C:\"
filename = "image.jpg"
t1 = loadfilename(path & filename)
' Do something here with t1, like insert into database...
' If insert statements don't like it, you can uuencode it
t1 = uuencodetext(t1)
' If uuencoded text has ' in it, replace it with '' for sqlite insert
t1 = Replace(t1, "'", "''")
' Now make sql string...
sql = "INSERT INTO pics(filename, image) VALUES ('" & _
filename & "', '" & t1 & "');"
MsgBox sql
' And execute it.
' And after reading it from database uudecode.
t1 = uudecodetext(t1)
savefilename t1, path & "test-" & filename
t2 = loadfilename(path & "test-" & filename)
If t1 = t2 Then
t1 = ""
t2 = ""
Kill path & "test-" & filename
MsgBox "Success!"
Exit Sub
Else
t1 = ""
t2 = ""
Kill path & "test-" & filename
MsgBox "Error: image modified when saved and loaded again!"
Exit Sub
End If
End Sub
Function loadfilename(filename As String) As String
If Not FileExists(filename) Then
loadfilename = "File does not exist!"
Exit Function
End If
Dim t As Variant
loadfilename = ""
Dim iFreeFile As Integer
Dim bytCount As Byte
Dim data() As Byte
iFreeFile = FreeFile
Open filename For Binary As iFreeFile
ReDim data(LOF(iFreeFile)) 'redim the array to take the whole file
Get #iFreeFile, , data 'read the entire file into the byte array
loadfilename = ByteArrayToString(data)
Close iFreeFile
End Function
Sub savefilename(text As String, filename As String)
Close
If FileExists(filename) Then Kill filename
Dim iFreeFile As Integer
Dim bytCount As Byte
Dim data() As Byte
iFreeFile = FreeFile
Open filename For Binary As iFreeFile
data = StrConv(text, vbFromUnicode)
Put #iFreeFile, , data 'read the entire file into the byte array
Close iFreeFile
End Sub
Function uudecodetext(text As String) As String
' 1) Take away uudecode start
text = Replace(text, "begin 644 data.dat" & vbLf, "")
' 2) Take away uudecode end
text = Replace(text, vbLf & "end" & vbLf, "")
' 3) Do uudecode
text = UUDecode(text)
' 4) Return result
uudecodetext = text
End Function
Function uuencodetext(text As String)
' 1) UUEncode text
text = UUEncode(text)
' 2) Add UUEncode beginning and end
text = "begin 644 data.dat" & vbLf & text & vbLf & "end" & vbLf
' 3) Return result
uuencodetext = text
End Function
Public Function ByteArrayToString(bytArray() As Byte) As String
Dim sAns As String
Dim iPos As String
sAns = StrConv(bytArray, vbUnicode)
iPos = InStr(sAns, Chr(0))
If iPos > 0 Then sAns = Left(sAns, iPos - 1)
ByteArrayToString = sAns
End Function
Function FileExists(ByVal FileName As String) As Boolean
On Error GoTo ErrorHandler
' get the attributes and ensure that it isn't a directory
FileExists = (GetAttr(FileName) And vbDirectory) = 0
ErrorHandler:
' if an error occurs, this function returns False
End Function
Public Function UUEncode(sString As String) As String
Dim bTrans(63) As B
[sqlite] Insert / Update images using MS VBScript
I have spent most of the day searching for examples in VBscript to add / update an image into a database without luck. If anyone has a snippet of code they could share I would be most grateful. I have either an image as an object in the script and or a physical file location i.e. "C:\image.jpg" None of the examples I looked at even came close to helping me understand what I need to do :( ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Setting Precision for Floating Point data
After experiencing some difficulty with comparing dates stored internally as a floating point I was informed about the following. "... due to the way floats are stored in computers, '=' isn't really a good choice of an operator for them. Instead of Date = 38953.5890509 you should use Date > 38953.5890508 AND Date < 38953.5890510" Is there any way to set a precision value? 10 or 12 even 8 decimal places would be fine in this instance for SQLite "REAL" data so that what is "seen" can be compared without surprises? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking for open transactions attach/detach database
I think I have found a solution After the commit I have set up a loop with a 1 sec sleep that checks for the existence of the file "ATTACHedDatabase-journal" Once it no longer exists the script continues, DETACHing the database without error. I would have thought there would be a similar SQL command to check for the existence of a ...-journal file. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of MoDementia Sent: Wednesday, 28 May 2008 6:10 AM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Checking for open transactions attach/detach database Thanks for the clarification. The error I am receiving must then be produced by the application rather than just reporting an SQLite error. Or The application is taking control of the commit wait time, allowing my script to continue. Or more correct perhaps; the script commands are passed to the application which allows the script to continue producing the DETACH error. * There was a problem querying the database: Error executing SQL statement "DETACH ExportDB " : SQL logic error or missing database (1,1) Cancel, Retry, Ignore The error is ambiguous at best but I believe the second scenario is what is happening given the consistent time before clicking "Retry" is accepted without error. I have posted the information received so far to the developers however, I'm not expecting the application to be corrected/changed in a hurry as ATTACH/DETACH are unusual events in user scripts. So the question remains: Is there a way to check for open transactions / locks from the command line? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Tuesday, 27 May 2008 11:23 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Checking for open transactions attach/detach database MoDementia wrote: > The database file is ready to (copy) Detach as soon as the commit completes. > Yes. > This is the problem. > Detach: "This statement will fail if SQLite is in the middle of a > transaction." This is true. This is why you must commit your transaction before you can detach. > If I commit 10k row updates I cannot detach the database until it is > finished. > The database (SQLite) will be finished all its processing by the time it returns to your program after executing the commit statement. Attach Begin Loop to insert many rows Commit Detach > I need to be able to check some sort of table entry that will be clear once > the commit is finished. The commit is finished when it returns to your program. > > I don't have access to any of the higher level functions so it needs to be > something like > > SELECT Commit_Status FROM Active_Transactions > > Then I can wait till Commit_Status = something > Before I attempt to DETACH the database > > Obviously the application (written in some C language) can tell that it is > still committing and throws the error > But I need to do this check from the command line only The same logic applies to the command line. It has completed its processing by the time it displays the prompt after you enter the commit command. > > I hope this is making sense > Sort of. It seems like you are worrying about a non issue. HTH Dennis COte ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking for open transactions attach/detach database
Thanks for the clarification. The error I am receiving must then be produced by the application rather than just reporting an SQLite error. Or The application is taking control of the commit wait time, allowing my script to continue. Or more correct perhaps; the script commands are passed to the application which allows the script to continue producing the DETACH error. * There was a problem querying the database: Error executing SQL statement "DETACH ExportDB " : SQL logic error or missing database (1,1) Cancel, Retry, Ignore The error is ambiguous at best but I believe the second scenario is what is happening given the consistent time before clicking "Retry" is accepted without error. I have posted the information received so far to the developers however, I'm not expecting the application to be corrected/changed in a hurry as ATTACH/DETACH are unusual events in user scripts. So the question remains: Is there a way to check for open transactions / locks from the command line? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Tuesday, 27 May 2008 11:23 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Checking for open transactions attach/detach database MoDementia wrote: > The database file is ready to (copy) Detach as soon as the commit completes. > Yes. > This is the problem. > Detach: "This statement will fail if SQLite is in the middle of a > transaction." This is true. This is why you must commit your transaction before you can detach. > If I commit 10k row updates I cannot detach the database until it is > finished. > The database (SQLite) will be finished all its processing by the time it returns to your program after executing the commit statement. Attach Begin Loop to insert many rows Commit Detach > I need to be able to check some sort of table entry that will be clear once > the commit is finished. The commit is finished when it returns to your program. > > I don't have access to any of the higher level functions so it needs to be > something like > > SELECT Commit_Status FROM Active_Transactions > > Then I can wait till Commit_Status = something > Before I attempt to DETACH the database > > Obviously the application (written in some C language) can tell that it is > still committing and throws the error > But I need to do this check from the command line only The same logic applies to the command line. It has completed its processing by the time it displays the prompt after you enter the commit command. > > I hope this is making sense > Sort of. It seems like you are worrying about a non issue. HTH Dennis COte ___ 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] Checking for open transactions attach/detach database and Trigger behaviour with attached databases
What is the appropriate etiquette if I don't receive a solution/answer in ?? days -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of MoDementia Sent: Friday, 23 May 2008 12:32 PM To: sqlite-users@sqlite.org Subject: [sqlite] Checking for open transactions attach/detach database and Trigger behaviour with attached databases Hi, My first submission 1 Problem and 1 question J My problem I have 2 processes that are causing problems after commits/locks 1. Copy main database to copydatabase using filesystem object How to determine when copydatabase is ready for attach? Attach copydatabase <-- need to retry a few times Delete some rows Detach copydatabase 2. Attach copydatabase Begin transaction Update main database from data in copydatabase Commit How to determine when copydatabase is ready for detach? Detach copydatabase <-- Need to retry many times The main application traps the errors before my MS VBScript can use ON ERROR so I need to test for the error before it occurs. Hope There is an easy solution My question (This seems to be answered as I didn't lose all the rows in the main database) Are triggers restricted to their respective databases? Main Database Table Name = Songs Attached Database Table Name = Songs Both have triggers CREATE TRIGGER delete_songs DELETE ON Songs BEGIN ... END Will these act only their respective tables or will they both act on the main database? Regards Terry Ganly __ 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] Checking for open transactions attach/detach database and Trigger behaviour with attached databases
Hi, My first submission 1 Problem and 1 question J My problem I have 2 processes that are causing problems after commits/locks 1. Copy main database to copydatabase using filesystem object How to determine when copydatabase is ready for attach? Attach copydatabase <-- need to retry a few times Delete some rows Detach copydatabase 2. Attach copydatabase Begin transaction Update main database from data in copydatabase Commit How to determine when copydatabase is ready for detach? Detach copydatabase <-- Need to retry many times The main application traps the errors before my MS VBScript can use ON ERROR so I need to test for the error before it occurs. Hope There is an easy solution My question Are triggers restricted to their respective databases? Main Database Table Name = Songs Attached Database Table Name = Songs Both have triggers CREATE TRIGGER delete_songs DELETE ON Songs BEGIN ... END Will these act only their respective tables or will they both act on the main database? Regards Terry Ganly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
