Executing Stored Procedure from ODBC, is possible?

[Edimator]

I need to know if is possible execute a Stored procedure from a Oracle Database 8.0.5, in ColdFusion 5 professional.

I try with ODBC driver from Microsoft & Oracle, but dreamwaver told me
"Error calling GetProcedures, Stored Procedures for Oracle are not supported"

Any kind of help is really appreciated.

 

[ManjulaMadhu]

can you sepcify the sample of your code : how you are executing the procedure?

 

[Edimator]

Thanks for your interest.

Coldfusion have a cftag to execute stored procedures with this syntax:

<CFStoredProc procedure = &quot;get_subscriber_info&quot;
datasource = &quot;connORACLE&quot;
username = &quot;theUser&quot;
password = &quot;thePassword&quot;
>

Is possible execute a Stored Procedure from ODBC Driver in another way without coldfusion?, for example via ADO and how is the syntax.

 

[ManjulaMadhu]

yes, you can execute stored procedure with ADO. I have got some sample code here from codeguru.

{
CString strTmp;

CString m_sdatasource; // Data source name
CString m_sUserID; // User Id
CString m_sPassword; // Password

// GET the above values from the user
//Without creating Datasource we can use database by the following code
/* strTmp.Format( &quot;driver={sql server};&quot;
&quot;server=%s;&quot;
&quot;Database=%s;&quot;&quot;UID=%s;&quot;&quot;PWD=%s;&quot;,
m_server,m_sdatabase,m_sUserID,m_sPassword );*/

strTmp.Format( &quot;dsn=%s;&quot;&quot;UID=%s;&quot;&quot;PWD=%s;&quot;,m_sdatasource,m_sUserID,m_sPassword );
_bstr_t bstrSQLServerConnect;
_bstr_t bstrProc =( L&quot;sp_StartByteImport&quot; );; //Stored procedure name
_variant_t Final;
bstrSQLServerConnect = (LPCTSTR) strTmp;
m_status=&quot;Empty File&quot;;
_ConnectionPtr Conn1; // connection object pointer
_CommandPtr Cmd1; // command object pointer
_RecordsetPtr Rs1; // recordset object pointer
bool bvalid = false;
try
{
Conn1.CreateInstance( __uuidof( Connection ) ); // Instantiating connection object
Conn1->ConnectionString = bstrSQLServerConnect; // giving the sqlconnection
Conn1->Open( bstrEmpty, bstrEmpty, bstrEmpty ); // open the connection object
Cmd1.CreateInstance( __uuidof( Command ) ); // creating command object
Cmd1->ActiveConnection = Conn1; // giving the connection handle
Cmd1->CommandText = _bstr_t( bstrProc ); // passing the stored procedue
Cmd1->CommandType = adCmdStoredProc; // type
Cmd1->Parameters->Refresh(); // passing string value as argument to stored procedure
Cmd1->Parameters->Item[ _variant_t( (long) 1 ) ]->Value = _variant_t( (LPCTSTR)m_sfilename );
Rs1 = Cmd1->Execute( &vtEmpty, &vtEmpty2, adCmdUnknown ); // executing the stored procedure and storing the recordset value
bvalid = true;
Final = Rs1->Fields->GetItem( _variant_t( 0L ) )->Value; // getting the first column value of the result row
strTmp.Format( &quot;%s&quot;, CrackStrVariant( Final) ); // to see the value
// put your code to see all column values