Solved

How to Connect to SQL Server to Retrieve Data from Query

Posted on 2004-10-03
11
6,870 Views
Last Modified: 2013-11-20
Earlier today, I had to change strategies for retrieving information that I need for a utility program I am working on. Essentially, the program performs some low level Windows profile translations as we move to a new domain structure (AD) and a new standardized ID convention.

We presently have two formats of standardized user IDs: one in our pre-merger format and used with the older NT domains, and a new standardized user ID. I have a table in SQL Server 2000 that contains two fields, one for each type of ID, and the table contains approximately 275,000 rows.

This program is going to be electronically distributed to roughly 25,000 users, and in the program I will call GetUserName to pick up the currently logged on user, and then test the user ID to see which of the two types of ID's it is. I then want to create a connection to the SQL server, and perform a query that will return the value of the other valid ID for that user.

In the past, when I have used MFC to connect to databases, it has been on a limited number of machines, so I could always create the system DSN that I used in my code to ensure that it worked. I don't think that approach would be practical in this situation.

What would be the quickest and easiest way for me to connect to the SQL database, retrieve the data via a query and close the connection? I have spent so much time reading about RDO, ADO, ODBC, ADO.NET that I am now totally confused and time is running out.

I would appreciate any and all help.

Thanks,
Jeff
0
Comment
Question by:jpetter
  • 5
  • 5
11 Comments
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 12213073
Here is a good tutorial about different connection methods using Visual C++:
http://www.codeproject.com/database/connectionstrings.asp
0
 

Author Comment

by:jpetter
ID: 12216443
Like dozens of sites, that site does give examples of connection strings for just about every RDBMS and technology, but doesn't provide any insight into how to put the pieces together and use the connection.

0
 
LVL 6

Accepted Solution

by:
nabehs earned 500 total points
ID: 12235303
1. In Class View, Right Click, New Class. From the Dialog Combo, select Generic Class, Enter the name CQuery1 and press OK. A new class is created with query1.cpp and query1.h files
2. Open query1.h file
3. Assume that you have the table Users with the following fields
user_id : varchar(xxx)
other_user_id : varchar(100)
4. Modify the class CQuery1 in query1.h to the following
class CQuery1
{
public:
    CQuery1();
    virtual ~CQuery1();

    CHAR m_szOtherUserId[101];// always character array size + 1. So you have varchar(100): you will need CHAR [101]

BEGIN_COLUMN_MAP(CQuery1)
    COLUMN_ENTRY(1, m_szOtherUserId)
END_COLUMN_MAP( )
};

5. In stdafx.h add the following line:
#include <atldbcli.h>

6. Add the following function to get the other user id from the existing user id:

#include "query1.h"
void GetOtherUserId(LPCTSTR szUserId /*in*/, CString& strOtherUserId /*out*/);

void GetOtherUserId(LPCTSTR szUserId, CString& strOtherUserId)
{
    CoInitialize(NULL);

    CCommand<CAccessor<CQuery1> > rs;
    CString strConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=MyDatabase;Data Source=MyServer;User Id=sa;Password=;";
    // Replace MyDatabase with your database name
    // Replace MyServer with the server name where SQL Server is installed
    // Replace sa with the user id
    // Replace the blank password with the correct password

    CDataSource ds;
    CSession session;
    HRESULT hr;

    CString strQuery;
    strQuery.Format("SELECT other_user_id FROM Users WHERE user_id = '%s'", szUserId);
    // without error checking
    hr = ds.OpenFromInitializationString(strConnectionString.AllocSysString( ));
    hr = session.Open(ds);
    hr = rs.Open(session, LPCTSTR(strQuery));
    while(rs.MoveNext( ) == S_OK)
    {
        strOtherUserId = rs.m_szOtherUserId;
        break;
    }
    rs.Close( );
    session.Close( );
    ds.Close( );

    CoUninitialize( );
}

7. Call this function and pass the user id to get the other user id

0
 

Author Comment

by:jpetter
ID: 12238528
nabehs,

Thanks for the detailed information. That helps considerably. I still cannot get it to work as "rs" never gets populated; the debugger jumps right over the while loop.

I'll play around with both my connection string and query, though I'm guessing the connection string is okay as hr returns '0'.

This is certainly worth the points and more! I really appreciate your help and effort.

Thanks,
Jeff
0
 
LVL 6

Expert Comment

by:nabehs
ID: 12246073
If you have any difficulty, try to post the problematic section and I will check it out.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jpetter
ID: 12258034
nabehs,

Thanks again, and I'll take you up on your offer. I've been up against a hard stop on another aspect of this project and haven't been able to try as many different approaches as I would like. If you spot something obvious I would appreciate you pointing it out. I'll include the code for the class too since this approach you have suggested is different than one I've seen before, so I probably have it coded incorrectly.

