183 replies to this topic

[infogulch]

IsNull has it on github already (first post), but he hasn't pushed the latest changes yet.

[panofish]

What about support for mysql commit and rollback?

[panofish]

I also write batch programs that access my database.
I don't want a msgbox when an error occurs in batch mode, I want to log the error, yet continue processing.

How can I indicate that I don't want a msgbox error if my db.query fails... I just want the error code and error string returned?

[infogulch]

If IsNull would upload his changes I would probably have added some of these features... HINT HINT

[IsNull]

Hey there,
I was on a short holiday trip to Vienna, so I'm sorry for the delay on github. I pushed the latest changes up, so I hope you guys can start enhancing the code.

How can I indicate that I don't want a msgbox error if my db.query fails... I just want the error code and error string returned?

The plan is, to handle all sorts of errors by Exceptions, so you can catch them where ever you want.

What about support for mysql commit and rollback?

Transaction has already a simple implementation on mysql, however, rollback is currently missing.

BeginTransaction(){
		this.Query("START TRANSACTION;")
	}
	
	EndTransaction(){
		this.Query("COMMIT;")
	}

[IsNull]

I've merged the contributions of infogulch. Thanks

The only changes I've made are, that the DB Types are static now. <!-- m -->https://github.com/I... ... 3a64910ebe<!-- m -->
They can be used as Instance Fields too, so db.NULL is possible as well.

[infogulch]

Oops I meant to do that. You should have made me fix it before you merged.

I think I'll look into UPDATE next.

[panofish]

I'm excited to see progress on the library.

#1 for me is error handling:

I would like to see msgbox errors by default, with the ability to indicate no msgbox and just return error codes/messages.
This can save a lot of coding in some programs where you dont want to add error handling logic, but you still want to see the sql errors.
It also would give the ability to handle errors in your code, which is especially valuable when creating "batch" programs.
It's the best of both worlds, minimal code or complete control.

#2 is commit and rollback:

THANKS guys!

[IsNull]

Hey panofish,

I've added the db.Rollback() Method. So you have basic Transaction support now. (named Transactions etc may follow)

For the error-handling, like I said, using msgboxes in the deeps of the DBA Framework is not good practice. However, I see your point for simplicity, and I've come up with the following solution:

We will make the core of DBA just with exceptions, but as we are in the OOP world, there are many possibilities to handle the issue. We use transparent Proxies around the real DBA classes, which internaly call the original Methods but catch every exception for us and display them in a msgbox.

Proof of Concept: (working code)

mydbConnection := CreateDB(showErrors := true) ; we don't know if we get a Proxy here or the Original. Doesn't matter, it will be a "DataBase" anyways. :)
mydbConnection.Query()

ExitApp


CreateDB(showErrorMsgBoxes){
   db := new DataBase
   
   if(showErrorMsgBoxes)
      db := new DataBaseNoExceptionProxy(db)
   return db
}

/*
DBA class which throws errors
*/
class DataBase
{
   Query(){ ; method which trows an excpetion
      throw Exception("World is a strange place o.0")
   }
}

/*
Proxy around a DataBase class which will catch exceptions and "translate" them to error messages
*/
class DataBaseNoExceptionProxy extends DataBase
{
   db := 0
   __new(mydb){
      this.db := mydb
   }
   
   Query(){ ; method which trows an excpetion
      try {
         this.db.Query() ; forward the method call to the original db
      }catch e{
         MsgBox % e.Message
      }
   }
}

[panofish]

Most users will want a clean and simple way to handle errors, they will mostly be focused on their sql logic.
The following 2 examples are how my mysql library handles errors automatically OR manually.

Example 1 will fail with a friendly system msgbox error if there is an sql syntax or if the delete fails. This is the preferred default.

sql = 
(
   delete
     from fileindex
    where path like '%path_escape%`%'
      and type = 'f'
      and level = %filelevel%
)

result := mysql.query(db, sql)  

Example 2 is how I can handle errors when I wish to do so.

sql = 
(
   delete
     from fileindex
    where path like '%path_escape%`%'
      and type = 'f'
      and level = %filelevel%
)

result := mysql.query(db, sql, 0)   ;  last parameter indicates no msgbox 

if mysql.error {
    debug( "delete failed : " mysql.errstr )
   ; rollback logic goes here.
}  

I believe it is important that the "default" operation provide a msgbox on error, except when the developer wishes to handle the error.

[infogulch]

