ADO stored procedures - Parameters - type casting - simplification 
Author Message
 ADO stored procedures - Parameters - type casting - simplification

Ok, I have lots of questions about stored procedures and parameters.  I
don't know where else to go other than this forum.  Thank you everyone for
helping!

QUESTION 1:
How could I simplify the code to call a stored procedure in VC++/ ASP?  I
really really don't like the method of calling a stored procedure provided
to me.  Can I write decent applications withoug ever using an output
parameter ever from a stored procedure?? (just using recordsets instead) :)

I've tried to simplify this code a little, however it seems it takes me 11
lines of code to get the data from a string buffer, into a _bstr, into a
variant, then into a command parameter.  Am I missing something?  11 lines
of code seems to be alot of code.

SOLUTION 1.A?????
I could just use a string printf, then call the strSQL.. similar to building
a request in ASP??  It is only one line of code, and it is very readable!!
For example:

sprintf (strSQL, "exec insertHistoryRecord '%s', '%s', '%s'\n", symbol,
date, price);
pCmd->Execute (strSQL, NULL, adCmdText);  // syntax may not be entirely
correct, just guessed there..

However, I've read that I'll get an empty/closed recordset returned back to
me that I don't want.  I want my code to be efficient, yet not super time
consuming to write.  Does adExecuteNoRecords really do the trick?

I think this is the option I am leaning towards for ALL STORED PROCEDURE
calls across my applications, web sites, etc.. It is just SO FRIGGING easy
to read..  in ASP I use:

strSQL = "exec insertHistoryRecord " & SQLFormat(array(a,b,c,d,e))
conn.execute (strSQL)
//sqlformat puts quotes where needed .. makes the code easy to read and
change .. and adds security if someone passes me bogus values on purpose.

SOLUTION 1.B??????
Ok, for VC++ I could create a complier macro to write the code for me?  I'm
not big into compiler macros, as I feel they can make code unreadable to
someone trying to maintain the existing code. e.g. I have a developer who
refuses to use request.querystring ("myVariable") in asp, and uses his
custom param ("myVariable", "myDefault") because he used to program in Cold
Fusion.

For ASP, I would like to just stick to 1.A.. I don't want to confuse my ASP
programmers any more than I need to.

SOLUTION 1.C????
Just use 11 * N  + X lines of code to call a stored procedure, where N = the
number of parameters.  This really is the way to go... its 400% faster, even
though I have to create N parameter objects, Variants, etc.. !! ?? bull {*filter*}
??

///////////////////////////////////////////////////////////
//                                                       //
//      INSERT HISTORY RECORD FUNCTION                   //
//                                                       //
///////////////////////////////////////////////////////////
void insertHistoryRecord (char *symbol, char *date, char *price)
{

 // ok - how about here?
 //printf ("exec insertHistoryRecord '%s', '%s', '%s'\n", symbol, date,
price);

   HRESULT hr = S_OK;

   // Define ADO object pointers.
    // Initialize pointers on define.
    // These are in the ADODB::  namespace.
   _CommandPtr     pCmd  = NULL;

try
{
   //Define a command object for a stored procedure.

   TESTHR(pCmd.CreateInstance(__uuidof(Command)));

   pCmd->ActiveConnection = pConnection;
   pCmd->CommandText = "history..spInsertHistoryRecord";
   pCmd->CommandType = adCmdStoredProc;
   pCmd->CommandTimeout = 15;

   // yup -- the data gets put into strings, then variants, then
parameters.. ahhh!!.. ?!?!
   // when will the overhead/madness stop?!?!?
   // will the data ever make it to the database.?
   // tune in later. same bat chanel , same bat time.
   // variants? in c++ .. too much fun
   VARIANT vtSymbol ;
    _bstr_t bstrSymbol = symbol;
 vtSymbol.vt = VT_BSTR;
 vtSymbol.bstrVal = bstrSymbol;
   _ParameterPtr    pPrmSymbol = NULL;
   TESTHR(pPrmSymbol.CreateInstance(__uuidof(Parameter)));
   pPrmSymbol->Type = adVarChar;
   pPrmSymbol->Size = strlen(symbol);  // -- OH YEAH.. what is the strlen
function for a variant ? :)
   pPrmSymbol->Direction = adParamInput;
   pPrmSymbol->Value = vtSymbol;
   pCmd->Parameters->Append(pPrmSymbol);

   VARIANT vtDate ;
    _bstr_t bstrDate = date;
 vtDate.vt = VT_BSTR;
 vtDate.bstrVal = bstrDate;
   _ParameterPtr    pPrmDate  = NULL;
   TESTHR(pPrmDate.CreateInstance(__uuidof(Parameter)));
   pPrmDate->Type = adVarChar;
   pPrmDate->Size = strlen(date);
   pPrmDate->Direction = adParamInput;
   pPrmDate->Value = vtDate;
   pCmd->Parameters->Append(pPrmDate);

   VARIANT vtPrice ;
 _bstr_t bstrPrice = price;
 vtPrice.vt = VT_BSTR;
 vtPrice.bstrVal = bstrPrice;
   _ParameterPtr    pPrmPrice = NULL;
   TESTHR(pPrmPrice.CreateInstance(__uuidof(Parameter)));
   pPrmPrice->Type = adVarChar;
   pPrmPrice->Size = strlen(price);
   pPrmPrice->Direction = adParamInput;
   pPrmPrice->Value = vtPrice;
   pCmd->Parameters->Append(pPrmPrice);

   //Create a recordset by executing a command.
   pCmd->Execute(NULL,NULL,adCmdStoredProc);

Quote:
}

catch(_com_error &e)
{
   // Notify the user of errors if any.
   _bstr_t bstrSource(e.Source());
   _bstr_t bstrDescription(e.Description());

    PrintProviderError(pConnection);
     printf("Source : %s \n Description : %s
\n",(LPCSTR)bstrSource,(LPCSTR)bstrDescription);

Quote:
}
}



Fri, 25 Jun 2004 08:47:00 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. ado recordsets from stored procedures, stored procedures have input parameters

2. ado recordsets from stored procedures, stored procedures have input parameters

3. Casting data type inside Stored Procedure

4. How to bind BLOB parameter of a stored procedure using Parameter Object of ADO

5. Cursor Type Output Parameter for SQL Stored Procedure

6. text data type support for output parameter for stored procedure

7. Example of stored procedure with OUTPUT parameter of type TEXT

8. char data type parameter in stored procedure

9. passing table data type as parameter to a Stored procedure

10. pass a TABLE data type parameter to a stored procedure

11. PASSING RECORD TYPE PARAMETERS TO STORED PROCEDURE WITH JAVA

12. How to Register Stored Procedure Out Parameter for Array Type


 
Powered by phpBB® Forum Software