Solved

beginning odbc

Posted on 1997-03-07
8
339 Views
Last Modified: 2008-02-07
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.
0
Comment
Question by:willman
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 2

Expert Comment

by:tovergaard
ID: 1249693
ODBC is also maded and owned by MicroSoft - or proprietary junk as you call it..
0
 
LVL 1

Expert Comment

by:roy020697
ID: 1249694
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
 
LVL 1

Expert Comment

by:roy020697
ID: 1249696
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
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 2

Author Comment

by:willman
ID: 1249697
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
 
LVL 1

Expert Comment

by:bhoward
ID: 1249698
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
 
LVL 2

Author Comment

by:willman
ID: 1249699
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
 
LVL 1

Expert Comment

by:bhoward
ID: 1249700
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
 
LVL 1

Accepted Solution

by:
roy020697 earned 100 total points
ID: 1249701
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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Convert image to byte array 8 203
c++ substatte a varabe for a string in a LPCTSTR statment 8 86
convert char array to number in c 5 85
Line meaning 9 86
An Outlet in Cocoa is a persistent reference to a GUI control; it connects a property (a variable) to a control.  For example, it is common to create an Outlet for the text field GUI control and change the text that appears in this field via that Ou…
Summary: This tutorial covers some basics of pointer, pointer arithmetic and function pointer. What is a pointer: A pointer is a variable which holds an address. This address might be address of another variable/address of devices/address of fu…
The goal of this video is to provide viewers with basic examples to understand and use structures in the C programming language.
The goal of this video is to provide viewers with basic examples to understand recursion in the C programming language.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question