Calling an oracle package from VC++

[DevDog]

here is one for all you database gurus

I am writing in MS VC++ (MFC)

I need to call an oracle package sending a couple of variables and receiving one back....
this is what I have been given

The name of the stored procedure is:
FN_CONFIRM_POS,
the package name if needed is:

pkg_test.FN_CONFIRM_POS

details:
pkg_test.FN_CONFIRM_POS
PROCEDURE FN_CONFIRM_POS
(
v_string IN VARCHAR,
v_status OUT NUMBER,
v_found OUT NUMBER
);

I am using the ODBC SQL api in VC++6. Up to now I have used SQLExecDirect to call stored procedures on oracle and sybase databases with not too many problems but I am not sure where to start.

The clients have experience of calling from VB, so though they are trying to help I think the api I am using is lower level.

If anyone can give me a pointer to how I would format this call I would be very grateful. I guess I have to bind the variable somewhere ??

thanks for any suggestions

Jim

[Don]

Doesn't matter if its Oracle or anything else - ODBC will deal with this the same.

First declare yourself some memory variables which will hold the parameters you want to pass into the procedure.

SQLPrepare your SQL statement then...

Then you use a function called SQLBindParameter to bind input and output parameters to the parameters in your SQL statement.

Then you use SQLExecute to do it.

If your stored procedure returns a resultset you'll need to SQLBindCol variables to the columns in the resultset and iterate through them..

BTW: This is a HORRIBLE way of doing it. Time consuming and goes wrong easily and is difficult to debug.

I recommend creating yourself a generic class of your own which can deal with any SQL statement or stored procedure. Then you only have to get that right once and you can just use it everywhere.

I created a little jobby I call CSnapshot to do all that stuff for me...

[DevDog]

cheers Don just the pointer I needed - now for a quick while I code it up ....

I have a sql class that I set up for all the other DB stuff I do so i'll add it in there.

[Don]

Let me know how it turned out...I'll worry else....

[DevDog]

will do ... its all compiled and emailed off ready for testing in the morning ....

BTW well done on the IAM front ...
though on the computer front I am not sure I would want my coding style observed too much

[DevDog]

not working yet.

They had a go yesterday afternoon - I am still waiting for the error message / log info ... I think I might have made a mess of one of the SQLBindParameter calls. the error message on the SQLExecute has something to do with invalid variable name/number.

[ATG]

Dumb question probably, but have you not got some kind of ODBC source locally that you can test your code against ... e.g. SQL Server, Jet?

[Don]

