Jump to content

Microsoft Access database Third version

wolf9228

By wolf9228,
in AutoIt Example Scripts

 Share

Recommended Posts

wolf9228 Universalist

wolf9228

Posted
Posted

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 Consideration

Microsoft Access Data Types

Microsoft Access Data Types

 

MS_AccessDB

MS_AccessDB.zip

New in this version

You can work in two modes

- ASCII char

- UNICODE wide char

Adding a param Tow the GetRecordsData Is The BOOL $WtheDescribe

 

MS_AccessDB.au3 

#Include <WinAPI.au3>
#include <Math.au3>

Global $odbc32 = DllOpen("odbc32.dll") , $StructToString = False , $UNICODE = 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

$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

if $UNICODE Then

$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

Else

$Return = DllCall($odbc32,"short","SQLDriverConnectA", _
"ptr",$hdbc ,"HWND",$DshWnd,"str",$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

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,$WtheDescribe = 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 $SQL_VARCHAR = 12  ,  $SQL_LONGVARCHAR = -1 , $SQL_CHAR = 1  ,  $DatType
Local $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)

if $WtheDescribe Then

Local $ReturnArray[$RowsCount + 3][$ColsCount]
$Row_Num = 4

Else

Local $ReturnArray[$RowsCount + 1][$ColsCount]
$Row_Num = 2

EndIf

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) And $WtheDescribe Then
$ReturnArray[0][$Col_Num - 1] = $ColName
$ReturnArray[1][$Col_Num - 1] = $ColSize
$ReturnArray[2][$Col_Num - 1] = GetDataTypesName($DatType)
EndIf

if ($Row_Num = 2) And Not $WtheDescribe Then
$ReturnArray[0][$Col_Num - 1] = $ColName
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_CHAR , $SQL_LONGVARCHAR , $SQL_VARCHAR

