Sign in to follow this  
Followers 0
davee

FileOpenDialog and SQLite Error

13 posts in this topic

I have a forms, which is a feature where you can choose image, this image I want to insert a SQLite-based database (database.db) the path and other data, the image will choose FileOpenDialog function, the insert works as long as I do not use this feature, but let's say I enter the path manually. As I use insert on the selection process will fail utterly pointless.

The main point I took from the program:

GUI part:

 

$Label5 = GUICtrlCreateLabel("Select Image", 13, 278, 98, 22)
$Button2 = GUICtrlCreateButton("Browse...", 360, 272, 89, 25)
GUICtrlSetOnEvent(-1, "OpenImage") ;OpenImage
$image = GUICtrlCreateInput("", 112, 272, 241, 26)

Background:

OpenDialog Function:

Func OpenImage()

$var = FileOpenDialog("Select Image", "","Images (*.jpg;*.bmp;*.jpeg;*.gif)", 1)
If $var Then
GUICtrlSetData($image, $var)

EndIf

EndFunc

SQL Insert:

Local $listQuery, $listRow, $listMsg
         _SQLite_Startup()
         _SQLite_Open('database.db')
         _SQLite_Exec(-1, "CREATE TABLE IF NOT EXISTS cars (id INTEGER PRIMARY KEY, type TEXT, vintage TEXT, enginecode TEXT, cubic TEXT, performance TEXT, license TEXT, image TEXT);", $listQuery) ; Create Table
         _SQLite_Exec(-1, "INSERT INTO cars (type,vintage,enginecode,cubic,performance,license,image) VALUES ('" & GUICtrlRead($type) & "','" & GUICtrlRead($vintage) & "','" & GUICtrlRead($enginecode) & "','" & GUICtrlRead($cubic) & "','" & GUICtrlRead($performance) & "','" & GUICtrlRead($license) & "','" & GUICtrlRead($image) & "');", $listQuery) ; Insert Row
         MsgBox(0, "SQLite Error", "Error Code: " & _SQLite_ErrCode() & @CRLF & "Error Message: " & _SQLite_ErrMsg())
         _SQLite_Close()
         _SQLite_Shutdown()
         
      
EndFunc

When I use the function in the error message is as follows:

 

--> Error:    table cars has no column named license

If you hand write it runs the process without any defect

What could be the problem?
The FileOpenDialog is incompatible with SQLite?

Thank you in advance for your help!

Sorry for my bad english.

Share this post


Link to post
Share on other sites



davee,

Can you post this as runnable code?

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

davee,

Can you post this as runnable code?

kylomas

#include <ButtonConstants.au3>
#include <DateTimeConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <GUIListBox.au3>
#include <GuiListView.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
;SQLite Include
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <file.au3>

Opt("GUIOnEventMode", 1)
   
Global $type,$vintage,$enginecode,$cubic,$performance,$license,$image

$AddForm = GUICreate("Autó Hozzáadás", 468, 364, -1, -1)
GUISetOnEvent($GUI_EVENT_CLOSE, "_GoMainScreen") ;GoMainScreen
GUISetState(@SW_SHOW)
GUISetFont(11, 400, 0, "Myriad Hebrew")
$Group1 = GUICtrlCreateGroup("Autó adatai", 8, 8, 449, 225)
$Label1 = GUICtrlCreateLabel("Típus", 90, 38, 38, 22)
$type = GUICtrlCreateInput("", 144, 32, 289, 26)
$Label2 = GUICtrlCreateLabel("Évjárat", 83, 68, 46, 22)
$vintage = GUICtrlCreateInput("", 144, 64, 289, 26)
$Label3 = GUICtrlCreateLabel("Motorkód", 64, 103, 65, 22)
$enginecode = GUICtrlCreateInput("", 144, 96, 289, 26)
$cubic = GUICtrlCreateInput("", 144, 128, 289, 26)
$performance = GUICtrlCreateInput("", 144, 160, 289, 26)
$license = GUICtrlCreateInput("", 144, 192, 289, 26)
$Label4 = GUICtrlCreateLabel("Hengerürtartalom", 16, 133, 118, 22)
$Label6 = GUICtrlCreateLabel("Teljesítmény", 48, 163, 86, 22)
$Label7 = GUICtrlCreateLabel("Rendszám", 64, 198, 68, 22)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Group2 = GUICtrlCreateGroup("Kép", 8, 248, 449, 65)
$Label5 = GUICtrlCreateLabel("Kép kiválasztás", 13, 278, 98, 22)
$Button2 = GUICtrlCreateButton("Tallózás...", 360, 272, 89, 25)
GUICtrlSetOnEvent(-1, "OpenImage") ;OpenImage
$image = GUICtrlCreateInput("", 112, 272, 241, 26)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Button3 = GUICtrlCreateButton("Mégse", 328, 320, 121, 33)
GUICtrlSetOnEvent(-1, "_GoMainScreen") ;GoMainScreen
$Button4 = GUICtrlCreateButton("Hozzáadás", 192, 320, 121, 33)
GUICtrlSetOnEvent(-1, "InsertCar") ;Insert Car
#EndRegion ### END Koda GUI section ###