/*------------------------------------------------------------*/
long DMODBC_GetNextCpyKey(hEnv,hDbc,MSPProKey,MSPVerKey)
/*------------------------------------------------------------*/
HENV hEnv;
HDBC hDbc;
LPSTR MSPProKey;
LPSTR MSPVerKey;
{

HSTMT hStmt1;
SDWORD cbMSPProKey;
SDWORD cbMSPVerKey;
SDWORD cbCpyKey;
RETCODE ret;

long nCpyKey;
BOOL bError;

static char * szPrjSelect = "SELECT MSPCurrCpy FROM MSPProjVer "
"WHERE MSPProKey = ? AND MSPVerKey = ?";



/* assume we fail */

bError = TRUE;
nCpyKey = -1L;

/* string parameters are NULL Terminated */

cbMSPProKey = SQL_NTS;
cbMSPVerKey = SQL_NTS;

/* allocate a new statement handle */

if(SQLAllocStmt(hDbc,&hStmt1) != SQL_SUCCESS)
{

return(-1L);
}

/* compile SQL statement */

ret = SQLPrepare(hStmt1,szPrjSelect,SQL_NTS);

if(!ODBC_SUCCESS(ret)) goto tidyup;

/* set the parameters */

ret = SQLBindParameter(hStmt1,1,SQL_PARAM_INPUT,
SQL_C_CHAR,SQL_CHAR,255,0,MSPProKey,255,&cbMSPProKey);

if(!ODBC_SUCCESS(ret)) goto tidyup;

ret = SQLBindParameter(hStmt1,2,SQL_PARAM_INPUT,
SQL_C_CHAR,SQL_CHAR,255,0,MSPVerKey,255,&cbMSPVerKey);

if(!ODBC_SUCCESS(ret)) goto tidyup;

/* execute the statement */

ret = SQLExecute(hStmt1);

/* bind the return variable the the results set column */

ret = SQLBindCol(hStmt1,1,SQL_C_LONG,&nCpyKey,0,&cbCpyKey);

/* fetch the result - populates the nCpyKey variable */

ret = SQLFetch(hStmt1);

/* no data found so this must be a new project set to 0 */

if(ret == SQL_NO_DATA_FOUND)
{
nCpyKey = 0L;
}
else if(!ODBC_SUCCESS(ret)) goto tidyup;

/* increment the key */

nCpyKey++;

/* we succeeded */

bError = FALSE;

tidyup:

if(bError)
{
DMODBCSQLErrHandler(hEnv,hDbc,hStmt1);
}

/* Free Statement */

ret = SQLFreeStmt(hStmt1,SQL_DROP);

/* it failed */

if(bError) return(-1L);
else return(nCpyKey);

} /* DMODBC_GetNextCpyKey() *

[Don]

Hope you find the code snippet useful - the function order is important as is the format of the SQL statement. You will need to read up on ODBC SQL syntax for stored procedures (all in the ODBC help).

[DevDog]

I had Sybase running on one of my machines till a couple of years ago - but I used it so rarely that I dumped it. It would probably take me couple of days to set it all up again - read the manauls (even finding them will be monumental task) Up till now I've probably been too lazy letting the client's IT departments supply me with their code to cut into mine. ... They only want me to make one call sending them a string and get a couple of results back...and of course as it is all for a test no one will be paying for it. LOL do I sound bitter, in a way its fun trying to work these things out when you only have half a clue what you are doing.

anyway all hints gratefully accepted

[DevDog]

ta again Don

I think I am supposed to send and receieve all in one call so I don't believe I should be using the SQLBindCol and SQLFetch calls but am not sure ...
here is a cutdown set of my code - I removed all the checks for clarity - I only get an error on the SQLExecute call

// setup variables
SQLRETURN retCode;
DBSql DbConn;
char sqlstr[256];
SQLCHAR sql_pages[1024];
SQLINTEGER sql_status=0;
SQLINTEGER sql_found=0;
SQLINTEGER cbPages=SQL_NTS,cbFound=0,cbStatus=0;
SQLINTEGER nPagesLen=csPageColour.GetLength();
strcpy(sql_pages,"41414141414141444414141414141414");
strcpy(sqlstr,"begin FN_CONFIRM_IMPOSITION(? ? ?); end;");

retCode=SQLPrepare(hstmt,(unsigned char*)sqlstr,SQL_NTS);
retCode=SQLBindParameter(DbConn.hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,SQL_CHAR, nPagesLen, 0, sql_pages, 0, &cbPages);
retCode=SQLBindParameter(DbConn.hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_SSHORT,SQL_INTEGER, 0, 0, &sql_status, 0, &cbStatus);
retCode=SQLBindParameter(DbConn.hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_SSHORT,SQL_INTEGER, 0, 0, &sql_found, 0, &cbFound);

Cheers Jim

[Don]

The reason you only get an error on the SQLExecute call is that is when the actual communication with the database happens. Since your stored proc doesn't return a resultset you don't need all that SQLBindCol and SQLFetch business.

What's the exact error message?

Oh..and are you certain that the syntax of your SQL statement is right... I can't say I've ever put "begin" or "end" in and usually seperate parameters with commas....

BTW: you can get a FREE Oracle database on evaluation from oracle. Downloadable I think... you could get that and have a better platform for diagnosis...

[DevDog]

thanks Don
the 'begin' and 'end' bits were insisted upon by another client using a similar DB - I've have purposefully kept that statement - the one that goes into the SQLPrepare call in an xml file that the client can change. I'll get them to try it on monday without the begin end bits.

I don't know the exact error message - they have as yet not sent it to me and the guy doing things their end is at another client site today and he has me working on other non db things, and blow me if he can't remember the error no !!! and he forgot to email it to me last night before he left - will probably know more on monday.

Thanks again for you interest

[DevDog]

just receieve my log - here is the error message
[Microsoft][ODBC driver for Oracle][Oracle]ORA-01036: illegal variable name/number

will now go and hunt for it
Jim

[ATG]

also, are those semi-colons in the SQL correct?

The "begin" and "end" should be fine, but probably then needs an "Exec" to call the stored proc. Also I don't think you want any parentheses around the parameters ... but this may be an ODBC thing.

My guess is your SQL string should look like

"Begin Exec [StoredProcName] ?,?,? End"

[DevDog]

thanks again guys
I'll post how it goes on monday hopefully

[devdog]

Woopeee its works

Thanks again for all your advice

Jim

[Don]

devdog said:

Woopeee its works Thanks again for all your advice Jim

Well done, mate.

Now...what did that SQL look like and what the problem? You can't not let us know.....

[devdog]

LOL Don you asked for it - AND no narky comments on my style
Its a bit long winded but allows me to run the non db part to make sure that is working ok.

the original sql statement was:
begin FN_CONFIRM_IMPOSITION('?','?','?'); end;

the one that works is:
{ call PKG_ADVERTISING.FN_CONFIRM_IMPOSITION(?,?,?) }
The one thing I never got an answer to was in the 2nd and 3rd SQLPrepare statements there is '22' ie SQL_C_SSHORT, SQL_INTEGER, 22 ,
this was the clients advice - not sure why - only thing i saw was in the header file was #define SQL_API_SQLSETPARAM 22 ????

ooooh what a long post.
cheers anyway
jim

BOOL COpenDlg::CheckForTemplateOnDB(CString csPagFileName)
{
SUfile pagFile;
pagFile.suToDo=PAG;
char *pagRec;
CString csColourCodes,csPageColour;
pagFile.otherFileName=csPagFileName;
PagRec *pRec;
short ret;
if (pagFile.Load()!=1)
{
return FALSE;
}
long foundRec=pagFile.GetFirstRO(&pagRec);
while (foundRec)
{
pRec=(PagRec *)pagRec;
csPageColour="1";
if (pRec->color==1)
csPageColour="2";
else if(pRec->color==2)
csPageColour="3";
else if(pRec->color==3)
csPageColour="4";
csColourCodes+=csPageColour;
foundRec=pagFile.GetNextRO(foundRec,&pagRec);
}
ret=csColourCodes.GetLength();
csPageColour=csColourCodes.Left(ret/2);
// Get database paramaters from dbparams.xml
CString csFile,csTag,csStatement,csField;
long lStartPos=0;
SUfile dbParamsFile;
dbParamsFile.otherFileName=CurSets.dataPath+"univdbparams.xml";
dbParamsFile.otherStrNo=IDS_DBPARAMS_FILE;
dbParamsFile.suToDo=OTHER;
dbParamsFile.showEmptyFnF=1;
if (dbParamsFile.Load()!=1)
return FALSE;
dbParamsFile.buffer[dbParamsFile.lof]=0;
Convert.BuffToString(&csFile,dbParamsFile.buffer,dbParamsFile.lof,TRUE);
egDBName="DBNAME";
egDBUserID="DBUSER";
egDbPassWord="DBPASS";
if (Convert.GetTaggedField(csFile,&csField,"DBNAME",&lStartPos)==TRUE)
{
egDBName=csField;
}
if (Convert.GetTaggedField(csFile,&csField,"DBUSERID",&lStartPos)==TRUE)
{
egDBUserID=csField;
}
if (Convert.GetTaggedField(csFile,&csField,"DBPASS",&lStartPos)==TRUE)
{
egDbPassWord=csField;
}
if (Convert.GetTaggedField(csFile,&csField,"DBSTATE",&lStartPos)==TRUE)
{
csStatement=csField;
}
// NOW check on the database
SQLRETURN retCode;
DBSql DbConn;
char sqlstr[256];
CString cs,cs1,s1,s2;
// setup the sql string
SQLCHAR sql_pages[1024];
SQLINTEGER sql_status=0;
SQLINTEGER sql_found=0;
SQLINTEGER cbPages=SQL_NTS,cbFound=0,cbStatus=0;
SQLINTEGER nPagesLen=csPageColour.GetLength();
Convert.StringToBuff(&csPageColour,(char*)sql_pages,csPageColour.GetLength(),TRUE);
// sql_found 0= fail 1=success
// sql_status 0= dbase failure 1=success
// 1 & 1 = valid template found
BOOL bWriteLogFile=FALSE,bRet=FALSE;
if (OutputMenu.DbLog)
bWriteLogFile=TRUE;
// Setup Log Path
if (CurSets.importPath.GetLength())
DbConn.LogPath=CurSets.importPath;
else
DbConn.LogPath=CurSets.dataPath+"dumpbkp";
// Open Connect to Database
BOOL bTesting=FALSE;
#ifdef _DEBUG
bTesting=TRUE;
#endif
retCode=123; // 123 is initial value for testing (see log file)
if (!bTesting)
{
retCode=DbConn.Open(egDBName,egDBUserID,egDbPassWord,UserID);
}
if (bWriteLogFile)
{
DbConn.DoLog(IDS_DB_CONNECT,retCode);
}
if (retCode==-1 && !bTesting)
{
DbConn.Close();
return FALSE;
}
Convert.StringToBuff(&csStatement,sqlstr,csStatement.GetLength(),TRUE);
if (!bTesting)
{
DbConn.PrepareStatement(sqlstr,0);
}
if (bWriteLogFile)
{
Convert.BuffToString(&cs1,sqlstr,strlen(sqlstr),TRUE);
cs="Prepare SQL = "+cs1;
DbConn.DoLog(cs);
}
if (!bTesting)
{
retCode=SQLBindParameter(DbConn.hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, nPagesLen, 0, &sql_pages, 0, &cbPages);
if (retCode==SQL_ERROR)
{
DbConn.DbError(SQL_HANDLE_STMT);
}
}
if (bWriteLogFile)
{
Convert.BuffToString(&cs1,sqlstr,strlen(sqlstr),TRUE);
cs.Format("SQLBindParameter 1 - retCode=%d",retCode);
DbConn.DoLog(cs);
}
if (!bTesting)
{
retCode=SQLBindParameter(DbConn.hstmt, 2, SQL_PARAM_INPUT_OUTPUT, SQL_C_SSHORT, SQL_INTEGER, 22, 0, &sql_status, 0, &cbStatus);
if (retCode==SQL_ERROR)
{
DbConn.DbError(SQL_HANDLE_STMT);
}
}
if (bWriteLogFile)
{
Convert.BuffToString(&cs1,sqlstr,strlen(sqlstr),TRUE);
cs.Format("SQLBindParameter 2 - retCode=%d",retCode);
DbConn.DoLog(cs);
}
if (!bTesting)
{
retCode=SQLBindParameter(DbConn.hstmt, 3, SQL_PARAM_INPUT_OUTPUT, SQL_C_SSHORT, SQL_INTEGER, 22, 0, &sql_found, 0, &cbFound);
if (retCode==SQL_ERROR)
{
DbConn.DbError(SQL_HANDLE_STMT);
}
}
if (bWriteLogFile)
{
Convert.BuffToString(&cs1,sqlstr,strlen(sqlstr),TRUE);
cs.Format("SQLBindParameter 3 - retCode=%d",retCode);
DbConn.DoLog(cs);
}
if (!bTesting)
{
retCode=DbConn.ExecSQL();
}
if (bWriteLogFile)
{
cs.Format("SQLExecDirect - Ret=%ld - Status=%ld Found=%ld Txt=%s",retCode,sql_status,sql_found,csPageColour);
DbConn.DoLog(cs);
}
if (retCode!=0 || sql_status==0)
{
csErr1.LoadString(IDS_OPENPLAN_DBCHECK_ERR1);
csErr2.LoadString(IDS_OPENPLAN_DBCHECK_ERR2);
Error err;
err.General(&csErr1,&csErr2);
}
else
{
if (sql_found==0)
{
csErr1.LoadString(IDS_OPENPLAN_DBCHECK_ERR3);
csErr2.LoadString(IDS_OPENPLAN_DBCHECK_ERR4);
Error err;
err.General(&csErr1,&csErr2);
}
else
{
csErr1.LoadString(IDS_OPENPLAN_DBCHECK_OK);
Error err;
csErr2="";
err.Msg(&csErr1,&csErr2);
bRet=TRUE;
}
}
if (bWriteLogFile)
{
DbConn.DoLog(IDS_DB_SQLEXEC,retCode);
}
if (!bTesting)
{
retCode=DbConn.RenewStatement();
}
if (bWriteLogFile)
{
DbConn.DoLog(IDS_DB_RENEW_STAT,retCode);
}
if (!bTesting)
{
DbConn.Close();
}
if (bWriteLogFile)
{
DbConn.DoLog(IDS_CLOSE_DB);
}
return bRet;
}

>> Edited by devdog on Monday 30th June 16:08

[Don]

I can't believe I just read that...

I'm off now to get a life...

Glad its working, mate...