CGI with MS Access database integration

[Tarran]

Hi All,

I am wanting to interact with a MS Access database through a cgi web app.

Has anybody seen any tutorials or example code for how to do this?

I have searched google and this message board for a suitable example or tutorial but for the life of me I can not find one.

If some one could please point me in the right direction I would be very greatful.

Thanks,
Tarran

[Thantos]

Easy way: Use a SQL database instead
Hard way: Learn the file format, open the file, parse, make changes, close file.

[Tarran]

Cheers Thantos ,

Is there no way can not connect through ODBC or OLEDB and make SQL commands to interact with the database?

[Thantos]

Well in access the database information is just a file. There is no service running to allow connection with those files. Now if are using access as a front end then the actual database could be an SQL which would make connecting from a web app pretty simple.

[Tarran]

I assumed you could access the provider through a connection string (ie. Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.mdb") or a DSN. Once you have done this I assumed you should be able to access the database in the c app like in scripting lanaguages.

Even if I used MS SQL and accessed a DSN, the c code should be the same??? SHouldn't It?

[anonytmouse]

Yes, an Access database can be used through ODBC or OLE-DB. These can be accessed through an ODBC api or via COM (recommended). In additions, there are several apis to help you use COM.

Read through the links below and if you need more help, please specify your compiler and language (C or C++).
ADO or ODBC?
http://cboard.cprogramming.com/searc...earchid=112529
http://www.google.com/search?q=+site...eproject+-C%23
http://cvs.sourceforge.net/viewcvs.p...pp?view=markup
http://www.john.findlay1.btinternet....e/database.htm

[Tarran]

Hi anonytmouse,

This was orginally posted in the c formum but for some reason got moved to the tech board instead.

I am trying to do this in C using Dev-C++.

I've tried using one of the examples in your list:
http://www.john.findlay1.btinternet....e/database.htm

and found MS_odbc which looks ideal for what I am wanting to start with.

For some reason when I try and compile this I get errors.

Does anything jump out in the code below saying I am wrong and need fixing?

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

#pragma lib <ODBC32.lib>

#define SZLEN 50

void process(void);
void dbError( LPSTR lp, HENV henv,HDBC hdbc,HSTMT hstmt);

int main(int argc,char *argv[])
{
	process();
	return 0;
}

void process(void)
{
	long int   liid = 0;
	char       szartist[SZLEN+1];
	HSTMT      hstmt = SQL_NULL_HSTMT;
	SQLRETURN  retcode;
	HENV       henv = SQL_NULL_HANDLE;
	HDBC       hdbc = SQL_NULL_HANDLE;
	char       szSql[256];
	char       szout[256];
	char	   szdatabase[] = "DSN=MS Access Database;DBQ=db1.mdb;FIL=MS Access;";

	retcode = SQLAllocEnv(&henv);              /* Environment handle*/

	if (retcode != SQL_SUCCESS)
	{
		dbError( "SQLAllocEnv()",henv,hdbc,hstmt);
		return;
	}

	retcode = SQLAllocConnect(henv, &hdbc); /* Connection handle */

	if (retcode != SQL_SUCCESS)
	{
		dbError( "SQLAllocConnect()",henv,hdbc,hstmt);
		SQLFreeEnv(henv);
		return;
	}

	retcode = SQLDriverConnect(hdbc, NULL, (SQLCHAR *)szdatabase, (short) (strlen(szdatabase)+1), NULL, 0, NULL, SQL_DRIVER_NOPROMPT); /* Connect to data source */

/* If there was a DSN already set-up instead of SQLDriverConnect you could use

	retcode = SQLConnect(hdbc, "my_dsn", SQL_NTS, NULL, 0, NULL, 0);
*/
	if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
	{
		dbError( "SQLDriverConnect()",henv,hdbc,hstmt);
		SQLFreeEnv(henv);
		return;
	}

	retcode = SQLAllocStmt(hdbc, &hstmt); /* Statement handle */

	if (retcode != SQL_SUCCESS)
	{
		dbError( "SQLAllocStmt()",henv,hdbc,hstmt);
		SQLFreeEnv(henv);
		return;
	}

	lstrcpy( szSql,"SELECT artistID, artistName FROM Artists ORDER BY artistName"); /* Select statement */

	retcode = SQLExecDirect(hstmt, szSql, SQL_NTS);

	if (retcode != SQL_SUCCESS)
	{
		dbError( " SQLExecDirect()",henv,hdbc,hstmt);
	}

	if (retcode == SQL_SUCCESS)
	{

	while (TRUE)
		{
		retcode = SQLFetch(hstmt);

		if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO)
		{
			dbError( "SQLFetch()",henv,hdbc,hstmt);
		}

		if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
		{
			retcode = SQLGetData(hstmt, 1, SQL_C_DEFAULT, &liid, 0, NULL);
			if (retcode != SQL_SUCCESS)
				{
					dbError( "SQLGetData(1)",henv,hdbc,hstmt);
					SQLFreeEnv(henv);
					return;
				}

			memset(szartist, 0, SZLEN+1);
			retcode = SQLGetData(hstmt, 2, SQL_CHAR, szartist, SZLEN, NULL);
			if (retcode != SQL_SUCCESS)
				{
					dbError( "SQLGetData(2)",henv,hdbc,hstmt);
					SQLFreeEnv(henv);
					return;
				}

			fprintf(stdout, "ID: %d\tArtist: %s\n", liid, szartist);
		}
		else
		{
			break;
		}
		}
	}

	SQLFreeStmt(hstmt, SQL_DROP);
	SQLDisconnect(hdbc);
	SQLFreeConnect(hdbc);
	SQLFreeEnv(henv);
}
// ------------------------------------------------------------------------------------------------------------

