Solved

Retrieving Data Using ADODB

Posted on 2004-10-04
9
915 Views
Last Modified: 2012-06-21
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 have found the most examples using ADODB, so that is what I am trying to implement.

So, far, the code below is what I've come up with, but the program crashers when it calls: rec = con->Execute (ConStr, vRecordsAffected, 1);


I would appreciate any and all help.

Thanks,
Jeff


bool SetupProfileStrings ( )
{
      HRESULT                  hResult;
      CLSID                  clsid;
      char                  buf[MAX_PATH];
      TCHAR                  newbuf[MAX_PATH];
      char                  szUserName[NAMEBUF];
      char                  szLookup[NAMEBUF];
      char                  szProfilePath[PROFILEBUF];
      //FILE                  *fin;
      ifstream                            inFile;
      ADODB::_ConnectionPtr      con = NULL;
      ADODB::_RecordsetPtr      rec = NULL;
      ADODB::FieldPtr            pVal;
      HRESULT                  hr = S_OK;
      char                  ConStr[512];
      char                  strID[10];
      VARIANT                  *vRecordsAffected = NULL;
      int                  itype;

      ConStr[0] = '\0';
      lstrcat (ConStr, "Provider=sqloledb;Data Source=USNYMEN11WXX01;"
                  "Initial Catalog=US1Migration;"
                  "User Id=migrationUser;Password=!dataV");

      hr = con.CreateInstance (__uuidof(ADODB::Connection));
      con->Open (ConStr, "", "", 0);
      
      ConStr[0] = '\0';
      if ((szUserName[0] == 'b') || (szUserName[0] == 'B') ||
            (szUserName[0] == 'd') || (szUserName[0] == 'D'))
      {
                     lstrcat (ConStr, "SELECT VZID FROM tblIDs WHERE BAID=");
            lstrcat (ConStr, szUserName);
            itype = 0;
      }

      else if ((szUserName[0] == 'v') || (szUserName[0] == 'V') ||
            (szUserName[0] == 'z') || (szUserName[0] == 'Z'))
      {
            lstrcat (ConStr, "SELECT BAID FROM tblIDs WHERE VZID=");
            lstrcat (ConStr, szUserName);
            itype = 1;
      }
            
      rec = con->Execute (ConStr, vRecordsAffected, 1);
      //rec = con->OpenSchema(ADODB::adSchemaColumns, vtCriteria, vtMissing);

      //long limit = rec->GetFields()->Count;

      if (itype == 0)
            pVal = rec->Fields->GetItem ("BAID");
      else if (itype == 1)
            pVal = rec->Fields->GetItem ("VZID");

      vVal.Clear();
      vVal = pVal->Value;
      WideCharToMultiByte (CP_ACP, 0, vVal.bstrVal, -1, strID, sizeof (strID), NULL, NULL);

      MessageBox (NULL, szUserName, strID, MB_OK);      
      return true;
                }
}



0
Comment
Question by:jpetter
  • 4
  • 4
9 Comments
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
Comment Utility
If you have experiences with MFC recordsets (either CDaoRecordset or CRecordset) you can make DSN-less connections to an SQL Server DBMS by using a connect string like that:

ODBC;driver={SQL Server};server=YourServerName;database=
YourDatabaseName;uid=UserName;pwd=Password;dsn=;


ADODB requires a lot of COM overhead including UNICODE string handling that seems to be overkill for that you want to do here. For example alle string constants like "Initial Catalog=US1Migration;"
must be prefixed by a 'L' character, e. g.   L"Initial Catalog=US1Migration;"

Regards, Alex

                             




0
 

Author Comment

by:jpetter
Comment Utility
Alex,

Thats sounds very promising, but it also sounds like I need to abandon the ADODB approach.

In actuality, I was nervous about the adodb approach because it is dependant upon the #import declarative #import "c:\program files\common files\system\ado\msado15.dll" rename("EOF", "EOFile") , and I don't feel comfortable assuming that will be available on all the disparate Windows machines. If I use a MFC solution, I can statically link it and be a little safer that I have a successful operation.

Do you by any chance happen to have a link to a sample where they show how to implement somethink like that?

Thanks,
Jeff
0
 
LVL 39

Accepted Solution

by:
itsmeandnobodyelse earned 500 total points
Comment Utility
>>>>#import "c:\program files\common files\system\ado\msado15.dll"

The #import directive is a compiler directive, simply extracting include files from a dll. So, using an #import statement not necessarily means that you have to use a dll at run-time. But you are right, you would need a compatible msado*.dll on any client machine (my current WinNt machine doesn't have any msado*.dll). Your setup routine must install a valid msado*.dll if not already avalaible. On XP Pro clients you may use .manifest configuration files, all other Windows platforms must register an appropriate  ADO dll.

