ODBC & MFC
Mario Contestabile -- Mario_Contestabile.UOS__MTL@UOSMTL2.universal.com
Wednesday, January 15, 1997
Environment: MSVC 4.2b Windows 95 Windows NT 3.51
How can I retrieve Table Names and Stored Proc Names from an SQL Server
WITHOUT creating a Data Source?
I achieve this functionality currently, but I'd like to bypass the Data Source
creation step.
Once I do create a Data Source, I currently
use CDatabase->OpenEx(OpenStr, CDatabase::noOdbcDialog);
where OpenStr == "ODBC; SERVER=%s;DSN=...", to successfully
retrieve the tables and stored procs with
::SQLTables() and ::SQLProcedures().
CDatabase->OpenEx() (which boils down to calling ::SQLDriverConnect())
doesn't seem to like it when "DSN=xxx" is absent.
mcontest@universal.com
William W. Whiting -- billw@usa.net
Thursday, January 16, 1997
[Mini-digest: 2 responses]
> At 12:01 1/15/97 MARIO CONTESTABILE wrote:
>How can I retrieve Table Names and Stored Proc Names from an SQL Server
> WITHOUT creating a Data Source?
> Once I do create a Data Source, I currently=20
> use CDatabase->OpenEx(OpenStr, CDatabase::noOdbcDialog);
> where OpenStr =3D=3D "ODBC; SERVER=3D%s;DSN=3D...
Instead of specifying DSN=3D, specify DRIVER=3D, =
where driver description is what would be returned from a SQLDrivers =
call. Be careful here that you specify enough attributes to allow the =
connection to complete.
Bill Whiting
-----From: "Michael J. Morel"
Mario,
You can connect without having a data source using a different form of the
connect string. The discussion below show how to do it with an Access
database. I would think it would work with a SQL-Server db also (using
Database and Server instead of DBQ, of course). This is from the June,
1996 issue of MFC For Yourself:
Connecting Without a Using Data Source
You can also connect to a database "on-the-fly", without first creating a
data source. We do this in our sample programs, DBBrowse and DBViewer,
because these programs are meant to allow the user to browse databases.
Since the user may never connect to the same database again, there is no
need for a permanent data source.
To connect without using a data source, use a different form of connect
string. Normally, you connect with a string that looks something like
"ODBC;DSN=My Data Source;?". But since you'll have no data source name
(DSN), use the DRIVER keyword instead. Supply the driver name for this
keyword. This is how we open a given Microsoft Access database in
DBBrowse:
CDBBrowseDoc::OnOpenDocument
if (csExt == ".mdb")
{
csConn =
"ODBC;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=";
csConn += lpszPathName;
csConn += ";";
}
return m_db.Open(NULL, FALSE, TRUE, csConn);
For the Access driver, the DBQ keyword is used to specify the complete path
to the Access file.
Mike Morel
mmorel@mushroomsoft.com
Mushroom Software
Home of MFC For Yourself
http://www.mushroomsoft.com
----------
From: Mario Contestabile[SMTP:Mario_Contestabile.UOS__MTL@universal.com]
Sent: Wednesday, January 15, 1997 1:00 PM
To: mfc-l
Subject: ODBC & MFC
Environment: MSVC 4.2b Windows 95 Windows NT 3.51
How can I retrieve Table Names and Stored Proc Names from an SQL Server
WITHOUT creating a Data Source?
I achieve this functionality currently, but I'd like to bypass the Data
Source
creation step.
Once I do create a Data Source, I currently
use CDatabase->OpenEx(OpenStr, CDatabase::noOdbcDialog);
where OpenStr == "ODBC; SERVER=%s;DSN=...", to successfully
retrieve the tables and stored procs with
::SQLTables() and ::SQLProcedures().
CDatabase->OpenEx() (which boils down to calling ::SQLDriverConnect())
doesn't seem to like it when "DSN=xxx" is absent.
mcontest@universal.com
James P. Kelleghan -- jpk01@tag.acnet.net
Friday, January 17, 1997
Two ideas that might work (though I haven't tried either under MFC).
1.-Use CDatabase objects withe the CDataBase::Open() querring your
databases system table. (Note: this works for Oracle, I have no idea about
SQL Server).
2.- Use the ODBC API directly, th ::SQLGetInfo() call will provide you with
all the information about your tables you could ever need, provided the
ODBC driver supports this call.
----------
> From: Mario Contestabile
> To: mfc-l
> Subject: ODBC & MFC
> Date: Wednesday, January 15, 1997 12:00 PM
>
> Environment: MSVC 4.2b Windows 95 Windows NT 3.51
>
> How can I retrieve Table Names and Stored Proc Names from an SQL Server
> WITHOUT creating a Data Source?
>
> I achieve this functionality currently, but I'd like to bypass the Data
Source
> creation step.
>
> Once I do create a Data Source, I currently
> use CDatabase->OpenEx(OpenStr, CDatabase::noOdbcDialog);
> where OpenStr == "ODBC; SERVER=%s;DSN=...", to successfully
> retrieve the tables and stored procs with
> ::SQLTables() and ::SQLProcedures().
>
> CDatabase->OpenEx() (which boils down to calling ::SQLDriverConnect())
> doesn't seem to like it when "DSN=xxx" is absent.
>
>
> mcontest@universal.com
Senthil .P -- senthilp@rsi.ramco.com
Friday, January 17, 1997
[Mini-digest: 2 responses]
Mario Contestabile wrote:
>>How can I retrieve Table Names and Stored Proc Names from an SQL Server
>>WITHOUT creating a Data Source?
>
>>I achieve this functionality currently, but I'd like to bypass the Data
>>Source
>>creation step.
Creation? Do you actually create Datasources? If you have configured
your ODBC datasources, then you can just pass that in the OpenStr. If it
is NULL then automatically it throws up the ODBC connection dialog
(SQL_DRIVER_CONNECT).
>>Once I do create a Data Source, I currently
>>use CDatabase->OpenEx(OpenStr, CDatabase::noOdbcDialog);
>>where OpenStr == "ODBC; SERVER=%s;DSN=...", to successfully
>>retrieve the tables and stored procs with
>>::SQLTables() and ::SQLProcedures().
>
>>CDatabase->OpenEx() (which boils down to calling ::SQLDriverConnect())
>>doesn't seem to like it when "DSN=xxx" is absent.
If you don't want to use a datasource (no ODBC), then use DB-library or
some such library which the database vendor supplies.
-----From: Ajay K Sanghi
With no intention to offend Mr. James Kalleghan this solution may not
suffice cause to Open the data source using CDatabase Object the
prerequisition of having a DataSource created at design time is still
there , cause CDataBase Open Requires a 'CONNECT STRING' in which the
DataSource name has to be provided . Thus this might not work WITHOUT
creating a DataSource .
From
Aditya Sanghi.
On Fri, 17 Jan 1997, James P. Kelleghan wrote:
> Two ideas that might work (though I haven't tried either under MFC).
>
> 1.-Use CDatabase objects withe the CDataBase::Open() querring your
> databases system table. (Note: this works for Oracle, I have no idea about
> SQL Server).
>
> 2.- Use the ODBC API directly, th ::SQLGetInfo() call will provide you with
> all the information about your tables you could ever need, provided the
> ODBC driver supports this call.
>
> ----------
> > From: Mario Contestabile
> > To: mfc-l
> > Subject: ODBC & MFC
> > Date: Wednesday, January 15, 1997 12:00 PM
> >
> > Environment: MSVC 4.2b Windows 95 Windows NT 3.51
> >
> > How can I retrieve Table Names and Stored Proc Names from an SQL Server
> > WITHOUT creating a Data Source?
> >
> > I achieve this functionality currently, but I'd like to bypass the Data
> Source
> > creation step.
> >
> > Once I do create a Data Source, I currently
> > use CDatabase->OpenEx(OpenStr, CDatabase::noOdbcDialog);
> > where OpenStr == "ODBC; SERVER=%s;DSN=...", to successfully
> > retrieve the tables and stored procs with
> > ::SQLTables() and ::SQLProcedures().
> >
> > CDatabase->OpenEx() (which boils down to calling ::SQLDriverConnect())
> > doesn't seem to like it when "DSN=xxx" is absent.
> >
> >
> > mcontest@universal.com
>
Mario Contestabile -- Mario_Contestabile.UOS__MTL@UOSMTL2.universal.com
Wednesday, January 22, 1997
My original question was=>
>>How can I retrieve Table Names and Stored Proc Names from an SQL Server
>>WITHOUT creating a Data Source?
>
>>I achieve this functionality currently, but I'd like to bypass the Data
>>Source
>>creation step.
I thank those who answered, but unfortunately I may have understated the
question.
I want to use ODBC, not DB-Library. I don't want to create a data source.
I have achieved this functionality as follows:
CString OpenStr;
// "ConnectFunctions= a three character string indicating whether the driver
supports
// SQLConnect, SQLDriverConnect and SQLBrowseConnect
OpenStr.Format(_T("ODBC;ConnectFunctions={YYY};DRIVER={SQL Server};SERVER=%s;
DATABASE=%s;UID=%s;PWD=%s"), szSQLSrv, szDbase, m_UserName, m_UserPwd );
CDatabase *m_db;
m_db = new CDatabase;
try{
m_db->OpenEx(OpenStr, CDatabase::noOdbcDialog | CDatabase::openReadOnly);
}
catch(CDBException* e){
AfxMessageBox(e->m_strStateNativeOrigin + e->m_strError, MB_ICONEXCLAMATION);
e->Delete();
delete m_db;
return;
}
CRecordset xxx(m_db);
int ret = ::SQLTables(xxx.m_hstmt, NULL, 0, NULL, 0, NULL, 0, NULL, 0);
if(SQL_SUCCESS == ret || SQL_SUCCESS_WITH_INFO == ret){
UCHAR buf[200] = "";
SDWORD num;
ret = ::SQLBindCol(xxx.m_hstmt, 3, SQL_C_CHAR, buf, 200, &num);
if(SQL_SUCCESS == ret || SQL_SUCCESS_WITH_INFO == ret){
while(::SQLFetch(xxx.m_hstmt) == SQL_SUCCESS){
TRACE2("dbase[%s] table-> [%s]\n", szDbase, buf);
}
} // if SQLBindCol() succeeded
} // if SQLTables() succeeded
m_db->Close();
delete m_db;
You see I used the DRIVER keyword. But, this keyword exists only in ODBC 2.0.
How come I don't check for CDBException::m_nRetCode ==
AFX_SQL_ERROR_ODBC_V2_REQUIRED?
That way I could tell the user, hay upgrade!
Well, consider this scenario. Since I use CDatabase and CRecordset, I have to
ship the .EXE, ODBC32.DLL and ODBCINT.DLL. Failure to do so will result in
"Required DLL ODBC32.DLL missing" and the EXE won't load.
So the 3 files are put in a directory on the client's Win95 machine, but alas,
the client
has ODBC 1.0 installed. The app loads, but when the above function is executed,
CDBException::m_nRetCode == SQL_ERROR (-1).
I can only presume this is due to the fact that the user's ODBC DLLs (ODBC.DLL)
are in windows/system, which don't match up with my 2, more recent ODBC DLLs,
that reside in the app's directory. Thus the return code is -1, and I can't
check the DRIVER version.
You see that it's a catch 22. If I don't ship those 2 DLLs with the app, it
won't load,
but if I ship those 2 DLLs with the app and the user has ODBC 1.0 installed,
the above function will choke.
Possible work arounds:
- Use ::SQLGetInfo with SQL_DRIVER_ODBC_VER. How to pass it a valid HDBC
since my OpenEx() throws an exception?
- Find method to avoid having to ship the two DLLs, which presumably would have
CDBException::m_nRetCode == AFX_SQL_ERROR_ODBC_V2_REQUIRED.
Comments and suggestions welcome,
mcontest@universal.com
P.J. Tezza -- pj@exemplarsoftware.com
Friday, January 24, 1997
[Mini-digest: 2 responses]
>Well, consider this scenario. Since I use CDatabase and CRecordset, I have to
ship the .EXE, ODBC32.DLL and ODBCINT.DLL. Failure to do so will result in
"Required DLL ODBC32.DLL missing" and the EXE won't load.
>So the 3 files are put in a directory on the client's Win95 machine, but alas,
the client
has ODBC 1.0 installed. The app loads, but when the above function is executed,
CDBException::m_nRetCode == SQL_ERROR (-1).
I can only presume this is due to the fact that the user's ODBC DLLs (ODBC.DLL)
are in windows/system, which don't match up with my 2, more recent ODBC DLLs,
that reside in the app's directory. Thus the return code is -1, and I can't
check the DRIVER version.
>You see that it's a catch 22. If I don't ship those 2 DLLs with the app, it
won't load,
but if I ship those 2 DLLs with the app and the user has ODBC 1.0 installed,
the above function will choke.
Why don't you just ship and install ODBC 2.5 correctly? Putting ODBC DLLs in your application directory is bound to give you bad karma.
PJ
-----From: Joao Marcos Melo Mendes
Hy, there, :)
On 22 Jan 1997, Mario Contestabile wrote:
> I can only presume this is due to the fact that the user's ODBC DLLs (ODBC.DLL)
> are in windows/system, which don't match up with my 2, more recent ODBC DLLs,
> that reside in the app's directory. Thus the return code is -1, and I can't
> check the DRIVER version.
Why not simply install the DLLs to the client's WinSysDir? It seems like
the obvious solution. InstallShield can take care of that, and of
uninstalling as well.
Joao Mendes
MegaMedia, S.A.
"We're fools to make war on our brothers in arms." - Mark Knopfler
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: 2.6.2
mQBNAzKu3TgAAAECAL8+YSEFZ0XrlBMu9t2xDq3rhpWZoscP83VrX5MevAm3UOd6
fOtDKsJxsWugnVMexo50NfBjeWOHz5nA1b9hYx0ABRG0H0pvYW8gTWVuZGVzIDxq
bW1tQG1lZ2FtZWRpYS5wdD4=
=sspP
-----END PGP PUBLIC KEY BLOCK-----
| Вернуться в корень Архива
|