// ------------------------------------------------------------------------------------------------------------
void dbError( LPSTR lp, HENV henv,HDBC hdbc,HSTMT hstmt)
{
	unsigned char buf[250];
	unsigned char sqlstate[15];

	SQLError( henv, hdbc, hstmt, sqlstate, NULL,buf, sizeof(buf),NULL);
	fprintf(stderr, "%s. %s, SQLSTATE=%s\n",lp, buf, sqlstate);
}

Thanks for your help.

[anonytmouse]

Just add windows.h to your list of includes.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>

You will also need to add -lodbc32 to the linker box under Project->Project Options->Parameters to link with odbc32.lib.

It is usually helpful if you post the first couple of errors you are getting.

Have a look at the OdbcExcl sample on the page you linked to for another example of using the ODBC api. I would recommend ADO rather than the ODBC api as it is more versatile and may be easier to use. DispHelper inlcudes the ado.c sample that will compile cleanly with Dev-C++. This sample works with Excel but can easily be changed to work with Access.

[ober]

I have to go with Thantos' first suggestion. Switch to MySQL or some other more widely accepted database and use PHP instead of CGI.

You're doing things the hard way otherwise.

[Fordy]

Originally Posted by ober

I have to go with Thantos' first suggestion. Switch to MySQL or some other more widely accepted database and use PHP instead of CGI.

You're doing things the hard way otherwise.

Why is it that whenever anyone asks a question on Access on this board they are given a "helpful" nudge to use MySQL? To use MySQL, you need to use an API just the same as using anything Microsoft offer. I doubt there are that many people on these boards that actually use MySQL on a windows machine via C++, but there are loads of people advocating it!

anonytmouse is offering you good advice, he knows what he's doing, so follow him. Have a go at his disp helper too if you want to try it in ADO. If you have any more ODBC questions, post them on the windows board as there are people there who have been using that API for ages (Novacain & adrianxw spring to mind) and I'm sure they will be happy to help.

[ober]

Ok.... but I wasn't advocating using CGI or C++ in any manner. I was suggesting to change the entire platform to PHP/MySQL.

What's eating at you anyways, Fordy?

[Fordy]

Nothing's "eating at" me. It just annoys the hell out of me that when someone asks a fair question, people think they are helping by suggesting a completely different method.

Sometimes the principle might be impractical, such as trying to code a decent FPS using GDI, so a suggested alternative is helpful. But a question about "wanting to interact with a MS Access database through a cgi web app" isnt outlandish, and as the guy has gone most of the way to lookup methods of doing this, he deserves help in what he's doing, not a complete alternative.

I'm not having a go at you Ober, it's something a lot of people on these boards do, and I dont see how it helps

[ober]

No offense taken, I just thought you were a getting a little too fired up over something rather trivial. I would say that if he posted back saying that going another route was impossible, I'd offer other methods. But seeing as how he didn't and didn't specify origionally that he was rather deep into the project, I don't see how suggesting a different method is so far off the mark.

I'd rather see someone abandon 3 hours of research into a poor method and adopt a better method that would save them at least that much time if not more in the future.

Edit: That, and it is my personal crusade to rid the world of Access databases.