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

[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.

[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.

[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

[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

[DevDog]

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

[devdog]

Woopeee its works

Thanks again for all your advice

Jim

[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

[devdog]

don't I at least get a coloured star
now what shall I do for the rest of the afternoon