Generally, the developer would want one or the other for all calls, so it should be a per-connection option. Much easier than adding arbitrary "show message box" args to dozens of methods. ("Just add another arg!" is a solution that people turn to far too often, especially since we have objects. This is why we have libraries that have functions with half a dozen rarely used args and you need to consult the documentation just to write one call.)

Also, it ought to be implemented in a way that can be switched on and off. Probably a method in the Base class like ShowError(Exception_Object), and a complimentary way to change how ShowError actually handles the errors.

Then all errors should do something like this:

if (error encountered)
    return this.showError(Exception("Error msg", -1))

If ShowError is configured to throw exceptions, it will just throw the exception. If it's configured to show a msgbox, it will do that instead with the information from the exception object. There could even be other methods of handling errors, and this can be easily extended to include them.

[panofish]

Yes, I agree. I was just using an example to demonstrate what I wanted to say.
I'm all for a "pure" solution. I wanted to emphasize the importance of flexibility and simplicity, so I think we are in agreement.
Your suggestion looks like it fits the bill!

I have plenty of production source code that implements my own mysql library and it works very well.
But, I'd like the benefit to use sqlite when it makes sense.
I have tried implementing this OOP SQL database in place of my own several times, but I hit a snag each time.
I think this library is getting close and the remaining shortcoming, is good error handling.

Thanks Infogulch and IsNull!

[Aravind]

I got a serious problem while using SQLite3! The wrapper returns the "Database Is Locked" error even after the functions EndTransaction() & Close() are invoked after modifications.
This is my scenario:
I'm using this as a parking management system for a project. I'm running 2 AHK programs running on 2 different computers to read/write(No simultaneous read/write but both the scripts will be running at the same time) from a single database stored in one of the computers having the database. The system works perfectly for 2-3 writes & after sometime both the scripts returns the error "Database Is Locked". I can read from the database normally, but when i try to write, it gives out the error!

I've done some googling & found that SQLite locks the Database when a program tries to write into it & releases the lock after the operation is complete or EndTranscaction() is called! But here, nothing helps! But the scripts will work normally, if i close them & open them. Again, works for 2-3 times & then same error! :evil:

Here is my code for the server(The one used in the system storing the database):

#singleinstance,force
#notrayIcon
#include Date.ahk
#include Anchor.ahk
#Warn, LocalSameAsGlobal, Off
;SetWorkingDir %A_ScriptDir% 
#Include <DBA>


gui,+resize
gui,color,FFFFFF
Gui, Add, StatusBar,,Everything Ok!


;-------------------------------Script Using On The System With Database



Menu, HelpMenu,Add,About,MenuHandler
Menu, FileMenu, Add, Generate Report, MenuHandler
Menu, FileMenu, Add, Admin Options, MenuHandler
Menu, FileMenu, Add, Exit, MenuHandler
Menu, MyMenuBar, Add, File,:filemenu
Menu, MyMenuBar, Add, Help,:Helpmenu
Gui, Menu, MyMenuBar




Gui, Add, Text, x10 y510,Coded/Developed By: Aravind.V.S


Gui,font,s12 q4
Gui, Add, Tab, x12 y60 w950 h450 vtab, Entry|Exit

Gui, Tab, Entry
gui,font,s19
Gui, Add, Edit, x402 y150 w310 h40 Uppercase limit13 vregnumber,
gui,font,s12
Gui, Add, Text, x189 y160 h40 vt_reg, Vehicle Registration Number:
Gui, Add, Text, x302 y222 h30 vt_type, Vehicle Type:

Gui, Add, DropDownList, x402 y220 w85 vtype gGet, Type1|Type2|Type3|Type4

;Gui, Add, Monthcal, x780 y90
Gui, Add, Button, x802 y460 w120 h40 gOkandPrint vOKIE, Ok && Print

Gui, Add, GroupBox, x132 y110 w630 h180 vt_grp_vehicle, Vehicle Details
Gui, Add, GroupBox, x132 y300 w630 h140 vt_grp_details, Entered Details

Gui, Add, Text, x152 y330 h20 vt_date, Date Of Entry:

gui,font,s15 cred
Gui, Add, Text, x255 y328 h20 w250 vdisplay_date,
gui,font,s12 cBlack
Gui, Add, Text, x152 y360 h20 vt_reg_no, Vehicle Registration Number:

gui,font,s15 cred
Gui, Add, Text, x363 y358 w250 h20 vdisplay_reg,
gui,font,s12 cBlack
Gui, Add, Text, x252 y400 h20, Coupon Number:

