Saving an in-memory database to file

[Mark Stewart]

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?

[P Kishor]

Post by Mark Stewart
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$

[Bruce Robertson]

Well, an interesting illustration of basic sqlite; but no relation to the
question being asked.

Post by P Kishor

Post by Mark Stewart
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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Jay A. Kreibich]

Post by Bruce Robertson
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

Post by Bruce Robertson

Post by P Kishor

Post by Mark Stewart
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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Mark Stewart]

Post by Jay A. Kreibich
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.

[John Stanton]

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.

Post by Mark Stewart

Post by Jay A. Kreibich
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.

[Mark Stewart]

Post by John Stanton
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.

[John Stanton]

Post by Mark Stewart

Post by John Stanton
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.

[Bruce Robertson]

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

Post by Jay A. Kreibich

Post by Bruce Robertson
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

Post by Bruce Robertson

Post by P Kishor

Post by Mark Stewart
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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

--
"'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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Keith Goodman]

Post by Bruce Robertson
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.
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)
"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>

[Keith Goodman]

Post by Keith Goodman

Post by Bruce Robertson
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.
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)
"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.)

Post by Keith Goodman
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)")

[Bruce Robertson]

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;

<TR><TH>recID</TH><TH>Name</TH><TH>statusMarker</TH></TR>
<TR><TD>1</TD>
<TD>one</TD>
<TD>3</TD>
</TR>
<TR><TD>2</TD>
<TD>Line A
Line B
Line C</TD>
<TD>3</TD>
</TR>
<TR><TD>22</TD>
<TD>tw22o</TD>
<TD>12</TD>
</TR>

<TR><TH>Record ID</TH><TH>NAME</TH></TR>
<TR><TD>1</TD>
<TD>one</TD>
</TR>
<TR><TD>2</TD>
<TD>Line A
Line B
Line C</TD>
</TR>
<TR><TD>22</TD>
<TD>tw22o</TD>
</TR>

recID = 2
Name = Line A
Line B
Line C
statusMarker = 3

recID = 1
Name = one
statusMarker = 3

recID = 22
Name = tw22o
statusMarker = 12"

Post by Keith Goodman

Post by Bruce Robertson
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.
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)
"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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Bruce Robertson]

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

Post by Keith Goodman

Post by Bruce Robertson
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.
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)
"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>

[Keith Goodman]

Post by Bruce Robertson
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

[Bruce Robertson]

Post by Keith Goodman

Post by Bruce Robertson
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

Excellent, I knew it was something simple.

Now one odd problem.

When \n is properly substituted, this all works fine EXCEPT for the select
statement.

There the "*" is somehow seen as though it was asking for a directory. If I
change the "*" to a valid field name, I get correct results.

Command string:

x=".read /a.sql
.schema
.tables
select * from Responses;
.dump
"; echo -e $x |sqlite3 :memory:

Result:

CREATE TABLE Responses (GFUP_ID TEXT,FullQNum TEXT,ResponseNumber
TEXT,SurveyVersion TEXT,RecordID TEXT);
Responses
select Applications Desktop DB Desktop DF Developer Documents
LassoImageMagick.6 Library MyTestDatabase.sqlite Network PDF Resp.mer System
User Guides And Information Users Volumes a.db a.sql a.tab a.txt a1.db aa.db
aa.sqlite ab.db ab.txt adb.txt automount b.txt b2.txt bin c.txt cores d.sql
db.sql db2.db db2.sql dev dump.sqlite etc mach mach.sym mach_kernel
mydata.db mydata.db
mydata.db
.mode mydata.db.zip opt private resp.db sbin sort tmp usr var x.db from
MemoryDB.Responses;
SQL error: near "DB": syntax error
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');
INSERT INTO "Responses" VALUES('36780001', '02.1.03', '', '2000', '4');

[Bruce Robertson]

Post by Bruce Robertson

Post by Keith Goodman

Post by Bruce Robertson
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

Excellent, I knew it was something simple.
Now one odd problem.
When \n is properly substituted, this all works fine EXCEPT for the select
statement.
There the "*" is somehow seen as though it was asking for a directory. If I
change the "*" to a valid field name, I get correct results.
x=".read /a.sql
.schema
.tables
select * from Responses;
.dump

