why do not this SQL execute?

I design a program with vc++(MFC ODBC) ,which can config the parameter of sybase database. For example,
create  the sybase dbcc database(command line inISQL:crecreate database dbccdb on dbccdb_data=13 log on dbccdb_log=2)
,configure the cache (command line in ISQL:sp_cacheconfig dbccdb,'1280K'),
configure the number of process(command line in ISQL:sp_configure "number of worker processes",2).
I  call the CDatabase::ExecuteSQL(str) that do it.
when str is the express(" create database dbccdb on dbccdb_data=13 log on dbccdb_log=2),the ExecuteSQL ok .
but the str is assigned "sp_cacheconfig dbccdb_cache,'1280K'", I can't get the result that I want.
why ?
when I call CDatabase::ExecuteSQL(), who do the sql sentence belong to ,the standard sql or the sybase sql?
I want to know the answer?
thank you .

Who is Participating?
I found something regarding calls to stored procedures but maybe first you should try the following statements where m_pDB is a pointer to the CDatabase object

   m_pDB->ExecuteSQL("execute sp_cacheconfig  ,\"1280K\"");

   m_pDB->ExecuteSQL("execute sp_cacheconfig  dbccdb_cache,\"1280K\"");

   m_pDB->ExecuteSQL("execute sp_cacheconfig  ,'1280K'");

   m_pDB->ExecuteSQL("execute sp_cacheconfig  dbccdb_cache,'1280K'");

Put them in a TRY CATCH block where you may evaluate the exception - if any

   CATCH (CDBException, e)
           // The error code is in e->m_nRetCode

If all didn't work, you may call the stored procedure 'sp_cacheconfig' by using the call interface.

I found this in MSDN:

Calling Stored Procedures
The following ODBC shorthand syntax is used for calling stored programs. It supports SQL Server procedures, and Oracle procedures, functions, and packages.

{?=} call procedure_name[(parameter(s))]}

The optional "?=" syntax is used to capture the return value for an Oracle function or a SQL Server procedure. The parameter syntax is used to pass and return values to and from the called program. In most situations, the same syntax can be generically applied to Oracle and SQL Server applications. This program code works with either DBMS:

SQLExecDirect(hstmt1,(SQLCHAR *)"{? = call DEPT_ADMIN.DELETE_DEPT(?)}",SQL_NTS);

I can't tell whether Sybase database resp. Sybase ODBC driver to that database will accept this statement. However as SQL Server originally is a Sybase DBMS there is a good chance for that. SQLExecDirect is the native ODBC interface of CDatabase::ExecuteSQL.

To call native ODBC functions you need CDatabase::m_hdbc or CRecordset::m_hstmt.  I found this in MSDN giving an example how a stored procedure could be called and how a 'named' argument could be passed to it.

As an example of named parameters, suppose a SQL Server stored procedure has been defined as follows:

CREATE PROCEDURE test @title_id int = 1, @quote char(30) AS <blah>

In this procedure, the first parameter, @title_id, has a default value of 1. An application can use the following code to invoke this procedure such that it specifies only one dynamic parameter. This parameter is a named parameter with the name “@quote”.

// prepare the procedure invocation statement.
SQLPrepare(hstmt, "{call test(?)}", SQL_NTS);

// populate record 1 of ipd.
       30, 0, szQuote, 0, &cbValue);

// get ipd handle and set the SQL_DESC_NAMED and SQL_DESC_UNNAMED fields for record #1.
SQLGetStmtAttr(hstmt, SQL_ATTR_IMP_PARAM_DESC, &hIpd, 0, 0);
SQLSetDescField(hIpd, 1, SQL_DESC_NAME, "@quote", SQL_NTS);

// assuming that szQuote has been appropriately initialized,
// execute

Actually not easy to unterstand...

We could override virtual member function CDatabase::BindParameters that gets called with CDatabase::ExecuteSQL to bind the input parameters.
The question is now how to pass the parameters: by number or by name. First try it by number:

void CMyDatabase::BindParameters(HSTMT hstmt)
   char szCache[] = "dbccdb_cache";
   int   cbCache    = sizeof(szCache);
   char szCacheSize[] = "1280K";
   int   cbCacheSize   = sizeof(szCacheSize);
   SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
       cbCache-1, 0, szCache, 0, &cbCache);
   SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
       cbCacheSize-1, 0, szCache, 0, &cbCacheSize);

If that didn't work, you may test a 'named' binding using "cache_size" as name following the MSDN example above.

Hope, that helps. I'll check Google to getting a better example code.

With ExecuteSQL you invoke an SQL statement that is interpreted by the Sybase ODBC driver you've assigned when creating the Datasource in Windows System Settings. So, it is most likely an Sybase SQL Statement that is expected (if you don't use any old generic ODBC driver).

However, "sp_cacheconfig dbccdb_cache,'1280K'" isn't an SQL statement. It looks like a preprocessor statement or a configuration file entry and i bet you can't process it at an Sybase SQL Command Window either. Check your documentation how you can invoke that configuration option. Maybe it is SQLSetConnectAttr(..) where you can set it intreacively or you have to configure ODBC Datasource using odbcad32.exe.

Regards, Alex
chinaperrinAuthor Commented:
Thank you.
In microsoft ISQL_w (belong to Microsoft SQL Server 6.5) window ,the command (i.e. sp_cacheconfigcan,sp_configure) work well. It can the result which my program execute a certain funtion.   I don't  invoke SQLSetConnectAttr(..) before I invoke Open(databasesourcename). but the program can execute "sp_configre "number of worker processes",2 " with ExecuteSQL(...) sentence(the result I can see throung ISQL(microsoft)).
How can I get the result of calling ExecuteSQL(...),
it tell me the result of ExecuteSQL(...) whether it succeed or fail.
How can I set SQLSetAttribute() ,I can run "sp_cacheconfig...".
My ODBC is odbcad32.exe and my database is sybase 12.5.
I use a program named Query Tool (using ODBC)
(download from www.codeguru.com ,MFC)
the "spc_cacheconfig" don't work. Does  it mean I can't run "sp_cacheconfig" in my program?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.