gui,font,s22 cblue
Gui, Add, Text, x380 y393 w100 vdisplay_coupon,
gui,font,s12 cBlack

Gui,Tab,Exit
Gui, Add, Text, x260 y160 h40 vt_cop2, Coupon Number:

Gui, Add,Button, x715 y150 h40 glost vlost,Coupon Lost
gui,font,s15 cBlue
Gui, Add, Edit, x402 y150 w310 h40 Uppercase vexisting_coupon,
gui,font,s12
gui,font,cBlack
Gui, Add, Button, x802 y460 w120 h42 gGet2 vget2,Ok && Print
Gui, Add, GroupBox, x132 y200 w630 h140 vt_grp_details_db,Vehicle Details In Database
Gui, Add, Text, x152 y230 h20 vt_date_entry, Date Of Entry:

gui,font,s15 cBlue
Gui, Add, Text, x255 y228 h20 w250 vdisplay_date_onexit,
gui,font,s12 cBlack
Gui, Add, Text, x152 y260 h20 vt_reg2 , Vehicle Registration Number:

gui,font,s15 cBlue
Gui, Add, Text, x363 y258 w250 h20 vdisplay_reg_onexit,
gui,font,s12 cBlack
Gui, Add, GroupBox, x132 y350 w630 h140 vt_grp_chrge,Charge Details
Gui, Add, Text, x252 y300 h20 , Type:

gui,font,s22 cblue
Gui, Add, Text, x380 y293 w100 vdisplay_type_onexit,
Gui, Add, Text, x660 y404 h20 vt_chrge,Charge:

gui,font,s30 cRED
Gui, Add, Text, x768 y400 w100 vdisplay_charge_onexit,
gui,font,s12 cBlack

Gui, Add, Text, x152 y380 h20 vt_dys,Days Since Entry:

Gui, Add, Text, x280 y381 h20 vE_days  +redraw,000000
Gui, Add, Text, x152 y420 h20 vt_total_hrs,Total Hours Since Entry:

Gui, Add, Text, x327 y421 h20 vE_hours +redraw,000000
Gui, Add, Text, x152 y460 h20 vt_extra_hrs,Extra Hours:

Gui, Add, Text, x245 y460 h20 vEx_hours +redraw,000000
guicontrol,disable,okie
Gui, Show,h562 w981,Parking Management System Version 1.0 -->Server
Gui,show,maximize
Return





GuiClose:

ExitApp

get:
gui,submit,nohide
if(regnumber=="")
 return
dbx:= DBA.DataBaseFactory.OpenDataBase("SQLite","D:\Database\Test.DB")
qry=Select * From Vehicle Where Number = '%regnumber%' AND Status= '1'
foundRow := dbx.QueryRow(qry)
dbx.EndTransaction()
dbx.close()
if(IsObject(foundRow ))
{
  msgbox,262160,Vehicle Already In DB,Vehicle Already In The DataBase!
  Guicontrol,,regnumber,
   return
}


;---------------------Read The Last Generated Coupon From A File & Increment 1 To Generate The Next

iniread,coupon,D:\Database\Info.ini,info,count
coupon++
Guicontrol,,display_reg,%regnumber%
Guicontrol,,display_date,%A_DD%-%A_MM%-%A_YYYY%
Guicontrol,,display_coupon,BS%coupon%
SB_SetText(dbx.GetLastErrorMsg())
guicontrol,,Type
Guicontrol,enable,okie
return



OKandPrint:
FormatTime, TimeString,%A_Now%,hh:mm:ss tt
dbx:= DBA.DataBaseFactory.OpenDataBase("SQLite",dbpath)
qry=INSERT INTO Vehicle Values ('%regnumber%','BS%coupon%','%type%',date(),'%TimeString%','0','0','0','1','0')
dbx.query(qry)
dbx.EndTransaction()
dbx.close()
msgbox,% dbx.GetLastErrorMsg()

;----------------Write The Currently Generated Coupon Number Into File
iniwrite,%coupon%,D:\Database\Info.ini,info,count
Guicontrol,,display_reg,
Guicontrol,,display_date,
Guicontrol,,display_coupon,
Guicontrol,,regnumber,
guicontrol,disable,okie





sb_settext(dbx.GetLastErrorMsg())
return


get2:
gui,submit,nohide
if(existing_coupon=="")
 return

