ODBC, MFC & SQL
Mario Contestabile -- Mario_Contestabile.UOS__MTL@UOSMTL2.universal.com
Monday, August 19, 1996
Environment: MSVC 4.1 Windows95 Windows NT 3.51
During the conception of an application here, I did the GUI work,
and another programmer did the SQL work. This was fine with me,
as I'm not an SQL expert. Now, this programmer is no longer with us,
and as I look through his code, I'm questioning if it is implemented
correctly. Basically, the user enters SQL statements, after which
the app must verify the syntax. To verify the syntax, the app connects
to an SQL server, creates a temporary table, and executes the statement.
I'm assuming this is the correct way of doing it...
The way it is coded at the present, doesn't make use of MFC.
(I can't help but notice there is a CDataBase class and a CRecordSet
class in MFC).
Instead of doing this:
if FAILED(hr = CoCreateInstance(CLSID_SQLOLEServer, NULL, CLSCTX_INPROC_SERVER,
IID_ISQLOLEServer, (LPVOID*)&g_pSQLServer))
return FALSE;
if FAILED(hr = g_pSQLServer->Connect(m_ServerName, m_UserName, m_Pwd))
return FALSE;
if FAILED(hr = g_pSQLServer->GetDatabaseByName(m_DbName, &g_pDatabase))
return FALSE;
if FAILED(hr = g_pDatabase->ExecuteWithResults(csCreateTbl1, &ppQueryResult)) {
if (SCODE_CODE(hr) != 3701) //Cannot drop the...
return FALSE;
This could be the right way of doing it, or it can be one way of doing it.
Which is it?
mcontest@universal.com
Frank McGeough -- fm@synchrologic.com
Thursday, August 22, 1996
[Mini-digest: 4 responses]
At 09:28 AM 8/19/96 EDT, you wrote:
>Environment: MSVC 4.1 Windows95 Windows NT 3.51
>
>During the conception of an application here, I did the GUI work,
>and another programmer did the SQL work. This was fine with me,
>as I'm not an SQL expert. Now, this programmer is no longer with us,
>and as I look through his code, I'm questioning if it is implemented
>correctly. Basically, the user enters SQL statements, after which
>the app must verify the syntax. To verify the syntax, the app connects
>to an SQL server, creates a temporary table, and executes the statement.
>I'm assuming this is the correct way of doing it...
>The way it is coded at the present, doesn't make use of MFC.
>(I can't help but notice there is a CDataBase class and a CRecordSet
>class in MFC).
It sounds like a puzzling solution but since the actual problem that
you are attempting to solve is not described let's assume that it's
downright peachy for users to be typing in sql statements. I'm
supposing that the database that you're connecting to is set aside
for just the purpose of verifying the SQL statements typed in by
the user (if not, it would seem you have other problems -- like
the user could delete data that you don't want deleted). Also the
statement that you are allowing must be simple since you say that
the code is creating a single temporary table for the statement to
run against (i.e. it's not a join statement or an update with a
subquery, or any of the other fun SQL stuff). [and by the way, how
does the code verify that it's not this type of statement]. It may
be perfectly o.k. to connect to the db and execute the statement
to verify it's syntax. Personally, I would stay connected to the db
after the first statement, leave my temporary table, and simply execute
the statement so that the user is not sitting and waiting for the
connect and create table each time.
An alternative, if all you want to do is verify the syntax of the
statement you might want to check out www.sand-stone.com. They
offer parsers (including an SQL one). That way you don't have
to execute the statements against a database at all.
I guess my main point is not that you should or should not be
using MFC related database classes but that you need to get a
real good handle on what you want to accomplish with that part
of the code first.
Good luck.
------------------------------
Frank McGeough
e fm@synchrologic.com
v 404.876.3209 f 404.876.3809
-----From: Jeff Grossman
At 09:28 AM 8/19/96 EDT, you wrote:
[snip]
and as I look through his code, I'm questioning if it is implemented
Basically, the user enters SQL statements, after which
>the app must verify the syntax. To verify the syntax, the app connects
>to an SQL server, creates a temporary table, and executes the statement.
>I'm assuming this is the correct way of doing it...
>The way it is coded at the present, doesn't make use of MFC.
>(I can't help but notice there is a CDataBase class and a CRecordSet
>class in MFC).
>
>Instead of doing this:
>
>[snip]
>
>This could be the right way of doing it, or it can be one way of doing it.
>Which is it?
>
>mcontest@universal.com
>
I don't think the MFC classes provide SQL syntax verification. Besides,
the medthod your predecessor used only provides a go/no-go answer. I'm
not sure I'd call this a "clever hack" or simply baroque.
Anyway, I've seen advertized in MSJ, a parsing tool from
SandStone Technology
800-988-9023
I have not used it, but they claim to have a drop-in SQL parser. This
may be a better approach and it provides a way to give more meaningfull
feedback to your users.
Jeff Grossman
Excell Data Corporation
-----From: wei@cmgroup.com
It depends on your application. If only a few SQL query, it might not
be a bad way to do it. If there're lot's of database interactions,
the performance may suffer a lot. And some SQL database run-time
version does not support creating temporary table.
Use MFC save your time on maitaining code. But be warned there're
very bad bugs for 16 bit MFC odbc classes.
-----From: murugesh@mail.cswl.com
I think u'ver code uses SQL Server's Distrubuted Management Objects
(DMO) (It's nothing but an OLE Automation server through which u can
perform all server management). SQL-DMO provides an easy way for
server management. U can use this OLE Automation objects from VB or
VC++. I think even the SQL Server Enterprise Manager is written thru
SQL-DMO.
There is no need for going MFC classes. If u want to connect to
different types of DBMS, then u can make use of MFC ODBC classes.
Connecting to SQL server, u can do it three different ways.
1.Use ODBC (either API or MFC classes)
2.Use DB-Library
3.Use SQL-DMO OLE Automation Objects
I believe u'ver colleague had choose the 3rd option.
Regards
Murugesh SS
murugesh@cswl.com
Mark F. Fling -- mfling@stratacorp.com
Sunday, August 25, 1996
[Mini-digest: 2 responses]
>Environment: MSVC 4.1 Windows95 Windows NT 3.51
>
>During the conception of an application here, I did the GUI work,
>and another programmer did the SQL work. This was fine with me,
>as I'm not an SQL expert. Now, this programmer is no longer with us,
>and as I look through his code, I'm questioning if it is implemented
>correctly. Basically, the user enters SQL statements, after which
>the app must verify the syntax. To verify the syntax, the app connects
>to an SQL server, creates a temporary table, and executes the =
statement.
>I'm assuming this is the correct way of doing it...
>The way it is coded at the present, doesn't make use of MFC.
>(I can't help but notice there is a CDataBase class and a CRecordSet
>class in MFC).=20
If portability across different DBMS packages isn't an issue, and you =
plan on sticking with MS SQL Server (sound like it since you mention =
SQL-DMO), DB-Library's DBPARSEONLY option might make sense. Here's some =
code:
// Globals
int gnDbErr;
CString gstrDBErr;
void TestSQLStmt(LPCTSTR pszSqlStmt)
{ PDBPROCESS dbproc; // The connection with SQL Server.=20
PLOGINREC login; // The login information.=20
// Install user-supplied message-handling function.
dbmsghandle (msg_handler);
=20
// Initialize DB-Library.
dbinit ();
// Get a LOGINREC.
login =3D dblogin ();
DBSETLUSER (login, "my_login");
DBSETLPWD (login, "my_password");
DBSETLAPP (login, "example");
// Get a DBPROCESS structure for communication with SQL Server.=20
dbproc =3D dbopen (login, "my_server");
// Consult with the DB-Library programmer's guide
dbsetopt(dbproc, DBPARSEONLY, NULL);
// Parse, but do not execute the statement
if (dbcmd(dbproc, pszSqlStmt) =3D=3D SUCCEED)
{ // Try the statement, error exits will be called prior to return =
from dbsqlexec
if (dbsqlexec(dbproc ) =3D=3D FAIL)
{ CString strMsg;
// Error exit will be called=20
strMsg.Format("SQL statement error %i: %s", gnDBErr, gstrDBErr);
AfxMessageBox(strMsg);
}=20
}=20
dbclropt(dbproc, DBPARSEONLY, NULL);
dbexit();
=20
return
} // TestSqlStmtint
int msg_handler (PDBPROCESS dbproc, DBINT msgno, INT msgstate,
INT severity, LPCSTR msgtext, LPCSTR server,
LPCSTR procedure, DBUSMALLINT line)
{
gnDBErr =3D msgno;
gnstrEBErr =3D msgtext;
return (0);
}
This is only a rough outline of what actually needs to be implemented, =
but you get the idea.
------
Mark Fling
mfling@stratacorp.com
Strata Corporation
-----From: DevTrain@aol.com
I think that there may be a more elegant way to check the SQL syntax: use the
ODBC API function SQLPrepare (see also SQLExecute and SQLError). Note that
the ODBC Programmer's Reference states that "...some drivers cannot return
syntax errors...", but I have had no problem using either the Microsoft or
Intersolve drivers for Oracle 7, Paradox, SQL Server and Access.
Basically, just allocate a statement, call SQLPrepare with your SQL query
text and then check the return code for SQL_ERROR or SQL_SUCCESS_WITH_INFO to
see if there were any problems; if so call SQLError as often as needed,
otherwise call SQLExecute. Don't forget to free the statement.
| Вернуться в корень Архива
|