locked
query table-value function from ADO in C++ (not .net) RRS feed

  • Question

  • Hey everybody!

    I create a function to call stored procedure, and query a view, but I am having troubles with quering table-value function.
    I looked for an example on the net on how to do it, but I couldn't find any (I find only .net examples :-) )

    Can anyone direct me to an example, or write a small sample?



    Thanks a lot in advance!!!
    Tuesday, April 22, 2008 4:01 PM

Answers

  • Actually it looks like I found a way to get the parameters.

    Using VB-code since it is easier for me to play with it and look at the structures immediately for the same UDF I used above:

    Dim cn As New ADODB.connection
    Dim param1 As ADODB.Parameter

    cn.ConnectionString = "provider=sqloledb;integrated security=sspi;Data Source=.; initial catalog=master"
    cn.Open
    Set cmd = CreateObject("ADODB.Command")

    cmd.CommandText = "f_test"
    cmd.CommandType = adCmdStoredProc
    cmd.ActiveConnection = cn

    cmd.Parameters.Refresh

    REM at this point you will get the parameters collection starting with the bogus return value
    REM the command text will be like: "{?=call f_test(?)}"
    REM this is not what we want so we are going to save the parameter we need

    Set param1 = cmd.Parameters(1)

    REM and reset the command text to use it as UDF

    cmd.CommandText = "select * from f_test(?)"
    cmd.CommandType = adCmdText

    REM at this point parameters collection is destroyed, so we need to put the parameter back

    cmd.Parameters.Append param1

    Debug.Print param1.Name, param1.Type
    REM prints @x 3

    param1.Value = 25

    Set rs = cmd.Execute

    Do While Not rs.EOF
    For i = 0 To rs.Fields.Count - 1
    Debug.Print rs(i).Name, rs(i).Value
    Next i
    rs.MoveNext
    Loop


    Wednesday, May 14, 2008 5:24 AM