main:
dbx:= DBA.DataBaseFactory.OpenDataBase("SQLite",dbpath)
qry=SELECT * FROM Vehicle WHERE CouponNumber='%existing_coupon%'
mainx:
rs:=dbx.OpenRecordSet(qry)
msgbox,% dbx.GetLastErrorMsg()
ex_time:=rs["time"]
ex_date:=rs["date"]
ex_reg:=rs["Number"]
ex_type:=rs["type"]
ex_status:=rs["status"]
if(ex_status==0)
 {
  msgbox,262160,Error!,Vehicle Already Dispatched!
  ;db.Close()
  return
 }


if(ex_time=="")
{
 msgbox,262160,Error!,Invalid Coupon Number!
  SB_SetText("Vehicle Not In Database!!")
  ;db.Close()
 return
}


qry=UPDATE Vehicle SET Status='0' WHERE CouponNumber='%existing_coupon%'
dbx.query(qry)
FormatTime, TimeString,%A_Now%,hh:mm:ss tt
qry=UPDATE Vehicle SET Edate=date(),Etime='%TimeString%' WHERE CouponNumber='%existing_coupon%'
dbx.query(qry)
msgbox,% dbx.GetLastErrorMsg()
SB_SetText(dbx.GetLastErrorMsg())




SB_SetText("Vehicle Record Found")
StringMid,rep1,ex_date,0, 4
StringMid,rep2,ex_date,6, 2
StringMid,rep3,ex_date,9, 2


sourcedate = %rep3%-%rep2%-%rep1% %A_Space% %ex_time%
Guicontrol,,display_reg_onexit,%ex_reg%
Guicontrol,,display_date_onexit,%ex_date% %A_Space% %ex_time%
Guicontrol,,display_type_onexit,%ex_type%
ch:=get_charge(ex_type,sourcedate)
Guicontrol,,display_charge_onexit,%ch%


qry=UPDATE Vehicle SET charges='%ch%' WHERE CouponNumber='%existing_coupon%'
dbx.query(qry)
dbx.EndTransaction()
dbx.close()
;db.Close()
xday := DateParse(sourcedate)

StringTrimRight, result,A_Now, 2
hour:=result
EnvSub, result, %xday%, days
EnvSub, hour, %xday%, hours

y:=mod(hour,24)
Guicontrol,,e_days,%result%
Guicontrol,,e_hours,%hour%
Guicontrol,,ex_hours,%y%



Guicontrol,,e_days,
Guicontrol,,e_hours,
Guicontrol,,ex_hours,
Guicontrol,,display_reg_onexit,
Guicontrol,,display_date_onexit,
Guicontrol,,display_type_onexit,
Guicontrol,,display_charge_onexit,
Guicontrol,,existing_coupon,
return




get_charge(type,inputdate)
{

day := DateParse(inputdate)

StringTrimRight, result,A_Now, 2
hour:=result
EnvSub, result, %day%, days
EnvSub, hour, %day%, hours

y:=mod(hour,24)
sb_settext(y)
if(y>0)
 result:=result+1
if(type=="Type2")
{
if(result >= 1)
 charge:=result*10
else
 if(result<1)
  {
   if(hour<=2)
     charge=2
   if(hour>2 && hour<=4)
 	charge=3
   if(hour>4 && hour<=8)
	charge=4
   if(hour>8 && hour<=16)
     	charge=5
   if(hour>16 && hour<=24)
	charge=7
  }
}

if(type=="Type3")
{

if(result >= 1)
 charge:=result*20
else
 if(result<1)
  {
   if(hour<=2)
     charge=5
   if(hour>2 && hour<=4)
 	charge=8
   if(hour>4 && hour<=8)
	charge=11
   if(hour>8 && hour<=16)
     	charge=15
   if(hour>16 && hour<=24)
	charge=20
  }
}

if(type=="Type4")
{
 if(result >= 1)
 charge:=result*30
else
 if(result<1)
  {
   if(hour<=2)
     charge=10
   if(hour>2 && hour<=4)
 	charge=15
   if(hour>4 && hour<=8)
	charge=20
   if(hour>8 && hour<=16)
     	charge=25
   if(hour>16 && hour<=24)
	charge=30
  }
}

if(type=="Type1")
{
 if(result >= 1)
 charge:=result*6
else
 if(result<1)
  {
   if(hour<=2)
     charge=1
   if(hour>2 && hour<=16)
 	charge=2
   if(hour>16 && hour<=24)
	charge=3
  }
}

return charge
}