Never mind; it was the variable before the echo statement.

Echo -e ".read /a.sql
.schema
.tables
select * from Responses;
.dump
"|sqlite3

[MoDementia]

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 :(

[Lauri Ojansivu]

Post by MoDementia
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))

End If



bIn = StrConv(sString, vbFromUnicode) 'Load the input string.

lLen = ((UBound(bIn) + 1) \ 3) * 4 'Length of resulting string.

lTemp = lLen \ 60

ReDim bOut((lTemp * 3) + lLen + 3) 'Make the output buffer
with space for vbCrLfs and counts.



lLen = 0 'Reusing this one, so reset it.

lPos = 1 'Leave a space for the
first line's count.



For lChar = LBound(bIn) To UBound(bIn) Step 3

lTrip = lPowers16(bIn(lChar)) + lPowers8(bIn(lChar + 1)) +
bIn(lChar + 2) 'Combine the 3 bytes

lTemp = lTrip And clOneMask 'Mask for the first 6 bits

bOut(lPos) = bTrans(lTemp \ cl2Exp18) 'Shift it down to
the low 6 bits and get the value

lTemp = lTrip And clTwoMask 'Mask for the second set.

bOut(lPos + 1) = bTrans(lTemp \ cl2Exp12) 'Shift it down and
translate.

lTemp = lTrip And clThreeMask 'Mask for the third set.

bOut(lPos + 2) = bTrans(lTemp \ cl2Exp6) 'Shift it down and
translate.

bOut(lPos + 3) = bTrans(lTrip And clFourMask) 'Mask for the low set.

If lLen = 56 Then 'Ready for a newline

bOut(lPos + 4) = 13 'Chr(13) = vbCr

bOut(lPos + 5) = 10 'Chr(10) = vbLf

lLen = 0 'Reset the counter

bOut(lPos - 57) = 77 'Insert the byte
count for this line.

lPos = lPos + 7 'Skip an extra
position for the next line's count.

Else

lLen = lLen + 4

lPos = lPos + 4

End If

Next lChar



