Database connectitvity

[rubeea.jaffari]

Hello everyone,

Can someone please tell me to how to connect MS-Access database using C. Are there any drivers or libraries that I would have to download? Please specify in detail.

Regards,
Rubeea

[Ancient Dragon]

Your options are somewhat limited, but here is an ODBC tutorial

[rubeea.jaffari]

I tried connecting the database but I am getting an error. Please tell me what am I doing wrong?
Here's the code:

#include<sqlext.h>
#include<stdio.h>
#include<sql.h>





    int main(void){



char szDSN[256] = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=E:\\student.mdb;";

/* 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 faculty.[name], faculty.[remarks] FROM faculty;";

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 'student.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);

    /* Execute 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.\n", DAM, szDSN);
}

/* 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;
}

[rubeea.jaffari]

I am getting an error, when I run this file using netbeans(C/C++ supported) with MinGW compiler at the header file:

include<sqlext.h>

[Ancient Dragon]

With VC++ 2014 I have to include windows.h before sqlext.h

#include <Windows.h>
#include <sqlext.h>

[rubeea.jaffari]

So can't I execute the code without Visual C++?? I mean with NetBeans?

[Ancient Dragon]

Yes you can if you have the Windows SDK installed. I just successfully compiled your program with Code::Blocks and MinGW compiler after adding windows.h as the first include file.

[rubeea.jaffari]

I included Windows.h as the first header file. But now I am getting a different error as:
[Error] C:\Users\rubeea\Documents\C-Free\Temp\Untitled1.cpp:26: error: invalid conversion from const char*' tounsigned char*'

and when I cast it explicitly I get a series of errors in which all the SQL variables are undefined!! Please help me I am now using C-Free 5.0 to run the code and I also have Windows SDK installed!!

[Ancient Dragon]

Maybe you need a different compiler. Install Code::Blocks with MinGW compiler -- it's free.

[rubeea.jaffari]

I also tried the above code with Visual Studio and it gives me the same error!

[Ancient Dragon]

I just compiled it with VS 2013, all I had to do was compile as C program and do some typecasting. Make sure the file name has *.c file extension, not *cpp.

[rubeea.jaffari]

Yes it is finally working. Thanks a lot!!

[rubeea.jaffari]

One more thing, the insert query has the format "insert into scores (Player,Score) values ('"+name+"','"+sc+"')";

in java. But if I want to use it in C++ to insert the value of sc variable f type char. How would I specify it in the values clause. Pls help

[Ancient Dragon]

I would use sprintf() to format the string

char namne[20], sc[5];
char query[255] = {0};
// fill in name and sc not shown here

sprintf(query,"INSERT INTO scores (Player,Score) VALUES ('%s','%s');",
  name,sc);

[rubeea.jaffari]

Okay and when I am executing the query using
rc = SQLPrepare(hStmt, query, SQL_NTS);
rc = SQLExecute(hStmt);
Though, there are no errors but the data isn't inserted in the database. Please specify a way for that as well!

[Ancient Dragon]

Most likely you got some sql error(s), for example you might be attempting to insert a new row with data that duplicates a row already in the table and the column is marked as unique.

First check the table to make sure it is not trying to insert a new row with duplicate data. If that's ok then print out the contents of the query just before calling SQLPrepare() so you can see what it contains.

[rubeea.jaffari]

Thank you the code works fine. One more I want to insert values into database after each minutes. Can anybody help please!

[rubeea.jaffari]

ten minutes!!

[Ancient Dragon]

use a timer to invoke the function then the time expires. How to do that might depend on the compiler. Usually you would create another thread which has an infinite loop. Inside the loop sleep() for the time you want then when it wakes up do the sql stuff and return to sleep.

[rubeea.jaffari]

You asked me to use sprintf. Well I did but the problem is that sprintf takes a char array named query and that char array is not compatible with rc = SQLPrepare(hStmt, query, SQL_NTS);
because SQLPrepare needs an SQL CHAR* or unsigned char*. I tried the type casting but it doesnot work. Pls help

[rubeea.jaffari]

if(counter>=500){

               file1=fopen("log.txt","r+");

               while ((c = fgetc(file1)) != EOF) {
                   for (j = 0; j<22; j++) 
                       currentLine[j]= (char)c;

               }

                sprintf(query,"INSERT INTO faculty (name,remarks) VALUES ('%s','checkk');",
                currentLine);
                rc = SQLPrepareA(hStmt, (SQLCHAR*)query, SQL_NTS); 

                rc= SQLExecute(hStmt);
                if((SQL_SUCCEEDED(rc))) 
                {   
                    printf("inserted\n");
                }
                SQLDisconnect(hDbc);
                SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
                SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

           }

I am using the above code to insert the value from a file into an array called currentline and then formatting the query to contain value of that array. The query doesnot executes. What am I doing wrong?

[Ancient Dragon]

line 7: why are you repeating the same character 22 times in currentLine? Isn't once enough?

int j = 0;
while ((c = fgetc(file1)) != EOF) {
     currentLine[j++]= (char)c;
currentLine[j] = 0;

line 10: you need to NULL terminate currentLine before calling sprintf().

[rubeea.jaffari]

Yeah well I figured it out and my app works great. Thank you :)

[rubeea.jaffari]

One more thing. I am using an unsigned array of 200 characters to store strings. But I want to store more than 200 characters. Whenever I specify a char array with more than 200 characters it gives me an error that this large index is not possible. Please help

[Ancient Dragon]

you need to post the code that you tried, you should be able to declare arrays in excess of several thousand, depending on the compiler. If you are using old Turbo C then you may have run out of memory. It has limited memory and a very small stack.

[rubeea.jaffari]

I am using Visual Studio 2010.
Here it is unsigned char currentLine[200]

[rubeea.jaffari]

One more question why do I have to null terminate currentLine before sprintf?

[Ancient Dragon]

spintf() and many othere functions found in string.h expect strings to be null-terminated. That's how those functions know where the end-of-string is. Without that null terminator the functions will just keep looking through memory until it finds a byte whose value is 0, very possibly causing your program to crash.

I am using Visual Studio 2010.

then you should have no problem declaring an array such as unsigned char currentLine[20000]

[rubeea.jaffari]

Where should I null terminate? Please specify in the code above

[Ancient Dragon]

after the last valid character in the string. See this post I made a week ago that told you how.