MenuHandler:
if(A_ThisMenuItem=="About")
 {
  Gui,2:+toolwindow
  Gui,2: add,Picture,x0 y0,about.jpg
  Gui,2: Show, h355 w784,About This Software
  Return



}
if(A_ThisMenuItem=="Generate Report")
 {
  gui,hide
  runwait,report.exe
  gui,show
}
if(A_ThisMenuItem=="Admin Options")
 {
   InputBox,adminpassword,Enter Password,Enter Password For Admin Options:,HIDE,250,125
   if(adminpassword=="")
    return
    if(adminpassword=="Admin123")
    {
     msgbox,48,Information,There Are No Admin Configurable Options Availabe At This Moment
     return
    }
   else
    msgbox,262160,Error!,Admin Password Incorrect!
   return
}
if(A_ThisMenuItem=="Exit")
 exitapp
return





lost:
InputBox, lost_coupon,Enter Registration,Enter The Registration Number:
if(lost_coupon=="")
 return
stringupper,lost_coupon,lost_coupon
qry=Select * From Vehicle Where Number = '%lost_coupon%' AND Status= '1'
foundRow := dbx.QueryRow(qry)
dbx.EndTransaction()
dbx.close()
if(IsObject(foundRow ))
{
qry=UPDATE Vehicle SET LostCoupon='1' WHERE CouponNumber='%lost_coupon%'
dbx.query(qry)
dbx.EndTransaction()
dbx.close()
goto,mainx
}
else
{
msgbox,262160,Error!,Vehicle Not In Database or Already Dispatched!
dbx.EndTransaction()
dbx.close()
;db.Close()
}
return


GuiSize:
Anchor("type", "w","true")
Anchor("regnumber", "w","true")
Anchor("okie", "x","true")
Anchor("t_grp_vehicle", "w","true")
Anchor("t_grp_details", "w","true")
Anchor("t_cop", "y","true")
Anchor("lost", "x1","true")
Anchor("existing_coupon", "w","true")
Anchor("t_grp_details_db", "w","true")
Anchor("t_grp_chrge", "w","true")
Anchor("get2", "x","true")
Anchor("tab","w")
return

#IfWinActive ahk_class AutoHotkeyGUI
Enter::
NumPadEnter::
Gui,submit,nohide
if(tab=="Exit")
 goto,get2
return

2GuiClose:
Gui,2: destroy
return

Here is the one i'm using as client(The script running on another system):

#singleinstance,force
#notrayIcon
#include Date.ahk
#include Anchor.ahk
#Warn, LocalSameAsGlobal, Off
;SetWorkingDir %A_ScriptDir% 
#Include <DBA>


gui,+resize
gui,color,FFFFFF
Gui, Add, StatusBar,,Everything Ok!



;--------------Script Running On Another System On The Network


Menu, HelpMenu,Add,About,MenuHandler
Menu, FileMenu, Add, Generate Report, MenuHandler
Menu, FileMenu, Add, Admin Options, MenuHandler
Menu, FileMenu, Add, Exit, MenuHandler
Menu, MyMenuBar, Add, File,:filemenu
Menu, MyMenuBar, Add, Help,:Helpmenu
Gui, Menu, MyMenuBar




Gui, Add, Text, x10 y510,Coded/Developed By: Aravind.V.S


Gui,font,s12 q4
Gui, Add, Tab, x12 y60 w950 h450 vtab, Entry|Exit

Gui, Tab, Entry
gui,font,s19
Gui, Add, Edit, x402 y150 w310 h40 Uppercase limit13 vregnumber,
gui,font,s12
Gui, Add, Text, x189 y160 h40 vt_reg, Vehicle Registration Number:
Gui, Add, Text, x302 y222 h30 vt_type, Vehicle Type:

Gui, Add, DropDownList, x402 y220 w85 vtype gGet, Type1|Type2|Type3|Type4

;Gui, Add, Monthcal, x780 y90
Gui, Add, Button, x802 y460 w120 h40 gOkandPrint vOKIE, Ok && Print

Gui, Add, GroupBox, x132 y110 w630 h180 vt_grp_vehicle, Vehicle Details
Gui, Add, GroupBox, x132 y300 w630 h140 vt_grp_details, Entered Details

Gui, Add, Text, x152 y330 h20 vt_date, Date Of Entry:

gui,font,s15 cred
Gui, Add, Text, x255 y328 h20 w250 vdisplay_date,
gui,font,s12 cBlack
Gui, Add, Text, x152 y360 h20 vt_reg_no, Vehicle Registration Number:

gui,font,s15 cred
Gui, Add, Text, x363 y358 w250 h20 vdisplay_reg,
gui,font,s12 cBlack
Gui, Add, Text, x252 y400 h20, Coupon Number:

gui,font,s22 cblue
Gui, Add, Text, x380 y393 w100 vdisplay_coupon,
gui,font,s12 cBlack

Gui,Tab,Exit
Gui, Add, Text, x260 y160 h40 vt_cop2, Coupon Number:

Gui, Add,Button, x715 y150 h40 glost vlost,Coupon Lost
gui,font,s15 cBlue
Gui, Add, Edit, x402 y150 w310 h40 Uppercase vexisting_coupon,
gui,font,s12
gui,font,cBlack
Gui, Add, Button, x802 y460 w120 h42 gGet2 vget2,Ok && Print
Gui, Add, GroupBox, x132 y200 w630 h140 vt_grp_details_db,Vehicle Details In Database
Gui, Add, Text, x152 y230 h20 vt_date_entry, Date Of Entry:

gui,font,s15 cBlue
Gui, Add, Text, x255 y228 h20 w250 vdisplay_date_onexit,
gui,font,s12 cBlack
Gui, Add, Text, x152 y260 h20 vt_reg2 , Vehicle Registration Number:

gui,font,s15 cBlue
Gui, Add, Text, x363 y258 w250 h20 vdisplay_reg_onexit,
gui,font,s12 cBlack
Gui, Add, GroupBox, x132 y350 w630 h140 vt_grp_chrge,Charge Details
Gui, Add, Text, x252 y300 h20 , Type:

gui,font,s22 cblue
Gui, Add, Text, x380 y293 w100 vdisplay_type_onexit,
Gui, Add, Text, x660 y404 h20 vt_chrge,Charge:

gui,font,s30 cRED
Gui, Add, Text, x768 y400 w100 vdisplay_charge_onexit,
gui,font,s12 cBlack

Gui, Add, Text, x152 y380 h20 vt_dys,Days Since Entry:

Gui, Add, Text, x280 y381 h20 vE_days  +redraw,000000
Gui, Add, Text, x152 y420 h20 vt_total_hrs,Total Hours Since Entry:

Gui, Add, Text, x327 y421 h20 vE_hours +redraw,000000
Gui, Add, Text, x152 y460 h20 vt_extra_hrs,Extra Hours:

Gui, Add, Text, x245 y460 h20 vEx_hours +redraw,000000


;-----------Read Database Path From The Config File & Use It To Initiate Connection