bOut(lPos) = 13 'Terminate the
encoded data with a vbCrLF and `.

bOut(lPos + 1) = 10

bOut(lPos + 2) = 96

bOut(lPos - (lLen + 1)) = ((lLen \ 4) * 3) - iPad + 32 'Calculate
and add the byte count for the last line.



UUEncode = StrConv(bOut, vbUnicode) 'Convert back to a
string and return it.



End Function



Public Function UUDecode(sString As String) As String



Dim bOut() As Byte, bIn() As Byte, bTrans(255) As Byte,
lPowers6(64) As Long, lPowers12(64) As Long

Dim lPowers18(64) As Long, lQuad As Long, iPad As Integer, lPos As
Long, sOut As String

Dim lTemp As Long, lLines As Long, lLast As Long, lLen As Long,
lIndex As Long, lSkip As Long



sString = Replace(sString, vbCr, vbNullString) 'Get rid of the vbCrLfs.

sString = Replace(sString, vbLf, vbNullString)



For lTemp = 32 To 127 'Fill the translation table.

bTrans(lTemp) = lTemp - 32

Next lTemp

bTrans(96) = 0 'The 'grave' character.



For lTemp = 0 To 64 'Fill the 2^6,
2^12, and 2^18 lookup tables.

lPowers6(lTemp) = lTemp * cl2Exp6

lPowers12(lTemp) = lTemp * cl2Exp12

lPowers18(lTemp) = lTemp * cl2Exp18

Next lTemp



lLen = Len(sString)

lLines = (lLen) \ 61 'Find the number
of lines in the input.

lLast = (lLen) Mod 61 'Find out how long
the last line is.



lTemp = (lLast) Mod 4 'Make sure the
last line is comprised of quads.

sString = sString & String(61 - lLast, Chr$(96))



bIn = StrConv(sString, vbFromUnicode) 'Load the input byte array.



iPad = bIn(UBound(bIn) - (60)) - 32 'Number of last
line bytes, as encoded at the line start.

ReDim bOut((lLines + 1) * 45) 'Prepare the
output buffer. (45 per 60 char line)



lIndex = 1 'Skip the first
byte--It's the first line byte count.



Do Until lIndex > UBound(bIn)

lSkip = lSkip + 1

If lSkip > 15 Then

lIndex = lIndex + 1 'Skip the start of
the next line.

lSkip = 1

End If

lQuad = lPowers18(bTrans(bIn(lIndex))) +
lPowers12(bTrans(bIn(lIndex + 1))) + _

lPowers6(bTrans(bIn(lIndex + 2))) + bTrans(bIn(lIndex
+ 3)) 'Rebuild the bits.

lTemp = lQuad And clHighMask 'Mask for the first byte

bOut(lPos) = lTemp \ cl2Exp16 'Shift it down

lTemp = lQuad And clMidMask 'Mask for the second byte

bOut(lPos + 1) = lTemp \ cl2Exp8 'Shift it down

bOut(lPos + 2) = lQuad And clLowMask 'Mask for the third byte

lPos = lPos + 3

lIndex = lIndex + 4

Loop



sOut = StrConv(bOut, vbUnicode)
'Convert back to a string.

sOut = Left$(sOut, ((lLines) * 45) + iPad) 'Chop
off any extra bytes.

UUDecode = sOut



End Function



Public Function Replace(ByVal sIn As String, ByVal sFind As _

String, ByVal sReplace As String, Optional nStart As _

Long = 1, Optional nCount As Long = -1, _

Optional bCompare As VbCompareMethod = vbBinaryCompare) As _

String



Dim nC As Long, nPos As Long

Dim nFindLen As Long, nReplaceLen As Long



nFindLen = Len(sFind)

nReplaceLen = Len(sReplace)



If (sFind <> "") And (sFind <> sReplace) Then

nPos = InStr(nStart, sIn, sFind, bCompare)

Do While nPos

nC = nC + 1

sIn = Left(sIn, nPos - 1) & sReplace & _

Mid(sIn, nPos + nFindLen)

If nCount <> -1 And nC >= nCount Then Exit Do

nPos = InStr(nPos + nReplaceLen, sIn, sFind, _

bCompare)

Loop

End If



Replace = sIn

End Function

[MoDementia]

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: sqlite-users-bounces-CzDROfG0BjIdnm+***@public.gmane.org
[mailto:sqlite-users-bounces-CzDROfG0BjIdnm+***@public.gmane.org] 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

Post by MoDementia
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))

End If



bIn = StrConv(sString, vbFromUnicode) 'Load the input string.

lLen = ((UBound(bIn) + 1) \ 3) * 4 'Length of resulting string.

lTemp = lLen \ 60

ReDim bOut((lTemp * 3) + lLen + 3) 'Make the output buffer
with space for vbCrLfs and counts.



lLen = 0 'Reusing this one, so reset it.

lPos = 1 'Leave a space for the
first line's count.



For lChar = LBound(bIn) To UBound(bIn) Step 3

lTrip = lPowers16(bIn(lChar)) + lPowers8(bIn(lChar + 1)) +
bIn(lChar + 2) 'Combine the 3 bytes

lTemp = lTrip And clOneMask 'Mask for the first 6
bits

bOut(lPos) = bTrans(lTemp \ cl2Exp18) 'Shift it down to
the low 6 bits and get the value

lTemp = lTrip And clTwoMask 'Mask for the second
set.

bOut(lPos + 1) = bTrans(lTemp \ cl2Exp12) 'Shift it down and
translate.

lTemp = lTrip And clThreeMask 'Mask for the third set.

bOut(lPos + 2) = bTrans(lTemp \ cl2Exp6) 'Shift it down and
translate.

bOut(lPos + 3) = bTrans(lTrip And clFourMask) 'Mask for the low set.

If lLen = 56 Then 'Ready for a newline

bOut(lPos + 4) = 13 'Chr(13) = vbCr

bOut(lPos + 5) = 10 'Chr(10) = vbLf

lLen = 0 'Reset the counter

bOut(lPos - 57) = 77 'Insert the byte
count for this line.

lPos = lPos + 7 'Skip an extra
position for the next line's count.

Else

lLen = lLen + 4

lPos = lPos + 4

End If

Next lChar



bOut(lPos) = 13 'Terminate the
encoded data with a vbCrLF and `.

