Solved

returning sql values

Posted on 1998-12-29
6
237 Views
Last Modified: 2013-11-20
I am trying to find a way to return values in C++ from a sql expression that I send to the database.  I can run queries like "DELETE FROM TABLE_NAME" without a problem.  But I want return a value from a sql statement...example "SELECT COUNT(ID) FROM TABLE_NAME"...I would like the count to be stored in a variable in my program.  Does any one have a way to do this???  Thanks.
0
Comment
Question by:meetze
6 Comments
 
LVL 1

Expert Comment

by:perrizo
ID: 1326922
Hi,

   You can do it but, it takes some work.  The thing is that MFC provides some classes for this.  Recordsets aren't the greatest classes around but, they do save some work.  If you want to get return values you'll have to write a class similar to a recordset.  If you really want to I'll give you some code but, most everything you'll do you can do with a recordset.
0
 

Author Comment

by:meetze
ID: 1326923
yes I would appreciate the code...would this class support dynamic queries?? For example...can I use the same class to run a number of different queiries that have a different number of return values???
0
 
LVL 1

Accepted Solution

by:
appdev earned 100 total points
ID: 1326924
  Hi !
You can use DB-Library. This is the code.


PDBPROCESS *dbproc;
char *Acc="MyAccount";
char *Password="MyPassword";
char *Server="MyServer";
char *DBName="MyDB";
PLOGINREC      loginrec;
RETCODE rc=SUCCEED;
      
if(dbinit() == (char *)NULL){
   sprintf("Communication failure with [%s]",Server);
   return -1;
}

loginrec = (PLOGINREC)dblogin();
DBSETLUSER ((PLOGINREC)loginrec, Acc);
DBSETLAPP ((PLOGINREC)loginrec, "meetze");
DBSETLPWD ((PLOGINREC)loginrec, Password);

if( (*dbproc  = dbopen ((PLOGINREC)loginrec,Server)) == NULL)
   return -1;        

if ((rc=dbuse (*dbproc,DBName)) == FAIL) {
   sprintf ("I can't open %s",DBNombre);
   return -1;
}

//   You're connected and the database is open.
      
DBINT count;
RETCODE rc=SUCCEED;                                    
                                     
rc = dbcmd(dbproc1,"SELECT COUNT(ID) FROM TABLE_NAME");

if ( (rc = dbsqlexec(dbproc1)) != SUCCEED ) {
   MessageBox("Error executing SELECT","meetze",MB_OK|MB_ICONEXCLAMATION);
   return -1;
}

if ((rc = dbresults(dbproc1)) == SUCCEED) {
   rc=dbbind(dbproc1, 1, INTBIND, (DBINT)0, (BYTE *)&count);
}

char Buffer[50];
sprintf(Buffer,"There are %s ID's",count);      
MessageBox(Buffer,"meetze",MB_OK | MB_ICONEXCLAMATION);


   Hope this help you.
   Salvador.

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:meetze
ID: 1326925
Appdev....these are all API calls???  It will work for returning one value..but what if the query returns multiple records??
0
 
LVL 1

Expert Comment

by:appdev
ID: 1326926
    This is the code. With a loop and another API call.
Sorry, I forgot the header files.

#include <sqlfront.h>
#include <sqldb.h>

PDBPROCESS *dbproc;
      char *Acc="MyAccount";
      char *Password="MyPassword";
      char *Server="MyServer";
      char *DBName="MyDB";

      PLOGINREC      loginrec;
      RETCODE rc=SUCCEED;
      
      if(dbinit() == (char *)NULL){
          sprintf("Communication failure with [%s]",Server);
          return -1;
      }


      loginrec = (PLOGINREC)dblogin();
      DBSETLUSER ((PLOGINREC)loginrec, Acc);
      DBSETLAPP ((PLOGINREC)loginrec, "meetze");
      DBSETLPWD ((PLOGINREC)loginrec, Password);

      if( (*dbproc  = dbopen ((PLOGINREC)loginrec,Server)) == NULL)
            return -1;        
      if ((rc=dbuse (*dbproc,DBName)) == FAIL) {
            sprintf ("I can't open %s",DBNombre);
            return -1;
      }

      You're connected and the database is open.
      
      /////////////////

      DBINT count,ID;

      RETCODE rc=SUCCEED;                                    
                                     
      rc = dbcmd(dbproc,"SELECT COUNT(ID),ID FROM TABLE_NAME");
      if ( (rc = dbsqlexec(dbproc)) != SUCCEED ) {
            MessageBox("Error executing SELECT","meetze",MB_OK | MB_ICONEXCLAMATION);
            return -1;
      }

      if ((rc = dbresults(dbproc)) == SUCCEED) {
                  rc=dbbind(dbproc, 1, INTBIND, (DBINT)0, (BYTE *)&count);
                  rc=dbbind(dbproc, 2, INTBIND, (DBINT)0, (BYTE *)&ID);
      }


      char Buffer[50];

      while ( (rc=dbnextrow(dbproc)) != NO_MORE_ROWS ){
            
            sprintf(Buffer,"There are %s of %s",count,ID);      
            MessageBox(Buffer,"meetze",MB_OK | MB_ICONEXCLAMATION);

      }
0
 
LVL 2

Expert Comment

by:SamratAshok
ID: 1326927
You don't need to use the DB-Library or anything else. You can accomplish
same very easily with very few lines of code. Depending upon your query type
you can make the query return any value with or without using a class structure.

For e.g. If all you ever want from this kind of function is a count, it does not make
too much sense to create a class. You need to be more specific in your need
before you can create a class.
For the above mentioned query, I find this function to be the most convenient. It
is assumed here that the tablename you are querying is accessible from pointer
to recordset. If this is not the case, you need to somehow provide the complete
SQL query to the function before it executes countSet.Open ...

//It will return current record count for any open record set. Since this value is not //ODBC-layer dependant, it is generally lot more reliable

long GetRecordsetCount(CRecordset *pSet)
{
      try
      {
            CRecordset countSet(pSet->m_pDatabase);

            CString strQuery;
            CString strTableName;
            CDBVariant dbValue;

            strTableName = pSet->GetDefaultSQL();

            // No reserved words should already be in use

            ASSERT(strTableName.Find("SELECT") < 0);
            ASSERT(strTableName.Find("FROM") < 0);
            ASSERT(strTableName.Find("WHERE") < 0);

            strQuery.Format("SELECT COUNT(*) CNT FROM %s",strTableName);
            countSet.m_strFilter = pSet->m_strFilter;
            countSet.Open(CRecordset::forwardOnly, strQuery);

            countSet.GetFieldValue((short )0, dbValue, SQL_C_DOUBLE);

            countSet.Close();
            
            return (long )dbValue.m_dblVal;
      }
      catch(CException *e)
      {
                            e->ReportError();
            e->Delete();
            return -1;
      }
}

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Change owner from userA to server Local Administrators 7 59
Fibonacci challenge 11 112
changeXy challenge 13 69
x-bar in Google Sheets 2 27
Introduction: Displaying information on the statusbar.   Continuing from the third article about sudoku.   Open the project in visual studio. Status bar – let’s display the timestamp there.  We need to get the timestamp from the document s…
Introduction: Hints for the grid button.  Nested classes, templated collections.  Squash that darned bug! Continuing from the sixth article about sudoku.   Open the project in visual studio. First we will finish with the SUD_SETVALUE messa…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

863 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

25 Experts available now in Live!

Get 1:1 Help Now