Marshin Posted April 9, 2008 Share Posted April 9, 2008 I have a made a user interface for entering information into a SQL database, but now I am trying to have the code add a column using the SQL command ALTER TABLE with the option of ADD COLUMN. The function works and adds the new column and enters the intended information into the new column but when I use the ArrayDisplay on the database the program hard crashes with no information sent back to SciTE . I don't have the same problem of it crashing on viewing the data if I simply make a new database with the same column arraignment within the same program. I have tried using the VACUM command, but I am not completely sure that my syntax was correct due to limited information on how it is used. At last, after two thousand years of research, the illudium Q-36 explosive space modulator.Well, back to the old drawing board. Link to comment Share on other sites More sharing options...
PsaltyDS Posted April 9, 2008 Share Posted April 9, 2008 I have a made a user interface for entering information into a SQL database, but now I am trying to have the code add a column using the SQL command ALTER TABLE with the option of ADD COLUMN. The function works and adds the new column and enters the intended information into the new column but when I use the ArrayDisplay on the database the program hard crashes with no information sent back to SciTE . I don't have the same problem of it crashing on viewing the data if I simply make a new database with the same column arraignment within the same program. I have tried using the VACUM command, but I am not completely sure that my syntax was correct due to limited information on how it is used. ...and I'm not sure this has anything to do with SQLite due to limited information on how you used it. Post a short reproducer script so we can see what you are doing. Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
zorphnog Posted April 9, 2008 Share Posted April 9, 2008 As Psalty said, we need your code to check for errors. BTW the command is VACUUM and you would use it like 'VACUUM;'. Link to comment Share on other sites More sharing options...
Marshin Posted April 10, 2008 Author Share Posted April 10, 2008 (edited) here is a sample script for you to look over and try out . I made it self contained with a GUI and it does save the database file to the scrip location for your connivance . Note: I know the program is not writhen the best but I had to hack up a 1400 line program to get just what was needed. CODE #include <GUIConstants.au3> #include <SQLite.au3> #include <SQLite.dll.au3> #include <Array.au3> Opt("GUIOnEventMode", 1) Global $hQuery, $aRow, $aResult, $iRows, $iColumns, $db Global $DB_Table = "Date_Table" $run_once = 1 $Display = "off" $number = 0 $columns = 5 GUICreate ( "Sample SQL Program" ) GUISetOnEvent($GUI_EVENT_CLOSE, "SpecialEvents") $Wright_Data_Button = GUICtrlCreateButton ( "Wright data", 50 , 50 , 100 ) GUICtrlSetOnEvent ( $Wright_Data_Button , "Wright_Data" ) $Array_Display_Button = GUICtrlCreateButton ( "Array Display", 50 , 100 , 100 ) GUICtrlSetOnEvent ( $Array_Display_Button , "Array_Display" ) $Add_Column_Button = GUICtrlCreateButton ( "Add Column", 50 , 150 , 100 ) GUICtrlSetOnEvent ( $Add_Column_Button , "Add_Column" ) GUISetState (@SW_SHOW) While 1 $number = Random( 100000 , 999999, 1) Sleep(10) Wend Func Wright_Data () _SQLite_Startup () ;Start SQLite If @error > 0 Then MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!") ;Message if SQL will not start Exit - 1 EndIf $db = _SQLite_Open (@ScriptDir & "\Example_DB.db") ;Open a :memory: database and assign Database Name/number to $db If @error > 0 Then MsgBox(16, "SQLite Error", "Can't Load Database!") ;Message if can't open Database file Exit - 1 EndIf If $run_once = 0 Then If $columns = 5 Then If Not _SQLite_Exec ($db, "INSERT INTO " & $DB_Table & " VALUES ('"& $number & "','" & $number & "','" & $number & "','" & $number & "','" & $number & "');") = $SQLITE_OK Then ;adding data to the database MsgBox(16, "SQLite Error", _SQLite_ErrMsg ()) ;message upon error of trying to write table of database with Part number that already exsist EndIf EndIf If $columns = 6 Then If Not _SQLite_Exec ($db, "INSERT INTO " & $DB_Table & " VALUES ('"& $number & "','" & $number & "','" & $number & "','" & $number & "','" & $number & "','" & $number & "');") = $SQLITE_OK Then ;adding data to the database MsgBox(16, "SQLite Error", _SQLite_ErrMsg ()) ;message upon error of trying to write table of database with Part number that already exsist EndIf EndIf EndIf If $run_once = 1 Then ;Create Table becuase it is a new database If Not _SQLite_Exec ($db, "CREATE TABLE " & $DB_Table & " (PartNumber NOT NULL PRIMARY KEY, Description, Date, Notes, History);") = $SQLITE_OK Then _ ;add the missing table (this should only be needed the first time ever running progam MsgBox(16, "SQLite Error", _SQLite_ErrMsg ()) ;message upon error of trying to write table to database If Not _SQLite_Exec ($db, "INSERT INTO " & $DB_Table & " VALUES ('"& $number & "','" & $number & "','" & $number & "','" & $number & "','" & $number & "');") = $SQLITE_OK Then _ ;adding data to the database MsgBox(16, "SQLite Error", _SQLite_ErrMsg ()) ;message upon error of trying to write table of database with Part number that already exsist $iTmp = _SQLite_Query($db, "SELECT name FROM sqlite_master WHERE type = 'table' OR type = 'view'", $hQuery);Query Master Table for tables in DB If Not $iTmp = $SQLITE_OK Then MsgBox(0,'error', "Cant Read out Tables") ;Message if can read Master Table EndIf $run_once = 0 $Display = "On" EndIf MsgBox(1, "Wright Data", "Data has Been Written" ) EndFunc Func Array_Display () If $Display = "On" Then $iRval = _SQLite_GetTable2d ($db, 'SELECT * FROM ' & $DB_Table & ";", $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then _ArrayDisplay($aResult, "data") ;MsgBox ( 0 , "testing" , $iRows ) Else MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ()) EndIf EndIf EndFunc Func Add_Column () If $columns = 5 Then If Not _SQLite_Exec ($db, "ALTER TABLE " & $DB_Table & " ADD COLUMN User") = $SQLITE_OK Then MsgBox(16, "SQLite Error", _SQLite_ErrMsg ()) EndIf $columns = 6 MsgBox(1, "Add Column", "column had been added" ) EndIf EndFunc Func SpecialEvents() If @GUI_CTRLID = $GUI_EVENT_CLOSE then _SQLite_Close () _SQLite_Shutdown () Exit EndIf EndFunc Edited April 10, 2008 by Marshin At last, after two thousand years of research, the illudium Q-36 explosive space modulator.Well, back to the old drawing board. Link to comment Share on other sites More sharing options...
ChrisL Posted April 10, 2008 Share Posted April 10, 2008 here is a sample script for you to look over and try out . I made it self contained with a GUI and it does save the database file to the scrip location for your connivance . Note: I know the program is not writhen the best but I had to hack up a 1400 line program to get just what was needed. The only problem I can see there is that once you've added a new coloumn your not supplying enough data to fill the required column quantities [u]Scripts[/u]Minimize gui to systray _ Fail safe source recoveryMsgbox UDF _ _procwatch() Stop your app from being closedLicensed/Trial software system _ Buffering Hotkeys_SQL.au3 ADODB.Connection _ Search 2d Arrays_SplashTextWithGraphicOn() _ Adjust Screen GammaTransparent Controls _ Eventlogs without the crap_GuiCtrlCreateFlash() _ Simple Interscript communication[u]Websites[/u]Curious Campers VW Hightops Lambert Plant Hire Link to comment Share on other sites More sharing options...
zorphnog Posted April 10, 2008 Share Posted April 10, 2008 (edited) You gotta increase your $columns variable in the Add_Column function so that the correct amount of data is inserted into the table. Func Add_Column() If $columns = 5 Then If Not _SQLite_Exec($db, "ALTER TABLE " & $DB_Table & " ADD COLUMN User") = $SQLITE_OK Then MsgBox(16, "SQLite Error", _SQLite_ErrMsg()) EndIf MsgBox(1, "Add Column", "column had been added") $columns = 6 EndIf EndFunc ;==>Add_Column It would also be a good idea to use data types for your table columns. Edited April 10, 2008 by zorphnog Link to comment Share on other sites More sharing options...
PsaltyDS Posted April 10, 2008 Share Posted April 10, 2008 I thought this may be related to an _ArrayDisplay() bug in 3.2.10.0 Prod, that was fixed in the 3.2.11.x Betas. I cleaned up the code thusly, so it doesn't need to use any disk access (database in memory only), and to fix the add column logic: expandcollapse popup#include <GUIConstants.au3> #include <SQLite.au3> #include <SQLite.dll.au3> #include <Array.au3> Opt("GUIOnEventMode", 1) Global $hQuery, $aRow, $aResult, $iRows, $iColumns, $db Global $DB_Table = "Date_Table" $number = 0 $columns = 5 ;Start SQLite _SQLite_Startup() If @error > 0 Then MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!") ;Message if SQL will not start Exit -1 EndIf ;Open a :memory: database $db = _SQLite_Open() If @error > 0 Then MsgBox(16, "SQLite Error", "Can't Load Database!") ;Message if can't open Database file Exit -1 EndIf ; Create Table becuase it is a new database If Not _SQLite_Exec($db, "CREATE TABLE " & $DB_Table & " (PartNumber NOT NULL PRIMARY KEY, Description, Date, Notes, History);") = $SQLITE_OK Then _ ;add the missing table (this should only be needed the first time ever running progam MsgBox(16, "SQLite Error", _SQLite_ErrMsg()) ;message upon error of trying to write table to database If Not _SQLite_Exec($db, "INSERT INTO " & $DB_Table & " VALUES ('" & $number & "','" & $number & "','" & $number & "','" & $number & "','" & $number & "');") = $SQLITE_OK Then _ ;adding data to the database MsgBox(16, "SQLite Error", _SQLite_ErrMsg()) ;message upon error of trying to write table of database with Part number that already exsist $iTmp = _SQLite_Query($db, "SELECT name FROM sqlite_master WHERE type = 'table' OR type = 'view'", $hQuery);Query Master Table for tables in DB If Not $iTmp = $SQLITE_OK Then MsgBox(16, 'error', "Cant Read out Tables") ;Message if can read Master Table EndIf $hGUI = GUICreate("Sample SQL Program", 300, 160) GUISetOnEvent($GUI_EVENT_CLOSE, "SpecialEvents") $ctrlLabel1 = GUICtrlCreateLabel("Current cols: " & $columns, 10, 10, 280, 20) $Wright_Data_Button = GUICtrlCreateButton("Write data", 100, 40, 100, 30) GUICtrlSetOnEvent($Wright_Data_Button, "Write_Data") $Array_Display_Button = GUICtrlCreateButton("Array Display", 100, 80, 100, 30) GUICtrlSetOnEvent($Array_Display_Button, "Array_Display") $Add_Column_Button = GUICtrlCreateButton("Add Column", 100, 120, 100, 30) GUICtrlSetOnEvent($Add_Column_Button, "Add_Column") GUISetState(@SW_SHOW) While 1 $number = Random(100000, 999999, 1) Sleep(10) WEnd Func Write_Data() ; Assemble the SQL command string for the variable number of columns Local $sSQLCmd = "INSERT INTO " & $DB_Table & " VALUES ('" For $n = 1 To $columns $sSQLCmd &= $number & "','" Next $sSQLCmd = StringTrimRight($sSQLCmd, 3) & "');" If Not _SQLite_Exec($db, $sSQLCmd) = $SQLITE_OK Then ; add data row MsgBox(16, "SQLite Error", _SQLite_ErrMsg()) ;message upon error EndIf MsgBox(1, "Wright Data", "Data has Been Written", 2) EndFunc ;==>Write_Data Func Array_Display() $iRval = _SQLite_GetTable2d($db, 'SELECT * FROM ' & $DB_Table & ";", $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then _ArrayDisplay($aResult, "data") Else MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf EndFunc ;==>Array_Display Func Add_Column() $columns += 1 ControlSetText($hGUI, "", $ctrlLabel1, "Current cols: " & $columns) If Not _SQLite_Exec($db, "ALTER TABLE " & $DB_Table & " ADD COLUMN User" & $columns & ";") = $SQLITE_OK Then MsgBox(16, "SQLite Error", _SQLite_ErrMsg()) EndIf MsgBox(1, "Add Column", "column had been added", 2) EndFunc ;==>Add_Column Func SpecialEvents() If @GUI_CtrlId = $GUI_EVENT_CLOSE Then _SQLite_Close() _SQLite_Shutdown() Exit EndIf EndFunc ;==>SpecialEvents It STILL works in 3.2.11.5 and crashes 3.2.10.0! There were DllCall() fixes also made in the current Betas, maybe they have more to do with it, but it works in Beta. Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
Marshin Posted April 10, 2008 Author Share Posted April 10, 2008 thank you for your help, and for showing me new ways of displaying the data. looks like I will be using beta :-) At last, after two thousand years of research, the illudium Q-36 explosive space modulator.Well, back to the old drawing board. Link to comment Share on other sites More sharing options...
zorphnog Posted April 10, 2008 Share Posted April 10, 2008 Ah ha! I was wondering why it never crashed my prod 3.2.10.0. Then I remembered I was having problems with the _SQLite_GetTable2d and found a bug in SQLite.au3. The problem actually is coming from the SQLite internal function __SQLite_szStringRead: Func __SQLite_szStringRead($iszPtr, $iLen = -1) Local $aStrLen, $vszString If $iszPtr < 1 Then Return "" If $iLen < 1 Then If $g_avSafeMode_SQLite[3] < 1 Then $g_avSafeMode_SQLite[3] = DllOpen("msvcrt.dll") $aStrLen = DllCall($g_avSafeMode_SQLite[3], "int:cdecl", "strlen", "ptr", $iszPtr) If @error Then Return SetError(1, 0, "") $iLen = $aStrLen[0] + 1 EndIf $vszString = DllStructCreate("char[" & $iLen & "]", $iszPtr) If @error Then Return SetError(2, 0, "") Return SetError(0, $iLen, DllStructGetData($vszString, 1)) EndFunc ;==>__SQLite_szStringRead The problem is that if you have a null column value in a row, the $iszPtr is 0 but fails the "If $iszPtr < 1 Then Return ''" statement. Changing the statement to "If $iszPtr < 1 Or $iszPtr = 0 Then Return ''" will fix the issue and has already been fixed in the beta. 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