关于sqlite数据库写入数据数据库锁定问题-『提问交流』-AUTOIT CN

shenrenba 发表于 2010-9-10 11:20:40

关于sqlite数据库写入数据数据库锁定问题

本帖最后由 shenrenba 于 2010-9-12 11:36 编辑

下面是代码请问下原因

#NoTrayIcon
#Region ;**** 参数创建于 ACNWrapper_GUI ****
#AutoIt3Wrapper_Icon=D:\AU3\AU3TOOL.exe_0.ico
#AutoIt3Wrapper_OutFile=D:\AU3\MiniERP.exe
#AutoIt3Wrapper_Res_Description=Cooclk制作
#AutoIt3Wrapper_Run_Obfuscator=y
#EndRegion ;**** 参数创建于 ACNWrapper_GUI ****
AutoItWinSetTitle('MyMiniERP')
#include <GuiListView.au3>
#include <GuiComboBox.au3>
#include <GuiComboBoxEx.au3>
#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <GuiEdit.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <Misc.au3>
#include <Date.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
Opt("WinTitleMatchMode", 2);标题的任意子串皆可匹配
Opt("GUICloseOnESC", 0);在按下 ESC 时不退出

If _Singleton(@ScriptName, 1) = 0 Then
MsgBox(16, "警告", "该程序已经运行，禁止重复运行！", 5)
Exit
EndIf

$sqlpath = @ScriptDir & "\MyMiniERP.db"
$sqllogin = "login"
$sqlname = "company"
$sqlwork = "work"
$scr = 1

_SQLite_Startup()
If Not FileExists($sqlpath) Then
sqlcreate()
EndIf

Local $hQuery, $aRow, $aNames
Local $a, $b, $c, $d, $e, $f, $g, $h, $i, $j, $x, $y, $z

Login()
Func Login()
#Region ### START Koda GUI section ### Form=
$Login = GUICreate("用户登陆", 300, 200, -1, -1)
$LoginGroup = GUICtrlCreateGroup("登陆", 5, 5, 290, 190)
$LoginLabel1 = GUICtrlCreateLabel("用户名", 30, 40, 50, 20)
$LoginLabel2 = GUICtrlCreateLabel("密码", 30, 80, 50, 20)
$LoginCombo1 = GUICtrlCreateCombo("", 120, 40, 130, 20, BitOR($CBS_DROPDOWN, $CBS_AUTOHSCROLL, $CBS_SIMPLE))
showname($LoginCombo1)
$LoginInput1 = GUICtrlCreateInput("", 120, 80, 130, 20, @SW_DISABLE)
_GUICtrlEdit_SetPasswordChar($LoginInput1, "*")
$LoginButton1 = GUICtrlCreateButton("登陆", 30, 130, 70, 40)
$LoginButton2 = GUICtrlCreateButton("取消", 180, 130, 70, 40)
GUICtrlCreateGroup("", -99, -99, 1, 1)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
While 1
$nMg = GUIGetMsg()
Switch $nMg
Case $GUI_EVENT_CLOSE, $LoginButton2
_SQLite_Close()
_SQLite_Shutdown()
GUIDelete($Login)
Exit

Case $LoginButton1
If GUICtrlRead($LoginCombo1) = "" Or GUICtrlRead($LoginInput1) = "" Then
MsgBox(16, "警告", "帐号和密码均不能为空！", 5)
Else
Loginin(GUICtrlRead($LoginCombo1), GUICtrlRead($LoginInput1), $x, $y, $LoginInput1)
EndIf

EndSwitch
WEnd
EndFunc ;==>Login

