Re: [sqlite] Saving an in-memory database to file
On Sat, May 31, 2008 at 9:48 PM, Bruce Robertson <[EMAIL PROTECTED]> wrote: > Here's a more basic example. This is really just a shell script formatting > problem and it must be really simple. I'm trying to use \n as new line. The > result I want from the echo statement is as follows but I can't figure out > how to set x to get there. > > A > B > C > D > > > set x="A\nB\nC\nD"; echo $x $ x="A\nB\nC\nD"; echo -e $x A B C D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Saving an in-memory database to file
Here's a more basic example. This is really just a shell script formatting
problem and it must be really simple. I'm trying to use \n as new line. The
result I want from the echo statement is as follows but I can't figure out
how to set x to get there.
A
B
C
D
set x="A\nB\nC\nD"; echo $x
> On Sat, May 31, 2008 at 8:30 PM, Bruce Robertson <[EMAIL PROTECTED]> wrote:
>> An example of how to do this with the shell would be helpful.
>>
>> Oddly enough I can do it with applescript; but I can't do it with some other
>> shell tools I'm trying to use.
>>
>> My problem has to do with how to pass multiple lines to a single command.
>> I'm sure it's quite simple but I keep poking around not getting anywhere.
>>
>> This is the applescript version:
>>
>> set this to "echo '
>> .read /a.sql
>> .o stdout
>> .dump
>> .q
>> '|sqlite3 "
>> set this to paragraphs of this
>> set applescript's text item delimiters to "\n"
>> do shell script (this as text)
>> -- result:
>> "BEGIN TRANSACTION;
>> CREATE TABLE Responses (GFUP_ID TEXT,FullQNum TEXT,ResponseNumber
>> TEXT,SurveyVersion TEXT,RecordID TEXT);
>> INSERT INTO \"Responses\" VALUES('36780001', '00.1.01', '1', '2000', '1');
>> INSERT INTO \"Responses\" VALUES('36780001', '02.1.01', '1', '2000', '2');
>> INSERT INTO \"Responses\" VALUES('36780001', '02.1.02', '', '2000', '3');
>
> Are you trying to dump one database into another, new database? I
> played around at the command line and came up with this. Not sure it
> fits your needs. (I create a database test.db and then dump into a new
> database test2.db.)
>
> $ sqlite3 test.db
> SQLite version 3.5.7
> Enter ".help" for instructions
> sqlite> create table test (one integer, two integer);
> sqlite> insert into test values (1,2);
> sqlite> select * from test;
> 1|2
> $
> $ echo '.dump' | sqlite3 test.db | sqlite3 test2.db
> $ sqlite3 test2.db
> SQLite version 3.5.7
> Enter ".help" for instructions
> sqlite> select * from test;
> 1|2
> sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Saving an in-memory database to file
No, I'm trying to do what I said I'm trying to do.
Pass a series of commands to a single sqlite action and get back all the
results.
Here's a little more representative example done as applescript.
I'm trying to generalize it so I can use it with some other command line
tools (FileMaker shell script plugins in this case)
The example creates a simple table, populates it, creates a derivative
table, performs selects in a few different modes, performs a dump, and
passes ALL the results back as stdout.
It works fine and does all those things when performed as applescript.
-- start applescript
set cmd to "CREATE TABLE t1 (recID, Name, statusMarker);
INSERT INTO t1 VALUES (1, \"one\", 3);
INSERT INTO t1 VALUES (2, \"Line A
Line B
Line C\", 3);
INSERT INTO t1 VALUES (22,\"tw22o\", 12);
CREATE TABLE t2 as select recID as R1 , Name as R2 from t1;
.dump
.mode html
.header on
.echo off
SELECT * FROM t1;
select R1 as \"Record ID\", R2 as NAME from t2 as table2;
.mode line
SELECT * FROM t1 order by name;"
set cmd to "echo '" & cmd & "'|sqlite3 :memory: "
do shell script cmd
-- end script
=
RESULT showing the sql dump + HTML output + line output
=
"BEGIN TRANSACTION;
CREATE TABLE t1 (recID, Name, statusMarker);
INSERT INTO \"t1\" VALUES(1, 'one', 3);
INSERT INTO \"t1\" VALUES(2, 'Line A
Line B
Line C', 3);
INSERT INTO \"t1\" VALUES(22, 'tw22o', 12);
CREATE TABLE t2(R1,R2);
INSERT INTO \"t2\" VALUES(1, 'one');
INSERT INTO \"t2\" VALUES(2, 'Line A
Line B
Line C');
INSERT INTO \"t2\" VALUES(22, 'tw22o');
COMMIT;
recIDNamestatusMarker
1
one
3
2
Line A
Line B
Line C
3
22
tw22o
12
Record IDNAME
1
one
2
Line A
Line B
Line C
22
tw22o
recID = 2
Name = Line A
Line B
Line C
statusMarker = 3
recID = 1
Name = one
statusMarker = 3
recID = 22
Name = tw22o
statusMarker = 12"
> On Sat, May 31, 2008 at 8:30 PM, Bruce Robertson <[EMAIL PROTECTED]> wrote:
>> An example of how to do this with the shell would be helpful.
>>
>> Oddly enough I can do it with applescript; but I can't do it with some other
>> shell tools I'm trying to use.
>>
>> My problem has to do with how to pass multiple lines to a single command.
>> I'm sure it's quite simple but I keep poking around not getting anywhere.
>>
>> This is the applescript version:
>>
>> set this to "echo '
>> .read /a.sql
>> .o stdout
>> .dump
>> .q
>> '|sqlite3 "
>> set this to paragraphs of this
>> set applescript's text item delimiters to "\n"
>> do shell script (this as text)
>> -- result:
>> "BEGIN TRANSACTION;
>> CREATE TABLE Responses (GFUP_ID TEXT,FullQNum TEXT,ResponseNumber
>> TEXT,SurveyVersion TEXT,RecordID TEXT);
>> INSERT INTO \"Responses\" VALUES('36780001', '00.1.01', '1', '2000', '1');
>> INSERT INTO \"Responses\" VALUES('36780001', '02.1.01', '1', '2000', '2');
>> INSERT INTO \"Responses\" VALUES('36780001', '02.1.02', '', '2000', '3');
>
> Are you trying to dump one database into another, new database? I
> played around at the command line and came up with this. Not sure it
> fits your needs. (I create a database test.db and then dump into a new
> database test2.db.)
>
> $ sqlite3 test.db
> SQLite version 3.5.7
> Enter ".help" for instructions
> sqlite> create table test (one integer, two integer);
> sqlite> insert into test values (1,2);
> sqlite> select * from test;
> 1|2
> $
> $ echo '.dump' | sqlite3 test.db | sqlite3 test2.db
> $ sqlite3 test2.db
> SQLite version 3.5.7
> Enter ".help" for instructions
> sqlite> select * from test;
> 1|2
> sqlite>
> ___
> 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] Saving an in-memory database to file
On Sat, May 31, 2008 at 8:57 PM, Keith Goodman <[EMAIL PROTECTED]> wrote:
> On Sat, May 31, 2008 at 8:30 PM, Bruce Robertson <[EMAIL PROTECTED]> wrote:
>> An example of how to do this with the shell would be helpful.
>>
>> Oddly enough I can do it with applescript; but I can't do it with some other
>> shell tools I'm trying to use.
>>
>> My problem has to do with how to pass multiple lines to a single command.
>> I'm sure it's quite simple but I keep poking around not getting anywhere.
>>
>> This is the applescript version:
>>
>> set this to "echo '
>> .read /a.sql
>> .o stdout
>> .dump
>> .q
>> '|sqlite3 "
>> set this to paragraphs of this
>> set applescript's text item delimiters to "\n"
>> do shell script (this as text)
>> -- result:
>> "BEGIN TRANSACTION;
>> CREATE TABLE Responses (GFUP_ID TEXT,FullQNum TEXT,ResponseNumber
>> TEXT,SurveyVersion TEXT,RecordID TEXT);
>> INSERT INTO \"Responses\" VALUES('36780001', '00.1.01', '1', '2000', '1');
>> INSERT INTO \"Responses\" VALUES('36780001', '02.1.01', '1', '2000', '2');
>> INSERT INTO \"Responses\" VALUES('36780001', '02.1.02', '', '2000', '3');
>
> Are you trying to dump one database into another, new database? I
> played around at the command line and came up with this. Not sure it
> fits your needs. (I create a database test.db and then dump into a new
> database test2.db.)
>
> $ sqlite3 test.db
> SQLite version 3.5.7
> Enter ".help" for instructions
> sqlite> create table test (one integer, two integer);
> sqlite> insert into test values (1,2);
> sqlite> select * from test;
> 1|2
> $
> $ echo '.dump' | sqlite3 test.db | sqlite3 test2.db
> $ sqlite3 test2.db
> SQLite version 3.5.7
> Enter ".help" for instructions
> sqlite> select * from test;
> 1|2
> sqlite>
If you like python, here's a python script to dump from memory to
file. (pysqlite doesn't yet have access to sqlite's dump so it loops
through all the tables instead.)
>From http://oss.itsystementwicklung.de/trac/pysqlite/wiki/DumpToDisk
try:
import sqlite3 as sqlite
except:
from pysqlite2 import dbapi2 as sqlite
def dump_to_disk(con, filename):
"""
Dumps the tables of an in-memory database into a file-based SQLite database.
@param con: Connection to in-memory database.
@param filename:Name of the file to write to.
"""
cur = con.cursor()
cur.execute("attach '%s' as __extdb" % filename)
cur.execute("select name from sqlite_master where type='table'")
table_names = cur.fetchall()
for table_name, in table_names:
cur.execute("create table __extdb.%s as select * from %s" %
(table_name, table_name))
cur.execute("detach __extdb")
con = sqlite.connect(":memory:")
cur = con.cursor()
cur.execute("create table t1(x)")
cur.execute("insert into t1(x) values (1)")
cur.execute("create table t2(x)")
cur.execute("insert into t2(x) values (2)")
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Saving an in-memory database to file
On Sat, May 31, 2008 at 8:30 PM, Bruce Robertson <[EMAIL PROTECTED]> wrote:
> An example of how to do this with the shell would be helpful.
>
> Oddly enough I can do it with applescript; but I can't do it with some other
> shell tools I'm trying to use.
>
> My problem has to do with how to pass multiple lines to a single command.
> I'm sure it's quite simple but I keep poking around not getting anywhere.
>
> This is the applescript version:
>
> set this to "echo '
> .read /a.sql
> .o stdout
> .dump
> .q
> '|sqlite3 "
> set this to paragraphs of this
> set applescript's text item delimiters to "\n"
> do shell script (this as text)
> -- result:
> "BEGIN TRANSACTION;
> CREATE TABLE Responses (GFUP_ID TEXT,FullQNum TEXT,ResponseNumber
> TEXT,SurveyVersion TEXT,RecordID TEXT);
> INSERT INTO \"Responses\" VALUES('36780001', '00.1.01', '1', '2000', '1');
> INSERT INTO \"Responses\" VALUES('36780001', '02.1.01', '1', '2000', '2');
> INSERT INTO \"Responses\" VALUES('36780001', '02.1.02', '', '2000', '3');
Are you trying to dump one database into another, new database? I
played around at the command line and came up with this. Not sure it
fits your needs. (I create a database test.db and then dump into a new
database test2.db.)
$ sqlite3 test.db
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> create table test (one integer, two integer);
sqlite> insert into test values (1,2);
sqlite> select * from test;
1|2
$
$ echo '.dump' | sqlite3 test.db | sqlite3 test2.db
$ sqlite3 test2.db
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> select * from test;
1|2
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Saving an in-memory database to file
An example of how to do this with the shell would be helpful.
Oddly enough I can do it with applescript; but I can't do it with some other
shell tools I'm trying to use.
My problem has to do with how to pass multiple lines to a single command.
I'm sure it's quite simple but I keep poking around not getting anywhere.
This is the applescript version:
set this to "echo '
.read /a.sql
.o stdout
.dump
.q
'|sqlite3 "
set this to paragraphs of this
set applescript's text item delimiters to "\n"
do shell script (this as text)
-- result:
"BEGIN TRANSACTION;
CREATE TABLE Responses (GFUP_ID TEXT,FullQNum TEXT,ResponseNumber
TEXT,SurveyVersion TEXT,RecordID TEXT);
INSERT INTO \"Responses\" VALUES('36780001', '00.1.01', '1', '2000', '1');
INSERT INTO \"Responses\" VALUES('36780001', '02.1.01', '1', '2000', '2');
INSERT INTO \"Responses\" VALUES('36780001', '02.1.02', '', '2000', '3');
Etc
> On Fri, May 30, 2008 at 09:24:29PM -0700, Bruce Robertson scratched on the
> wall:
>> Well, an interesting illustration of basic sqlite; but no relation to the
>> question being asked.
>
> Actually, it is a good answer to the question that was asked.
>
> Running SQLite without a database file creates an in-memory database.
> Using the .dump command will dump that in-memory DB to a SQL file that
> can then be re-read into a file-backed database (or back into a memory
> database), just as Mark asked about.
>
> Of course, I assume Mark wants to do this via code. That will
> require poking around the shell code to see how the ".dump" command
> is implemented within the command shell.
>
> -j
>
>
>>> On 5/30/08, Mark Stewart <[EMAIL PROTECTED]> wrote:
Is there a recommended way to save an in-memory database to a file? Is
there
a way to access the underlying in-memory data directly to save out to disk
(if that would even work)?
My other thought was to create an empty file based db and attach it,
creating tables and transferring all the data through sql.
Maybe there is some other option?
>>>
>>>
>>> [12:04 AM] ~/foo$ ls
>>> [12:04 AM] ~/foo$ sqlite3
>>> SQLite version 3.5.6
>>> Enter ".help" for instructions
>>> sqlite> CREATE TABLE t (a, b);
>>> sqlite> INSERT INTO t VALUES (1, 'one');
>>> sqlite> INSERT INTO t VALUES (2, 'two');
>>> sqlite> SELECT * FROM t;
>>> 1|one
>>> 2|two
>>> sqlite> .q
>>> [12:04 AM] ~/foo$ ls
>>> [12:04 AM] ~/foo$ sqlite3
>>> SQLite version 3.5.6
>>> Enter ".help" for instructions
>>> sqlite> CREATE TABLE t (a, b);
>>> sqlite> INSERT INTO t VALUES (1, 'one');
>>> sqlite> INSERT INTO t VALUES (2, 'two');
>>> sqlite> SELECT * FROM t;
>>> 1|one
>>> 2|two
>>> sqlite> .o foo.sql
>>> sqlite> .dump
>>> sqlite> .q
>>> [12:05 AM] ~/foo$ ls
>>> foo.sql
>>> 12:05 AM] ~/foo$ cat foo.sql
>>> BEGIN TRANSACTION;
>>> CREATE TABLE t (a, b);
>>> INSERT INTO "t" VALUES(1,'one');
>>> INSERT INTO "t" VALUES(2,'two');
>>> COMMIT;
>>> [12:06 AM] ~/foo$
>>> ___
>>> 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
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "'People who live in bamboo houses should not throw pandas.' Jesus said that."
> - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
> ___
> 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] Insert / Update images using MS VBScript
2008/6/1 MoDementia <[EMAIL PROTECTED]>: > 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. If you run into any problems, reply to this sqlite-users list so we'll figure out the solution. - Lauri ___ 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 Byte, lPowers8(255) As Long, lPowers16(255) As
Long, bOut() As Byte, bIn() As Byte
Dim lChar As Long, lTrip As Long, iPad As Integer, lLen As Long,
lTemp As Long, lPos As Long
For lTemp = 1 To 63 'Fill the translation table.
bTrans(lTemp) = lTemp + 32
Next lTemp
bTrans(0) = 96 'Replace spaces with 'graves'
For
Re: [sqlite] Saving an in-memory database to file
Mark Stewart wrote: > > John Stanton-3 wrote: > >>I wonder why you do not just use a file in the first place. Sqlite >>caches data in memory so a file based database and memory based perform >>much the same. >> > > > For this app, I didn't want to ask the user to enter a filename for a new > document before they decided to 'save'. I guess I could use a temporary > file though. > > I would invent a file name and have it as a well known name in the application. You can just copy the file for persistent storage. Let Sqlite do the work. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Saving an in-memory database to file
John Stanton-3 wrote: > > I wonder why you do not just use a file in the first place. Sqlite > caches data in memory so a file based database and memory based perform > much the same. > For this app, I didn't want to ask the user to enter a filename for a new document before they decided to 'save'. I guess I could use a temporary file though. -- View this message in context: http://www.nabble.com/Saving-an-in-memory-database-to-file-tp17571347p17575893.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] Saving an in-memory database to file
I wonder why you do not just use a file in the first place. Sqlite caches data in memory so a file based database and memory based perform much the same. Mark Stewart wrote: > > > Jay A. Kreibich-2 wrote: > >> Of course, I assume Mark wants to do this via code. That will >> require poking around the shell code to see how the ".dump" command >> is implemented within the command shell. >> > > > Yes, I'm trying to do this using the C API. I'll have a look through the > .dump code and see what I can find. > > Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3 file format
On May 31, 2008, at 7:41 AM, Aladdin Lampé wrote: > > Hi! > I remember a message from DRH about SQLite's roadmap for 2008, > stating that the file format specification would be explained and > released in itself. > Are you still working on that? Yes D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite3 file format
Hi! I remember a message from DRH about SQLite's roadmap for 2008, stating that the file format specification would be explained and released in itself. Are you still working on that? Thank you and have a nice day, Aladdin _ Caroline vient de mettre à jour son profil Messenger ! Connectez-vous ! http://login.live.com/login.srf?wa=wsignin1.0=10=1198837564=4.0.1534.0=MBI=http:%2F%2Fhome.services.spaces.live.com%2F=1036=73625 ___ 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
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 Byte, lPowers8(255) As Long, lPowers16(255) As
Long, bOut() As Byte, bIn() As Byte
Dim lChar As Long, lTrip As Long, iPad As Integer, lLen As Long,
lTemp As Long, lPos As Long
For lTemp = 1 To 63 'Fill the translation table.
bTrans(lTemp) = lTemp + 32
Next lTemp
bTrans(0) = 96 'Replace spaces with 'graves'
For lTemp = 0 To 255'Fill the 2^8 and 2^16
lookup tables.
lPowers8(lTemp) = lTemp * cl2Exp8
lPowers16(lTemp) = lTemp * cl2Exp16
Next lTemp
iPad = Len(sString) Mod 3 'See if the length is divisible by 3
If iPad Then'If not, figure out the
odd bytes and resize the input.
iPad = 3 - iPad
sString = sString & String(iPad, Chr(0))
Re: [sqlite] get the actual database size.
> From: [EMAIL PROTECTED] > Date: Fri, 30 May 2008 20:26:14 +0200 > Would sqlite3_analyzer work for you? > It produces both a human readable report as well as a table > definition and insert statements to feed to sqlite3 command > line tool. Where can we download the source of this tool "sqlite3_analyser"? (The precompiled binary is on the sqlite3 web site, download section). It seems to be an interesting reading to understand the sqlite3 file format. Thanks, Aladdin _ Caroline vient de mettre à jour son profil Messenger ! Connectez-vous ! http://login.live.com/login.srf?wa=wsignin1.0=10=1198837564=4.0.1534.0=MBI=http:%2F%2Fhome.services.spaces.live.com%2F=1036=73625 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Saving an in-memory database to file
Jay A. Kreibich-2 wrote: > > Of course, I assume Mark wants to do this via code. That will > require poking around the shell code to see how the ".dump" command > is implemented within the command shell. > Yes, I'm trying to do this using the C API. I'll have a look through the .dump code and see what I can find. Thanks. -- View this message in context: http://www.nabble.com/Saving-an-in-memory-database-to-file-tp17571347p17573902.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] 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
