Jump to content

Microsoft Access database Second version

wolf9228

By wolf9228,
in AutoIt Example Scripts

 Share

Recommended Posts

wolf9228 Universalist

wolf9228

Posted
Posted (edited)

Microsoft Access database Second version

MS_AccessDB.zip

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 Consideration
http://msdn.microsoft.com/en-us/library/windows/desktop/ms710302%28v=vs.85%29.aspx

Microsoft Access Data Types
http://msdn.microsoft.com/en-us/library/windows/desktop/ms714540%28v=vs.85%29.aspx

Microsoft Access Data Types
http://www.w3schools.com/sql/sql_datatypes.asp

MS_AccessDB.au3 

#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 by wolf9228

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

 

Link to comment
Share on other sites
mLipok Universalist

mLipok

Posted
Posted

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:

Spoiler

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"
:naughty:  :ranting:, 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

Link to comment
Share on other sites
  • 2 months later...
petenyc1 Seeker

petenyc1

Posted
Posted

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

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
Link to comment
Share on other sites
  • 2 weeks later...
wolf9228 Universalist

wolf9228

Posted
  • Author
Posted

 

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

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

 

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.html

00001 /**************************************************
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.aspx

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

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

 

Link to comment
Share on other sites
  • 2 weeks later...
  • 9 months later...
123disconnect Seeker

123disconnect

Posted
Posted

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

Error MSG is : "Error is Number 2"

Help me this error please .

 

 

 

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