wolf9228 Posted November 11, 2014 Posted November 11, 2014 (edited) Microsoft Access database Second version MS_AccessDB.zipFetching info... New in this version - ODBC UNICODE Functions - Improvements to the SetBinary function - General improvements to the project functions ODBC is an API that uses Structured Query Language (SQL) as the database access language. You can access a wide variety of database management systems (DBMSs) with the same ODBC source code that is directly incorporated into an application's source code. With the Microsoft ODBC Desktop Database Drivers, a user of an ODBC-enabled application can open, query, and update a desktop database through the ODBC interface. The Microsoft ODBC Desktop Database Drivers are a Microsoft Jet-based set of ODBC drivers. Whereas Microsoft ODBC Desktop Database Drivers 2.0 include both 16-bit and 32-bit drivers, versions 3.0 and later include only 32-bit drivers that work on Windows 95 or later, Windows NT Workstation or Server version 4.0, Windows 2000 Professional, or Windows 2000 Server. These drivers provide access to the following types of data sources: Microsoft Access Microsoft Excel Paradox dBASE Text Microsoft Access Driver Programming Considerationhttp://msdn.microsoft.com/en-us/library/windows/desktop/ms710302%28v=vs.85%29.aspx Microsoft Access Data Typeshttp://msdn.microsoft.com/en-us/library/windows/desktop/ms714540%28v=vs.85%29.aspx Microsoft Access Data Typeshttp://www.w3schools.com/sql/sql_datatypes.asp MS_AccessDB.au3 expandcollapse popup#Include <WinAPI.au3> #include <Math.au3> Global $odbc32 = DllOpen("odbc32.dll") , $StructToString = False , $AUTOCOMMIT = True Func MS_AccessDatabaseCreate($DbFileName = "MSDataBase.mdb",$DshWnd = 0,$OverExFile = False) ; $OverExFile ==> overwrite existing file Local $ODBC_ADD_SYS_DSN = 4 Local $lpszDriver = "Microsoft Access Driver (*.MDB)" & Chr(0) Local $lpszAttributes = "CREATE_DB=" & $DbFileName & " General" & Chr(0) if Not($DshWnd) Then $DshWnd = _WinAPI_GetDesktopWindow() if ($OverExFile) And (FileExists($DbFileName)) Then if Not(FileDelete($DbFileName)) Then Return SetError(1,0,False) EndIf Local $Return = DllCall("odbccp32.dll","BOOL","SQLConfigDataSourceW","HWND",$DshWnd , _ "WORD",$ODBC_ADD_SYS_DSN,"wstr",$lpszDriver,"wstr",$lpszAttributes) if @error Or Not($Return[0]) Then Return SetError(2,0,False) Return SetError(0,0,True) EndFunc Func MS_AccessDatabaseConnect($DbFileName = "MSDataBase.mdb",$DshWnd = 0) Local $SQL_HANDLE_ENV = 1 , $SQL_NULL_HANDLE = 0 , $SQL_ATTR_ODBC_VERSION = 200 Local $SQL_OV_ODBC3 = 3,$Return = 0,$SQL_HANDLE_DBC = 2,$SQL_LOGIN_TIMEOUT = 103 Local $SQL_SUCCESS = 0 , $SQL_SUCCESS_WITH_INFO = 1 , $SQL_DRIVER_NOPROMPT = 0 Local $TIMEOUT = 5 , $hDatabase[2], $SQL_AUTOCOMMIT = 102,$SQL_AUTOCOMMIT_ON = 1 Local $SQL_IS_INTEGER = -6 Local $InConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & $DbFileName if Not($DshWnd) Then $DshWnd = _WinAPI_GetDesktopWindow() Local $Return = DllCall($odbc32,"short","SQLAllocHandle", _ "short",$SQL_HANDLE_ENV,"ptr",$SQL_NULL_HANDLE,"ptr*",0) if @error Or ($Return[0] <> $SQL_SUCCESS And $Return[0] <> $SQL_SUCCESS_WITH_INFO) Then Return SetError(1,0,0) EndIf Local $henv = $Return[3] $Return = DllCall("odbc32.dll","short","SQLSetEnvAttr","ptr",$henv, _ "long",$SQL_ATTR_ODBC_VERSION,"long",$SQL_OV_ODBC3,"long",0) if @error Or ($Return[0] <> $SQL_SUCCESS And $Return[0] <> $SQL_SUCCESS_WITH_INFO) Then DllCall($odbc32,"short","SQLFreeHandle","short",$SQL_HANDLE_ENV,"ptr",$henv) Return SetError(2,0,0) EndIf $Return = DllCall($odbc32,"short","SQLAllocHandle","short",$SQL_HANDLE_DBC,"ptr",$henv,"ptr*",0) if @error Or ($Return[0] <> $SQL_SUCCESS And $Return[0] <> $SQL_SUCCESS_WITH_INFO) Then DllCall($odbc32,"short","SQLFreeHandle","short",$SQL_HANDLE_ENV,"ptr",$henv) Return SetError(3,0,0) EndIf Local $hdbc = $Return[3] $Return = DllCall($odbc32,"short","SQLSetConnectAttr","ptr", _ $hdbc,"long",$SQL_LOGIN_TIMEOUT,"long",$TIMEOUT,"long",$SQL_IS_INTEGER) if @error Or ($Return[0] <> $SQL_SUCCESS And $Return[0] <> $SQL_SUCCESS_WITH_INFO) Then DllCall($odbc32,"short","SQLDisconnect","ptr",$hdbc) DllCall($odbc32,"short","SQLFreeHandle","short",$SQL_HANDLE_DBC,"ptr",$hdbc) DllCall($odbc32,"short","SQLFreeHandle","short",$SQL_HANDLE_ENV,"ptr",$henv) Return SetError(4,0,0) EndIf if ($AUTOCOMMIT) Then $Return = DllCall($odbc32,"short","SQLSetConnectAttr","ptr", _ $hdbc,"long",$SQL_AUTOCOMMIT,"long",$SQL_AUTOCOMMIT_ON,"long",$SQL_IS_INTEGER) if @error Or ($Return[0] <> $SQL_SUCCESS And $Return[0] <> $SQL_SUCCESS_WITH_INFO) Then DllCall($odbc32,"short","SQLDisconnect","ptr",$hdbc) DllCall($odbc32,"short","SQLFreeHandle","short",$SQL_HANDLE_DBC,"ptr",$hdbc) DllCall($odbc32,"short","SQLFreeHandle","short",$SQL_HANDLE_ENV,"ptr",$henv) Return SetError(5,0,0) EndIf EndIf $Return = DllCall($odbc32,"short","SQLDriverConnectW", _ "ptr",$hdbc ,"HWND",$DshWnd,"wstr",$InConnectionString, _ "short",-3,"ptr",0,"short",0,"short*",0,"short",$SQL_DRIVER_NOPROMPT) if @error Or ($Return[0] <> $SQL_SUCCESS And $Return[0] <> $SQL_SUCCESS_WITH_INFO) Then DllCall($odbc32,"short","SQLDisconnect","ptr",$hdbc) DllCall($odbc32,"short","SQLFreeHandle","short",$SQL_HANDLE_DBC,"ptr",$hdbc) DllCall($odbc32,"short","SQLFreeHandle","short",$SQL_HANDLE_ENV,"ptr",$henv) Return SetError(6,0,0) EndIf $hDatabase = DllStructCreate("ptr hdbc;ptr henv") DllStructSetData($hDatabase,"hdbc",$hdbc) DllStructSetData($hDatabase,"henv",$henv) Return SetError(0,0,$hDatabase) EndFunc Func MS_AccessDatabaseDisconnect($hDatabase) Local $SQL_HANDLE_ENV = 1,$SQL_HANDLE_DBC = 2 Local $hdbc = DllStructGetData($hDatabase,"hdbc") Local $henv = DllStructGetData($hDatabase,"henv") SQLDisconnect($hdbc) if @error Then Return SetError(1,0,False) SQLFreeHandle($SQL_HANDLE_DBC,$hdbc) if @error Then Return SetError(2,0,False) SQLFreeHandle($SQL_HANDLE_ENV,$henv) if @error Then Return SetError(3,0,False) Return SetError(0,0,True) EndFunc Func CreateTable($hDatabase,$TableName,$ColumnsListNameTypeSize) Local $SQL_HANDLE_STMT = 3 Local $StatementText = "CREATE TABLE " & $TableName & " (" & $ColumnsListNameTypeSize & ")" Local $hStatement = GetStmtHandle($hDatabase) if @error Then Return SetError(1,0,False) SQLExecDirect($hStatement,$StatementText) if @error Then SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) Return SetError(2,0,False) EndIf SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) Return SetError(0,0,True) EndFunc Func InsertInToTable($hDatabase,$TableName,$ColumnsList,$ValueList) Local $SQL_HANDLE_STMT = 3 Local $StatementText = "INSERT INTO " & $TableName & " (" & $ColumnsList & _ ") VALUES(" & $ValueList & ")" Local $hStatement = GetStmtHandle($hDatabase) if @error Then Return SetError(1,0,False) SQLExecDirect($hStatement,$StatementText) if @error Then SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) Return SetError(2,0,False) EndIf SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) Return SetError(0,0,True) EndFunc Func ColumnsAdd($hDatabase,$TableName,$ColumnsListNameTypeSize) Local $SQL_HANDLE_STMT = 3 Local $StatementText = "ALTER TABLE " & $TableName & _ " ADD " & $ColumnsListNameTypeSize Local $hStatement = GetStmtHandle($hDatabase) if @error Then Return SetError(1,0,False) SQLExecDirect($hStatement,$StatementText) if @error Then SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) Return SetError(2,0,False) EndIf SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) Return SetError(0,0,True) EndFunc Func UpdateTable($hDatabase,$TableList,$ColumnsList,$OperationsList) Local $SQL_HANDLE_STMT = 3 Local $StatementText = "UPDATE " & $TableList & " SET " & _ $ColumnsList & " WHERE " & $OperationsList Local $hStatement = GetStmtHandle($hDatabase) if @error Then Return SetError(1,0,False) SQLExecDirect($hStatement,$StatementText) if @error Then SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) Return SetError(2,0,False) EndIf SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) Return SetError(0,0,True) EndFunc Func DeleteFromTable($hDatabase,$TableName,$OperationsList) Local $SQL_HANDLE_STMT = 3 Local $StatementText = "DELETE FROM " & _ $TableName & " WHERE " & $OperationsList Local $hStatement = GetStmtHandle($hDatabase) if @error Then Return SetError(1,0,False) SQLExecDirect($hStatement,$StatementText) if @error Then SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) Return SetError(2,0,False) EndIf SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) Return SetError(0,0,True) EndFunc Func TableRemove($hDatabase,$TableName) Local $SQL_HANDLE_STMT = 3 Local $StatementText = "DROP TABLE " & $TableName Local $hStatement = GetStmtHandle($hDatabase) if @error Then Return SetError(1,0,False) SQLExecDirect($hStatement,$StatementText) if @error Then SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) Return SetError(2,0,False) EndIf SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) Return SetError(0,0,True) EndFunc Func SelectRecordsGroup($hDatabase,$TableName,$ColumnsList = "",$OperationList = "") ;$ColumnsList Can be empty string "" ;$OperationList Can be empty string "" Local $SQL_HANDLE_STMT = 3 , $SQL_Error = -1 , $SQL_INTEGER = 4 Local $CountStatText = "SELECT COUNT(*) FROM " & $TableName Local $StatementText = "SELECT " if StringLen($ColumnsList) Then $StatementText &= $ColumnsList & " FROM " & $TableName Else $StatementText &= "* FROM " & $TableName EndIf if StringLen($OperationList) Then $StatementText &= " WHERE " & $OperationList $CountStatText &= " WHERE " & $OperationList EndIf Local $hStatement = GetStmtHandle($hDatabase) if @error Then Return SetError(1,0,0) SQLExecDirect($hStatement,$CountStatText) if @error Then SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) Return SetError(2,0,0) EndIf SQLFetch($hStatement) if @error Then SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) Return SetError(3,0,0) EndIf Local $DataStruct = DllStructCreate("INT") , $Col_Num = 1 SQLGetData($hStatement,$Col_Num,$SQL_INTEGER,$DataStruct) if @error Or @extended = $SQL_Error Then SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) Return SetError(4,0,0) EndIf Local $RowsCount = DllStructGetData($DataStruct,1) SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) $hStatement = GetStmtHandle($hDatabase) if @error Then Return SetError(5,0,0) SQLExecDirect($hStatement,$StatementText) if @error Then SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) Return SetError(6,0,0) EndIf Local $ColsCount = SQLGetColsCount($hStatement) if @error Then SQLFreeHandle($SQL_HANDLE_STMT,$hStatement) Return SetError(7,0,0) EndIf Local $ihStatement = 0, $tagSt = "" $tagSt = "PTR HST;INT RSC;INT CSC;WCHAR STT[" & (StringLen($StatementText) + 1) & "]" $ihStatement = DllStructCreate($tagSt) DllStructSetData($ihStatement,"HST",$hStatement) DllStructSetData($ihStatement,"RSC",$RowsCount) DllStructSetData($ihStatement,"CSC",$ColsCount) DllStructSetData($ihStatement,"STT",$StatementText) Return SetError(0,0,$ihStatement) EndFunc Func GetRecordsData(ByRef $hStatement,$hStatementFree = True) Local $SQL_TYPE_DATE = 91 , $SQL_TYPE_TIME = 92 , $SQL_TYPE_TIMESTAMP = 93 Local $SQL_BIT = -7,$SQL_WVARCHAR = -9,$SQL_WLONGVARCHAR = -10,$SQL_WCHAR = -8 Local $SQL_VARBINARY = -3 , $SQL_REAL = 7 , $SQL_GUID = -11 , $SQL_INTEGER = 4 Local $SQL_TINYINT = -6 , $SQL_DOUBLE = 8 , $Row_Num = 4 , $SQL_SMALLINT = 5 Local $SQL_LONGVARBINARY = -4 , $SQL_NUMERIC = 2 , $SQL_HANDLE_STMT = 3 Local $DatType , $ColSize , $ColName , $SQL_BINARY = -2 , $SQL_Error = -1 Local $HandleSta = DllStructGetData($hStatement,"HST") Local $RowsCount = DllStructGetData($hStatement,"RSC") Local $ColsCount = DllStructGetData($hStatement,"CSC") Local $StateText = DllStructGetData($hStatement,"STT") if Not($RowsCount) Or Not($ColsCount) Then Return SetError(1,0,0) Local $ReturnArray[$RowsCount + 3][$ColsCount] While(SQLFetch($HandleSta)) For $Col_Num = 1 To $ColsCount Local $DescribeArray = SQLDescribeCol($HandleSta,$Col_Num) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(2,0,0) EndIf $ColName = $DescribeArray[0] $ColSize = $DescribeArray[1] $DatType = $DescribeArray[2] if ($Row_Num = 4) Then $ReturnArray[0][$Col_Num - 1] = $ColName $ReturnArray[1][$Col_Num - 1] = $ColSize $ReturnArray[2][$Col_Num - 1] = $DatType EndIf Local $VALUES = "",$GetDataerror = 0 Switch $DatType Case $SQL_WCHAR , $SQL_WLONGVARCHAR , $SQL_WVARCHAR SQLGetData($HandleSta,$Col_Num,$SQL_WCHAR,0,True) ; $GetSize = True if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(3,0,0) EndIf if @extended <> $SQL_Error Then Local $Length = @extended Local $DataStruct = DllStructCreate("WCHAR[" & ($Length + 1) & "]") SQLGetData($HandleSta,$Col_Num,$SQL_WCHAR,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(4,0,0) Else $VALUES = DllStructGetData($DataStruct,1) EndIf Else $VALUES = "" ;NULL VALUE EndIf Case $SQL_BINARY , $SQL_VARBINARY , $SQL_LONGVARBINARY SQLGetData($HandleSta,$Col_Num,$SQL_BINARY,0,True) ; $GetSize = True if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(5,0,0) EndIf if @extended <> $SQL_Error Then Local $Length = @extended Local $DataStruct = DllStructCreate("BYTE[" & $Length & "]") SQLGetData($HandleSta,$Col_Num,$SQL_BINARY,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(6,0,0) Else $VALUES = $DataStruct EndIf Else $VALUES = "" ;NULL VALUE EndIf Case $SQL_TYPE_DATE if ($StructToString) Then Local $DataStruct = DllStructCreate("WCHAR[" & ($ColSize + 1) & "]") SQLGetData($HandleSta,$Col_Num,$SQL_WCHAR,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(7,0,0) Else $GetDataerror = @extended $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Else Local $DataStruct = DllStructCreate("SHORT year;SHORT month;SHORT day") SQLGetData($HandleSta,$Col_Num,$SQL_TYPE_DATE,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(8,0,0) Else $GetDataerror = @extended $VALUES = $DataStruct if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf EndIf Case $SQL_TYPE_TIME if ($StructToString) Then Local $DataStruct = DllStructCreate("WCHAR[" & ($ColSize + 1) & "]") SQLGetData($HandleSta,$Col_Num,$SQL_WCHAR,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(9,0,0) Else $GetDataerror = @extended $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Else $DataStruct = DllStructCreate("SHORT hour;SHORT minute;SHORT second") SQLGetData($HandleSta,$Col_Num,$SQL_TYPE_TIME,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(10,0,0) Else $GetDataerror = @extended $VALUES = $DataStruct if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf EndIf Case $SQL_TYPE_TIMESTAMP if ($StructToString) Then Local $DataStruct = DllStructCreate("WCHAR[" & ($ColSize + 1) & "]") SQLGetData($HandleSta,$Col_Num,$SQL_WCHAR,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(11,0,0) Else $GetDataerror = @extended $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Else Local $DataStruct = DllStructCreate("SHORT year;SHORT month;" & _ "SHORT day;SHORT hour;SHORT minute;SHORT second") if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(12,0,0) Else $GetDataerror = @extended $VALUES = $DataStruct if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf EndIf Case $SQL_GUID if ($StructToString) Then Local $DataStruct = DllStructCreate("WCHAR[" & ($ColSize + 1) & "]") SQLGetData($HandleSta,$Col_Num,$SQL_WCHAR,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(13,0,0) Else $GetDataerror = @extended $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Else Local $DataStruct = DllStructCreate("ulong Data1;" & _ "ushort Data2;ushort Data3;byte Data4[8]") $VALUES = $DataStruct SQLGetData($HandleSta,$Col_Num,$SQL_GUID,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(14,0,0) Else $GetDataerror = @extended $VALUES = $DataStruct if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf EndIf Case $SQL_TINYINT , $SQL_BIT Local $DataStruct = DllStructCreate("BYTE") SQLGetData($HandleSta,$Col_Num,$SQL_TINYINT,$DataStruct) $GetDataerror = @extended if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(15,0,0) Else $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Case $SQL_INTEGER Local $DataStruct = DllStructCreate("INT") SQLGetData($HandleSta,$Col_Num,$SQL_INTEGER,$DataStruct) $GetDataerror = @extended if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(16,0,0) Else $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Case $SQL_REAL Local $DataStruct = DllStructCreate("float") SQLGetData($HandleSta,$Col_Num,$SQL_REAL,$DataStruct) $GetDataerror = @extended if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(17,0,0) Else $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Case $SQL_DOUBLE,$SQL_NUMERIC Local $DataStruct = DllStructCreate("DOUBLE") SQLGetData($HandleSta,$Col_Num,$SQL_DOUBLE,$DataStruct) $GetDataerror = @extended if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(18,0,0) Else $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Case $SQL_SMALLINT Local $DataStruct = DllStructCreate("SHORT") SQLGetData($HandleSta,$Col_Num,$SQL_SMALLINT,$DataStruct) $GetDataerror = @extended if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(19,0,0) Else $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Case Else Return SetError(20,0,0) EndSwitch $ReturnArray[$Row_Num - 1][$Col_Num - 1] = $VALUES Next $Row_Num += 1 WEnd if ($hStatementFree) Then SQLFreeHandle($SQL_HANDLE_STMT,$HandleSta) $hStatement = 0 Else SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) EndIf ;$ReturnArray[0][Column Number] = Column Name ;$ReturnArray[1][Column Number] = Column Max Size ;$ReturnArray[2][Column Number] = Column Data Type Return SetError(0,0,$ReturnArray) EndFunc Func EnumRecordsData(ByRef $hStatement,$EnumRecDataProc,$hStatementFree = True) ;---------------------EnumRecDataProc---------------------------------------------------------- ;Func EnumRecDataProc($IsNullValue,$Value,$ColName,$ColFormat,$ColMaxSize,$RowNum,$ColNum) ;return ;True ;Continue ;False ; Stop ; ;return true; ;EndFunc ;---------------------EnumRecDataProc---------------------------------------------------------- Local $SQL_TYPE_DATE = 91 , $SQL_TYPE_TIME = 92 , $SQL_TYPE_TIMESTAMP = 93 Local $SQL_BIT = -7,$SQL_WVARCHAR = -9,$SQL_WLONGVARCHAR = -10,$SQL_WCHAR = -8 Local $SQL_VARBINARY = -3 , $SQL_REAL = 7 , $SQL_GUID = -11 , $SQL_INTEGER = 4 Local $SQL_TINYINT = -6 , $SQL_DOUBLE = 8 , $Row_Num = 4 , $SQL_SMALLINT = 5 Local $SQL_LONGVARBINARY = -4 , $SQL_NUMERIC = 2 , $SQL_HANDLE_STMT = 3 Local $DatType , $ColSize , $ColName , $SQL_BINARY = -2 , $SQL_Error = -1 Local $iReg = DllCallbackRegister($EnumRecDataProc,"int","int;int;int;int;int;int;int") if @error Then Return SetError(1,0,False) DllCallbackFree($iReg) Local $HandleSta = DllStructGetData($hStatement,"HST") Local $RowsCount = DllStructGetData($hStatement,"RSC") Local $ColsCount = DllStructGetData($hStatement,"CSC") Local $StateText = DllStructGetData($hStatement,"STT") if Not($RowsCount) Or Not($ColsCount) Then Return SetError(2,0,False) While(SQLFetch($HandleSta)) For $Col_Num = 1 To $ColsCount Local $DescribeArray = SQLDescribeCol($HandleSta,$Col_Num) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(3,0,0) EndIf $ColName = $DescribeArray[0] $ColSize = $DescribeArray[1] $DatType = $DescribeArray[2] Local $VALUES = "",$GetDataerror = 0 Switch $DatType Case $SQL_WCHAR , $SQL_WLONGVARCHAR , $SQL_WVARCHAR SQLGetData($HandleSta,$Col_Num,$SQL_WCHAR,0,True) ; $GetSize = True if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(4,0,0) EndIf if @extended <> $SQL_Error Then Local $Length = @extended Local $DataStruct = DllStructCreate("WCHAR[" & ($Length + 1) & "]") SQLGetData($HandleSta,$Col_Num,$SQL_WCHAR,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(5,0,0) Else $VALUES = DllStructGetData($DataStruct,1) EndIf Else $VALUES = "" ;NULL VALUE EndIf Case $SQL_BINARY , $SQL_VARBINARY , $SQL_LONGVARBINARY SQLGetData($HandleSta,$Col_Num,$SQL_BINARY,0,True) ; $GetSize = True if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(6,0,0) EndIf if @extended <> $SQL_Error Then Local $Length = @extended Local $DataStruct = DllStructCreate("BYTE[" & $Length & "]") SQLGetData($HandleSta,$Col_Num,$SQL_BINARY,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(7,0,0) Else $VALUES = $DataStruct EndIf Else $VALUES = "" ;NULL VALUE EndIf Case $SQL_TYPE_DATE if ($StructToString) Then Local $DataStruct = DllStructCreate("WCHAR[" & ($ColSize + 1) & "]") SQLGetData($HandleSta,$Col_Num,$SQL_WCHAR,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(8,0,0) Else $GetDataerror = @extended $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Else Local $DataStruct = DllStructCreate("SHORT year;SHORT month;SHORT day") SQLGetData($HandleSta,$Col_Num,$SQL_TYPE_DATE,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(9,0,0) Else $GetDataerror = @extended $VALUES = $DataStruct if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf EndIf Case $SQL_TYPE_TIME if ($StructToString) Then Local $DataStruct = DllStructCreate("WCHAR[" & ($ColSize + 1) & "]") SQLGetData($HandleSta,$Col_Num,$SQL_WCHAR,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(10,0,0) Else $GetDataerror = @extended $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Else $DataStruct = DllStructCreate("SHORT hour;SHORT minute;SHORT second") SQLGetData($HandleSta,$Col_Num,$SQL_TYPE_TIME,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(11,0,0) Else $GetDataerror = @extended $VALUES = $DataStruct if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf EndIf Case $SQL_TYPE_TIMESTAMP if ($StructToString) Then Local $DataStruct = DllStructCreate("WCHAR[" & ($ColSize + 1) & "]") SQLGetData($HandleSta,$Col_Num,$SQL_WCHAR,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(12,0,0) Else $GetDataerror = @extended $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Else Local $DataStruct = DllStructCreate("SHORT year;SHORT month;" & _ "SHORT day;SHORT hour;SHORT minute;SHORT second") if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(13,0,0) Else $GetDataerror = @extended $VALUES = $DataStruct if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf EndIf Case $SQL_GUID if ($StructToString) Then Local $DataStruct = DllStructCreate("WCHAR[" & ($ColSize + 1) & "]") SQLGetData($HandleSta,$Col_Num,$SQL_WCHAR,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(14,0,0) Else $GetDataerror = @extended $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Else Local $DataStruct = DllStructCreate("ulong Data1;" & _ "ushort Data2;ushort Data3;byte Data4[8]") $VALUES = $DataStruct SQLGetData($HandleSta,$Col_Num,$SQL_GUID,$DataStruct) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(15,0,0) Else $GetDataerror = @extended $VALUES = $DataStruct if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf EndIf Case $SQL_TINYINT , $SQL_BIT Local $DataStruct = DllStructCreate("BYTE") SQLGetData($HandleSta,$Col_Num,$SQL_TINYINT,$DataStruct) $GetDataerror = @extended if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(16,0,0) Else $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Case $SQL_INTEGER Local $DataStruct = DllStructCreate("INT") SQLGetData($HandleSta,$Col_Num,$SQL_INTEGER,$DataStruct) $GetDataerror = @extended if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(17,0,0) Else $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Case $SQL_REAL Local $DataStruct = DllStructCreate("float") SQLGetData($HandleSta,$Col_Num,$SQL_REAL,$DataStruct) $GetDataerror = @extended if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(18,0,0) Else $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Case $SQL_DOUBLE,$SQL_NUMERIC Local $DataStruct = DllStructCreate("DOUBLE") SQLGetData($HandleSta,$Col_Num,$SQL_DOUBLE,$DataStruct) $GetDataerror = @extended if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(19,0,0) Else $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Case $SQL_SMALLINT Local $DataStruct = DllStructCreate("SHORT") SQLGetData($HandleSta,$Col_Num,$SQL_SMALLINT,$DataStruct) $GetDataerror = @extended if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(20,0,0) Else $VALUES = DllStructGetData($DataStruct,1) if ($GetDataerror = $SQL_Error) Then $VALUES = "" ;NULL VALUE EndIf Case Else Return SetError(21,0,0) EndSwitch Local $IsNullValue = ($GetDataerror == $SQL_Error) Local $Return = Call($EnumRecDataProc,$IsNullValue , _ $VALUES,$ColName,$DatType,$ColSize,$Row_Num - 3,$Col_Num) if Not($Return) Then if ($hStatementFree) Then SQLFreeHandle($SQL_HANDLE_STMT,$HandleSta) $hStatement = 0 Else SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) EndIf Return SetError(0,0,True) EndIf Next $Row_Num += 1 WEnd if ($hStatementFree) Then SQLFreeHandle($SQL_HANDLE_STMT,$HandleSta) $hStatement = 0 Else SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) EndIf Return SetError(0,0,True) EndFunc Func SaveStreamFile($hDatabase,$TableName,$ColumnName,$PrimaryKeyName,$PrimaryKeyValue,$FilePath) Local $nBytes Local $ByteStruct = GetBinary($hDatabase,$TableName,$ColumnName,$PrimaryKeyName,$PrimaryKeyValue) if @error Then Return SetError(1,0,False) Local $hFile = _WinAPI_CreateFile($FilePath,1) if Not($hFile) Then Return SetError(2,0,False) Local $Return = _WinAPI_WriteFile($hFile,DllStructGetPtr($ByteStruct),DllStructGetSize($ByteStruct),$nBytes) if Not($Return) Then _WinAPI_CloseHandle($hFile) Return SetError(3,0,False) EndIf _WinAPI_CloseHandle($hFile) Return SetError(0,0,True) EndFunc Func SetStreamFile($hDatabase,$TableName,$ColumnName,$PrimaryKeyName,$PrimaryKeyValue,$FilePath) Local $nBytes Local $FileSize = FileGetSize($FilePath) if @error Then Return SetError(1,0,False) Local $hFile = _WinAPI_CreateFile($FilePath,2,2) if Not($hFile) Then Return SetError(2,0,False) Local $DataStruct = DllStructCreate("BYTE[" & $FileSize & "]") Local $Return = _WinAPI_ReadFile($hFile,DllStructGetPtr($DataStruct),$FileSize,$nBytes) if Not($Return) Then _WinAPI_CloseHandle($hFile) Return SetError(3,0,False) EndIf _WinAPI_CloseHandle($hFile) SetBinary($hDatabase,$TableName,$ColumnName,$PrimaryKeyName,$PrimaryKeyValue,$DataStruct) if @error Then Return SetError(4,0,False) Return SetError(0,0,True) EndFunc Func GetBinary($hDatabase,$TableName,$ColumnName,$PrimaryKeyName,$PrimaryKeyValue) Local $OpList = $PrimaryKeyName & " = " & $PrimaryKeyValue , $nBytes Local $SQL_BINARY = -2 , $SQL_VARBINARY = -3 , $SQL_LONGVARBINARY = -4 Local $hStatement = SelectRecordsGroup($hDatabase,$TableName,$ColumnName,$OpList) if @error Then Return SetError(1,0,0) Local $ReturnArray = GetRecordsData($hStatement) if @error Then Return SetError(2,0,0) Switch $ReturnArray[2][0] Case $SQL_BINARY,$SQL_VARBINARY,$SQL_LONGVARBINARY Local $ByteStruct = $ReturnArray[3][0] Return SetError(0,0,$ByteStruct) Case Else Return SetError(3,0,0) EndSwitch EndFunc Func SetBinary($hDatabase,$TableName,$ColumnName,$PrimaryKeyName,$PrimaryKeyValue,$DataStruct) Local $SQL_VARBINARY = -3 , $SQL_LONGVARBINARY = -4, $SQL_BINARY = -2 , $ColNu = 1 Local $BinarySize = DllStructGetSize($DataStruct),$ParamNu = 1,$SQL_PARAM_INPUT = 1 Local $SQL_C_BINARY = -2 , $SQL_HANDLE_STMT = 3 , $SizeAtOneTime = 1048576 Local $SQL_NEED_DATA = 99 ,$ByteData = DllStructGetPtr($DataStruct) , $InSize = 0 ;SizeAtOneTime = 1048576 bytes // 1 MB Local $hStatement = SelectRecordsGroup($hDatabase,$TableName,$ColumnName) if @error Then Return SetError(1,0,False) EndIf Local $HandleSta = DllStructGetData($hStatement,"HST") Local $DescribeArray = SQLDescribeCol($HandleSta,$ColNu) if @error Then SQLFreeHandle($SQL_HANDLE_STMT,$HandleSta) Return SetError(2,0,False) EndIf SQLFreeHandle($SQL_HANDLE_STMT,$HandleSta) Local $ColName = $DescribeArray[0] Local $ColSize = $DescribeArray[1] Local $DatType = $DescribeArray[2] Local $pBinary = ($DatType == $SQL_BINARY Or $DatType == $SQL_LONGVARBINARY Or $DatType == $SQL_VARBINARY) If Not($pBinary) Then Return SetError(3,0,False) Local $HandleSta = GetStmtHandle($hDatabase) if @error Then Return SetError(4,0,False) Local $LenAtExec = SQL_LEN_DATA_AT_EXEC($BinarySize) Local $ParameterValuePtrSt = DllStructCreate("INT") DllStructSetData($ParameterValuePtrSt,1,1) Local $LenAtExecSt = DllStructCreate("INT") DllStructSetData($LenAtExecSt,1,$LenAtExec) Local $Return = DllCall($odbc32,"short","SQLBindParameter","ptr",$HandleSta,"USHORT",$ParamNu, _ "short",$SQL_PARAM_INPUT,"short",$SQL_C_BINARY,"short",$DatType,"LONG" , $BinarySize , "short" , _ 0,"ptr" , DllStructGetPtr($ParameterValuePtrSt) ,"LONG" ,0,"ptr",DllStructGetPtr($LenAtExecSt)) if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then SQLFreeHandle($SQL_HANDLE_STMT,$HandleSta) Return SetError(5,0,0) EndIf Local $StatementText = "UPDATE " & $TableName & " SET " & $ColumnName & " = ? WHERE " & _ $PrimaryKeyName & " = " & $PrimaryKeyValue Local $Return = DllCall($odbc32,"short","SQLExecDirectW","ptr",$HandleSta, _ "wstr",$StatementText,"long",StringLen($StatementText)) if @error Or $Return[0] <> $SQL_NEED_DATA Then SQLFreeHandle($SQL_HANDLE_STMT,$HandleSta) Return SetError(6,0,False) EndIf Local $ValuePtrPtrSt = DllStructCreate("PTR") $Return = DllCall($odbc32,"short","SQLParamData","ptr",$HandleSta,"ptr",DllStructGetPtr($ValuePtrPtrSt)) if @error Or $Return[0] <> $SQL_NEED_DATA Then SQLFreeHandle($SQL_HANDLE_STMT,$HandleSta) Return SetError(7,0,False) EndIf While (($BinarySize > 0)) $InSize = _Min($SizeAtOneTime,$BinarySize) $Return = DllCall($odbc32,"short","SQLPutData","ptr",$HandleSta,"ptr",$ByteData,"LONG",$InSize) if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then SQLFreeHandle($SQL_HANDLE_STMT,$HandleSta) Return SetError(8,0,False) EndIf $BinarySize -= $InSize WEnd $Return = DllCall($odbc32,"short","SQLParamData","ptr",$HandleSta,"ptr",DllStructGetPtr($ValuePtrPtrSt)) if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then SQLFreeHandle($SQL_HANDLE_STMT,$HandleSta) Return SetError(9,0,False) EndIf SQLFreeHandle($SQL_HANDLE_STMT,$HandleSta) Return SetError(0,0,True) EndFunc Func SQLDisconnect($ConnectionHandle) Local $Return = DllCall($odbc32,"short","SQLDisconnect","ptr",$ConnectionHandle) if @error Or ($Return[0] <> 0) Then Return SetError(1,0,False) Return SetError(0,0,True) EndFunc Func SQLExecDirect($StatementHandle,$StatementText) Local $TextLength = StringLen($StatementText) Local $Return = DllCall($odbc32,"short","SQLExecDirectW","ptr",$StatementHandle, _ "wstr",$StatementText,"long",$TextLength) if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then Return SetError(1,0,False) Return SetError(0,0,True) EndFunc Func SQLFreeStmt($StatementHandle,$Option) Local $Return = DllCall($odbc32,"short","SQLFreeStmt","ptr",$StatementHandle,"ushort",$Option) if @error Or ($Return[0] <> 0) Then Return SetError(1,0,False) Return SetError(0,0,True) EndFunc Func SQLFreeHandle($HandleType,$Handle) Local $Return = DllCall($odbc32,"short","SQLFreeHandle","short",$HandleType,"ptr",$Handle) if @error Or ($Return[0] <> 0) Then Return SetError(1,0,False) Return SetError(0,0,True) EndFunc Func GetStmtHandle($hDatabase) Local $SQL_HANDLE_STMT = 3 Local $hdbc = DllStructGetData($hDatabase,"hdbc") Local $Return = DllCall($odbc32,"short","SQLAllocHandle","short",$SQL_HANDLE_STMT,"ptr",$hdbc,"ptr*",0) if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then Return SetError(1,0,0) Local $hStatement = $Return[3] Return SetError(0,0,$hStatement) EndFunc Func SQLFetch($StatementHandle) Local $Return = DllCall($odbc32,"short","SQLFetch","ptr",$StatementHandle) if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then Return SetError(1,0,False) Return SetError(0,$Return[0],True) EndFunc Func SQLDescribeCol($StatementHandle,$ColumnNumber) Local $Return = DllCall($odbc32,"short","SQLDescribeColW","ptr",$StatementHandle _ ,"short",$ColumnNumber,"wstr*",0,"short",0,"short*",0,"short*",0,"ULONG*",0, _ "short*",0,"short*",0) if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then Return SetError(1,0,0) Local $NameLengthPtr = $Return[5] Local $ColumnNameSt = DllStructCreate("WCHAR[" & ($NameLengthPtr + 1) & "]") $Return = DllCall($odbc32,"short","SQLDescribeColW","ptr",$StatementHandle,"short", _ $ColumnNumber,"struct*",$ColumnNameSt,"short",($NameLengthPtr * 2),"ptr",0,"short*" _ ,0,"ULONG*",0,"short*",0,"short*",0) if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then Return SetError(2,0,0) Local $DescribeArray[3] $DescribeArray[0] = DllStructGetData($ColumnNameSt,1) $DescribeArray[1] = $Return[7] $DescribeArray[2] = $Return[6] Return SetError(0,0,$DescribeArray) EndFunc Func SQLGetData($StatementHandle,$Col_Num,$TargetType,$DataStruct,$GetSize = False) if ($GetSize) Then $Return = DllCall($odbc32,"short","SQLGetData","ptr",$StatementHandle _ ,"USHORT",$Col_Num,"short",$TargetType,"ptr",-1,"ULONG",0,"ULONG*",0) Else $Return = DllCall($odbc32,"short","SQLGetData","ptr",$StatementHandle _ ,"USHORT",$Col_Num,"short",$TargetType,"struct*",$DataStruct,"ULONG", _ DllStructGetSize($DataStruct),"ULONG*",0) EndIf if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then Return SetError(1,0,False) Return SetError(0,$Return[6],True) EndFunc Func SQLCOMMIT($hDatabase) Local $hdbc = DllStructGetData($hDatabase,"hdbc") , $SQL_HANDLE_DBC = 2,$SQL_COMMIT = 0 Local $Return = DllCall($odbc32,"short","SQLEndTran","short",$SQL_HANDLE_DBC _ ,"ptr",$hdbc,"short",$SQL_COMMIT) if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then Return SetError(1,0,False) Return SetError(0,0,True) EndFunc Func SQLGetColsCount($StatementHandle) Local $Return = DllCall($odbc32,"short","SQLNumResultCols","ptr",$StatementHandle,"short*",0) if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then Return SetError(1,0,0) Return SetError(0,0,$Return[2]) EndFunc Func SQL_LEN_DATA_AT_EXEC($length) Local $SQL_LEN_DATA_AT_EXEC_OFFSET = (-100) Return (-($length)+$SQL_LEN_DATA_AT_EXEC_OFFSET) EndFunc Edited June 25, 2016 by wolf9228 mesale0077, coffeeturtle, 123disconnect and 1 other 4 ØµØ±Ø Ø§Ù„Ø³Ù…Ø§Ø¡ كان هنا
mLipok Posted November 11, 2014 Posted November 11, 2014 Today I do not have much time, but at first glance it looks very interesting. Signature beginning:* Please remember: "AutoIt"..... * Wondering who uses AutoIt and what it can be used for ? * Forum Rules ** ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Code * for other useful stuff click the following button: Reveal hidden contents Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API * ErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 * My contribution to others projects or UDF based on others projects: * _sql.au3 UDF * POP3.au3 UDF * RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF * SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane * Useful links: * Forum Rules * Forum etiquette * Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * Wiki: * Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX IE Related: * How to use IE.au3 UDF with AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskScheduler * IE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related: * How to get reference to PDF object embeded in IE * IE on Windows 11 * I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions * EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *I also encourage you to check awesome @trancexx code: * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuff * OnHungApp handler * Avoid "AutoIt Error" message box in unknown errors * HTML editor * winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/ "Homo sum; humani nil a me alienum puto" - Publius Terentius Afer"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming" , be and \\//_. Anticipating Errors : "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty." Signature last update: 2023-04-24
wolf9228 Posted November 12, 2014 Author Posted November 12, 2014 On 11/11/2014 at 7:40 PM, mLipok said: Today I do not have much time, but at first glance it looks very interesting. Thanks ØµØ±Ø Ø§Ù„Ø³Ù…Ø§Ø¡ كان هنا
petenyc1 Posted February 9, 2015 Posted February 9, 2015 Big thank you Wolf. This really really helped me with a project. I added field type "widestring" and "memo" to the string case otherwise it worked perfect!!!!
ldub Posted February 9, 2015 Posted February 9, 2015 @petenyc1 Could you share ? (I added field type "widestring" and "memo" to the string case) Thanks
petenyc1 Posted February 10, 2015 Posted February 10, 2015 So I started with the _ArrayData routine in the examples and entered my MDB name and Table name. Got error 20 which is from datatype not found in GetRecordsData which is the MS_accessDB routines. If you go through the routine Error 20 is from the datatype being unknown. "WideString" is type -12 and "Memo" is type 1. I added the type number to the local definitions at the top of GetRecordsData. Then midway through the routine is the Switch for the way it processes each datatype. I added the $SQL_Widestring and $SQL_Memo types to the string processing. That solved the problem of the datatypes. And the routine worked perfectly!! expandcollapse popupFunc GetRecordsData(ByRef $hStatement,$hStatementFree = True) Local $SQL_TYPE_DATE = 91 , $SQL_TYPE_TIME = 92 , $SQL_TYPE_TIMESTAMP = 93 Local $SQL_BIT = -7,$SQL_WVARCHAR = -9,$SQL_WLONGVARCHAR = -10,$SQL_WCHAR = -8 Local $SQL_VARBINARY = -3 , $SQL_REAL = 7 , $SQL_GUID = -11 , $SQL_INTEGER = 4 Local $SQL_TINYINT = -6 , $SQL_DOUBLE = 8 , $Row_Num = 4 , $SQL_SMALLINT = 5 Local $SQL_LONGVARBINARY = -4 , $SQL_NUMERIC = 2 , $SQL_HANDLE_STMT = 3 Local $DatType , $ColSize , $ColName , $SQL_BINARY = -2 , $SQL_Error = -1 Local $HandleSta = DllStructGetData($hStatement,"HST") Local $RowsCount = DllStructGetData($hStatement,"RSC") Local $ColsCount = DllStructGetData($hStatement,"CSC") Local $StateText = DllStructGetData($hStatement,"STT") if Not($RowsCount) Or Not($ColsCount) Then Return SetError(1,0,0) Local $ReturnArray[$RowsCount + 3][$ColsCount] While(SQLFetch($HandleSta)) For $Col_Num = 1 To $ColsCount Local $DescribeArray = SQLDescribeCol($HandleSta,$Col_Num) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(2,0,0) EndIf $ColName = $DescribeArray[0] $ColSize = $DescribeArray[1] $DatType = $DescribeArray[2] if ($Row_Num = 4) Then $ReturnArray[0][$Col_Num - 1] = $ColName $ReturnArray[1][$Col_Num - 1] = $ColSize $ReturnArray[2][$Col_Num - 1] = $DatType EndIf Local $VALUES = "",$GetDataerror = 0 Switch $DatType Case $SQL_WCHAR , $SQL_WLONGVARCHAR , $SQL_WVARCHAR
ldub Posted February 10, 2015 Posted February 10, 2015 Thank you petenyc1 for those very clear information.It's very exciting, but I am not able to create the "case". My knowledge is limited.
wolf9228 Posted February 23, 2015 Author Posted February 23, 2015 On 2/10/2015 at 12:00 AM, petenyc1 said: So I started with the _ArrayData routine in the examples and entered my MDB name and Table name. Got error 20 which is from datatype not found in GetRecordsData which is the MS_accessDB routines. If you go through the routine Error 20 is from the datatype being unknown. "WideString" is type -12 and "Memo" is type 1. I added the type number to the local definitions at the top of GetRecordsData. Then midway through the routine is the Switch for the way it processes each datatype. I added the $SQL_Widestring and $SQL_Memo types to the string processing. That solved the problem of the datatypes. And the routine worked perfectly!! expandcollapse popupFunc GetRecordsData(ByRef $hStatement,$hStatementFree = True) Local $SQL_TYPE_DATE = 91 , $SQL_TYPE_TIME = 92 , $SQL_TYPE_TIMESTAMP = 93 Local $SQL_BIT = -7,$SQL_WVARCHAR = -9,$SQL_WLONGVARCHAR = -10,$SQL_WCHAR = -8 Local $SQL_VARBINARY = -3 , $SQL_REAL = 7 , $SQL_GUID = -11 , $SQL_INTEGER = 4 Local $SQL_TINYINT = -6 , $SQL_DOUBLE = 8 , $Row_Num = 4 , $SQL_SMALLINT = 5 Local $SQL_LONGVARBINARY = -4 , $SQL_NUMERIC = 2 , $SQL_HANDLE_STMT = 3 Local $DatType , $ColSize , $ColName , $SQL_BINARY = -2 , $SQL_Error = -1 Local $HandleSta = DllStructGetData($hStatement,"HST") Local $RowsCount = DllStructGetData($hStatement,"RSC") Local $ColsCount = DllStructGetData($hStatement,"CSC") Local $StateText = DllStructGetData($hStatement,"STT") if Not($RowsCount) Or Not($ColsCount) Then Return SetError(1,0,0) Local $ReturnArray[$RowsCount + 3][$ColsCount] While(SQLFetch($HandleSta)) For $Col_Num = 1 To $ColsCount Local $DescribeArray = SQLDescribeCol($HandleSta,$Col_Num) if @error Then SQLFreeStmt($HandleSta,0) ; $SQL_CLOSE = 0 SQLExecDirect($HandleSta,$StateText) Return SetError(2,0,0) EndIf $ColName = $DescribeArray[0] $ColSize = $DescribeArray[1] $DatType = $DescribeArray[2] if ($Row_Num = 4) Then $ReturnArray[0][$Col_Num - 1] = $ColName $ReturnArray[1][$Col_Num - 1] = $ColSize $ReturnArray[2][$Col_Num - 1] = $DatType EndIf Local $VALUES = "",$GetDataerror = 0 Switch $DatType Case $SQL_WCHAR , $SQL_WLONGVARCHAR , $SQL_WVARCHAR 00014 #define SQL_WCHAR (-8) 00015 #define SQL_WVARCHAR (-9) 00016 #define SQL_WLONGVARCHAR (-10) 00017 #define SQL_C_WCHAR SQL_WCHAR 00018 00019 #ifdef UNICODE 00020 #define SQL_C_TCHAR SQL_C_WCHAR 00021 #else 00022 #define SQL_C_TCHAR SQL_C_CHAR 00023 #endif 00024 http://www.ncbi.nlm.nih.gov/IEB/ToolBox/CPP_DOC/doxyhtml/sqlucode_8h_source.html00001 /************************************************** 00002 * sqlucode.h 00003 * 00004 * These should be consistent with the MS version. 00005 * 00006 **************************************************/ 00007 #ifndef __SQLUCODE_H 00008 #define __SQLUCODE_H 00009 00010 #ifdef __cplusplus 00011 extern "C" { 00012 #endif 00013 00014 #define SQL_WCHAR (-8) 00015 #define SQL_WVARCHAR (-9) 00016 #define SQL_WLONGVARCHAR (-10) 00017 #define SQL_C_WCHAR SQL_WCHAR 00018 00019 #ifdef UNICODE 00020 #define SQL_C_TCHAR SQL_C_WCHAR 00021 #else 00022 #define SQL_C_TCHAR SQL_C_CHAR 00023 #endif 00024 00025 #define SQL_SQLSTATE_SIZEW 10 /* size of SQLSTATE for unicode */ MEMO LONGTEXT SQL_LONGVARCHAR[2] SQL_WLONGVARCHAR[3] https://msdn.microsoft.com/en-us/library/windows/desktop/ms714540%28v=vs.85%29.aspxexpandcollapse popup#include <Array.au3> #include <ScreenCapture.au3> #include "MS_AccessDB.au3" _ScreenCapture_Capture(@ScriptDir & "\InImage.jpg") $jFile = FileOpen("jFile.txt",2) FileWrite($jFile,"Stream jFile") FileClose($jFile) MS_AccessDatabaseCreate("MSDataBase.mdb",0,True) if @error Then Exit(MsgBox(0,"ErrorMsg 1"," Error Is Number " & @error)) $hDatabase = MS_AccessDatabaseConnect("MSDataBase.mdb") if @error Then Exit(MsgBox(0,"ErrorMsg 2"," Error Is Number " & @error)) CreateTable($hDatabase,"iBinary","Id INT PRIMARY KEY,Files LONGBINARY,iString LONGCHAR,iMEMO MEMO") ;Create iBinary Table And Three Columns Id , Files And iString ;(Id Data Type Int) , (Files Data Type LONGBINARY) , (iString Data Type LONGCHAR) , (PRIMARY KEY Is Id) if @error Then Exit(MsgBox(0,"ErrorMsg 3"," Error Is Number " & @error)) InsertInToTable($hDatabase,"iBinary","Id,iString,iMEMO","1,'One','MEMO'") ;Insert In iBinary Table (Id = 1 and iString = 'One') if @error Then Exit(MsgBox(0,"ErrorMsg 4"," Error Is Number " & @error)) InsertInToTable($hDatabase,"iBinary","Id,iString,iMEMO","2,'Two','MEMO'") ;Insert In iBinary Table (Id = 2 and iString = 'Two') if @error Then Exit(MsgBox(0,"ErrorMsg 5"," Error Is Number " & @error)) SetStreamFile($hDatabase,"iBinary","Files","Id","1","InImage.jpg") ;SetStreamFile WHERE Id = 1 if @error Then Exit(MsgBox(0,"ErrorMsg 6"," Error Is Number " & @error)) SetStreamFile($hDatabase,"iBinary","Files","Id","2","jFile.txt") ;SetStreamFile WHERE Id = 2 if @error Then Exit(MsgBox(0,"ErrorMsg 7"," Error Is Number " & @error)) $hstmt = SelectRecordsGroup($hDatabase,"iBinary") ; Select iBinary Table if @error Then Exit(MsgBox(0,"ErrorMsg 8"," Error Is Number " & @error)) $iReturnArray = GetRecordsData($hstmt) ; Get Array Datat For iBinary Table if @error Then Exit(MsgBox(0,"ErrorMsg 9"," Error Is Number " & @error)) ;$ReturnArray[0][Column Number] = Column Name ;$ReturnArray[1][Column Number] = Column Max Size ;$ReturnArray[2][Column Number] = Column Data Type _ArrayDisplay($iReturnArray,"Identity Table") SaveStreamFile($hDatabase,"iBinary","Files","Id","1","OutImage.jpg") ;SaveStreamFile WHERE Id = 1 if @error Then Exit(MsgBox(0,"ErrorMsg 10"," Error Is Number " & @error)) SaveStreamFile($hDatabase,"iBinary","Files","Id","2","jNewFile.txt") ;SaveStreamFile WHERE Id = 2 if @error Then Exit(MsgBox(0,"ErrorMsg 11"," Error Is Number " & @error)) MS_AccessDatabaseDisconnect($hDatabase) if @error Then Exit(MsgBox(0,"ErrorMsg 12"," Error Is Number " & @error)) ØµØ±Ø Ø§Ù„Ø³Ù…Ø§Ø¡ كان هنا
macran Posted March 6, 2015 Posted March 6, 2015 How can i use MS_AccessDB.au3? clicked "popup" -copy-save as access.au3 is right? why I can not open the file MS_AccessDB unziped by MS_AccessDB.zip thanks!
123disconnect Posted December 22, 2015 Posted December 22, 2015 i have code :#include <Array.au3> #include "MS_AccessDB.au3" Global $databaseName = @ScriptDir&"\Drives\Database.mdb" add("id1","VGA","10.2.123.2111","12/03/2014","drives\1.zip") Func Add($Hwid_, $Class_, $Version_, $Date_, $local_) Local $Database = MS_AccessDatabaseConnect($databaseName) if @error Then Exit(MsgBox(0,"ErrorMsg 13"," Error Is Number " & @error)) Local $st1 = "HardwareID,Class,Version,Date,Local" ;Local $st2 = $Hwid_&","&$Class_&","&$Version_&","&$Date_&","&$local_ Local $st2 = "'"&$Hwid_&"','"&$Class_&"','"&$Version_&"','"&$Date_&"','"&$local_&"'" InsertInToTable( $Database, "hwids", $st1, $st2) if @error Then Exit(MsgBox(0,"ErrorMsg 18"," Error Is Number " & @error)) MS_AccessDatabaseDisconnect($Database) EndFuncError MSG is : "Error is Number 2"Help me this error please .
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now