Here is my test which really is redundant because I never really delete all order lines in my program. I just used this because is was a table with more columns.
My test project is kind of cluttered. I tried a lot of things and didn’t bother to clean up.
test.db
whole gory project
Get data into memory:
[code]Sub Action()
db = new SQLiteDatabase
if not DB.Connect then msgbox “Error opening QB.DB”
db.SQLExecute(“CREATE TABLE OrderLines
(ID
INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,OrderID
INTEGER,Idx
INTEGER,Item
TEXT,Quantity
REAL,” _
- “
Description
TEXT,Price
REAL,Tax
TEXT,Category
TEXT,LF
TEXT, Status
INTEGER,ProductionID
INTEGER,QBID
TEXT);”)
db1 = new SQLiteDatabase
Dim dbFile As New FolderItem(“templocation\test.db”)
db1.DatabaseFile = dbFile
if db1.Connect then
end if
if db1 <> nil Then
dim t as Integer = Ticks
dim rs as RecordSet = db1.SQLSelect(“SELECT * FROM OrderLinesTest”)
t = Ticks - t
dim cnt as String = rs.RecordCount.ToText
if rs <> Nil and rs.RecordCount>0 Then
do
Dim row As New DatabaseRecord
for i as Integer = 1 to rs.FieldCount
row.Column(rs.IdxField(i).Name) = rs.IdxField(i).StringValue
next
db.InsertRecord(“OrderLines”, row)
rs.MoveNext
loop until rs.EOF
rs = db.SQLSelect(“SELECT * FROM OrderLines”)
if rs <> nil Then MsgBox rs.RecordCount.ToText + “, " + Format(t/60,”#0.00") + ", " + cnt
end if
end if
End Sub
[/code]
Write to PostgreSQL:
[code]Sub Action()
dim pDb as PostgreSQLDatabase
pDb = New PostgreSQLDatabase
pDb.Host = “localhost”
pDb.UserName = “postgres”
pDb.Password = “QBMS123”
pDb.DatabaseName = “Test”
If pDb.Connect Then
Else
Return
End If
pDb.SQLExecute(“BEGIN TRANSACTION”)
pdb.SQLExecute(“DROP TABLE OrderLinesTest”)
pdb.SQLExecute(“CREATE TABLE OrderLinesTest (ID Integer DEFAULT nextval('”“table1_ID_seq”“'::regclass) NOT NULL,OrderID INTEGER,Idx INTEGER,Item TEXT,Quantity REAL,” _
- “Description TEXT,Price REAL,Tax TEXT,Category TEXT,LF TEXT, Status INTEGER,ProductionID INTEGER,QBID TEXT);”)
if pDb <> nil Then
dim t as Integer = Ticks
dim rs as RecordSet = db.SQLSelect(“SELECT * FROM OrderLines”)
't = Ticks - t
dim cnt as String = rs.RecordCount.ToText
dim c1 as Integer
if rs <> Nil and rs.RecordCount>0 Then
dim recs() as DatabaseRecord
do
dim data as new DatabaseRecord
data.IntegerColumn( “ID” ) = rs.Field(“ID”).IntegerValue
data.IntegerColumn( “OrderID” ) = rs.Field(“OrderID”).IntegerValue
data.IntegerColumn( “Idx” ) = rs.Field(“Idx”).IntegerValue
data.Column(“Item”) = rs.Field(“Item”).StringValue
data.DoubleColumn( “Quantity” ) = rs.Field(“Quantity”).DoubleValue
data.Column(“Description”) = rs.Field(“Description”).StringValue
data.DoubleColumn(“Price”) = rs.Field(“Price”).DoubleValue
data.Column(“Tax”) = rs.Field(“Tax”).StringValue
data.Column(“Category”) = rs.Field(“Category”).StringValue
data.Column(“LF”) = rs.Field(“LF”).StringValue
data.IntegerColumn(“Status”) = rs.Field(“Status”).IntegerValue
data.IntegerColumn(“Status”) = rs.Field(“Status”).IntegerValue
data.Column(“QBID”) = rs.Field(“QBID”).StringValue
recs.Append data
rs.MoveNext
loop until rs.EOF
for each rec as DatabaseRecord in recs
pdb.InsertRecord "OrderLinesTest", rec
next
rs = pDb.SQLSelect("SELECT * FROM OrderLinesTest")
if rs <> nil Then MsgBox rs.RecordCount.ToText + ", " + Format((ticks-t)/60,"#0.00") + ", " + cnt
end if
end if
pDb.Commit
End Sub
[/code]