Solved

beginning odbc

Posted on 1997-03-07
8
337 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Preface I don't like visual development tools that are supposed to write a program for me. Even if it is Xcode and I can use Interface Builder. Yes, it is a perfect tool and has helped me a lot, mainly, in the beginning, when my programs were small…
This tutorial is posted by Aaron Wojnowski, administrator at SDKExpert.net.  To view more iPhone tutorials, visit www.sdkexpert.net. This is a very simple tutorial on finding the user's current location easily. In this tutorial, you will learn ho…
Video by: Grant
The goal of this video is to provide viewers with basic examples to understand and use nested-loops in the C programming language.
Video by: Grant
The goal of this video is to provide viewers with basic examples to understand and use while-loops in the C programming language.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now