Solved

why do  not this SQL execute?

Posted on 2004-04-22
5
972 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In this article, I'll describe -- and show pictures of -- some of the significant additions that have been made available to programmers in the MFC Feature Pack for Visual C++ 2008.  These same feature are in the MFC libraries that come with Visual …
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 Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
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.

895 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

16 Experts available now in Live!

Get 1:1 Help Now