>>>> If I use a MFC solution, I can statically link it

That's true, though you'll need an SQL Server client installation if your program directly connects to a SQLServer DBMS. I think that is independent from ADO, ODBC, DAO or others.

>>>> Do you by any chance happen to have a link to a sample where they
>>>> show how to implement somethink like that?

Do you mean the MFC solution?

Simply use a CDatebase object and connect it by

   CDatabase myDB;
   char szConnect[] = "ODBC;driver={SqlServer};server=YourServerName;"
                               "database=YourDatabaseName;uid=UserName;"
                               "pwd=Password;dsn=;";

   try
   {
       myDB.Open("", FALSE /*not exclusive*/, TRUE /*readonly*/, szConnect, TRUE);
   }
   catch (...)
   {
   }

Note, SQLServer (ODBC) driver has to be installed.


Another idea:

Why don't you implement a service running somewhere on a central server, that provides the information you need via Win Sockets. Then, only one service has to be connected to SQL Server and not all 300 000 client PC's. A simple client server application easily could be adapted to do this.

Regards, Alex



0
 

Author Comment

by:jpetter
Comment Utility
Alex,

Thanks for your help. I'll give that a shot at my first opportunity....the MFC solution that is. The service solution sounds very interesting, but I would be working totally in uncharted territory, so the odds of me getting a working model in their timeframe could be difficult.

Essentially this utility program is suppose to help the domain admins as we migrate from WinNT domains to AD, and btw, we are using NetIQ's DMA (Domain Migration Administrator) tool. Since all users will have a new ID for the AD environment, what I'm trying to do is to read the ID of the logged on user using GetUserName, and then query a two field table to grab their new ID when I find a match where the value returned from GetUserName = the old ID value in the field.

I was looking for (hoping for) something "down and dirty" to get the heat off, but would like to learn more about the service as I am sure similar situations will crop up. I've written Windows services, but that's my only service experience.

Thanks,
Jeff
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 39

Expert Comment

by:itsmeandnobodyelse
Comment Utility
There might occur problems with MFC solution and SQLServer as all user's will need access to that database. You may install a client to SQLServer while installing your prog, but that might arise new problems, e. g. you will need enough licenses for all users ...

If the ID's to read will not change permanently you should consider to change to a Access MDB (data easily can imported from SQL Server). The advantage is that a jet engine is part of any Windows client, so you only need net access to the mdb file.

Regards, Alex
   
0
 

Author Comment

by:jpetter
Comment Utility
I could do that. Isn't there a limit to the number of concurrent sessions that Access can handle, or is that only when you need to work with record locking and transactions?

In fact, I already have a copy of the table in Access, as I often use that as an intermediate step to import csv files.

I'll give it a try as soon as I can, and get back to you.

Thanks,
Jeff
0
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
Comment Utility
>>>> Isn't there a limit to the number of concurrent sessions that Access

No, it's not a server DBMS but a simple file, and the AD should be able to handle an unlimited number of concurrent clients. But you also could provide a local copy of the MDB to any client... or if it is a one-time job make the evaluation when installing your prog and save the result in a local environment variable.

Regards, Alex
0
 

Expert Comment

by:Scobar
Comment Utility
I know you are not haeding down the ADODB path anymore, but there maybe a problem with your call to ::Execute().  The second paramter is most likely wanting a pointer to a variant to assign the value to.  You have passed it a NULL pointer to a variant instead of the address of a variant.

Try ...

_variant_t vRecordsAffected;
rec = con->Execute (ConStr, &vRecordsAffected, 1);
0
 

Author Comment

by:jpetter
Comment Utility
Scobar,
Thanks for the effort. Actually I'm not sure which path I'll be heading down. They've cranked up the heat on another piece of the project, so I imagine I'll go in whichever direction I can get the results I need. When I tried your suggestion, and received:  error C2664: 'Execute' : cannot convert parameter 2 from 'struct tagVARIANT ** ' to 'struct tagVARIANT *'

itsmeandnobodyelse,
thanks again for the advice. I'll give that a try as soon as I can and get back.

Thanks,
Jeff
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

What is C++ STL?: STL stands for Standard Template Library and is a part of standard C++ libraries. It contains many useful data structures (containers) and algorithms, which can spare you a lot of the time. Today we will look at the STL Vector. …
This article will show you some of the more useful Standard Template Library (STL) algorithms through the use of working examples.  You will learn about how these algorithms fit into the STL architecture, how they work with STL containers, and why t…
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

744 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