DougH Posted February 7, 2008 Share Posted February 7, 2008 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 Link to comment Share on other sites More sharing options...
randallc Posted February 7, 2008 Share Posted February 7, 2008 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...] ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
DougH Posted February 7, 2008 Author Share Posted February 7, 2008 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, DougOpReport.zip Link to comment Share on other sites More sharing options...
randallc Posted February 8, 2008 Share Posted February 8, 2008 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,DougHi,I haven't looked yet, but it is named as though "mdb" access database, rather than "db3" Sqlite db?Best, randall ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
Bowmore Posted February 8, 2008 Share Posted February 8, 2008 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 "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook Link to comment Share on other sites More sharing options...
DougH Posted February 11, 2008 Author Share Posted February 11, 2008 Thanks for the response. I will try it and let you know how I make out. Have a great day. Doug Link to comment Share on other sites More sharing options...
DougH Posted February 13, 2008 Author Share Posted February 13, 2008 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. Link to comment Share on other sites More sharing options...
DougH Posted February 13, 2008 Author Share Posted February 13, 2008 I found my problem, I was using a reserved work in the Insert statement. It now works fine. Thanks, Doug Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now