C++

Forum

 
Problem connecting MS Access

Jul 30, 2011 at 8:14am
naderST (52)
Hi I'm trying to connect to MS Access, I have this source:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
#include <windows.h>
#include <sqlext.h>
#include <stdio.h>
#include <stdlib.h>

int main()
{
    char cnn[] = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\pp.accdb;";
    char szConnStrOut[255];
    int iConnStrLength2Ptr;

    HDBC hDbc;
    HENV hEnv;

    RETCODE rc;

    rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
    rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
    rc = SQLDriverConnect(hDbc, NULL, cnn, SQL_NTS, szConnStrOut, sizeof(szConnStrOut), &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);

    printf("OutMsg: %s\nrc: %d\nLastError: %d\n", szConnStrOut, rc, GetLastError());

    SQLDisconnect(hDbc);
    SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
    SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

    return 0;
}
Edit & run on cpp.sh


The problem SQLDriverConnect returns '-2' and GetLastError() returns 'ERROR_FILE_NOT_FOUND' I can't see where is the problem here, i hope you can help me.
Jul 30, 2011 at 11:56am
andywestken (4094)
-2 = SQL_INVALID_HANDLE, so maybe something is going wrong with SQLAllocHandle?

I suggest you add code to validate all return codes.

And 'ERROR_FILE_NOT_FOUND' usually means what it says!
Jul 30, 2011 at 5:15pm
naderST (52)
The problem is in this line:

 
 
rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);


Returns SQL_ERROR i don't know why.
Jul 30, 2011 at 5:43pm
andywestken (4094)
Use SQLGetDiagRec to get the diagnostic information.

(see the Diagnostics section of the MSDN entry for SQLAllocHandle)
Jul 30, 2011 at 6:57pm
naderST (52)
I couldn't use SQLGetDiagRec it returned some rare characters 

1
2
3
4
5
6
7
8
 
    int  Native;
    int count;
    char * State;
    char buff[255];

    SQLGetDiagRec(SQL_HANDLE_DBC, hDbc, 1, State, &Native, buff, sizeof(buff), &count);

    printf("%s\n", buff);
Jul 30, 2011 at 7:29pm
andywestken (4094)
I think you might have to ask on an ODBC forum... This it not a C++ problem, so out of scope here!

But I have just checked the Windows SDK sample. They make the same calls as you, with the same params, but there is a call between

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);

and

SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);

to SQLSetEnvAttr

SQLSetEnvAttr(lpEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC2, 0));

to enable version 2 functionality.

And there is a comment in the sample code that says "Register this as an application that expects 2.x behavior, you must register something if you use AllocHandle"...

If this fails, as I said above, I suggest you find an ODBC forum.

Andy

P.S. Have you tried the Windows SDK ODBC sample?
Jul 30, 2011 at 9:08pm
naderST (52)
I followed the example in this link:

http://msdn.microsoft.com/en-us/library/cc811599.aspx (Direct ODBC)
Jul 30, 2011 at 9:58pm
closed account (DSLq5Di1)
After a few headaches I managed to get their sample running, have you tried it yourself? do you have the Access 2007 runtime installed?
Jul 30, 2011 at 10:16pm
naderST (52)
I have Office 2010 installed i'm not sure if that's the problem.

EDIT: I'm going to download Access 2007 runtime and try again.
Last edited on Jul 30, 2011 at 10:21pm
Jul 30, 2011 at 10:26pm
closed account (DSLq5Di1)
I also had a file not found error until I installed the runtime (though in my case I dont have Office installed), but it wouldnt hurt to try anyhow..

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=4438

Compile the Direct ODBC example too and see how you go.
Jul 30, 2011 at 10:44pm
naderST (52)
I tried and GetLastError returns 1814 "ERROR_RESOURCE_NAME_NOT_FOUND" and if i make a few changes i get 2 "ERROR_FILE_NOT_FOUND"
Jul 30, 2011 at 11:07pm
closed account (DSLq5Di1)
Could you paste the code you are compiling to pastebin/codepad/ideone? I'll give it a try and see what results I get.
Jul 31, 2011 at 2:44am
naderST (52)
This gets the 1814 error code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
 
#include <windows.h>
#include <sqlext.h>
#include <stdio.h>
#include <stdlib.h>

