Memory Allocation Error

[DougH]

I have been working on an SQL parser that scans directories for a specific file name, and when found executes the SQL statement(s) that are found in the file. The SQL statement works fine if I use Microsft Query, but I receive a Memory allocation error when I use SQLite. The table I am attampting to write data to has 46 fields, thus it also has 46 values in the statement.

My code looks like;

Func

Local $sOut

_SQLite_Startup()

_SQLIte_Open(@ScriptDir & "OpReport.mdb")

If @error > 0 Then

MsgBox(16, "SQLite Error", "Can't Load Database!")

Exit - 1

EndIf

GUICtrlSetData($Facility, $Site)

GUICtrlSetData($FoodDir, $Usr)

$SQLFile = FileOPen($FileName, 0)

If $SQLFile = -1 Then

MsgBox(0, "Error", "Unable to open input file.")

EndIf

GUICtrlSetData($SQLLine, FileReadLine($SQLFile))

; Read in lines of text until the EOF is reached

While 1

_SQLite_Exec(-1, FileReadLine($SQLFile), $Sout)

Wend

_SQLite_Close()

_SQLite_Shutdown()

FileClose($SQLFile)

EndFunc

The statement I am attempting to use is ;

Insert into InvSum (FacilityID, Year, Month, Week, Meat1, Meat2, Meat3, Meat4, Meat5, TotalMeat, Fruits1, Fruits2, Friuts3, Fruits4, TotalFruits, Groceries1, Groceries2, Groceries3, Groceries4, Groceries5, Groceries6, Groceries7, Groceries8, Groceries9, Groceries10, Groceries11, Groceries12, Groceries13, Groceries14, Groceries15, Groceries16, Groceries17, TotalGroceries, Milk1, Milk2, TotalMilk, Food1, Food2, Food3, TotalFood, Paper1, Paper2, Paper3, Cleaning4, TotalPaper, TotalInv) Values ('00013','2008','February','Week1','250.66','0','0','0','85.00','335.66','66.25','0','0','0','66.25','101.99','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','58.98','160.97','35.08','0','0','0','0','0','0','0','0','0','0','0','562.88');

Is there any way I can get around this memory error?

Thank you in advance ,

Doug

[randallc]

Hi,

Not sure;

Is there the same error if you use "_SQLite_SQLiteExe"?

randall

[PS, otherwise, post an empty database file or script to make one that works, and I can test it...]

[DougH]

Randall,

Is there the same error if you use "_SQLite_SQLiteExe"? No, the system goes into some type of loop.

I can hear the disk heads thrashing and that stops after I terminate the script.

I did redo the Database update using ADO and it works fine, although reading a script from a file would be much easier, scripting wise.

There will be at least 10 to 12 tables in this database, and I would have to make a definition section for each table using ado.

I have attached the database.

Thank you,

Doug

OpReport.zip

[randallc]

Randall,

Is there the same error if you use "_SQLite_SQLiteExe"? No, the system goes into some type of loop.

I can hear the disk heads thrashing and that stops after I terminate the script.

I did redo the Database update using ADO and it works fine, although reading a script from a file would be much easier, scripting wise.

There will be at least 10 to 12 tables in this database, and I would have to make a definition section for each table using ado.

I have attached the database.

Thank you,

Doug

Hi,

I haven't looked yet, but it is named as though "mdb" access database, rather than "db3" Sqlite db?

Best, randall

[Bowmore]

I have been working on an SQL parser that scans directories for a specific file name, and when found executes the SQL statement(s) that are found in the file. The SQL statement works fine if I use Microsft Query, but I receive a Memory allocation error when I use SQLite. The table I am attampting to write data to has 46 fields, thus it also has 46 values in the statement.

My code looks like;

Func

Local $sOut

_SQLite_Startup()

_SQLIte_Open(@ScriptDir & "OpReport.mdb")

If @error > 0 Then

MsgBox(16, "SQLite Error", "Can't Load Database!")

Exit - 1

EndIf

GUICtrlSetData($Facility, $Site)

GUICtrlSetData($FoodDir, $Usr)

$SQLFile = FileOPen($FileName, 0)

If $SQLFile = -1 Then

MsgBox(0, "Error", "Unable to open input file.")

EndIf

GUICtrlSetData($SQLLine, FileReadLine($SQLFile))

; Read in lines of text until the EOF is reached

While 1

_SQLite_Exec(-1, FileReadLine($SQLFile), $Sout)