While 1
    Sleep(100)
WEnd

Func OpenImage()

$var = FileOpenDialog("Válaszd ki az album mappáját", "","Képek (*.jpg;*.bmp;*.jpeg;*.gif)", 1)
If $var Then
GUICtrlSetData($image, $var)

EndIf

EndFunc

Func InsertCar()
   
Local $listQuery, $listRow, $listMsg
         _SQLite_Startup()
         _SQLite_Open('database.db')
         _SQLite_Exec(-1, "CREATE TABLE IF NOT EXISTS cars (id INTEGER PRIMARY KEY, type TEXT, vintage TEXT, enginecode TEXT, cubic TEXT, performance TEXT, license TEXT, image TEXT);", $listQuery) ; Create Table
         _SQLite_Exec(-1, "INSERT INTO cars (type,vintage,enginecode,cubic,performance,license,image) VALUES ('" & GUICtrlRead($type) & "','" & GUICtrlRead($vintage) & "','" & GUICtrlRead($enginecode) & "','" & GUICtrlRead($cubic) & "','" & GUICtrlRead($performance) & "','" & GUICtrlRead($license) & "','" & GUICtrlRead($image) & "');", $listQuery) ; Insert Row
         MsgBox(0, "SQLite Error", "Error Code: " & _SQLite_ErrCode() & @CRLF & "Error Message: " & _SQLite_ErrMsg())
         _SQLite_Close()
         _SQLite_Shutdown()
         ;_UpdateListView()
         ;_GoMainScreen()
         
      
EndFunc
 
I hope you think so! :) (Originally prepare in Hungarian!)
Edited by davee

Share this post


Link to post
Share on other sites

davee,

Thank you but function _GoMainScreen is missing...

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

davee,

Thank you but function _GoMainScreen is missing...

kylomas

Really, I'm sorry.

Otherwise, there is only one Gui hide.

Share this post


Link to post
Share on other sites

Really, I'm sorry.

Otherwise, there is only one Gui hide.         

 

Huh???!?

Whenever you are adding strings to an SQLite table you should escape the strings using _SQLite_FastEscape.  See the Help file.  I don't know if this is your problem and until you post runnable code I can only guess.

kylomas

 


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

Huh???!?

Whenever you are adding strings to an SQLite table you should escape the strings using _SQLite_FastEscape. See the Help file. I don't know if this is your problem and until you post runnable code I can only guess.

kylomas

They have missed that piece of code only served to make the pop-up window to hide what I sent you closed it. The base image is not of the form. The error of no importance but the content of many features:

GuiSetState(@SW_HIDE)

The idea is to try tomorrow, thanks

Share this post


Link to post
Share on other sites

davee,

If SQLite is saying "--> Error:    table cars has no column named license" you have to give it some credit and consider that your table CARS might indeed miss the license column.

My (wild) guess is that you've created the table without the license column.

Afterwards, statements like the one you use with IF NOT EXISTS clause won't add new column by mere magic. Either recreate your database from scratch or use ALTER table cars add column license...

Refer to SQLite docs for more.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

davee,

There were several problems with your code.  See the comments in the code.  This works for me using the file select dialog.  I did not try to insert any other data.