bOut(lPos + 1) = 10

bOut(lPos + 2) = 96

bOut(lPos - (lLen + 1)) = ((lLen \ 4) * 3) - iPad + 32 'Calculate
and add the byte count for the last line.



UUEncode = StrConv(bOut, vbUnicode) 'Convert back to a
string and return it.



End Function



Public Function UUDecode(sString As String) As String



Dim bOut() As Byte, bIn() As Byte, bTrans(255) As Byte,
lPowers6(64) As Long, lPowers12(64) As Long

Dim lPowers18(64) As Long, lQuad As Long, iPad As Integer, lPos As
Long, sOut As String

Dim lTemp As Long, lLines As Long, lLast As Long, lLen As Long,
lIndex As Long, lSkip As Long



sString = Replace(sString, vbCr, vbNullString) 'Get rid of the vbCrLfs.

sString = Replace(sString, vbLf, vbNullString)



For lTemp = 32 To 127 'Fill the translation
table.

bTrans(lTemp) = lTemp - 32

Next lTemp

bTrans(96) = 0 'The 'grave' character.



For lTemp = 0 To 64 'Fill the 2^6,
2^12, and 2^18 lookup tables.

lPowers6(lTemp) = lTemp * cl2Exp6

lPowers12(lTemp) = lTemp * cl2Exp12

lPowers18(lTemp) = lTemp * cl2Exp18

Next lTemp



lLen = Len(sString)

lLines = (lLen) \ 61 'Find the number
of lines in the input.

lLast = (lLen) Mod 61 'Find out how long
the last line is.



lTemp = (lLast) Mod 4 'Make sure the
last line is comprised of quads.

sString = sString & String(61 - lLast, Chr$(96))



bIn = StrConv(sString, vbFromUnicode) 'Load the input byte
array.



iPad = bIn(UBound(bIn) - (60)) - 32 'Number of last
line bytes, as encoded at the line start.

ReDim bOut((lLines + 1) * 45) 'Prepare the
output buffer. (45 per 60 char line)



lIndex = 1 'Skip the first
byte--It's the first line byte count.



Do Until lIndex > UBound(bIn)

lSkip = lSkip + 1

If lSkip > 15 Then

lIndex = lIndex + 1 'Skip the start of
the next line.

lSkip = 1

End If

lQuad = lPowers18(bTrans(bIn(lIndex))) +
lPowers12(bTrans(bIn(lIndex + 1))) + _

lPowers6(bTrans(bIn(lIndex + 2))) + bTrans(bIn(lIndex
+ 3)) 'Rebuild the bits.

lTemp = lQuad And clHighMask 'Mask for the first byte

bOut(lPos) = lTemp \ cl2Exp16 'Shift it down

lTemp = lQuad And clMidMask 'Mask for the second
byte

bOut(lPos + 1) = lTemp \ cl2Exp8 'Shift it down

bOut(lPos + 2) = lQuad And clLowMask 'Mask for the third byte

lPos = lPos + 3

lIndex = lIndex + 4

Loop



sOut = StrConv(bOut, vbUnicode)
'Convert back to a string.

sOut = Left$(sOut, ((lLines) * 45) + iPad) 'Chop
off any extra bytes.

UUDecode = sOut



End Function



Public Function Replace(ByVal sIn As String, ByVal sFind As _

String, ByVal sReplace As String, Optional nStart As _

Long = 1, Optional nCount As Long = -1, _

Optional bCompare As VbCompareMethod = vbBinaryCompare) As _

String



Dim nC As Long, nPos As Long

Dim nFindLen As Long, nReplaceLen As Long



nFindLen = Len(sFind)

nReplaceLen = Len(sReplace)



If (sFind <> "") And (sFind <> sReplace) Then

nPos = InStr(nStart, sIn, sFind, bCompare)

Do While nPos

nC = nC + 1

sIn = Left(sIn, nPos - 1) & sReplace & _

Mid(sIn, nPos + nFindLen)

If nCount <> -1 And nC >= nCount Then Exit Do

nPos = InStr(nPos + nReplaceLen, sIn, sFind, _

bCompare)

Loop

End If



Replace = sIn

End Function

[Lauri Ojansivu]

Post by MoDementia
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