• C

beginning odbc

I have downloaded the MS ODBC SDK, but the documentation is pretty skimpy.  The example apps are all written in Visual C, making it difficult to distinguish between what is an odbc function, command etc. and what is some sort of proprietary windows junk.  I just want to know how to embed a simply query in my application that will retrieve data from an ODBC data source.  The query will be known at compile time.
LVL 2
willmanAsked:
Who is Participating?
 
roy020697Connect With a Mentor Commented:
I believe the header you need is:
#include <afxwin.h>         // MFC core and standard components
But it still doesn't compile correctly.

  You might want to look into CDatabase and CRecordset classes
but they are less (and maybe more :) obvious as to how to use.

TRY:
<PRE>
void main()
{
  HENV henv;
  HDBC hdbc;
  HSTMT hstmt;
  RETCODE retcode;
  UCHAR FAR *dsn;
  UCHAR FAR *uid;
  UCHAR FAR *pwd;
  UCHAR FAR *sql;

  dsn = (UCHAR FAR *) "EmpData";
  uid = (UCHAR FAR *) "JohnS";
  pwd = (UCHAR FAR *) "Sesame";
  sql = (UCHAR FAR *) "SELECT FirstName, LastName from Employee where EmpID = 1234";

 
  retcode = SQLAllocEnv(&henv);
  if (retcode == SQL_SUCCESS)
  {
    retcode = SQLAllocConnect(henv, &hdbc);
    if (retcode == SQL_SUCCESS)
    {
      retcode = SQLConnect(hdbc, dsn, SQL_NTS, uid, SQL_NTS, pwd, SQL_NTS);

      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO )
      {

        retcode = SQLAllocStmt(hdbc, &hstmt);
        if (retcode == SQL_SUCCESS)
        {
          retcode = SQLExecDirect( hstmt, sql, SQL_NTS);
          if ( retcode == SQL_SUCCESS )
          {
            while ( SQL_SUCCESS == SQLFetch(hstmt) )
            {
              // use SQLGetData() to get data  
            }
          }
          SQLFreeStmt(hstmt, SQL_DROP);
        }
        SQLDisconnect(hdbc);
      }
      SQLFreeConnect(hdbc);
    }
    SQLFreeEnv(henv);
  }
}
------------------------------------------------------------
OR
------------
#define VC_EXTRALEAN            // Exclude rarely-used stuff from Windows headers

#include <afxwin.h>         // MFC core and standard components
#include <afxext.h>         // MFC extensions

#include <afxdb.h>                  // MFC ODBC database classes

/* Note that you must set the
Project
  Settings
    C/C++
      Code Generation
        Use Run Time Library
          MultiThreadded <<<-----
*/
void main()
{
  CDatabase db;
  db.Open(_T("EmpData"), FALSE, FALSE, _T( "ODBC;UID=JohnS;PWD=Sesame" ), FALSE);
  /* execute a stored procedure with no parms or return value */
  CString strCmd = "EXEC sp_mystored";
  db.ExecuteSQL( strCmd );
  db.Close();
}
</PRE>
0
 
tovergaardCommented:
ODBC is also maded and owned by MicroSoft - or proprietary junk as you call it..
0
 
roy020697Commented:
The following code is a simple embedded SQL program, written in C. The program illustrates many, but not all, of the embedded SQL techniques. The program prompts the user for an order number, retrieves the customer number, salesperson, and status of the order, and displays them on the screen.

main()
{
   EXEC SQL INCLUDE SQLCA;
   EXEC SQL BEGIN DECLARE SECTION;
      int   OrderID;         /* Employee ID (from user)     */
      int   CustID;          /* Retrieved customer ID       */
      char  SalesPerson[10]  /* Retrieved salesperson name */
      char  Status[6]        /* Retrieved order status      */
   EXEC SQL END DECLARE SECTION;

   /* Set up error processing */
   EXEC SQL WHENEVER SQLERROR GOTO query_error;
   EXEC SQL WHENEVER NOT FOUND GOTO bad_number;

   /* Prompt the user for order number */
   printf ("Enter order number: ");
   scanf("%d", &OrderID);

   /* Execute the SQL query */
   EXEC SQL SELECT CustID, SalesPerson, Status
            FROM Orders
            WHERE OrderID = :OrderID
            INTO :CustID, :SalesPerson, :Status;

   /* Display the results */
   printf ("Customer number:  %d\n", CustID);
   printf ("Salesperson: %s\n", SalesPerson);
   printf ("Status: %s\n", Status);
   exit();

query_error:
   printf ("SQL error: %ld\n", SQLCA.SQLCODE);
   exit();

bad_number:
   printf ("Invalid order number.\n");
   exit();
}


0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
roy020697Commented:
I tried to compile this sample code, and it gave me errors -- but the errors were all in the sql header files -- something I did wrong?
0
 
willmanAuthor Commented:
The following code is straigth ODBC derived from the samples in the ODBC API spec.  It performs all the steps necessary to connect to a database and execute a simple SQL statement.  

#include "sql.h"
#include "sqlext.h"


HENV    henv;
HDBC    hdbc;
HSTMT   hstmt;
RETCODE retcode;

retcode = SQLAllocEnv(&henv);
if (retcode == SQL_SUCCESS) {
      retcode = SQLAllocConnect(henv, &hdbc);
      if (retcode == SQL_SUCCESS) {
            retcode = SQLConnect(hdbc, "EmpData", SQL_NTS, "JohnS", SQL_NTS, "Sesame", SQL_NTS);

            if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){

                  retcode = SQLAllocStmt(hdbc, &hstmt);
                  if (retcode == SQL_SUCCESS) {
                      retcode = SQLExecDirect( hstmt, "SELECT FirstName, LastName from Employee where EmpID = 1234", SQL_NTS );
                      if ( retcode == SQL_SUCCESS ) {
                          while ( SQL_SUCCESS == SQLFetch(hstmt) ) {
                              /* use SQLGetData() to get data */
                          }
                      }
                        SQLFreeStmt(hstmt, SQL_DROP);
                  }
                  SQLDisconnect(hdbc);
            }
            SQLFreeConnect(hdbc);
      }
      SQLFreeEnv(henv);
}


0
 
bhowardCommented:
I am STILL recieveing the following errors when compile is attempted:
C:\MSDEV\INCLUDE\sqltypes.h(98) : error C2282: 'HWND' is followed by 'SQLHWND' (missing ','?)
C:\MSDEV\INCLUDE\sqlext.h(72) : error C2146: syntax error : missing ')' before identifier 'hwnd'
C:\MSDEV\INCLUDE\sqlext.h(72) : error C2081: 'SQLHWND' : name in formal parameter list illegal
C:\MSDEV\INCLUDE\sqlext.h(72) : error C2280: missing '{' before identifier 'hwnd'?

0
 
willmanAuthor Commented:
Those look like syntax errors in the include files. I haven't seen any problems with the include files, but wouldn't bet money on them be exactly right in all versions of VC. The ODBC code in the example above should be close to correct.
0
 
bhowardCommented:
The first sample I gave you is (as I stated before) from a Microsoft Help file. If you had errors when compiling it, then it probably is a problem with my (nonexistant) header information. The header stuff you tried might be close, but that is all you should have to munge with; Try specifying the headers in a different order. Perhaps your scant documentation might offer clues as to which headers are used for which SQL functions? I would expect needing header files like sql.h and sqlca.h...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.