|
I cannot seem to retrieve a list of Databases on Trusted connections. My server is SQL 2000 and the user name and pass are handled via Windows Authentication.
Any ideas on the connection string which should be used to retrieve database names.
Thanks,
Andy
|
|
|
|
|
I changed the class to handle trusted connections.
I also added code to retrieve/test login information
and fixed the member to return false on errors.
The commented functions are an external error handler.
Here is the code (This includes the language fix).
Hope someone finds this useful.
<code>
// SQLInfoEnumerator.h: interface for the CSQLInfoEnumerator class.
//
//////////////////////////////////////////////////////////////////////
#if !defined(AFX_SQLENUMERATE_H__8DB40743_0B77_11D4_AB80_0050BAD05CD9__INCLUDED_)
#define AFX_SQLENUMERATE_H__8DB40743_0B77_11D4_AB80_0050BAD05CD9__INCLUDED_
#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000
#define MAX_RET_LENGTH 4824
class CSQLInfoEnumerator
{
public:
CSQLInfoEnumerator();
virtual ~CSQLInfoEnumerator();
int m_iRetcode;
H_AP *m_hAP;
public:
CStringArray m_szSQLServersArray;
CStringArray m_szSQLServerDatabaseArray;
CStringArray m_szSQLServerLanguageArray;
public:
BOOL EnumerateDatabase(LPCTSTR pszSQLServer,LPCTSTR pszUserId,LPCTSTR pszPwd, BOOL bWinAuth);
BOOL EnumerateDatabaseLanguage(LPCTSTR pszSQLServer,LPCTSTR pszUserId,LPCTSTR pszPwd, BOOL bWinAuth);
BOOL EnumerateSQLServers();
BOOL RetrieveLogin(LPCTSTR pszSQLServer, CString &szUID, CString &szPWD, BOOL &bWinAuth, short sPrompt, HWND hWnd);
protected:
BOOL RetrieveInformation(LPCTSTR pszInputParam,LPCTSTR pszLookUpKey,CStringArray &szArray, BOOL bWinAuth);
BOOL FillupStringArray(LPCTSTR pszData,CStringArray &szArray,TCHAR chSep = ',');
BOOL ClearAll();
};
#endif // !defined(AFX_SQLENUMERATE_H__8DB40743_0B77_11D4_AB80_0050BAD05CD9__INCLUDED_)
//=============================================================================
// SQLInfoEnumerator.cpp: implementation of the CSQLInfoEnumerator class.
//
//////////////////////////////////////////////////////////////////////
#include "stdafx.h"
#include <sys.h>
#include <geosoft.h>
#include <ap.h>
#include "geomfcsql.h"
#include "sqlinfoenumerator.h"
#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif
//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////
/*
Author: Santosh Rao
EMail Id: SantoshRao@bigfoot.com
Date: Dec 31st 1999
Comment: Simple Class to enable enumeration
of SQL Servers and their databases and supported
languages.
You may use this code in any way you want,
You can remove my copyright too.
But dont sue me in court please, i cant afford it.
*/
CSQLInfoEnumerator::CSQLInfoEnumerator()
{
m_hAP = NULL;
ClearAll();
}
CSQLInfoEnumerator::~CSQLInfoEnumerator()
{
ClearAll();
}
/*
Clear all internal storing CStringArrays
*/
BOOL CSQLInfoEnumerator::ClearAll()
{
m_szSQLServersArray.RemoveAll();
m_szSQLServerDatabaseArray.RemoveAll();
m_szSQLServerLanguageArray.RemoveAll();
return TRUE;
}
/*
Retrieve Information of SQL Servers
On Success the string contains identifier
SERVER:Server= followed by the list of SQL
Servers
*/
BOOL CSQLInfoEnumerator::EnumerateSQLServers()
{
//Browse Connect for SQL Server Driver defined servers
//The return information would contain SERVER:Server= Keyword followed by
//{list of Servers} separated by the character ';'
return RetrieveInformation(_T("Driver={SQL Server}"),_T("SERVER:"),m_szSQLServersArray, FALSE);
}
/*
Retrieve Information of databases in a SQL Server
You have to provide the User Id and Password
On Success the string contains identifier
DATABASE:Database= followed by the list of databases
*/
BOOL CSQLInfoEnumerator::EnumerateDatabase(LPCTSTR pszSQLServer, LPCTSTR pszUserId, LPCTSTR pszPwd, BOOL bWinAuth)
{
//Browse Connect for SQL Server Driver defined server using the authentication information
//The return information would contain DATABASE:Database= Keyword followed by
//{list of databases} separated by the character ';'
CString szInputParam;
szInputParam.Format("Driver={SQL Server};SERVER=%s;UID=%s;PWD=%s",pszSQLServer,pszUserId,pszPwd);
return RetrieveInformation(szInputParam,_T("DATABASE:"),m_szSQLServerDatabaseArray, bWinAuth);
}
/*
Retrieve Information of languages in a SQL Server
You have to provide the User Id and Password
On Success the string contains identifier
LANGUAGE:Language= followed by the list of languages
Character Translation is not done, so you may see
special characters and question marks in the list of
languages text
*/
BOOL CSQLInfoEnumerator::EnumerateDatabaseLanguage(LPCTSTR pszSQLServer, LPCTSTR pszUserId, LPCTSTR pszPwd, BOOL bWinAuth)
{
CString szInputParam;
//Browse Connect for SQL Server Driver defined server using the authentication information
//The return information would contain LANGUAGE:Language= Keyword followed by
//{list of languages} separated by the character ';'
szInputParam.Format("Driver={SQL Server};SERVER=%s;UID=%s;PWD=%s",pszSQLServer,pszUserId,pszPwd);
return RetrieveInformation(szInputParam,_T("LANGUAGE:"),m_szSQLServerLanguageArray, bWinAuth);
}
/*
This Function Checks for retrieving additional information
using the initial input information and returns true if the
look up key is found and fills ther result set into a string
array.
*/
BOOL CSQLInfoEnumerator::RetrieveInformation(LPCTSTR pszInputParam,LPCTSTR pszLookUpKey,CStringArray &szArray, BOOL bWinAuth)
{
SQLHENV hSQLEnv;
SQLHDBC hSQLHdbc;
short sConnStrOut;
BOOL bReturn = FALSE;
//Allocate the environment handle
m_iRetcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hSQLEnv);
if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO)
{
//Set the environment attribute to SQL_OV_ODBC3
m_iRetcode = SQLSetEnvAttr(hSQLEnv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO)
{
//Allocate a connection handle
m_iRetcode = SQLAllocHandle(SQL_HANDLE_DBC, hSQLEnv, &hSQLHdbc);
if (bWinAuth)
SQLSetConnectOption(hSQLHdbc, SQL_INTEGRATED_SECURITY, SQL_IS_ON);
if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO)
{
CString szConnStrOut;
//Call SQLBrowseConnect for additional information
m_iRetcode = SQLBrowseConnect(hSQLHdbc, (SQLCHAR *)pszInputParam, SQL_NTS,
(SQLCHAR *)(szConnStrOut.GetBuffer(MAX_RET_LENGTH)), MAX_RET_LENGTH, &sConnStrOut);
szConnStrOut.ReleaseBuffer();
//if the look up key is found
//fill in the result set
int iFind = szConnStrOut.Find(pszLookUpKey);
TRACE(szConnStrOut);
if(iFind != -1)
{
int iFind2 = szConnStrOut.Find('=', iFind);
if(iFind2 != -1)
{
for(int i=iFind;i<iFind2;i++)
{
char c = szConnStrOut[i];
if(!isalnum(c) && c != ':')
{
break;
}
}
if(i == iFind2)
{
CString szLookUpKey = pszLookUpKey;
szConnStrOut = szConnStrOut.Mid(iFind2+1);
iFind = szConnStrOut.Find('{');
if(iFind != -1)
{
szConnStrOut = szConnStrOut.Mid(iFind+1);
iFind = szConnStrOut.Find('}');
if(iFind != -1)
{
szConnStrOut = szConnStrOut.Left(iFind);
FillupStringArray(szConnStrOut,szArray);
bReturn = TRUE;
}
}
}
}
}
SQLDisconnect(hSQLHdbc);
}
SQLFreeHandle(SQL_HANDLE_DBC, hSQLHdbc);
}
SQLFreeHandle(SQL_HANDLE_ENV, hSQLEnv);
}
// if (!bReturn)
// ProcessODBCMessagesMFCSQL(m_hAP,
// SQL_HANDLE_DBC,
// hSQLHdbc,
// "CSQLInfoEnumerator::RetrieveLogin", FALSE);
return bReturn;
}
BOOL CSQLInfoEnumerator::RetrieveLogin(LPCTSTR pszSQLServer, CString &szUID, CString &szPWD, BOOL &bWinAuth, short sPrompt, HWND hWnd)
{
SQLHENV hSQLEnv;
SQLHDBC hSQLHdbc;
short sConnStrOut;
BOOL bReturn = FALSE;
//Allocate the environment handle
m_iRetcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hSQLEnv);
if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO)
{
//Set the environment attribute to SQL_OV_ODBC3
m_iRetcode = SQLSetEnvAttr(hSQLEnv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO)
{
//Allocate a connection handle
m_iRetcode = SQLAllocHandle(SQL_HANDLE_DBC, hSQLEnv, &hSQLHdbc);
if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO)
{
CString szConnStrIn;
CString szConnStrOut;
int iFind;
szConnStrIn.GetBuffer(MAX_RET_LENGTH);
if (bWinAuth)
szConnStrIn.Format("Driver={SQL Server};SERVER=%s;UID=%s;PWD=%s;Trusted_Connection=yes",pszSQLServer,szUID,szPWD);
else
szConnStrIn.Format("Driver={SQL Server};SERVER=%s;UID=%s;PWD=%s",pszSQLServer,szUID,szPWD);
//Call SQLDriverConnect for user information
m_iRetcode = SQLDriverConnect(hSQLHdbc, hWnd, (SQLCHAR *) (szConnStrIn.GetBuffer(MAX_RET_LENGTH)), MAX_RET_LENGTH,
(SQLCHAR *)(szConnStrOut.GetBuffer(MAX_RET_LENGTH)), MAX_RET_LENGTH, &sConnStrOut, sPrompt);
szConnStrIn.ReleaseBuffer();
szConnStrOut.ReleaseBuffer();
if (m_iRetcode == SQL_SUCCESS_WITH_INFO || m_iRetcode == SQL_SUCCESS) {
iFind = szConnStrOut.Find("UID");
if(iFind != -1)
{
szUID = szConnStrOut.Mid(iFind+4);
iFind = szUID.Find('=');
if(iFind != -1)
{
szUID = szUID.Mid(iFind+1);
iFind = szUID.Find(';');
if(iFind != -1)
szUID = szUID.Left(iFind);
}
}
iFind = szConnStrOut.Find("PWD");
if(iFind != -1)
{
szPWD = szConnStrOut.Mid(iFind+4);
iFind = szPWD.Find('=');
if(iFind != -1)
{
szPWD = szPWD.Mid(iFind+1);
iFind = szPWD.Find(';');
if(iFind != -1)
szPWD = szPWD.Left(iFind);
}
}
bWinAuth = FALSE;
iFind = szConnStrOut.Find("Trusted_Connection");
if(iFind != -1)
{
szPWD = szConnStrOut.Mid(iFind+4);
iFind = szPWD.Find('=');
if(iFind != -1)
{
szPWD = szPWD.Mid(iFind+1);
iFind = szPWD.Find(';');
if(iFind != -1) {
szPWD = szPWD.Left(iFind);
}
szPWD.MakeLower();
iFind = szPWD.Find("yes");
if(iFind != -1)
bWinAuth = TRUE;
}
}
bReturn = TRUE;
}
SQLDisconnect(hSQLHdbc);
}
SQLFreeHandle(SQL_HANDLE_DBC, hSQLHdbc);
}
SQLFreeHandle(SQL_HANDLE_ENV, hSQLEnv);
}
// if (!bReturn)
// ProcessODBCMessagesMFCSQL(m_hAP,
// SQL_HANDLE_DBC,
// hSQLHdbc,
// "CSQLInfoEnumerator::RetrieveLogin", FALSE);
return bReturn;
}
/*Breaks up return information into a CStringArray for easy parsing*/
BOOL CSQLInfoEnumerator::FillupStringArray(LPCTSTR pszData,CStringArray &szArray,TCHAR chSep)
{
CString szStr = pszData;
CString szSep = chSep;
szStr.TrimLeft();
szStr.TrimRight();
szArray.RemoveAll();
int iStrLen = szStr.GetLength(),iSepPos,iSepLength=szSep.GetLength();
if(iStrLen>0 )
{
if(szStr.Right(iSepLength) != szSep)
szStr += szSep;
iStrLen = szStr.GetLength();
}
else
return FALSE;
CString szContentStr;
while(!szStr.IsEmpty())
{
iSepPos = szStr.Find(szSep);
szContentStr = szStr.Left(iSepPos);
szContentStr.TrimLeft();
szContentStr.TrimRight();
if(!szContentStr.IsEmpty())
szArray.Add(szContentStr);
iStrLen = iStrLen - (iSepPos + iSepLength);
szStr = szStr.Right(iStrLen);
}
return TRUE;
}
</code>
|
|
|
|
|
Where are these 3 files available?
#include <sys.h>
#include <geosoft.h>
#include <ap.h>
Regards
BSR
|
|
|
|
|
Where are these 3 files available?
sys.h, geosoft.h and ap.h
regards
BSR
|
|
|
|
|
Sorry about that, they are internal GXs we use to use internal functions here and include them in most of our C/C++ sources. You can safely delete the 3 lines.
|
|
|
|