#include <ButtonConstants.au3>
#include <DateTimeConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <GUIListBox.au3>
#include <GuiListView.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
;SQLite Include
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <file.au3>

Opt("GUIOnEventMode", 1)

Global $type, $vintage, $enginecode, $cubic, $performance, $license, $image

; Start sqlite and open the DB once.  Add some error checking.
; There is no need to start/stop sqlite with every access to the DB.

_SQLite_Startup()
If @error Then
    ConsoleWrite('error loading sqlite.dll' & @LF)
    Exit
EndIf

Local $hDB = _SQLite_Open('database.db3')  ; changed extention to sqlite ".db3"
If @error Then
    ConsoleWrite('Unable to open DB' & @LF)
    _GoMainScreen()
EndIf
OnAutoItExitRegister("_GoMainScreen")

Local $sql = 'CREATE TABLE IF NOT EXISTS cars ' & _
        '(id INTEGER PRIMARY KEY, type TEXT, vintage TEXT, enginecode TEXT, cubic TEXT, performance TEXT, license TEXT, image TEXT);'

If _SQLite_Exec(-1, $sql) <> $sqlite_ok Then
    MsgBox(0, '*** Create Table Error ***', _SQLite_ErrMsg())
    Exit
EndIf

; The third parm of _sqlite_exec is for a callback function and is specified as a string.  What are you trying to do?
; _SQLite_Exec(-1, "CREATE TABLE IF NOT EXISTS cars (id INTEGER PRIMARY KEY, type TEXT, vintage TEXT, enginecode TEXT, cubic TEXT, performance TEXT, license TEXT, image TEXT);", $listQuery) ; Create Table

$AddForm = GUICreate("Autó Hozzáadás", 468, 364, -1, -1)
GUISetOnEvent($GUI_EVENT_CLOSE, "_GoMainScreen") ;GoMainScreen
GUISetState(@SW_SHOW)
GUISetFont(11, 400, 0, "Myriad Hebrew")
$Group1 = GUICtrlCreateGroup("Autó adatai", 8, 8, 449, 225)
$Label1 = GUICtrlCreateLabel("Típus", 90, 38, 38, 22)
$type = GUICtrlCreateInput("", 144, 32, 289, 26)
$Label2 = GUICtrlCreateLabel("Évjárat", 83, 68, 46, 22)
$vintage = GUICtrlCreateInput("", 144, 64, 289, 26)
$Label3 = GUICtrlCreateLabel("Motorkód", 64, 103, 65, 22)
$enginecode = GUICtrlCreateInput("", 144, 96, 289, 26)
$cubic = GUICtrlCreateInput("", 144, 128, 289, 26)
$performance = GUICtrlCreateInput("", 144, 160, 289, 26)
$license = GUICtrlCreateInput("", 144, 192, 289, 26)
$Label4 = GUICtrlCreateLabel("Hengerürtartalom", 16, 133, 118, 22)
$Label6 = GUICtrlCreateLabel("Teljesítmény", 48, 163, 86, 22)
$Label7 = GUICtrlCreateLabel("Rendszám", 64, 198, 68, 22)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Group2 = GUICtrlCreateGroup("Kép", 8, 248, 449, 65)
$Label5 = GUICtrlCreateLabel("Kép kiválasztás", 13, 278, 98, 22)
$Button2 = GUICtrlCreateButton("Tallózás...", 360, 272, 89, 25)
GUICtrlSetOnEvent(-1, "OpenImage") ;OpenImage
$image = GUICtrlCreateInput("", 112, 272, 241, 26)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Button3 = GUICtrlCreateButton("Mégse", 328, 320, 121, 33)
GUICtrlSetOnEvent(-1, "_GoMainScreen") ;GoMainScreen
$Button4 = GUICtrlCreateButton("Hozzáadás", 192, 320, 121, 33)
GUICtrlSetOnEvent(-1, "InsertCar") ;Insert Car
#endregion ### END Koda GUI section ###

While 1
    Sleep(100)
WEnd

Func OpenImage()

    $var = FileOpenDialog("Válaszd ki az album mappáját", "", "Képek (*.jpg;*.bmp;*.jpeg;*.gif)", 1)
    If $var Then
        GUICtrlSetData($image, $var)
    EndIf