All replies

  • Hey,

    The example shows how to use stored procedure, and not table value function.
    stored procedures return a scalar result (integer), and table value function (parameterized query) returns a table.

    In any way I tried what it says and I got _com_error with:
    "The request for procedure 'get_alert' failed because 'get_alert' is a table valued function object."

    I also tried to play with it a little bit, and it seems that only if I set the command object to adCmdStoredProc, I can get to:
    ptrCom->Parameters->Item[ _bstr_t(L"@id") ]->Value = 1;

    if I running this line when the prtCom is not set to adCmdStoredProc, error is being thrown.

    Any other ideas ???


    Thanks again!!!
    Tuesday, April 22, 2008 5:58 PM
  • Hello Green,

    After calling the stored procedure to query a view, you will get a recordset from SQLServer. So, do you want to know how to get the value from the recordset.

    If it is yes, you may find a sample from msdn http://support.microsoft.com/kb/184397

    I list the C++ sample here.

     

    try
    {
       _RecordsetPtr spRS(__uuidof(Recordset));
       _ConnectionPtr spConn(__uuidof(Connection));

       // Connect and get recordset.
       spConn->Open(OLESTR("dsn=SQLServer"),OLESTR("sa"),
                    OLESTR(""), -1);

       spRS->CursorLocation = adUseClient;
       spRS->Open(OLESTR("select * from Table1"),
         spConn.GetInterfacePtr(),
           adOpenForwardOnly, adLockBatchOptimistic, -1);

       // Disassociate the connection from the recordset.
       spRS->PutRefActiveConnection(NULL);

       // Change the value.
       spRS->Fields->Item[0L]->Value = OLESTR("NewValue"); << here you may know how to get the data from the table.

       // Re-connect.
       spRS->PutRefActiveConnection(spConn);

       // Send updates.
       spRS->UpdateBatch(adAffectAll);

       spRS->Close();
       spConn->Close();

    }
    catch( _com_error e)
    {
       // Do Exception handling.
    }
      

     

    Wednesday, April 23, 2008 2:45 AM
  • Hey! :-)


    It's not exactly what I meant.......

    I want to query a view, but to pass parameters to this view,
    but in SQL, you cannot place parameters in view. For that reason there is a "table-value function".

    so I can perform something like this:
    SELECT * FROM myFunc ('param1', 'param2', etc.)


    The thing is, that I can't find a way to use this table-value function from ADO.




    THANKS AGAIN for your help!!!
    Wednesday, April 23, 2008 7:07 AM
  • Can you treat it as a parameterized query?
    Do you have some working code which performs select from a table?

    I would expect that if you prepare a staetment like:

    SELECT * FROM myFunc(?, ?, etc.)

    and supply the corresponding parameters it should work.

    Does it fail for you?

    Tuesday, April 29, 2008 6:20 AM
  • Hey,

    This kind of query does fail for me.
    The only kind of query that does work for me is using RecordsetPtr's open() function.
    Instead of just writing the name of the table/view, I'm inserting the parameters as well:

    "<table/view> (param1, param2... etc)"

    This takes some string manipulation, and it works, but I prefer to use the Parameter class to add the function. It seems "more correct" to do it this way, and it allows me to validate the parameters without writing my own validating function...


    When I tried to use "?", it ignored the question marks. only when I placed it like this:
    select * from <table> where something > ? (or some other kind of boolean expression), it worked for me...



    Thanks again for your help!

    Tuesday, April 29, 2008 6:47 AM
  • How do you supply the parameters?


    I think that provider might have a limitation such as it cannot derive parameters from a "FROM" clause.

    So something like pCmd->Parameters->Refresh() will most likely fail.
    However I believe you should be able to create parameters yourself using CreateParameter from the command object and Append on a parameters collection.

     

    There is a sample for a stored procedure at http://msdn2.microsoft.com/en-us/library/ms677589(VS.85).aspx.

    Tuesday, April 29, 2008 7:11 PM
  • Hey

    I tried it, doesn't work......


    Tuesday, April 29, 2008 7:25 PM
  • What doesn't?

    I have created the following UDF:

     create function f_test(@x int) returns table as
     return select @x as arg, @x*@x as square


    And here is an ADO C++ code to get results from it:
    Code Snippet

    int __cdecl main(int argc, char * argv[])
    {
        CoInitialize(NULL);
        BSTR bstrCmdText;
        try
        {
            bstrCmdText = L"select * from f_test(?)";
    
            _ConnectionPtr  pConnection  = NULL;
            pConnection.CreateInstance(__uuidof(Connection));
            _bstr_t strCnn("Provider='sqloledb';Data Source='.'; Integrated Security='SSPI'; initial catalog='master';");
    
            _CommandPtr pCmd  = NULL;
            pCmd.CreateInstance(__uuidof(Command));
            pCmd->CommandText = bstrCmdText;
    
            pConnection->Open(strCnn, "", "", adConnectUnspecified);
    
            pCmd->ActiveConnection = pConnection;
    
            VARIANT vtInput;
            vtInput.vt = VT_I2;
            vtInput.iVal = 25;
    
            _ParameterPtr pInputParam = pCmd->CreateParameter(_bstr_t("P1"), adInteger, adParamInput, sizeof(int), vtInput);
    
            pCmd->Parameters->Append(pInputParam);
    
            pInputParam->Value = vtInput;
            _RecordsetPtr pRstResult = NULL;
            pRstResult = pCmd->Execute(0,0,adCmdText);
    
            while(!(pRstResult->GetadoEOF()))
            {
                int iSquare = pRstResult->Fields->Item["square"]->Value;
                printf("%d \n", iSquare);
                pRstResult->MoveNext();
            }
    
        }
        catch (_com_error& e)
        {
            HRESULT hr = e.Error();
    
            printf("Error (%X): \n", hr);
            printf("Message: %s \t", e.ErrorMessage());
            printf("Desc: %s \t",  e.Description());
    
            return hr;
        }




    Tuesday, April 29, 2008 9:33 PM
  • First of all, THANKS ALOT for your time!!! (really! thank!)


    But it is not exactly what I meant...

    When you're running a stored procedure, the _CommandPtr holds all the parameters in it's Parameters properties, it loads it from the DB.
    Than you can programmatically match the user input directly to the right parameters, without creating parameters and appending them.
    By doing so (not creating the parameters, and using those from the DB) you can validate the input and it's type.

    So what I'm looking for is to call a value function table, and using the _CommandPtr's Parameters property to get all the parameters, and than simply match it.

    (Using this way, I can get the parameters without any important order, and validate them).




    Again,
    Thanks A LOT!
    Sunday, May 4, 2008 1:46 PM
  • Sorry, as I mentioned above, it is highly probable that provider cannot derive parameter information out of the 'FROM' clause.
    Thursday, May 8, 2008 1:54 AM
  • Okay, Thanks for your help! :-)
    Thursday, May 8, 2008 7:04 AM
  • Here is a query which shows parameter information for a UDF.
    It can be used to obtain type information for the parameters of a UDF if necessary.

    SELECT

    param.name AS [Name],

    ISNULL(baset.name, N'') AS [SystemType],

    CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Length],

    CAST(param.precision AS int) AS [NumericPrecision],

    CAST(param.scale AS int) AS [NumericScale],

    null AS [DefaultValue]

    FROM

    sys.all_objects AS udf

    INNER JOIN sys.all_parameters AS param ON (param.is_output = 0) AND (param.object_id=udf.object_id)

    LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id= param.system_type_idand baset.user_type_id= baset.system_type_id

    WHERE

    (udf.type in ('TF', 'FN', 'IF', 'FS', 'FT'))and(udf.name=N'f_test' and SCHEMA_NAME(udf.schema_id)=N'dbo')

     

    Sunday, May 11, 2008 7:30 PM
  • Actually it looks like I found a way to get the parameters.

    Using VB-code since it is easier for me to play with it and look at the structures immediately for the same UDF I used above:

    Dim cn As New ADODB.connection
    Dim param1 As ADODB.Parameter

    cn.ConnectionString = "provider=sqloledb;integrated security=sspi;Data Source=.; initial catalog=master"
    cn.Open
    Set cmd = CreateObject("ADODB.Command")

    cmd.CommandText = "f_test"
    cmd.CommandType = adCmdStoredProc
    cmd.ActiveConnection = cn

    cmd.Parameters.Refresh

    REM at this point you will get the parameters collection starting with the bogus return value
    REM the command text will be like: "{?=call f_test(?)}"
    REM this is not what we want so we are going to save the parameter we need

    Set param1 = cmd.Parameters(1)

    REM and reset the command text to use it as UDF

    cmd.CommandText = "select * from f_test(?)"
    cmd.CommandType = adCmdText

    REM at this point parameters collection is destroyed, so we need to put the parameter back

    cmd.Parameters.Append param1

    Debug.Print param1.Name, param1.Type
    REM prints @x 3

    param1.Value = 25

    Set rs = cmd.Execute

    Do While Not rs.EOF
    For i = 0 To rs.Fields.Count - 1
    Debug.Print rs(i).Name, rs(i).Value
    Next i
    rs.MoveNext
    Loop


    Wednesday, May 14, 2008 5:24 AM