odbc

// odbc_1.cpp: определяет точку входа для консольного приложения.
//

#include "stdafx.h"

#include <iostream>
#include <windows.h>
#include <sqltypes.h>
#include <sql.h>
#include <sqlext.h>

struct TimeStamp
{
    short int year;
    short int month;
    short int day;
    short int hour;
    short int minute;
    short int second;
    unsigned long int fraction;
};

struct tagSQLGUID {
    DWORD Data1;
    WORD Data2;
    WORD Data3;
    BYTE Data4[8];
};



void PrintData(void* DataPtr, SQLSMALLINT CTypeArray, SQLINTEGER DataLenght);
SQLSMALLINT GetDefaultCType(SQLINTEGER SQLType);


#define ALIGNSIZE 4
#define ALIGNBUF(Length) Length % ALIGNSIZE ? \
    Length + ALIGNSIZE - (Length % ALIGNSIZE) : Length


int _tmain(int argc, _TCHAR* argv[])
{
    // Initialize the environment, connection, statement handles.
    SQLHENV env;
    SQLHDBC dbc;
    SQLHSTMT stmt;

    SQLRETURN ret;

    SQLWCHAR      SqlState[6], Msg[SQL_MAX_MESSAGE_LENGTH*2];
    SQLINTEGER    NativeError;
    SQLSMALLINT   i, MsgLen;


    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    ret = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);

    ret = SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
    ret = SQLSetConnectAttr(dbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)10, 0);
    //ret = SQLDriverConnectW(dbc, NULL, L"DRIVER={SQL Server};SERVER=name1;DATABASE=db1;UID=sa;PWD=pass;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    ret = SQLDriverConnectW(dbc, NULL, L"DRIVER={MySQL ODBC 5.3 UNICODE Driver}; SERVER = localhost; Port = 3307; DATABASE = worddb; UID = root; PWD = usbw; Option = 3", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);


    if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt))
    {
        std::cout << "Prepare statements" << std::endl;
        ret = SQLPrepare(stmt, L"SELECT * FROM words", SQL_NTSL);
        //ret = SQLPrepare(stmt, L"SELECT TOP 3 FirewallLog.* FROM FirewallLog", SQL_NTSL);
        if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
        {
            std::cout << "Execute" << std::endl;
            ret = SQLExecute(stmt);
            if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
            {
                std::cout << "  retrieve column count" << std::endl;
                // вот тут необходимо узнать количество колонок
                // их размер и тип данных
                SQLSMALLINT    siNumCols;
                SQLSMALLINT*   CTypeArray(nullptr), i;
                SQLINTEGER *   ColLenArray(nullptr), *OffsetArray(nullptr), SQLType;
                SQLINTEGER * RecLenArray(nullptr);
                void* DataPtr(nullptr);

                ret = SQLNumResultCols(stmt, &siNumCols);
                std::cout << "   count siNumCols: " << siNumCols << std::endl;

                CTypeArray = (SQLSMALLINT *)malloc(siNumCols * sizeof(SQLSMALLINT));
                ColLenArray = (SQLINTEGER *)malloc(siNumCols * sizeof(SQLINTEGER));
                RecLenArray = (SQLINTEGER *)malloc(siNumCols * sizeof(SQLINTEGER));
                OffsetArray = (SQLINTEGER *)malloc(siNumCols * sizeof(SQLINTEGER));

                OffsetArray[0] = 0;
                for (i = 0; i < siNumCols; i++)
                {
                    // тип колонки
                    SQLColAttribute(stmt, ((SQLUSMALLINT)i) + 1, SQL_DESC_TYPE, NULL, 0, NULL, (SQLPOINTER)&SQLType);
                    CTypeArray[i] = GetDefaultCType(SQLType);

                    // вычисляем размер колонки
                    SQLColAttribute(stmt, ((SQLUSMALLINT)i) + 1, SQL_DESC_OCTET_LENGTH, NULL, 0, NULL, &ColLenArray[i]);

                    ColLenArray[i] = ALIGNBUF(ColLenArray[i]);
                    if (i)
                        OffsetArray[i] = OffsetArray[i - 1] + ColLenArray[i - 1];

                    std::cout << i << ". SQLType: " << SQLType << " CType: " << CTypeArray[i] << " Length: " << ColLenArray[i] << "; OffsetArray[i]= " << OffsetArray[i] << std::endl;
                }

                // выделяем память под данные
                DataPtr = malloc(OffsetArray[siNumCols - 1] + ColLenArray[siNumCols - 1]);

                    for (i = 0; i < siNumCols; i++)
                        SQLBindCol(stmt,
                        ((SQLUSMALLINT)i) + 1,
                        CTypeArray[i],
                        (BYTE *)DataPtr + OffsetArray[i],
                        ColLenArray[i],
                        &RecLenArray[i]);


                    while ((ret = SQLFetch(stmt)) != SQL_NO_DATA) {
                        for (i = 0; i < siNumCols; i++) {
                            PrintData(((BYTE*)DataPtr + OffsetArray[i]), CTypeArray[i], RecLenArray[i]);
                            std::cout << " | ";
                        }
                        std::cout << "\n";
                    }

                    free(CTypeArray);
                    free(ColLenArray);
                    free(OffsetArray);

                    free(DataPtr);

            }
            else
            {
                i = 1;
                while ((ret = SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i, SqlState, &NativeError,
                    Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA) {
                    std::wcout << SqlState << " | " << NativeError << " | " << Msg << std::endl;
                    i++;
                }
            }

            SQLFreeStmt(stmt, SQL_DROP);
        }
        else
        {
            std::cout << "Failed to prepare" << std::endl;
        }
    }
    else
    {
        std::cout << "Failed to connect" << std::endl;
    }


    /*close handle*/
    if (stmt)
    {
        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
        stmt = NULL;
    }

    if (dbc)
    {
        SQLDisconnect(dbc);
        SQLFreeHandle(SQL_HANDLE_DBC, dbc);
        dbc = NULL;
    }

    if (env)
    {
        SQLFreeHandle(SQL_HANDLE_ENV, env);
        env = NULL;
    }


    std::cout << "Press enter to exit.";
    std::cin.get();

    return 0;
}