EndFunc   ;==>OpenImage

Func InsertCar()

    local $ret

    ; again, you had a 3RD parm specified.  Also, use _sqlite_fastescape() for string values.
    ; Always check for an error condition.  And, whatever sqlite errors you are getting should be mirrored in the console area.

    $ret = _SQLite_Exec(-1, 'INSERT INTO cars (type,vintage,enginecode,cubic,performance,license,image) VALUES (' & _
                    _sqlite_fastescape(GUICtrlRead($type)) & ',' & _
                    _sqlite_fastescape(GUICtrlRead($vintage)) & ',' & _
                    _sqlite_fastescape(GUICtrlRead($enginecode)) & ',' & _
                    _sqlite_fastescape(GUICtrlRead($cubic)) & ',' & _
                    _sqlite_fastescape(GUICtrlRead($performance)) & ',' & _
                    _sqlite_fastescape(GUICtrlRead($license)) & ',' & _
                    _sqlite_fastescape(GUICtrlRead($image)) & ');')

    if $ret <> $sqlite_ok then
        MsgBox(0, "SQLite Error", "Error Code: " & _SQLite_ErrCode() & @CRLF & "Error Message: " & _SQLite_ErrMsg())
        _GoMainScreen()
    endif

EndFunc   ;==>InsertCar

Func _GoMainScreen()
    _SQLite_Close()
    _SQLite_Shutdown()
    Exit
EndFunc   ;==>_GoMainScreen

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

What could be the problem?

The FileOpenDialog is incompatible with SQLite?

Using relative paths can a problem if the script is not handling the relative paths with any change of working directory.

I am not changing too much to the code other then getting it working. The single quotes around text parameters to SQLite seem fine. The major concern was the working directory change when using FileOpenDialog. FileOpenDialog changes working directory to the path of the selected file. The database.db being used in _SQLite_Open() may not be at the same path with successful use of FileOpenDialog. So when the working directory changed, then your SQL Insert statements failed as database.db did not exist in the current working directory.

This is what I tested with.

; Changes:
; Added missing _GoMainScreen() that was added to test with
; Updated OpenImage() to handle workingdir change to fix major issue
; Removed 3rd parameter used for _SQLite_Exec() as unneeded and it was doing nothing as being ""

#include <ButtonConstants.au3>
#include <DateTimeConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <GUIListBox.au3>
#include <GuiListView.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
;SQLite Include
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <file.au3>

Opt("GUIOnEventMode", 1)
   
Global $type,$vintage,$enginecode,$cubic,$performance,$license,$image

$AddForm = GUICreate("Autó Hozzáadás", 468, 364, -1, -1)
GUISetOnEvent($GUI_EVENT_CLOSE, "_GoMainScreen") ;GoMainScreen
GUISetState(@SW_SHOW)
GUISetFont(11, 400, 0, "Myriad Hebrew")
$Group1 = GUICtrlCreateGroup("Autó adatai", 8, 8, 449, 225)
$Label1 = GUICtrlCreateLabel("Típus", 90, 38, 38, 22)
$type = GUICtrlCreateInput("", 144, 32, 289, 26)
$Label2 = GUICtrlCreateLabel("Évjárat", 83, 68, 46, 22)
$vintage = GUICtrlCreateInput("", 144, 64, 289, 26)
$Label3 = GUICtrlCreateLabel("Motorkód", 64, 103, 65, 22)
$enginecode = GUICtrlCreateInput("", 144, 96, 289, 26)
$cubic = GUICtrlCreateInput("", 144, 128, 289, 26)
$performance = GUICtrlCreateInput("", 144, 160, 289, 26)
$license = GUICtrlCreateInput("", 144, 192, 289, 26)
$Label4 = GUICtrlCreateLabel("Hengerürtartalom", 16, 133, 118, 22)
$Label6 = GUICtrlCreateLabel("Teljesítmény", 48, 163, 86, 22)
$Label7 = GUICtrlCreateLabel("Rendszám", 64, 198, 68, 22)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Group2 = GUICtrlCreateGroup("Kép", 8, 248, 449, 65)
$Label5 = GUICtrlCreateLabel("Kép kiválasztás", 13, 278, 98, 22)
$Button2 = GUICtrlCreateButton("Tallózás...", 360, 272, 89, 25)
GUICtrlSetOnEvent(-1, "OpenImage") ;OpenImage
$image = GUICtrlCreateInput("", 112, 272, 241, 26)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Button3 = GUICtrlCreateButton("Mégse", 328, 320, 121, 33)
GUICtrlSetOnEvent(-1, "_GoMainScreen") ;GoMainScreen
$Button4 = GUICtrlCreateButton("Hozzáadás", 192, 320, 121, 33)
GUICtrlSetOnEvent(-1, "InsertCar") ;Insert Car
#EndRegion ### END Koda GUI section ###