Func sqlcreate()
_SQLite_Open($sqlpath)
_SQLite_Exec(-1, "Create Table IF NOT Exists " & $sqllogin & " (name TEXT PRIMARY KEY,password TEXT,competence INTEGER,sign INTEGER);")
_SQLite_Exec(-1, "Create Table IF NOT Exists " & $sqlname & " (name TEXT,ID TEXT,company TEXT,contact TEXT,tel TEXT,mobile TEXT,fax TEXT,e_mail TEXT,address TEXT,notes TEXT);")
_SQLite_Exec(-1, "Create Table IF NOT Exists " & $sqlwork & " (client TEXT,data TEXT,category TEXT,itemnumber TEXT,quantity INTEGER,price INTEGER,money INTEGER,paid INTEGER,notpay INTEGER,suppliers TEXT,purchaseprice TEXT,notes TEXT,annex TEXT);")
_SQLite_Exec(-1, "Insert into " & $sqllogin & " (name,password,competence,sign) values ('Cooclk','cooclk','1','0');")
_SQLite_Exec(-1, "Insert into " & $sqllogin & " (name,password,competence,sign) values ('ceshi1','ceshi1','2','0');")
_SQLite_Exec(-1, "Insert into " & $sqllogin & " (name,password,competence,sign) values ('ceshi2','ceshi2','3','0');")
_SQLite_Exec(-1, "Insert into " & $sqllogin & " (name,password,competence,sign) values ('ceshi3','ceshi3','3','1');")
_SQLite_Exec(-1, "Insert into " & $sqlname & " (name,ID,company,contact,tel,mobile,fax,e_mail,address,notes) values ('ceshi2','1','2','3','4','5','6','7','8','9');")
_SQLite_Exec(-1, "Insert into " & $sqlname & " (name,ID,company,contact,tel,mobile,fax,e_mail,address,notes) values ('ceshi2','11','22','33','44','55','66','77','88','99');")
_SQLite_Close()
EndFunc ;==>sqlcreate

Func showname($a)
_SQLite_Open($sqlpath)
_SQLite_Query(-1, "SELECT * FROM " & $sqllogin & " ORDER BY name DESC;", $hQuery)
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
_GUICtrlComboBox_BeginUpdate($a)
_GUICtrlComboBox_AddString($a, $aRow)
_GUICtrlComboBox_EndUpdate($a)
WEnd
_SQLite_Close()
EndFunc ;==>showname

Func Loginin($a, $b, $c, $d, $e)
Global $Login
_SQLite_Open($sqlpath)
_SQLite_Query(-1, "SELECT * FROM " & $sqllogin & " where name ='" & $a & "';", $hQuery)
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
$c = $aRow
$d = $aRow
If $d = 1 Then
MsgBox(16, "警告", "此帐号已禁用！", 5)
GUICtrlSetData($e, "")
ElseIf $b <> $aRow Then
MsgBox(16, "警告", "密码错误！", 5)
GUICtrlSetData($e, "")
Else
GUIDelete($Login)
company($a, $c)
EndIf
WEnd
_SQLite_Close()
EndFunc ;==>Loginin