int main()
{
    char szDSN[256] = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=C:\\pp.accdb;";
    /* Data Access Method used in this sample */
    const char* DAM = "Direct ODBC";

    HENV    hEnv;
    HDBC    hDbc;

    /* ODBC API return status */
    RETCODE rc;

    int     iConnStrLength2Ptr;
    char    szConnStrOut[256];

    unsigned char* query = "SELECT Customers.[Company], Customers.[First Name] FROM Customers;";

    SQLCHAR         chval1[128], chval2[128], colName[128];
    int             ret1;
    int             ret2;

    /* Number of rows and columns in result set */
    SQLINTEGER      rowCount = 0;
    SQLSMALLINT     fieldCount = 0, currentField = 0;
    HSTMT           hStmt;

    /* Allocate an environment handle */
    rc = SQLAllocEnv(&hEnv);
    /* Allocate a connection handle */
    rc = SQLAllocConnect(hEnv, &hDbc);

    /* Connect to the 'Northwind 2007.accdb' database */
    rc = SQLDriverConnect(hDbc, NULL, (unsigned char*)szDSN,
        SQL_NTS, (unsigned char*)szConnStrOut,
        255, (SQLSMALLINT*)&iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
    if (SQL_SUCCEEDED(rc))
    {
        printf("%s: Successfully connected to database. Data source name: \n  %s\n",
           DAM, szConnStrOut);

        /* Prepare SQL query */
        printf("%s: SQL query:\n  %s\n", DAM, query);

        rc = SQLAllocStmt(hDbc,&hStmt);
        rc = SQLPrepare(hStmt, query, SQL_NTS);

        /* Bind result set columns to the local buffers */
        rc = SQLBindCol(hStmt, 1, SQL_C_CHAR, chval1, 128, (SQLINTEGER*)&ret1);
        rc = SQLBindCol(hStmt, 2, SQL_C_CHAR, chval2, 128, (SQLINTEGER*)&ret2);

        /* Excecute the query and create a record set */
        rc = SQLExecute(hStmt);
        if (SQL_SUCCEEDED(rc))
        {
            printf("%s: Retrieve schema info for the given result set:\n", DAM);
            SQLNumResultCols(hStmt, &fieldCount);
            if (fieldCount > 0)
            {
                for (currentField=1; currentField <= fieldCount; currentField++)
                {
                    SQLDescribeCol(hStmt, currentField,
                        colName, sizeof(colName), 0, 0, 0, 0, 0);
                    printf(" | %s", colName);
                }
                printf("\n");
            }
            else
            {
                printf("%s: Error: Number of fields in the result set is 0.\n", DAM);
            }

            printf("%s: Fetch the actual data:\n", DAM);
            /* Loop through the rows in the result set */
            rc = SQLFetch(hStmt);
            while (SQL_SUCCEEDED(rc))
            {
                printf(" | %s | %s\n", chval1, chval2);
                rc = SQLFetch(hStmt);
                rowCount++;
            };

            printf("%s: Total Row Count: %d\n", DAM, rowCount);
            rc = SQLFreeStmt(hStmt, SQL_DROP);
        }
    }
    else
    {
        printf("%s: Couldn't connect to %s.\nLastError: %d\n", DAM, szDSN, GetLastError());
    }

    /* Disconnect and free up allocated handles */
    SQLDisconnect(hDbc);
    SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
    SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

    printf("%s: Cleanup. Done.\n", DAM);

    return 0;
}
Edit & run on cpp.sh


And this one gets the 2 error code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
 
#include <windows.h>
#include <sqlext.h>
#include <stdio.h>
#include <stdlib.h>

int main()
{
    char szDSN[256] = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=C:\\pp.accdb;";
    /* Data Access Method used in this sample */
    const char* DAM = "Direct ODBC";

    HENV    hEnv;
    HDBC    hDbc;

    /* ODBC API return status */
    RETCODE rc;

    int     iConnStrLength2Ptr;
    char    szConnStrOut[256];

    unsigned char* query = "SELECT Customers.[Company], Customers.[First Name] FROM Customers;";

    SQLCHAR         chval1[128], chval2[128], colName[128];
    int             ret1;
    int             ret2;

    /* Number of rows and columns in result set */
    SQLINTEGER      rowCount = 0;
    SQLSMALLINT     fieldCount = 0, currentField = 0;
    HSTMT           hStmt;

    rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
    rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);

    /* Connect to the 'Northwind 2007.accdb' database */
    rc = SQLDriverConnect(hDbc, NULL, (unsigned char*)szDSN,
        SQL_NTS, (unsigned char*)szConnStrOut,
        255, (SQLSMALLINT*)&iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
    if (SQL_SUCCEEDED(rc))
    {
        printf("%s: Successfully connected to database. Data source name: \n  %s\n",
           DAM, szConnStrOut);

        /* Prepare SQL query */
        printf("%s: SQL query:\n  %s\n", DAM, query);

        rc = SQLAllocStmt(hDbc,&hStmt);
        rc = SQLPrepare(hStmt, query, SQL_NTS);

        /* Bind result set columns to the local buffers */
        rc = SQLBindCol(hStmt, 1, SQL_C_CHAR, chval1, 128, (SQLINTEGER*)&ret1);
        rc = SQLBindCol(hStmt, 2, SQL_C_CHAR, chval2, 128, (SQLINTEGER*)&ret2);

        /* Excecute the query and create a record set */
        rc = SQLExecute(hStmt);
        if (SQL_SUCCEEDED(rc))
        {
            printf("%s: Retrieve schema info for the given result set:\n", DAM);
            SQLNumResultCols(hStmt, &fieldCount);
            if (fieldCount > 0)
            {
                for (currentField=1; currentField <= fieldCount; currentField++)
                {
                    SQLDescribeCol(hStmt, currentField,
                        colName, sizeof(colName), 0, 0, 0, 0, 0);
                    printf(" | %s", colName);
                }
                printf("\n");
            }
            else
            {
                printf("%s: Error: Number of fields in the result set is 0.\n", DAM);
            }

            printf("%s: Fetch the actual data:\n", DAM);
            /* Loop through the rows in the result set */
            rc = SQLFetch(hStmt);
            while (SQL_SUCCEEDED(rc))
            {
                printf(" | %s | %s\n", chval1, chval2);
                rc = SQLFetch(hStmt);
                rowCount++;
            };

            printf("%s: Total Row Count: %d\n", DAM, rowCount);
            rc = SQLFreeStmt(hStmt, SQL_DROP);
        }
    }
    else
    {
        printf("%s: Couldn't connect to %s.\nLastError: %d\n", DAM, szDSN, GetLastError());
    }

    /* Disconnect and free up allocated handles */
    SQLDisconnect(hDbc);
    SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
    SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

    printf("%s: Cleanup. Done.\n", DAM);

    return 0;
}
Edit & run on cpp.sh
Jul 31, 2011 at 2:17pm
closed account (DSLq5Di1)
Line 21 in both sources will spit a compiler error at me,
error C2440: 'initializing' : cannot convert from 'const char [67]' to 'unsigned char *'
Types pointed to are unrelated; conversion requires reinterpret_cast, C-style cast or function-style cast
1
2
 
unsigned char* query = "SELECT Customers.[Company], Customers.[First Name] FROM Customers;";
unsigned char query[] = "SELECT Customers.[Company], Customers.[First Name] FROM Customers;";

After that change the first source compiles and runs perfectly. Your second source requires a call to SQLSetEnvAttr following line 32,
1
2
3
4
 
rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv); // You should test these return codes.
SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);

rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);

MSDN wrote:
SQLAllocHandle does not set the SQL_ATTR_ODBC_VERSION environment attribute when it is called to allocate an environment handle; the environment attribute must be set by the application, or SQLSTATE HY010 (Function sequence error) will be returned when SQLAllocHandle is called to allocate a connection handle.
http://msdn.microsoft.com/en-us/library/ms712455

If you are still having problems after fixing those 2 errors, could your file be causing the problem? I have been running these tests with the Northwind database from this sample:-

http://archive.msdn.microsoft.com/ac2007DevSolutions/Release/ProjectReleases.aspx?ReleaseId=1434
Last edited on Jul 31, 2011 at 2:23pm
Jul 31, 2011 at 7:23pm
naderST (52)
I just compiled the example in this link:

http://archive.msdn.microsoft.com/ac2007DevSolutions/Release/ProjectReleases.aspx?ReleaseId=1434

and worked fine I'm not sure what was the problem.

Thank you very much for your time.
Topic archived. No new replies allowed.