While 1
    Sleep(100)
WEnd

Func OpenImage()
    Local $var, $workingdir
    
    ; save workingdir
    $workingdir = @WorkingDir
    
    ; FileOpenDialog changes the workingdir on success
    $var = FileOpenDialog("Válaszd ki az album mappáját", "","Képek (*.jpg;*.bmp;*.jpeg;*.gif)", 1)
    If $var Then
        GUICtrlSetData($image, $var)
    EndIf
    
    ; If FileExists code block is for temporary debugging
    If Not FileExists('database.db') Then
        MsgBox(0x40030, 'Debug', 'database.db does not exist' & @CRLF & _
            '$workingdir = "' & $workingdir & '"' & @CRLF & _
            '@WorkingDir = "' & @WorkingDir & '"' _
        )
    EndIf
    
    ; go back to save workingdir
    FileChangeDir($workingdir)
EndFunc

Func InsertCar()
    Local $listQuery, $listRow, $listMsg
     _SQLite_Startup()
     _SQLite_Open('database.db')
     _SQLite_Exec(-1, "CREATE TABLE IF NOT EXISTS cars (id INTEGER PRIMARY KEY, type TEXT, vintage TEXT, enginecode TEXT, cubic TEXT, performance TEXT, license TEXT, image TEXT);") ; Create Table
     _SQLite_Exec(-1, "INSERT INTO cars (type,vintage,enginecode,cubic,performance,license,image) VALUES ('" & GUICtrlRead($type) & "','" & GUICtrlRead($vintage) & "','" & GUICtrlRead($enginecode) & "','" & GUICtrlRead($cubic) & "','" & GUICtrlRead($performance) & "','" & GUICtrlRead($license) & "','" & GUICtrlRead($image) & "');") ; Insert Row
     MsgBox(0, "SQLite Error", "Error Code: " & _SQLite_ErrCode() & @CRLF & "Error Message: " & _SQLite_ErrMsg())
     _SQLite_Close()
     _SQLite_Shutdown()
     ;_UpdateListView()
     ;_GoMainScreen()
EndFunc

; missing function that was added to test with
Func _GoMainScreen()
    Exit
EndFunc

The code that kylomas has posted does not have an issue with working directory change as the handle created by _SQLite_Open() is kept open.

So hopefully the question of FileOpenDialog is compatible with SQLite has been answered. :)

Share this post


Link to post
Share on other sites

From the error u get it might be an issue i noticed with table column names in SQL . For example if i use "Test Test" as column name in the script it wont work but if i use "[Test Test]" it works.

You may be facing similar issue.

Share this post


Link to post
Share on other sites

From the error u get it might be an issue i noticed with table column names in SQL . For example if i use "Test Test" as column name in the script it wont work but if i use "[Test Test]" it works.

You may be facing similar issue.

Schema names must be enclosed in double quotes when they contain whitespaces or some other kid of special character (but Unicode letters and symbols are fine). SQLite also accepts schema names in square brackets and in backward quotes (for compatibility with some other engines).


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Thanks for all the quick help, it looks MHz FileChangeDir came up with a solution.
Looks like you caught quite a GoMainScreen function, so I share with you what is included in this function. :)

Func _GoMainScreen()
    GUISetState(@SW_HIDE)
    GUIDelete()
EndFunc   ;==>_GoMainScreen

kylomas your code was also a special thank you, I will look at in more detail, because I found a lot of good stuff in there, their cars, "programming" is still just learning. ;)

davee

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  
Followers 0