Func company($a, $c)
#Region ### START Koda GUI section ### Form=
$CForm1 = GUICreate("MyMiniERP-----用户:" & $a, 650, 450, -1, -1, $WS_MINIMIZEBOX + $WS_MAXIMIZEBOX)
$CMenuItem1 = GUICtrlCreateMenu("文件")
$CMenuItem11 = GUICtrlCreateMenuItem("新公司信息录入", $CMenuItem1)
$CMenuItem12 = GUICtrlCreateMenuItem("公司信息修改", $CMenuItem1)
GUICtrlSetState($CMenuItem12, $GUI_DISABLE)
$CMenuItem13 = GUICtrlCreateMenuItem("公司交易录入", $CMenuItem1)
$CMenuItem14 = GUICtrlCreateMenuItem("显示所有公司信息", $CMenuItem1)
GUICtrlSetState($CMenuItem14, $GUI_DISABLE)
$CMenuItem15 = GUICtrlCreateMenuItem("导入", $CMenuItem1)
GUICtrlSetState($CMenuItem15, $GUI_DISABLE)
$CMenuItem16 = GUICtrlCreateMenuItem("导出", $CMenuItem1)
GUICtrlSetState($CMenuItem16, $GUI_DISABLE)
$CMenuItem17 = GUICtrlCreateMenuItem("切换用户", $CMenuItem1)
$CMenuItem18 = GUICtrlCreateMenuItem("退出", $CMenuItem1)
$CMenuItem2 = GUICtrlCreateMenu("编辑")
$CMenuItem21 = GUICtrlCreateMenuItem("刷新", $CMenuItem2)
$CMenuItem22 = GUICtrlCreateMenuItem("删除", $CMenuItem2)
GUICtrlSetState($CMenuItem22, $GUI_DISABLE)
$CMenuItem23 = GUICtrlCreateMenuItem("搜索", $CMenuItem2)
$CMenuItem3 = GUICtrlCreateMenu("用户管理")
$CMenuItem31 = GUICtrlCreateMenuItem("添加新用户", $CMenuItem3)
$CMenuItem32 = GUICtrlCreateMenuItem("用户信息修改", $CMenuItem3)
$CMenuItem33 = GUICtrlCreateMenuItem("用户信息删除", $CMenuItem3)
GUICtrlSetState($CMenuItem3, $GUI_DISABLE)
If $c = "1" Then
GUICtrlSetState($CMenuItem3, $GUI_ENABLE)
GUICtrlSetState($CMenuItem12, $GUI_ENABLE)
GUICtrlSetState($CMenuItem14, $GUI_ENABLE)
GUICtrlSetState($CMenuItem15, $GUI_ENABLE)
GUICtrlSetState($CMenuItem16, $GUI_ENABLE)
GUICtrlSetState($CMenuItem22, $GUI_ENABLE)
ElseIf $c = "2" Then
GUICtrlSetState($CMenuItem12, $GUI_ENABLE)
GUICtrlSetState($CMenuItem22, $GUI_ENABLE)
Else
Sleep(20)
EndIf
$CMenuItem4 = GUICtrlCreateMenu("说明")
$CMenuItem41 = GUICtrlCreateMenuItem("关于", $CMenuItem4)
$CListView1 = GUICtrlCreateListView("", 5, 0, 635, 330, $LVS_LIST + $LVS_SINGLESEL + $LVS_SHOWSELALWAYS + $LVS_EX_GRIDLINES + $LVS_EX_FLATSB)
_GUICtrlListView_AddColumn($CListView1, "公司编号", 100, 0)
_GUICtrlListView_AddColumn($CListView1, "公司名", 100, 0)
_GUICtrlListView_AddColumn($CListView1, "联系人", 100, 0)
_GUICtrlListView_AddColumn($CListView1, "联系电话", 100, 0)
_GUICtrlListView_AddColumn($CListView1, "手机", 100, 0)
_GUICtrlListView_AddColumn($CListView1, "传真号", 100, 0)
_GUICtrlListView_AddColumn($CListView1, "E_mail", 100, 0)
_GUICtrlListView_AddColumn($CListView1, "联系地址", 100, 0)
_GUICtrlListView_AddColumn($CListView1, "备注", 100, 0)
$CGroup1 = GUICtrlCreateGroup("", 5, 330, 635, 45)
$CCombo1 = GUICtrlCreateCombo("", 70, 340, 150, 25, $CBS_DROPDOWN + $CBS_AUTOHSCROLL + $CBS_SIMPLE)
GUICtrlSetData($CCombo1, "公司编号|公司名|联系人", "公司名")
$CInput1 = GUICtrlCreateInput("", 300, 340, 150, 20, @SW_DISABLE)
$CButton1 = GUICtrlCreateButton("查询", 530, 340, 70, 25)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$CLabel1 = GUICtrlCreateLabel("当前系统：" & @OSVersion & " " & @OSServicePack & " " & @OSArch & "位", 5, 385, 250, 25)
GUICtrlSetState(-1, $GUI_DISABLE)
$CLabel2 = GUICtrlCreateLabel("", 255, 385, 200, 25)
GUICtrlSetState(-1, $GUI_DISABLE)
$CLabel3 = GUICtrlCreateLabel("By Cooclk", 505, 385, 100, 25)
GUICtrlSetState(-1, $GUI_DISABLE)
GUISetState()
sqlnameread($a, $CListView1)
#EndRegion ### END Koda GUI section ###

While 1
Global $sj2
$sj1 = _NowCalc()
If $sj1 <> $sj2 Then
$sj2 = $sj1
GUICtrlSetData($CLabel2, "系统时间：" & $sj1)
EndIf
$nMg = GUIGetMsg()
Switch $nMg
Case $GUI_EVENT_CLOSE, $CMenuItem18
_SQLite_Close()
_SQLite_Shutdown()
Exit

Case $CMenuItem21
sqlnameread($a, $CListView1)

Case $CMenuItem17
GUIDelete($CForm1)
Login()

Case $CMenuItem14
sqlnameread("*", $CListView1)

Case $CMenuItem11
GUIDelete($CForm1)
companywrite($a)

EndSwitch
WEnd
EndFunc ;==>company

