PureBasic Forums - English

Code: Select all

Enumeration
  #SQL_HANDLE_ENV = 1
  #SQL_HANDLE_DBC
  #SQL_HANDLE_STMT
  #SQL_HANDLE_DESC
EndEnumeration

#SQL_NULL_HANDLE = 0


#SQL_ATTR_ODBC_VERSION = 200
#SQL_ATTR_CONNECTION_POOLING = 201
#SQL_ATTR_CP_MATCH = 202

#SQL_OV_ODBC2 = 2
#SQL_OV_ODBC3 = 3


Enumeration
  #SQL_ACCESS_MODE = 101
  #SQL_AUTOCOMMIT
  #SQL_LOGIN_TIMEOUT
  #SQL_OPT_TRACE
  #SQL_OPT_TRACEFILE
  #SQL_TRANSLATE_DLL
  #SQL_TRANSLATE_OPTION
  #SQL_TXN_ISOLATION
  #SQL_CURRENT_QUALIFIER
  #SQL_ODBC_CURSORS
  #SQL_QUIET_MODE
  #SQL_PACKET_SIZE
EndEnumeration


Enumeration
  #SQL_UNKNOWN_TYPE
  #SQL_CHAR
  #SQL_NUMERIC
  #SQL_DECIMAL
  #SQL_INTEGER
  #SQL_SMALLINT
  #SQL_FLOAT
  #SQL_REAL
  #SQL_DOUBLE
  #SQL_DATETIME
  #SQL_VARCHAR = 12
EndEnumeration

#SQL_C_CHAR = #SQL_CHAR

#SQL_NTS = -3


Structure FieldStructure
  Name.s
  Type.s
  Size.i
  DecDigits.i
EndStructure



Procedure.i ODBC_GetTableSchema(ODBCName$, User$, Password$, Table$, List Fields.FieldStructure())

  Protected *henv
  Protected *hdbc
  Protected *hstmt = 0
  
  Protected szTypeName.s = Space(128)
  Protected cbTypeName.i
  
  Protected szColumnName.s = Space(128)
  Protected cbColumnName.i
  
  Protected szColumnSize.s = Space(128)
  Protected cbColumnSize.i
  
  Protected szDezimalDigits.s = Space(128)
  Protected cbDezimalDigits.i
  
  Protected Result.i
  
  Protected SQLReturn.w
  
  Result = #False
  
  SQLReturn = SQLAllocHandle_(#SQL_HANDLE_ENV, #SQL_NULL_HANDLE, @*henv)
  If SQLReturn = 0
    SQLReturn = SQLSetEnvAttr_(*henv, #SQL_ATTR_ODBC_VERSION, #SQL_OV_ODBC3, 0)
    If SQLReturn = 0
      SQLReturn = SQLAllocHandle_(#SQL_HANDLE_DBC, *henv, @*hdbc)
      If SQLReturn = 0
        SQLReturn = SQLSetConnectAttr_(*hdbc, #SQL_LOGIN_TIMEOUT, 5, 0)
        If SQLReturn = 0
          SQLReturn = SQLConnect_(*hdbc, ODBCName$, #SQL_NTS, User$, #SQL_NTS, Password$, #SQL_NTS)
          If SQLReturn = 0 Or SQLReturn = 1
            SQLReturn = SQLAllocHandle_(#SQL_HANDLE_STMT, *hdbc, @*hstmt)
            If SQLReturn = 0
;               Protected test.i
;               test = 2
;               SQLReturn = SQLSetStmtAttr_(*hstmt, 6, test, -6)
;               Debug SQLReturn
              
              SQLReturn = SQLColumns_(*hstmt, #Null, 0, #Null, 0, Table$, #SQL_NTS, #Null, 0)
              If SQLReturn = 0
                SQLReturn = SQLBindCol_(*hstmt, 4, #SQL_C_CHAR, szColumnName, 128, @cbColumnName)
                If SQLReturn = 0
                  SQLReturn = SQLBindCol_(*hstmt, 6, #SQL_C_CHAR, szTypeName, 128, @cbTypeName)
                  If SQLReturn = 0
                    SQLReturn = SQLBindCol_(*hstmt, 7, #SQL_C_CHAR, szColumnSize, 128, @cbColumnSize)
                    If SQLReturn = 0
                      SQLReturn = SQLBindCol_(*hstmt, 9, #SQL_C_CHAR, szDezimalDigits, 128, @cbDezimalDigits)
                      If SQLReturn = 0
                        
                        While SQLFetch_(*hstmt) & $FFFF = 0
                          AddElement(Fields())
                          Fields()\Name = szColumnName
                          Fields()\Type = szTypeName
                          Fields()\Size = Val(szColumnSize)
                          Fields()\DecDigits = Val(szDezimalDigits)
                          Result = #True
                        Wend
                        
                      EndIf
                    EndIf
                  EndIf
                EndIf
                
              EndIf
              SQLFreeHandle_(#SQL_HANDLE_STMT, *hstmt)
            EndIf
            SQLDisconnect_(*hdbc)
          EndIf
        EndIf
        SQLFreeHandle_(#SQL_HANDLE_DBC, *hdbc)
      EndIf
    EndIf
    SQLFreeHandle_(#SQL_HANDLE_ENV, *henv)
  EndIf
  
  ProcedureReturn Result
  
EndProcedure


NewList Fields.FieldStructure()

If ODBC_GetTableSchema("YourODBCName", "YourUsername", "YourPassword", "YourTable", Fields())
  ForEach Fields()
    Debug Fields()\Name + " ; " + Fields()\Type + "(" + Str(Fields()\Size) + "," + Str(Fields()\DecDigits) + ")"
  Next
EndIf