Solved

beginning odbc

Posted on 1997-03-07
8
341 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Converting a float to a string function in c languange 10 210
C#, VS15, StructLayout 1 135
Want to delete all my personal data 13 152
gdb doesn't stop on breakpoint 2 114
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…
The goal of this video is to provide viewers with basic examples to understand opening and writing to files in the C programming language.
The goal of this video is to provide viewers with basic examples to understand and use conditional statements in the C programming language.

733 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