Func sqlnameread($a, $CListView1)
_GUICtrlListView_DeleteAllItems(GUICtrlGetHandle($CListView1))
_SQLite_Open($sqlpath)
_SQLite_QueryReset($a)
If $a = "*" Then
_SQLite_Query(-1, "SELECT * FROM " & $sqlname & " ORDER BY name;", $hQuery)
Else
_SQLite_Query(-1, "SELECT * FROM " & $sqlname & " where name ='" & $a & "' ORDER BY name;", $hQuery)
EndIf
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
_GUICtrlListView_AddItem($CListView1, $aRow)
_GUICtrlListView_AddSubItem($CListView1, _GUICtrlListView_FindInText($CListView1, $aRow), $aRow, 1)
_GUICtrlListView_AddSubItem($CListView1, _GUICtrlListView_FindInText($CListView1, $aRow), $aRow, 2)
_GUICtrlListView_AddSubItem($CListView1, _GUICtrlListView_FindInText($CListView1, $aRow), $aRow, 3)
_GUICtrlListView_AddSubItem($CListView1, _GUICtrlListView_FindInText($CListView1, $aRow), $aRow, 4)
_GUICtrlListView_AddSubItem($CListView1, _GUICtrlListView_FindInText($CListView1, $aRow), $aRow, 5)
_GUICtrlListView_AddSubItem($CListView1, _GUICtrlListView_FindInText($CListView1, $aRow), $aRow, 6)
_GUICtrlListView_AddSubItem($CListView1, _GUICtrlListView_FindInText($CListView1, $aRow), $aRow, 7)
_GUICtrlListView_AddSubItem($CListView1, _GUICtrlListView_FindInText($CListView1, $aRow), $aRow, 8)
WEnd
_SQLite_Close()
EndFunc ;==>sqlnameread

Func companywrite($a)
#Region ### START Koda GUI section ### Form=
$CWForm1 = GUICreate("MyMiniERP-----用户:" & $a, 650, 400, -1, -1)
$CWGroup1 = GUICtrlCreateGroup("新公司信息录入", 10, 10, 630, 370)
$CWLabel1 = GUICtrlCreateLabel("公司编号", 90, 35, 60, 25)
$CWLabel2 = GUICtrlCreateLabel("公司名称", 90, 65, 60, 25)
$CWLabel3 = GUICtrlCreateLabel("联系人", 90, 95, 60, 25)
$CWLabel4 = GUICtrlCreateLabel("联系电话", 90, 125, 60, 25)
$CWLabel5 = GUICtrlCreateLabel("手机号", 90, 155, 60, 25)
$CWLabel6 = GUICtrlCreateLabel("传真号", 90, 185, 60, 25)
$CWLabel7 = GUICtrlCreateLabel("电子邮箱", 90, 215, 60, 25)
$CWLabel8 = GUICtrlCreateLabel("联系地址", 90, 245, 60, 25)
$CWLabel9 = GUICtrlCreateLabel("备注", 90, 275, 60, 25)
$CWInput1 = GUICtrlCreateInput("", 250, 30, 300, 20, @SW_DISABLE)
$CWInput2 = GUICtrlCreateInput("", 250, 60, 300, 20, @SW_DISABLE)
$CWInput3 = GUICtrlCreateInput("", 250, 90, 300, 20, @SW_DISABLE)
$CWInput4 = GUICtrlCreateInput("", 250, 120, 300, 20, @SW_DISABLE)
$CWInput5 = GUICtrlCreateInput("", 250, 150, 300, 20, @SW_DISABLE)
$CWInput6 = GUICtrlCreateInput("", 250, 180, 300, 20, @SW_DISABLE)
$CWInput7 = GUICtrlCreateInput("", 250, 210, 300, 20, @SW_DISABLE)
$CWInput8 = GUICtrlCreateInput("", 250, 240, 300, 20, @SW_DISABLE)
$CWInput9 = GUICtrlCreateInput("", 250, 270, 300, 20, @SW_DISABLE)
$CWButton1 = GUICtrlCreateButton("确认新增", 50, 315, 145, 45)
$CWButton2 = GUICtrlCreateButton("全部清空", 250, 315, 145, 45)
$CWButton3 = GUICtrlCreateButton("返回", 450, 315, 145, 45)
GUICtrlCreateGroup("", -99, -99, 1, 1)
GUISetState()
#EndRegion ### END Koda GUI sectio
While 1
$nMg = GUIGetMsg()
Switch $nMg
Case $GUI_EVENT_CLOSE, $CWButton3
GUIDelete($CWForm1)
_SQLite_Open($sqlpath)
_SQLite_Query(-1, "SELECT * FROM " & $sqllogin & " where name ='" & $a & "';", $hQuery)
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
$c = $aRow
company($a, $c)
WEnd
_SQLite_Close()

