why do  not this SQL execute?

Posted on 2004-04-22
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 ,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 30 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Introduction: Ownerdraw of the grid button.  A singleton class implentation and usage. Continuing from the fifth article about sudoku.   Open the project in visual studio. Go to the class view – CGridButton should be visible as a class.  R…
Introduction: The undo support, implementing a stack. Continuing from the eigth article about sudoku.   We need a mechanism to keep track of the digits entered so as to implement an undo mechanism.  This should be a ‘Last In First Out’ collec…
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.

733 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