Solved

why do  not this SQL execute?

Posted on 2004-04-22
5
956 Views
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 .




0
Comment
Question by:chinaperrin
  • 2
5 Comments
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
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
0
 

Author Comment

by:chinaperrin
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?
0
 
LVL 39

Accepted Solution

by:
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\"");

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

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

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

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

   TRY
   {
         m_pDB->ExecuteSQL(...);
   }
   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.
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
       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);
SQLSetDescField(hIpd, 1, SQL_DESC_UNNAMED, SQL_NAMED, 0);

// assuming that szQuote has been appropriately initialized,
// execute
SQLExecute(hstmt);
------------------------------------------------------------------------------------------------------

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.

Alex
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

This is to be the first in a series of articles demonstrating the development of a complete windows based application using the MFC classes.  I’ll try to keep each article focused on one (or a couple) of the tasks that one may meet.   Introductio…
Introduction: Load and Save to file, Document-View interaction inside the SDI. Continuing from the second article about sudoku.   Open the project in visual studio. From the class view select CSudokuDoc and double click to open the header …
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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now