SQLite Alter Table add column crashes

[Marshin]

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.

[PsaltyDS]

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.

[zorphnog]

As Psalty said, we need your code to check for errors. BTW the command is VACUUM and you would use it like 'VACUUM;'.

[Marshin]

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

[ChrisL]

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

[zorphnog]

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

[PsaltyDS]

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.

[Marshin]

thank you for your help, and for showing me new ways of displaying the data. looks like I will be using beta :-)

[zorphnog]

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.