guicontrol,disable,okie
IniRead,dbpath,Config.ini,Settings,DatabasePath
IfNotExist,%dbpath%
 {
   msgbox,262160,Database Connect Failed,Connection To Database Failed`n`nPossible Causes: `n`n-> Server Maybe Offline`n-> Proper Access Permissions May Not Be Defined`n-> Faulty Cabling`n-> Router or Any Other Connecting Device Is Not Working
   exitapp
  }
iniread,CounterPath,Config.ini,Settings,CounterPath
Gui, Show,h562 w981,Parking Management System Version 1.0 -->Client
Gui,show,maximize
Return





GuiClose:

ExitApp

get:
gui,submit,nohide
if(regnumber=="")
 return
dbx:= DBA.DataBaseFactory.OpenDataBase("SQLite",dbpath)
qry=Select * From Vehicle Where Number = '%regnumber%' AND Status= '1'
foundRow := dbx.QueryRow(qry)
dbx.EndTransaction()
dbx.close()
if(IsObject(foundRow ))
{
  msgbox,262160,Vehicle Already In DB,Vehicle Already In The DataBase!
  Guicontrol,,regnumber,
   return
}


;---------------------Read The Last Generated Coupon From A File & Increment 1 To Generate The Next

iniread,coupon,%counterpath%,info,count
coupon++
Guicontrol,,display_reg,%regnumber%
Guicontrol,,display_date,%A_DD%-%A_MM%-%A_YYYY%
Guicontrol,,display_coupon,BS%coupon%
SB_SetText(dbx.GetLastErrorMsg())
guicontrol,,Type
Guicontrol,enable,okie
return



OKandPrint:
FormatTime, TimeString,%A_Now%,hh:mm:ss tt
dbx:= DBA.DataBaseFactory.OpenDataBase("SQLite",dbpath)
qry=INSERT INTO Vehicle Values ('%regnumber%','BS%coupon%','%type%',date(),'%TimeString%','0','0','0','1','0')
dbx.query(qry)
dbx.EndTransaction()
dbx.close()
msgbox,% dbx.GetLastErrorMsg()

;----------------Write The Currently Generated Coupon Number Into File
iniwrite,%coupon%,%counterpath%,info,count
Guicontrol,,display_reg,
Guicontrol,,display_date,
Guicontrol,,display_coupon,
Guicontrol,,regnumber,
guicontrol,disable,okie





sb_settext(dbx.GetLastErrorMsg())
return


get2:
gui,submit,nohide
if(existing_coupon=="")
 return

main:
dbx:= DBA.DataBaseFactory.OpenDataBase("SQLite",dbpath)
qry=SELECT * FROM Vehicle WHERE CouponNumber='%existing_coupon%'
mainx:
rs:=dbx.OpenRecordSet(qry)
msgbox,% dbx.GetLastErrorMsg()
ex_time:=rs["time"]
ex_date:=rs["date"]
ex_reg:=rs["Number"]
ex_type:=rs["type"]
ex_status:=rs["status"]
if(ex_status==0)
 {
  msgbox,262160,Error!,Vehicle Already Dispatched!
  ;db.Close()
  return
 }


if(ex_time=="")
{
 msgbox,262160,Error!,Invalid Coupon Number!
  SB_SetText("Vehicle Not In Database!!")
  ;db.Close()
 return
}


qry=UPDATE Vehicle SET Status='0' WHERE CouponNumber='%existing_coupon%'
dbx.query(qry)
FormatTime, TimeString,%A_Now%,hh:mm:ss tt
qry=UPDATE Vehicle SET Edate=date(),Etime='%TimeString%' WHERE CouponNumber='%existing_coupon%'
dbx.query(qry)
msgbox,% dbx.GetLastErrorMsg()
SB_SetText(dbx.GetLastErrorMsg())




SB_SetText("Vehicle Record Found")
StringMid,rep1,ex_date,0, 4
StringMid,rep2,ex_date,6, 2
StringMid,rep3,ex_date,9, 2


sourcedate = %rep3%-%rep2%-%rep1% %A_Space% %ex_time%
Guicontrol,,display_reg_onexit,%ex_reg%
Guicontrol,,display_date_onexit,%ex_date% %A_Space% %ex_time%
Guicontrol,,display_type_onexit,%ex_type%
ch:=get_charge(ex_type,sourcedate)
Guicontrol,,display_charge_onexit,%ch%


qry=UPDATE Vehicle SET charges='%ch%' WHERE CouponNumber='%existing_coupon%'
dbx.query(qry)
dbx.EndTransaction()
dbx.close()
;db.Close()
xday := DateParse(sourcedate)

StringTrimRight, result,A_Now, 2
hour:=result
EnvSub, result, %xday%, days
EnvSub, hour, %xday%, hours

y:=mod(hour,24)
Guicontrol,,e_days,%result%
Guicontrol,,e_hours,%hour%
Guicontrol,,ex_hours,%y%



Guicontrol,,e_days,
Guicontrol,,e_hours,
Guicontrol,,ex_hours,
Guicontrol,,display_reg_onexit,
Guicontrol,,display_date_onexit,
Guicontrol,,display_type_onexit,
Guicontrol,,display_charge_onexit,
Guicontrol,,existing_coupon,
return




get_charge(type,inputdate)
{

day := DateParse(inputdate)

StringTrimRight, result,A_Now, 2
hour:=result
EnvSub, result, %day%, days
EnvSub, hour, %day%, hours

y:=mod(hour,24)
sb_settext(y)
if(y>0)
 result:=result+1
if(type=="Type2")
{
if(result >= 1)
 charge:=result*10
else
 if(result<1)
  {
   if(hour<=2)
     charge=2
   if(hour>2 && hour<=4)
 	charge=3
   if(hour>4 && hour<=8)
	charge=4
   if(hour>8 && hour<=16)
     	charge=5
   if(hour>16 && hour<=24)
	charge=7
  }
}

if(type=="Type3")
{

if(result >= 1)
 charge:=result*20
else
 if(result<1)
  {
   if(hour<=2)
     charge=5
   if(hour>2 && hour<=4)
 	charge=8
   if(hour>4 && hour<=8)
	charge=11
   if(hour>8 && hour<=16)
     	charge=15
   if(hour>16 && hour<=24)
	charge=20
  }
}

if(type=="Type4")
{
 if(result >= 1)
 charge:=result*30
else
 if(result<1)
  {
   if(hour<=2)
     charge=10
   if(hour>2 && hour<=4)
 	charge=15
   if(hour>4 && hour<=8)
	charge=20
   if(hour>8 && hour<=16)
     	charge=25
   if(hour>16 && hour<=24)
	charge=30
  }
}

if(type=="Type1")
{
 if(result >= 1)
 charge:=result*6
else
 if(result<1)
  {
   if(hour<=2)
     charge=1
   if(hour>2 && hour<=16)
 	charge=2
   if(hour>16 && hour<=24)
	charge=3
  }
}

return charge
}




MenuHandler:
if(A_ThisMenuItem=="About")
 {
  Gui,2:+toolwindow
  Gui,2: add,Picture,x0 y0,about.jpg
  Gui,2: Show, h355 w784,About This Software
  Return



}
if(A_ThisMenuItem=="Generate Report")
 {
  gui,hide
  runwait,report.exe
  gui,show
}
if(A_ThisMenuItem=="Admin Options")
 {
   InputBox,adminpassword,Enter Password,Enter Password For Admin Options:,HIDE,250,125
   if(adminpassword=="")
    return
    if(adminpassword=="Admin123")
    {
     msgbox,48,Information,There Are No Admin Configurable Options Availabe At This Moment
     return
    }
   else
    msgbox,262160,Error!,Admin Password Incorrect!
   return
}
if(A_ThisMenuItem=="Exit")
 exitapp
return





lost:
InputBox, lost_coupon,Enter Registration,Enter The Registration Number:
if(lost_coupon=="")
 return
stringupper,lost_coupon,lost_coupon
qry=Select * From Vehicle Where Number = '%lost_coupon%' AND Status= '1'
foundRow := dbx.QueryRow(qry)
dbx.EndTransaction()
dbx.close()
if(IsObject(foundRow ))
{
qry=UPDATE Vehicle SET LostCoupon='1' WHERE CouponNumber='%lost_coupon%'
dbx.query(qry)
dbx.EndTransaction()
dbx.close()
goto,mainx
}
else
{
msgbox,262160,Error!,Vehicle Not In Database or Already Dispatched!
dbx.EndTransaction()
dbx.close()
;db.Close()
}
return


GuiSize:
Anchor("type", "w","true")
Anchor("regnumber", "w","true")
Anchor("okie", "x","true")
Anchor("t_grp_vehicle", "w","true")
Anchor("t_grp_details", "w","true")
Anchor("t_cop", "y","true")
Anchor("lost", "x1","true")
Anchor("existing_coupon", "w","true")
Anchor("t_grp_details_db", "w","true")
Anchor("t_grp_chrge", "w","true")
Anchor("get2", "x","true")


Anchor("tab","w")
return

#IfWinActive ahk_class AutoHotkeyGUI
Enter::
NumPadEnter::
Gui,submit,nohide
if(tab=="Exit")
 goto,get2
return

2GuiClose:
Gui,2: destroy
return

The location "D:\Database" is shared with full access & not on an NFS, normal FAT32 filesystem. Both the systems runs on Windows XP SP2, i've tested by running on a windows 7 & XP combination also!
I'm wondering why this happens! It works perfectly 2-3 times before giving out this error! :shock:
Help please! :oops:

[IsNull]

1. You should not create new database connections in each method, especially NOT when using SQLite. Just create one "dbx" and make it global. Close the connection on AppExit.
2.

rs:=dbx.OpenRecordSet(qry)
msgbox,% dbx.GetLastErrorMsg()
ex_time:=rs["time"]
ex_date:=rs["date"]
ex_reg:=rs["Number"]
ex_type:=rs["type"]
ex_status:=rs["status"]

[color=#FF0000]rs.Close()[/color]

if(ex_status==0)
 {
  msgbox,262160,Error!,Vehicle Already Dispatched!
  return
 }

You MUST close any recordset after usage, otherwise the db connection may fall in a locked state.


On a side note:
SQLite does not support multiple Connections on the same DB which can write. You can do it, but you have to catch "lock" errors and retry. This is very nasty.
You should switch to another db which supports multiple write connections. MS SQL, MySQL or even an Access mdb would work. (DBA supports them all)

[Aravind]

Thanks IsNull! I chose SQLite because of its simplicity! Guess it can't be used for my application I'll try MySQL