Case $CWButton2
GUICtrlSetData($CWInput1, "")
GUICtrlSetData($CWInput2, "")
GUICtrlSetData($CWInput3, "")
GUICtrlSetData($CWInput4, "")
GUICtrlSetData($CWInput5, "")
GUICtrlSetData($CWInput6, "")
GUICtrlSetData($CWInput7, "")
GUICtrlSetData($CWInput8, "")
GUICtrlSetData($CWInput9, "")

Case $CWButton1
If GUICtrlRead($CWInput1) = "" Or GUICtrlRead($CWInput2) = "" Then
MsgBox(16, "提示", "公司编号和公司名称均不能为空！", 5)
Else
_SQLite_Open($sqlpath)
_SQLite_QuerySingleRow(-1, "SELECT * FROM " & $sqlname & " WHEREcompany = '" & GUICtrlRead($CWInput2) & "';", $aRow)
_SQLite_QuerySingleRow(-1, "SELECT * FROM " & $sqlname & " WHEREID = '" & GUICtrlRead($CWInput1) & "';", $aNames)
$Temp = $aRow
$hQuery = $aNames
If $Temp <> "" And $hQuery="" Then
MsgBox(16, "警告", "公司名中含有" & GUICtrlRead($CWInput2) & "的信息！"&@CRLF&"此客户编号为"&$aRow&"！"&@CRLF&"此客户属于"&$aRow&"！", 5)
ElseIf $hQuery <> "" And $Temp="" Then
MsgBox(16, "警告", "此编号" & GUICtrlRead($CWInput1) & "已使用！"&@CRLF&"使用于"&$aNames&"！"&@CRLF&"此客户属于"&$aNames&"！", 5)
ElseIf $hQuery <> "" And $Temp<>"" Then
MsgBox(16,"警告","编号和公司名均存在！",5)
Else
sqlnamewrite($a, GUICtrlRead($CWInput1), GUICtrlRead($CWInput2), GUICtrlRead($CWInput3), GUICtrlRead($CWInput4), GUICtrlRead($CWInput5), GUICtrlRead($CWInput6), GUICtrlRead($CWInput7), GUICtrlRead($CWInput8), GUICtrlRead($CWInput9))
GUICtrlSetData($CWInput1, "")
GUICtrlSetData($CWInput2, "")
GUICtrlSetData($CWInput3, "")
GUICtrlSetData($CWInput4, "")
GUICtrlSetData($CWInput5, "")
GUICtrlSetData($CWInput6, "")
GUICtrlSetData($CWInput7, "")
GUICtrlSetData($CWInput8, "")
GUICtrlSetData($CWInput9, "")
MsgBox(64, "提示", "写入完成！", 5)
EndIf
_SQLite_Close()
EndIf

EndSwitch
WEnd
EndFunc ;==>companywrite

Func sqlnamewrite($a, $b, $c, $d, $e, $f, $g, $h, $i, $j)
_SQLite_Open($sqlpath)
_SQLite_Exec(-1, "Insert into " & $sqlname & " (name,ID,company,contact,tel,mobile,fax,e_mail,address,notes) values ('" & $a & "','" & $b & "','" & $c & "','" & $d & "','" & $e & "','" & $f & "','" & $g & "','" & $h & "','" & $i & "','" & $j & "');")
_SQLite_Close()
EndFunc ;==>sqlnamewrite

hzxymkb 发表于 2010-9-10 15:03:45

这个要等蛋蛋来帮你了！他比较懂行！

woeiwoei 发表于 2010-9-10 16:36:06

没明白楼主意思。。。锁定？锁定什么呢?

shenrenba 发表于 2010-9-10 17:23:40

回复 3# woeiwoei

图片中有个错误说明 “Error: database is locked”

不是被锁定了吗?

newuser 发表于 2010-9-10 17:33:54

回复 1# shenrenba
可以的话,能否提供手里关于sqlite的相关资料.

netegg 发表于 2010-9-11 18:24:38

本帖最后由 netegg 于 2010-9-11 18:25 编辑

不太清楚你的意思，向sqlite写入数据？还是别的？可能的话把数据库发上来，帮你试试看
另外，有点不明白，就是为什么要多次开数据库？

shenrenba 发表于 2010-9-12 11:35:34

回复 6# netegg

数据库不是有个超时自动断开的功能吗为了防止这所以每次都是打开后就关闭

那个错误提示是录入界面写入的时候就会出错数据库会自动创建的

页: [1]

AUTOIT CN's Archiver

关于sqlite数据库写入数据数据库锁定问题