Here's a snippet from Query1.h
class CQuery1  
{
public:
      CQuery1();
      virtual ~CQuery1();

      char      m_szBaid[8];
      char      m_szVzid[8];
      void GetOtherUserId(LPCTSTR szUserId /*in*/, CString& strOtherUserId /*out*/);

      BEGIN_COLUMN_MAP (CQuery1)
            COLUMN_ENTRY (1, m_szBaid)
            COLUMN_ENTRY (2, m_szVzid)
      END_COLUMN_MAP()

};

Here's the function which is in my primary cpp file and not the Query1.cpp (not sure that is correct)
void CGetWkstnInfoDlg::GetOtherUserId(LPCTSTR szUserId, CString &strOtherUserId)
{
      CoInitialize (NULL);
      CCommand<CAccessor<CQuery1> > rs;
      CString strConnectionString = "Provider=sqloledb;Data Source=USNYMEN11WXX01;Initial Catalog=US1Migration;User Id=migrationUser;Password=!dataViewer";

      CDataSource ds;
    CSession session;
    HRESULT hr;

    CString strQuery;
    strQuery.Format("SELECT VZID FROM tblIDs WHERE BAID = '%s'", szUserId);
    // without error checking
    hr = ds.OpenFromInitializationString(strConnectionString.AllocSysString( ));
    hr = session.Open(ds);
    hr = rs.Open(session, LPCTSTR(strQuery));
    while(rs.MoveNext( ) == S_OK)
    {
        strOtherUserId = rs.m_szVzid;
        break;
    }
    rs.Close( );
    session.Close( );
    ds.Close( );

    CoUninitialize( );
}
 Thanks,
Jeff
0
 
LVL 6

Expert Comment

by:nabehs
ID: 12269321
you are returning only one column from the query VZID, but in the column map you have two columns COLUMN_ENTRY (1, m_szBaid) and COLUMN_ENTRY (2, m_szVzid). You have two options to correct this:

1. set the query statement to "SELECT BAID, VZID FROM tblIDs WHERE BAID = '%s'"

OR

2. delete the first column from the column map and it will look like this:

class CQuery1  
{
public:
     CQuery1();
     virtual ~CQuery1();

     char     m_szVzid[8];
     void GetOtherUserId(LPCTSTR szUserId /*in*/, CString& strOtherUserId /*out*/);

     BEGIN_COLUMN_MAP (CQuery1)
          COLUMN_ENTRY (1, m_szVzid)
     END_COLUMN_MAP()

};

Secondly, I wonder how you declared void GetOtherUserId(LPCTSTR szUserId /*in*/, CString& strOtherUserId /*out*/); in CQuery1 class but defined it in GetWkstnInfoDlg?

0
 

Author Comment

by:jpetter
ID: 12289295
nabehs,

You are definitely hitting on my weak point, which is creating classes. Lately too, I've been pulled in other directions, so this piece that was hot enough for them to call me at home on a Saturday morning, cooled off for a little while. But it's coming back.

Okay, what would be the correct approach? Should I define the void GetOtherUserId(LPCTSTR szUserId /*in*/, CString& strOtherUserId /*out*/); function in the class CQuery1? That would make sense to me....place the function prototype in the header, the actual code in the CQuery1.cpp file, and then in my program define a variable of CQuery1 type? I've seen it done so many different ways that it does get confusing.

Thanks,
Jeff
0
 
LVL 6

Expert Comment

by:nabehs
ID: 12294918
I suggest that you define GetOtherUserId as a static function inside CQuery1, and whenever you want to use it you can call:

CString strOtherUserId;
CQuery1::GetOtherUserId(szUserId, strOtherUserId); // it will fill the strOtherUserId with the other user id

This way the class will encapsulate all its related functionality.

0
 

Author Comment

by:jpetter
ID: 12304072
That is absolutely AWESOME! I really appreciate the help. I must admit I was amazed when it actually worked, but I can't tell you how much weight it took off my shoulders.

Thanks,
Jeff
0
 
LVL 6

Expert Comment

by:nabehs
ID: 12305278
my pleasure
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This is to be the first in a series of articles demonstrating the development of a complete windows based application using the MFC classes.  I’ll try to keep each article focused on one (or a couple) of the tasks that one may meet.   Introductio…
Introduction: The undo support, implementing a stack. Continuing from the eigth article about sudoku.   We need a mechanism to keep track of the digits entered so as to implement an undo mechanism.  This should be a ‘Last In First Out’ collec…
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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

747 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

13 Experts available now in Live!

Get 1:1 Help Now