void PrintData(void* DataPtr, SQLSMALLINT CTypeArray, SQLINTEGER DataLenght)
{
    switch (CTypeArray)
    {
    case SQL_C_CHAR:
    case SQL_C_WCHAR:
    {
                                 char *ch = (char*)DataPtr;
                                 for (int i = 0; i < DataLenght; ++i)
                                     std::cout << ch[i];
    }
        break;
    case SQL_C_TYPE_DATE:
    case SQL_C_TYPE_TIME:
    case SQL_C_TYPE_TIMESTAMP:
    {
                                 TimeStamp *ts = (TimeStamp *)DataPtr;
                                 std::cout << ts->day << "-" << ts->month << "-" << ts->year << " " << ts->hour << ":" << ts->minute << ":" << ts->minute;
    }
        break;
    case SQL_C_GUID:
    {
                       tagSQLGUID *g = (tagSQLGUID*)DataPtr;
                       std::cout << g->Data1 << "-" << g->Data2 << "-" << g->Data3 << "-" << g->Data4;
    }
        break;
    case SQL_C_SHORT:
        std::cout << *(int*)DataPtr;
        break;
    case SQL_C_LONG:
        std::cout << *(long*)DataPtr;
        break;
    default:
        std::cout << "Unknown";
    }
}


SQLSMALLINT GetDefaultCType(SQLINTEGER SQLType)
{

    switch (SQLType)
    {
    case SQL_CHAR:
    case SQL_VARCHAR:
    case SQL_LONGVARCHAR:
    case SQL_WCHAR:
    case SQL_WLONGVARCHAR:
        return SQL_C_CHAR;
    case SQL_TINYINT:
        return SQL_C_CHAR;
    case SQL_SMALLINT:
        return SQL_C_SHORT;
    case SQL_INTEGER:
        return SQL_C_LONG;
    case SQL_BIGINT:
        return SQL_C_UBIGINT;
    case SQL_REAL:
        return SQL_C_FLOAT;
    case SQL_FLOAT:
    case SQL_DOUBLE:
        return SQL_C_DOUBLE;
    case SQL_DECIMAL:
    case SQL_BIT:
    case SQL_BINARY:
    case SQL_VARBINARY:
    case SQL_LONGVARBINARY:
    case SQL_NUMERIC:
        return SQL_C_FLOAT;
    case SQL_TYPE_DATE:
    case SQL_TYPE_TIME:
    case SQL_INTERVAL_YEAR:
    case SQL_INTERVAL_MONTH:
        return SQL_C_CHAR;
    case SQL_TYPE_TIMESTAMP:
    case SQL_DATETIME:
        return SQL_C_TYPE_TIMESTAMP;

    case SQL_GUID:
        return SQL_C_GUID;
    default:
        return SQL_C_CHAR;
    }
}