CGI with MS Access database integration

This is a discussion on CGI with MS Access database integration within the Tech Board forums, part of the Community Boards category; Hi All, I am wanting to interact with a MS Access database through a cgi web app. Has anybody seen ...

  1. #1
    Registered User
    Join Date
    Dec 2004
    Location
    London
    Posts
    12

    CGI with MS Access database integration

    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

  2. #2
    & the hat of GPL slaying Thantos's Avatar
    Join Date
    Sep 2001
    Posts
    5,681
    Easy way: Use a SQL database instead
    Hard way: Learn the file format, open the file, parse, make changes, close file.

  3. #3
    Registered User
    Join Date
    Dec 2004
    Location
    London
    Posts
    12
    Cheers Thantos ,

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

  4. #4
    & the hat of GPL slaying Thantos's Avatar
    Join Date
    Sep 2001
    Posts
    5,681
    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.

  5. #5
    Registered User
    Join Date
    Dec 2004
    Location
    London
    Posts
    12
    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?

  6. #6
    Yes, my avatar is stolen anonytmouse's Avatar
    Join Date
    Dec 2002
    Posts
    2,544
    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

  7. #7
    Registered User
    Join Date
    Dec 2004
    Location
    London
    Posts
    12
    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?


    Code:
    #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.

  8. #8
    Yes, my avatar is stolen anonytmouse's Avatar
    Join Date
    Dec 2002
    Posts
    2,544
    Just add windows.h to your list of includes.
    Code:
    #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.

  9. #9
    5|-|1+|-|34|) ober's Avatar
    Join Date
    Aug 2001
    Posts
    4,429
    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.

  10. #10
    &TH of undefined behavior Fordy's Avatar
    Join Date
    Aug 2001
    Posts
    5,789
    Quote 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.

  11. #11
    5|-|1+|-|34|) ober's Avatar
    Join Date
    Aug 2001
    Posts
    4,429
    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?

  12. #12
    &TH of undefined behavior Fordy's Avatar
    Join Date
    Aug 2001
    Posts
    5,789
    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

  13. #13
    5|-|1+|-|34|) ober's Avatar
    Join Date
    Aug 2001
    Posts
    4,429
    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.

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Speed test result
    By audinue in forum C Programming
    Replies: 4
    Last Post: 07-07-2008, 06:18 AM
  2. DataBase access
    By itld in forum C++ Programming
    Replies: 2
    Last Post: 12-29-2001, 08:25 PM
  3. Question on MS Access
    By 041785h in forum A Brief History of Cprogramming.com
    Replies: 0
    Last Post: 10-08-2001, 10:18 AM
  4. Question on MS Access
    By 041785h in forum A Brief History of Cprogramming.com
    Replies: 0
    Last Post: 10-06-2001, 12:22 PM
  5. Database Access
    By bubbajones in forum C++ Programming
    Replies: 1
    Last Post: 09-26-2001, 11:05 PM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21