SQLGetData($HandleSta,$Col_Num,$SQL_CHAR,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("CHAR[" & ($Length + 1) & "]")
SQLGetData($HandleSta,$Col_Num,$SQL_CHAR,$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 $SQL_VARCHAR = 12  ,  $SQL_LONGVARCHAR = -1 , $SQL_CHAR = 1  ,  $DatType
Local $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_CHAR , $SQL_LONGVARCHAR , $SQL_VARCHAR

SQLGetData($HandleSta,$Col_Num,$SQL_CHAR,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("CHAR[" & ($Length + 1) & "]")
SQLGetData($HandleSta,$Col_Num,$SQL_CHAR,$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(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,GetDataTypesName($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 "BINARY","VARBINARY","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)

if $UNICODE Then

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)

Else

Local $Return = DllCall($odbc32,"short","SQLExecDirectA","ptr",$StatementHandle, _
"str",$StatementText,"long",$TextLength)
if @error Or ($Return[0] <> 0 And $Return[0] <> 1) Then Return SetError(1,0,False)
Return SetError(0,0,True)

EndIf

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


Func GetDataTypesName($DataType)

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,  $SQL_SMALLINT = 5 , $SQL_BINARY = -2
Local $SQL_LONGVARBINARY = -4,$SQL_NUMERIC = 2,$SQL_VARCHAR = 12,$SQL_CHAR = 1
Local $SQL_LONGVARCHAR = -1

Switch $DataType

Case $SQL_TYPE_DATE

Return "DATE"

Case $SQL_TYPE_TIME

Return "TIME"

Case $SQL_TYPE_TIMESTAMP

Return "TIMESTAMP"

Case $SQL_BIT

Return "BIT"

Case $SQL_WVARCHAR

Return "WVARCHAR"

Case $SQL_WLONGVARCHAR

Return "WLONGVARCHAR"

Case $SQL_WCHAR

Return "WCHAR"

Case $SQL_VARBINARY

Return "VARBINARY"

Case $SQL_REAL

Return "REAL"

Case $SQL_GUID

Return "GUID"

Case $SQL_INTEGER

Return "INTEGER"

Case $SQL_TINYINT

Return "TINYINT"

Case $SQL_DOUBLE

Return "DOUBLE"

Case $SQL_SMALLINT

Return "SMALLINT"

Case $SQL_LONGVARBINARY

Return "LONGVARBINARY"

Case $SQL_NUMERIC

Return "NUMERIC"

Case $SQL_BINARY

Return "BINARY"

Case $SQL_VARCHAR

Return "VARCHAR"

Case $SQL_CHAR

Return "CHAR"

Case $SQL_LONGVARCHAR

Return "LONGVARCHAR"

Case Else

Return SetError(1,0,"")

EndSwitch

EndFunc

 

Example_ArrayData.au3 

#include <Array.au3>
#include "MS_AccessDB.au3"

MS_AccessDatabaseCreate("MSDataBase.mdb",0,True)
if @error Then Exit(MsgBox(0,"ErrorMsg 1",@error))
$hDatabase = MS_AccessDatabaseConnect("MSDataBase.mdb")
if @error Then Exit(MsgBox(0,"ErrorMsg 2",@error))

CreateTable($hDatabase,"Identity","Id INT PRIMARY KEY,Name VARCHAR(100),Age INT,Country VARCHAR(100)")
if @error Then Exit(MsgBox(0,"ErrorMsg 3",@error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","1,'Sword',10,'British'")
if @error Then Exit(MsgBox(0,"ErrorMsg 4",@error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","2,'Knight',20,'Egypt'")
if @error Then Exit(MsgBox(0,"ErrorMsg 5",@error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","3,'Fighter',30,'Japan'")
if @error Then Exit(MsgBox(0,"ErrorMsg 6",@error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","4,'Lightning',40,'Turkey'")
if @error Then Exit(MsgBox(0,"ErrorMsg 7",@error))

$hstmt = SelectRecordsGroup($hDatabase,"Identity")
if @error Then Exit(MsgBox(0,"ErrorMsg 8",@error))
$iReturnArray = GetRecordsData($hstmt)
if @error Then Exit(MsgBox(0,"ErrorMsg 9",@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")

$hstmt = SelectRecordsGroup($hDatabase,"Identity","Name")
if @error Then Exit(MsgBox(0,"ErrorMsg 10",@error))
$iReturnArray = GetRecordsData($hstmt)
if @error Then Exit(MsgBox(0,"ErrorMsg 11",@error))
;$ReturnArray[0][Column Number] = Column Name
;$ReturnArray[1][Column Number] = Column Max  Size
;$ReturnArray[2][Column Number] = Column Data Type
_ArrayDisplay($iReturnArray,"Name Column")


 MS_AccessDatabaseDisconnect($hDatabase)
if @error Then Exit(MsgBox(0,"ErrorMsg 12",@error))

 

Example_CreateTable.au3 

#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")
;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","1,'One'")
;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","2,'Two'")
;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))

 

Example_DataTypes.au3 

#include <Array.au3>
#include "MS_AccessDB.au3"

$StructToString = False
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))
$nColumnsList = "iInt INT PRIMARY KEY,iLong Long,ibit bit,iByte Byte,ishort short,idouble double,ifloat float,iLongChar LONGCHAR,iLongBinary LONGBINARY,iDateTime TIMESTAMP,iGuid GUID,iImage image"
CreateTable($hDatabase,"DataTypesTable",$nColumnsList)
if @error Then Exit(MsgBox(0,"ErrorMsg 3"," Error Is Number  "  & @error))
$vColumnsList = "iInt,iLong,ibit,iByte,ishort,idouble,ifloat,iLongChar,iLongBinary,iDateTime,iGuid,iImage"
$vValuesList = "1,1234,1,255,12,10.999,99.9,'iLongChar',0x694C6F6E6742696E617279,{ts '2012-12-25 10:28:01'},{GUID '7BF80980-BF32-101A-8BBB-00AA00300CAB'},NULL"
InsertInToTable($hDatabase,"DataTypesTable",$vColumnsList,$vValuesList)
if @error Then Exit(MsgBox(0,"ErrorMsg 4"," Error Is Number  "  & @error))
$vValuesList = "2,1234,1,255,12,10.999,99.9,'iLongChar',0x694C6F6E6742696E617279,{ts '2012-12-25 10:28:01'},{GUID '7BF80980-BF32-101A-8BBB-00AA00300CAB'},NULL"
InsertInToTable($hDatabase,"DataTypesTable",$vColumnsList,$vValuesList)
if @error Then Exit(MsgBox(0,"ErrorMsg 5"," Error Is Number  "  & @error))
$vValuesList = "3,1234,1,255,12,10.999,99.9,'iLongChar',0x694C6F6E6742696E617279,{ts '2012-12-25 10:28:01'},{GUID '7BF80980-BF32-101A-8BBB-00AA00300CAB'},NULL"
InsertInToTable($hDatabase,"DataTypesTable",$vColumnsList,$vValuesList)
if @error Then Exit(MsgBox(0,"ErrorMsg 6"," Error Is Number  "  & @error))

$hstmt = SelectRecordsGroup($hDatabase,"DataTypesTable")
; Select DataTypesTable Table
if @error Then Exit(MsgBox(0,"ErrorMsg 7"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For DataTypesTable Table
if @error Then Exit(MsgBox(0,"ErrorMsg 8"," 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,"DataTypesTabl Table")

MS_AccessDatabaseDisconnect($hDatabase)
if @error Then Exit(MsgBox(0,"ErrorMsg 9"," Error Is Number  "  & @error))


$StructToString = True

$hDatabase = MS_AccessDatabaseConnect("MSDataBase.mdb")
if @error Then Exit(MsgBox(0,"ErrorMsg 10"," Error Is Number  "  & @error))
$hstmt = SelectRecordsGroup($hDatabase,"DataTypesTable")
; Select DataTypesTable Table
if @error Then Exit(MsgBox(0,"ErrorMsg 11"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For DataTypesTable Table
if @error Then Exit(MsgBox(0,"ErrorMsg 12"," 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,"DataTypesTabl Table")

MS_AccessDatabaseDisconnect($hDatabase)
if @error Then Exit(MsgBox(0,"ErrorMsg 13"," Error Is Number  "  & @error))

Example_Date_Time.au3 

#include <Array.au3>
#include "MS_AccessDB.au3"

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))
$iColumns = "Id INT PRIMARY KEY,iDATE DATE,iTIME TIME,iTIMESTAMP TIMESTAMP,iGUID GUID"
CreateTable($hDatabase,"Calendar",$iColumns)
if @error Then Exit(MsgBox(0,"ErrorMsg 3"," Error Is Number  "  & @error))
$ValueList = "1,{d '2012-11-22'},{t '12:30:11'},{ts '2012-11-11 12:30:01'}" & _
",'{56a868a9-0ad4-11ce-b03a-0020af0ba770}'"
; Id = 1 , iDATE = '2012-11-22' , iTIME = '12:30:01' , iTIMESTAMP = '2012-11-11 12:30:01'
InsertInToTable($hDatabase,"Calendar","Id,iDATE,iTIME,iTIMESTAMP,iGUID",$ValueList)
if @error Then Exit(MsgBox(0,"ErrorMsg 4"," Error Is Number  "  & @error))

$ValueList = "2,{d '2011-01-02'},{t '10:20:08'},{ts '2011-01-02 10:20:08'}," & _
"'{56a868b4-0ad4-11ce-b03a-0020af0ba770}'"
; Id = 2 , iDATE = '2011-01-02' , iTIME = '10:20:08' , iTIMESTAMP = '2011-01-02 10:20:08'
InsertInToTable($hDatabase,"Calendar","Id,iDATE,iTIME,iTIMESTAMP,iGUID",$ValueList)
if @error Then Exit(MsgBox(0,"ErrorMsg 5"," Error Is Number  "  & @error))

$ValueList = "3,{d '2000-12-04'},{t '01:01:01'},{ts '2000-12-04 01:01:01'}," & _
"'{56a868b1-0ad4-11ce-b03a-0020af0ba770}'"
; Id = 3 , iDATE = '2000-12-04' , iTIME = '01:01:01' , iTIMESTAMP = '2000-12-04 01:01:01'
InsertInToTable($hDatabase,"Calendar","Id,iDATE,iTIME,iTIMESTAMP,iGUID",$ValueList)
if @error Then Exit(MsgBox(0,"ErrorMsg 6"," Error Is Number  "  & @error))

 $hstmt = SelectRecordsGroup($hDatabase,"Calendar")
; Select Calendar Table
if @error Then Exit(MsgBox(0,"ErrorMsg 7"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For Calendar Table
if @error Then Exit(MsgBox(0,"ErrorMsg 8"," Error Is Number  "  & @error))

_ArrayDisplay($iReturnArray,"Calendar Table Structs Data")
;$ReturnArray[0][Column Number] = Column Name
;$ReturnArray[1][Column Number] = Column Max  Size
;$ReturnArray[2][Column Number] = Column Data Type
; if All values of the elements is a TIMESTAMP Struct
;http://msdn.microsoft.com/en-us/library/windows/desktop/ms716289%28v=vs.85%29.aspx
;In ODBC 3.x, SQL_TYPE_DATE, SQL_TYPE_TIME, or SQL_TYPE_TIMESTAMP is
;returned in *DataTypePtr for date, time, or timestamp data, respectively;
;in ODBC 2.x, SQL_DATE, SQL_TIME, or SQL_TIMESTAMP is returned. The Driver
;Manager performs the required mappings when an ODBC 2.x application is
;working with an ODBC 3.x driver or when an ODBC 3.x application is working
;with an ODBC 2.x driver.
;DATE Struct // tag "SHORT year;SHORT month;SHORT day" // or NullValue
;TIME Struct // tag "SHORT hour;SHORT minute;SHORT second" // or NullValue
;TIMESTAMP Struct // tag "SHORT year;SHORT month;SHORT day;SHORT hour;SHORT minute;SHORT second"

$StructToString = True
 $hstmt = SelectRecordsGroup($hDatabase,"Calendar")
; Select Calendar Table
if @error Then Exit(MsgBox(0,"ErrorMsg 9"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For Calendar Table
if @error Then Exit(MsgBox(0,"ErrorMsg 10"," Error Is Number  "  & @error))

_ArrayDisplay($iReturnArray,"Calendar Table String Data")
;$ReturnArray[0][Column Number] = Column Name
;$ReturnArray[1][Column Number] = Column Max  Size
;$ReturnArray[2][Column Number] = Column Data Type
; if All values of the elements is a TIMESTAMP Struct
;http://msdn.microsoft.com/en-us/library/windows/desktop/ms716289%28v=vs.85%29.aspx
;In ODBC 3.x, SQL_TYPE_DATE, SQL_TYPE_TIME, or SQL_TYPE_TIMESTAMP is
;returned in *DataTypePtr for date, time, or timestamp data, respectively;
;in ODBC 2.x, SQL_DATE, SQL_TIME, or SQL_TIMESTAMP is returned. The Driver
;Manager performs the required mappings when an ODBC 2.x application is
;working with an ODBC 3.x driver or when an ODBC 3.x application is working
;with an ODBC 2.x driver.
;DATE Struct // tag "SHORT year;SHORT month;SHORT day" // or NullValue
;TIME Struct // tag "SHORT hour;SHORT minute;SHORT second" // or NullValue
;TIMESTAMP Struct // tag "SHORT year;SHORT month;SHORT day;SHORT hour;SHORT minute;SHORT second"

 MS_AccessDatabaseDisconnect($hDatabase)
if @error Then Exit(MsgBox(0,"ErrorMsg 11"," Error Is Number  "  & @error))

 

Example_Delete_Add.au3 

#include <Array.au3>
#include "MS_AccessDB.au3"

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,"Identity","Id INT PRIMARY KEY,Name LONGCHAR,Age INT,Country LONGCHAR")
if @error Then Exit(MsgBox(0,"ErrorMsg 3"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","1,'Sword',10,'British'")
if @error Then Exit(MsgBox(0,"ErrorMsg 4"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","2,'Knight',20,'Egypt'")
if @error Then Exit(MsgBox(0,"ErrorMsg 5"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","3,'Fighter',30,'Japan'")
if @error Then Exit(MsgBox(0,"ErrorMsg 6"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","4,'Lightning',40,'Turkey'")
if @error Then Exit(MsgBox(0,"ErrorMsg 7"," Error Is Number  "  & @error))

$hstmt = SelectRecordsGroup($hDatabase,"Identity","Id,Name,Age,Country")
; Select Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 8"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For Identity 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")


DeleteFromTable($hDatabase,"Identity","Id = 2 OR Id = 4") ; Delete Row In Id = 2 And Row In Id = 4
$hstmt = SelectRecordsGroup($hDatabase,"Identity","Id,Name,Age,Country")
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 10"," 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")
 MS_AccessDatabaseDisconnect($hDatabase) ;Disconnect MSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 11"," Error Is Number  "  & @error))


MS_AccessDatabaseCreate("pMSDataBase.mdb",0,True)
if @error Then Exit(MsgBox(0,"ErrorMsg 12"," Error Is Number  "  & @error))
$hDatabase = MS_AccessDatabaseConnect("pMSDataBase.mdb")
if @error Then Exit(MsgBox(0,"ErrorMsg 13"," Error Is Number  "  & @error))


CreateTable($hDatabase,"Identity","Id INT PRIMARY KEY")
if @error Then Exit(MsgBox(0,"ErrorMsg 14"," Error Is Number  "  & @error))
ColumnsAdd($hDatabase,"Identity","Name LONGCHAR") ; Add Name Column // Data Type LONGCHAR
if @error Then Exit(MsgBox(0,"ErrorMsg 15"," Error Is Number  "  & @error))
ColumnsAdd($hDatabase,"Identity","Age INT") ; Add Age Column // Data Type INT
if @error Then Exit(MsgBox(0,"ErrorMsg 16"," Error Is Number  "  & @error))
ColumnsAdd($hDatabase,"Identity","Country LONGCHAR") ; Add Country Column // Data Type LONGCHAR
if @error Then Exit(MsgBox(0,"ErrorMsg 17"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","1,'Sword',10,'British'")
if @error Then Exit(MsgBox(0,"ErrorMsg 18"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","2,'Knight',20,'Egypt'")
if @error Then Exit(MsgBox(0,"ErrorMsg 19"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","3,'Fighter',30,'Japan'")
if @error Then Exit(MsgBox(0,"ErrorMsg 20"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","4,'Lightning',40,'Turkey'")
if @error Then Exit(MsgBox(0,"ErrorMsg 21"," Error Is Number  "  & @error))

$hstmt = SelectRecordsGroup($hDatabase,"Identity","Id,Name,Age,Country")
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 22"," 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")

TableRemove($hDatabase,"Identity") ; Remove Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 23"," Error Is Number  "  & @error))
$hstmt = SelectRecordsGroup($hDatabase,"Identity")
if (@error) Then
MsgBox(0," Error Is Number  "  & @error,"Error Select Identity Table")
Else
MsgBox(0," Error Is Number  "  & @error,"Select Identity Table")
EndIf

MS_AccessDatabaseDisconnect($hDatabase) ;Disconnect pMSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 24"," Error Is Number  "  & @error))

 

Example_EnumData.au3 

#include <Array.au3>
#include "MS_AccessDB.au3"

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,"Identity","Id INT PRIMARY KEY,Name LONGCHAR,Age INT,Country LONGCHAR,NULL_COL INT")
if @error Then Exit(MsgBox(0,"ErrorMsg 3"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country,NULL_COL","1,'Sword',10,'British',NULL")
if @error Then Exit(MsgBox(0,"ErrorMsg 4"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country,NULL_COL","2,'Knight',20,'Egypt',NULL")
if @error Then Exit(MsgBox(0,"ErrorMsg 5"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country,NULL_COL","3,'Fighter',30,'Japan',NULL")
if @error Then Exit(MsgBox(0,"ErrorMsg 6"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country,NULL_COL","4,'Lightning',40,'Turkey',NULL")
if @error Then Exit(MsgBox(0,"ErrorMsg 7"," Error Is Number  "  & @error))

$hstmt = SelectRecordsGroup($hDatabase,"Identity","Id,Name,Age,Country,NULL_COL")
; Select Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 8"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt,False)
; Get  Array Datat For Identity 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")

EnumRecordsData($hstmt,"EnumRecDataProc")
if @error Then Exit(MsgBox(0,"ErrorMsg 10"," Error Is Number  "  & @error))

MS_AccessDatabaseDisconnect($hDatabase)
if @error Then Exit(MsgBox(0,"ErrorMsg 11"," Error Is Number  "  & @error))

Func EnumRecDataProc($IsNullValue,$Value,$ColName,$ColFormat,$ColMaxSize,$RowNum,$ColNum)

;return
;True ;Continue
;False ; Stop
;$RowNum // $ColNum : Start From One 1
;$ColFormat : Data Taype
;NullValue Is Blank ("")

Local $SQL_TYPE_DATE = 91  ,   $SQL_TYPE_TIME = 92  ,   $SQL_TYPE_TIMESTAMP = 93
Local $SQL_BIT = -7,$SQL_WVARCHAR = -9,$SQL_WLONGVARCHAR = -10 ,$SQL_BINARY = -2
Local $SQL_WCHAR = -8,$SQL_VARBINARY = -3,$SQL_LONGVARBINARY = -4,$SQL_GUID = -11
Local $SQL_TINYINT = -6  ,  $SQL_INTEGER = 4  , $SQL_REAL = 7  ,  $SQL_DOUBLE = 8
Local $SQL_NUMERIC = 2 , $SQL_SMALLINT = 5

$MsgText = "Column Name Is " & $ColName & @CRLF
$MsgText &= "Column Max Size Is " &  $ColMaxSize & " byte" & @CRLF
$MsgText &= "Column Data Type Is " & $ColFormat & @CRLF
$MsgText &= "Column Number Is " & $ColNum & @CRLF
$MsgText &= "Row Number Is " & $RowNum & @CRLF
$MsgText &= "IsNullValue Is " & $IsNullValue & @CRLF
$MsgText &= "Value Is " & $Value & @CRLF

MsgBox(0,"EnumRecDataProc Msg",$MsgText)

Switch $ColFormat
Case $SQL_WCHAR , $SQL_WVARCHAR ,$SQL_WLONGVARCHAR
;WCHAR String or NullValue // $IsNullValue
Case $SQL_BINARY , $SQL_VARBINARY , $SQL_LONGVARBINARY
;BYTE Struct tag BYTE[dataSize] or NullValue // $IsNullValue
Case $SQL_TYPE_DATE
;DATE Struct // tag "SHORT year;SHORT month;SHORT day" // or NullValue // $IsNullValue
Case $SQL_TYPE_TIME
;TIME Struct // tag "SHORT hour;SHORT minute;SHORT second" // or NullValue // $IsNullValue
Case $SQL_TYPE_TIMESTAMP
;TIMESTAMP Struct // tag "SHORT year;SHORT month;SHORT day;SHORT hour;SHORT minute;SHORT second"
; or NullValue // $IsNullValue
Case $SQL_GUID
;GUID Struct // tag "ulong Data1;ushort Data2;ushort Data3;byte Data4[8]"
; or NullValue // $IsNullValue
Case $SQL_TINYINT , $SQL_BIT
;BYTE Number or NullValue // $IsNullValue
Case $SQL_INTEGER
;INT Number or NullValue // $IsNullValue
Case $SQL_REAL
;float Number or NullValue // $IsNullValue
Case $SQL_DOUBLE
;DOUBLE Number or NullValue // $IsNullValue
Case $SQL_NUMERIC
;DOUBLE Number or NullValue // $IsNullValue
Case $SQL_SMALLINT
;SHORT Number or NullValue // $IsNullValue
EndSwitch

return True
;return
;True ;Continue
;False ; Stop

EndFunc

 

Example_Set_Insert.au3 

#include <Array.au3>
#include "MS_AccessDB.au3"

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))

Dim $SetColumnsList
CreateTable($hDatabase,"Identity","Id INT PRIMARY KEY,Name LONGCHAR,Age INT,Country LONGCHAR ,NULL_COL INT")
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","1,'Sword',10,'British'")
if @error Then Exit(MsgBox(0,"ErrorMsg 3"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","2,'Knight',20,'Egypt'")
if @error Then Exit(MsgBox(0,"ErrorMsg 4"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","3,'Fighter',30,'Japan'")
if @error Then Exit(MsgBox(0,"ErrorMsg 5"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","4,'Lightning',40,'Turkey'")
if @error Then Exit(MsgBox(0,"ErrorMsg 6"," Error Is Number  "  & @error))
$hstmt = SelectRecordsGroup($hDatabase,"Identity")
; Select Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 7"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 8"," 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")

MS_AccessDatabaseDisconnect($hDatabase) ; Disconnect MSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 9"," Error Is Number  "  & @error))

$hDatabase = MS_AccessDatabaseConnect("MSDataBase.mdb") ; Connect MSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 10"," Error Is Number  "  & @error))

$SetColumnsList = "Name = 'Thunder',Age = 40,Country = 'France'"
UpdateTable($hDatabase,"Identity",$SetColumnsList,"Id = 1")
if @error Then Exit(MsgBox(0,"ErrorMsg 11"," Error Is Number  "  & @error))
$SetColumnsList = "Name = 'Spear',Age = 30,Country = 'India'"
UpdateTable($hDatabase,"Identity",$SetColumnsList,"Id = 2")
if @error Then Exit(MsgBox(0,"ErrorMsg 12"," Error Is Number  "  & @error))
$SetColumnsList = "Name = 'Flood',Age = 20,Country = 'Korea'"
UpdateTable($hDatabase,"Identity",$SetColumnsList,"Id = 3")
if @error Then Exit(MsgBox(0,"ErrorMsg 13"," Error Is Number  "  & @error))
$SetColumnsList = "Name = 'Star',Age = 10,Country = 'Russia'"
UpdateTable($hDatabase,"Identity",$SetColumnsList,"Id = 4")
if @error Then Exit(MsgBox(0,"ErrorMsg 14"," Error Is Number  "  & @error))
$hstmt = SelectRecordsGroup($hDatabase,"Identity")
; Select Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 15"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 16"," 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")
MS_AccessDatabaseDisconnect($hDatabase) ; Disconnect MSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 17"," Error Is Number  "  & @error))

$hDatabase = MS_AccessDatabaseConnect("MSDataBase.mdb") ; Connect MSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 18"," Error Is Number  "  & @error))

CreateTable($hDatabase,"iBinary","Id INT PRIMARY KEY,File LONGBINARY,iString LONGCHAR")
;Create iBinary Table And Three Columns Id , File And iStrinig
if @error Then Exit(MsgBox(0,"ErrorMsg 19"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"iBinary","Id,File,iString","1," & SToB('One') & ",'One'")
;Insert In iBinary Table (Id = 1 ; File = SToB('One') ; iString = 'One')
if @error Then Exit(MsgBox(0,"ErrorMsg 20"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"iBinary","Id,File,iString","2," & SToB('Tow') & ",'Two'")
;Insert In iBinary Table (Id = 2 ; File = SToB('Tow') ; iString = 'Two')
if @error Then Exit(MsgBox(0,"ErrorMsg 21"," Error Is Number  "  & @error))
$hstmt = SelectRecordsGroup($hDatabase,"iBinary")
; Select iBinary Table
if @error Then Exit(MsgBox(0,"ErrorMsg 22"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For iBinary Table
if @error Then Exit(MsgBox(0,"ErrorMsg 23"," 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,"iBinary Table")
MS_AccessDatabaseDisconnect($hDatabase) ; Disconnect MSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 24"," Error Is Number  "  & @error))

$hDatabase = MS_AccessDatabaseConnect("MSDataBase.mdb") ; Connect MSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 25"," Error Is Number  "  & @error))


$SetColumnsList = "File = " & SToB('UPDATE One') & ",iString = 'UPDATE One'"
UpdateTable($hDatabase,"iBinary",$SetColumnsList,"Id = 1")
if @error Then Exit(MsgBox(0,"ErrorMsg 26"," Error Is Number  "  & @error))
$SetColumnsList = "File = " & SToB('UPDATE Tow') & ",iString = 'UPDATE Two'"
UpdateTable($hDatabase,"iBinary",$SetColumnsList,"Id = 2")
if @error Then Exit(MsgBox(0,"ErrorMsg 27"," Error Is Number  "  & @error))
$hstmt = SelectRecordsGroup($hDatabase,"iBinary")
; Select iBinary Table
if @error Then Exit(MsgBox(0,"ErrorMsg 28"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt)
; Get  Array Datat For iBinary Table
if @error Then Exit(MsgBox(0,"ErrorMsg 29"," Error Is Number  "  & @error))
_ArrayDisplay($iReturnArray,"iBinary Table")
MS_AccessDatabaseDisconnect($hDatabase) ; Disconnect MSDataBase.mdb
if @error Then Exit(MsgBox(0,"ErrorMsg 30"," Error Is Number  "  & @error))

Func SToB($Str)
Return StringToBinary($Str)
EndFunc

 

GetRecordsDataWtheOutDescribe.au3 

#include <Array.au3>
#include "MS_AccessDB.au3"

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,"Identity","Id INT PRIMARY KEY,Name LONGCHAR,Age INT,Country LONGCHAR")
if @error Then Exit(MsgBox(0,"ErrorMsg 3"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","1,'Sword',10,'British'")
if @error Then Exit(MsgBox(0,"ErrorMsg 4"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","2,'Knight',20,'Egypt'")
if @error Then Exit(MsgBox(0,"ErrorMsg 5"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","3,'Fighter',30,'Japan'")
if @error Then Exit(MsgBox(0,"ErrorMsg 6"," Error Is Number  "  & @error))
InsertInToTable($hDatabase,"Identity","Id,Name,Age,Country","4,'Lightning',40,'Turkey'")
if @error Then Exit(MsgBox(0,"ErrorMsg 7"," Error Is Number  "  & @error))

$hstmt = SelectRecordsGroup($hDatabase,"Identity","Id,Name,Age,Country")
; Select Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 8"," Error Is Number  "  & @error))
$iReturnArray = GetRecordsData($hstmt,True,False) ; $hStatementFree = True // $WtheDescribe = False
; Get  Array Datat For Identity Table
if @error Then Exit(MsgBox(0,"ErrorMsg 9"," Error Is Number  "  & @error))
;$ReturnArray[0][Column Number] = Column Name
_ArrayDisplay($iReturnArray,"Identity Table")

 

صرح السماء كان هنا

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...