Jump to content
Sign in to follow this  
Marshin

SQLite Alter Table add column crashes

Recommended Posts

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 :party: . 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.

Share this post


Link to post
Share on other sites
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. :party:

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.

:lmao:


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

Share this post


Link to post
Share on other sites
zorphnog

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

Share this post


Link to post
Share on other sites
Marshin

here is a sample script for you to look over and try out :D . 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. :D

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 by Marshin

At last, after two thousand years of research, the illudium Q-36 explosive space modulator.Well, back to the old drawing board.

Share this post


Link to post
Share on other sites
ChrisL

here is a sample script for you to look over and try out :D . 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. :D

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

Share this post


Link to post
Share on other sites
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 by zorphnog

Share this post


Link to post
Share on other sites
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:

#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.

:D


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

Share this post


Link to post
Share on other sites
Marshin

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×