Wend

_SQLite_Close()

_SQLite_Shutdown()

FileClose($SQLFile)

EndFunc

The statement I am attempting to use is ;

Insert into InvSum (FacilityID, Year, Month, Week, Meat1, Meat2, Meat3, Meat4, Meat5, TotalMeat, Fruits1, Fruits2, Friuts3, Fruits4, TotalFruits, Groceries1, Groceries2, Groceries3, Groceries4, Groceries5, Groceries6, Groceries7, Groceries8, Groceries9, Groceries10, Groceries11, Groceries12, Groceries13, Groceries14, Groceries15, Groceries16, Groceries17, TotalGroceries, Milk1, Milk2, TotalMilk, Food1, Food2, Food3, TotalFood, Paper1, Paper2, Paper3, Cleaning4, TotalPaper, TotalInv) Values ('00013','2008','February','Week1','250.66','0','0','0','85.00','335.66','66.25','0','0','0','66.25','101.99','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','58.98','160.97','35.08','0','0','0','0','0','0','0','0','0','0','0','562.88');

Is there any way I can get around this memory error?

Thank you in advance ,

Doug

I think the problem may be that the database is not being opened. @ScriptDir needs a backslash appending to make it a valid path. Another thing is that if the error is within SqLite then @error will be set to -1 and @extended set to the error returned by SqLite, you were only checking if @error was > 0 . Although using -1 to identify the currently open database storing the database handle returned by _SQLIte_Open in a variable is a better way of doing it as it gives another way of checking if the database was successfully opened.

Try something like this.

$hDB = 0
$hDB =  _SQLIte_Open(@ScriptDir & "\OpReport.mdb")
If Not $hDB Then 
    If @error <> 0 Then
        MsgBox(16, "SQLite Error", "Can't Load Database!")
        Exit - 1
    EndIf
EndIf

[DougH]

Thanks for the response. I will try it and let you know how I make out.

Have a great day.

Doug

[DougH]

I have not been able to get SQLite to work inserting data into an Access database. I have begun trying to use ADO and the ADO.Execute command. I can now open the databse and read data, but I receive an error when inserting, syntax error in Insert Statement. I have verified the statement and do not see anything wrong with it. I have tried it with single quotes encasing each value and without. Single quotes for text fields and none for number fields. I am at a loss. I have included the section of code that is opening the DB and setting up the connection, along with the insert statement.

Any help would be appreciated.

Func ProcessSQL($Site, $Usr, $FileName)

_FileReadToArray ( $FileName, $SQLArray)

If @Error > 0 Then

MsgBox(16, "Error Reading", "Unable to open the input file")

Else

$adoCon = ObjCreate("ADODB.Connection")

;$adoCon.Open("Driver={SQL Server};Server=solomonsql.nmsc.com;Database=ptovac;Uid=sa;Pwd=nmsc2071;")

$adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)

$adoRs = ObjCreate ("ADODB.Recordset")

$adoSQL = "Select * from InvSum" ; $_Sql_3

$adoRs.Open($adoSQL, $adoCon)

$SQLFile = FileOPen($FileName, 0)

;MsgBox(0, "SQL Statement", FileReadLine($SQLFile))

$adoCon.Execute (FileReadLine($SQLFile))

FileClose($SQLFile)

$ADOCon.Close

EndFunc

The Insert statement is;

Insert into InvSum (FacilityID,Year,Month,Week,Meat1,Meat2,Meat3,Meat4,Meat5,TotalMeat,Fruits1,Fruits2,Fruits3,Fruits4,

TotalFruits,Groceries1,Groceries2,Groceries3,Groceries4,Groceries5,Groceries6,Groceries7,Groceries8,

Groceries9,Groceries10,Groceries11,Groceries12,Groceries13,Groceries14,Groceries15,Groceries16,Groce

ries17,TotalGroceries,Milk1,Milk2,TotalMilk,Food1,Food2,Food3,TotalFood,Paper1,Paper2,Paper3,Cleanin

g4,TotalPaper,TotalInv) Values ('00013','2008','February','Week1','250.66','0','0','0','85.00','335.66','66.25','0','0','0','66.25','101.99','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','58.98','160.97','38.08','0','0','0','0','0','0','0','0','0','0','0','562.88');

Thank you for the help.

[DougH]

I found my problem, I was using a reserved work in the Insert statement. It now works fine.

Thanks,

Doug