Learn how to a build a cloud-first strategyRegister Now


why do  not this SQL execute?

Posted on 2004-04-22
Medium Priority
Last Modified: 2013-11-20
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 .

Question by:chinaperrin
  • 2
LVL 39

Expert Comment

ID: 10899412
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

Author Comment

ID: 10915780
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?
LVL 39

Accepted Solution

itsmeandnobodyelse earned 120 total points
ID: 10917225
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.


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: Dynamic window placements and drawing on a form, simple usage of windows registry as a storage place for information. Continuing from the first article about sudoku.  There we have designed the application and put a lot of user int…
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

810 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