Re: [Orclib-users] Problem calling and receiving an oracle stored procedure resultset

[Miguel V. <pag...@gm...>]

Follow up:

if i add a Prepare:

string sql = "declare rc SYS_REFCURSOR; begin listatipos(rc); end;";
con.Open(connectString, user, passw);
Statement st(con);
Statement st2(con);
st.Prepare(sql);
st.Bind(":rc", st2, BindInfo::Out);
etc..

I get an output of "ORA-01036: illegal variable name/number"



best regards,
Miguel

On Sat, Jan 3, 2015 at 5:54 PM, Miguel Vaz <pag...@gm...> wrote:

> ​Hi,
> Thank you so much for all the replies.
>
> Luiz: with your code, i get an error at this line:
>
> st.Bind(":1", &st2, 1);
>
> specifically at the dot(.) after "st". The error is:
>
>  error C2664: 'void ocilib::Statement::Bind<ocilib::Statement*>(const
> ocilib::ostring &,TDataType &,ocilib::BindInfo::BindDirection)' : cannot
> convert argument 2 from 'ocilib::Statement *' to 'ocilib::Statement *&'
>
> Vincent: with your code, using st.Bind(":rc", st2, BindInfo::Out);
>
> no compiling error, but i get an output (while running) error:
>
> "The statement must be prepared to perform this operation"
>
> i have the call to a method that has my code inside a try catch:
> try{
> Environment::Initialize(Environment::Default | Environment::Threaded);
> Environment::EnableWarnings(true);
> ioc.getListaTiposCorpo(); // the c++ code on my first post
>
>
> }
> catch (Exception &ex){
> cout << ex.what() << endl;
>
> }
>
>
>
> So sorry for all these questions.
>
>
> best regards,
> Miguel
>
> On Sat, Jan 3, 2015 at 5:35 PM, Luiz Rafael Culik <lu...@xh...>
> wrote:
>
>> Hi Miguel
>>
>> the problem is the C++ Code
>>
>> try this
>>
>> string sql = "begin listatipos(:1); end;";
>> con.Open(connectString, user, passw);
>> Statement st(con);
>> Statement st2(con);
>> St.Bind(":1",&st2,1);
>> st.Execute(sql);
>> // something missing to bind the oracle variable "rc"?
>> Resultset rs = st2.GetResultset();
>> int total_colunas = int(rs.GetColumnCount());
>> cout << "Tabela com " << total_colunas << " colunas." << endl;
>> while (rs.Next())
>> {
>> for (int i = 1; i <= total_colunas; i++){
>> cout << rs.Get<string>(i) << "\t";
>> }
>> cout << std::endl;
>> }
>> con.Close();
>>
>> Regards
>> Luiz
>>
>> 2015-01-03 15:09 GMT-02:00 Miguel Vaz <pag...@gm...>:
>>
>> Forgive my ignorance, but can you point me to an example or show me a bit
>>> of code to get me started? I am a beginner when it comes to oracle. From
>>> what i gathered from your reply, the PL/SQL procedure is the problem, not
>>> the c++ code, right?
>>>
>>> Thank you for the swift reply, vincent.
>>>
>>> best regards,
>>> Miguel
>>>
>>> On Sat, Jan 3, 2015 at 3:44 PM, vincent rogier <vin...@gm...>
>>> wrote:
>>>
>>>> Hi,
>>>>
>>>> In your procedure the ref cursor is not a return value but an out
>>>> parameter. You must bind another local statement object indicating to the
>>>> statement executing the sql and add a " :"in the sql string. When you call
>>>> st.Bind() passing the other stmt object that will receive the ref cursir
>>>> you must specify the out direction
>>>>
>>>> Regards
>>>>
>>>> Vincent
>>>> Le 3 janv. 2015 14:56, "Miguel Vaz" <pag...@gm...> a écrit :
>>>>
>>>>> Hi,
>>>>>
>>>>> I searched everywhere for a solution to this apparently simple
>>>>> question, but i couldnt find it anywhere. All the solutions i find are for
>>>>> C using OCI_* way.
>>>>>
>>>>> I have a stored procedure on an oracle db (12) and i simply want to
>>>>> call it, retrieve the resulting resultset and display it on my c++
>>>>> application (using visual studio 2013).
>>>>>
>>>>> My stored procedure:
>>>>>
>>>>> CREATE OR REPLACE
>>>>> procedure listatipos(p_recordset OUT SYS_REFCURSOR) as
>>>>> BEGIN
>>>>> open p_recordset FOR
>>>>> select id_ctipo,nome from corpostipos cpt order by id_ctipo asc;
>>>>> END listatipos;
>>>>>
>>>>>
>>>>> My c++ code:
>>>>>
>>>>> string sql = "declare rc SYS_REFCURSOR; begin listatipos(rc); end;";
>>>>> con.Open(connectString, user, passw);
>>>>> Statement st(con);
>>>>> st.Execute(sql);
>>>>> // something missing to bind the oracle variable "rc"?
>>>>> Resultset rs = st.GetResultset();
>>>>> int total_colunas = int(rs.GetColumnCount());
>>>>> cout << "Tabela com " << total_colunas << " colunas." << endl;
>>>>> while (rs.Next())
>>>>> {
>>>>> for (int i = 1; i <= total_colunas; i++){
>>>>> cout << rs.Get<string>(i) << "\t";
>>>>> }
>>>>> cout << std::endl;
>>>>> }
>>>>> con.Close();
>>>>>
>>>>> This results in "A null Resultset handle has been provided". If i use
>>>>> a simple query like "select * from table1", the records are listed
>>>>> perfectly.
>>>>>
>>>>> From comparing my code to the previous version ones i find online, i
>>>>> can see its probably something with a bind(?)? Is it something with my
>>>>> procedure?
>>>>>
>>>>> Please help, i find the ocilib a very straightforward library to work
>>>>> with but this simple problem is driving me crazy. there are no references
>>>>> online as to how to call and retrieve procedure results using it.
>>>>>
>>>>> Should i use the other code? I mean, use OCI_ etc? Shouldnt the c++
>>>>> api be complete?
>>>>>
>>>>> Thanks!
>>>>>
>>>>> best regards,
>>>>> MV
>>>>>
>>>>> NOTE: i also posted this question on the forums, please forgive the
>>>>> duplication.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> ------------------------------------------------------------------------------
>>>>> Dive into the World of Parallel Programming! The Go Parallel Website,
>>>>> sponsored by Intel and developed in partnership with Slashdot Media,
>>>>> is your
>>>>> hub for all things parallel software development, from weekly thought
>>>>> leadership blogs to news, videos, case studies, tutorials and more.
>>>>> Take a
>>>>> look and join the conversation now. http://goparallel.sourceforge.net
>>>>> _______________________________________________
>>>>> Orclib-users mailing list
>>>>> Orc...@li...
>>>>> https://lists.sourceforge.net/lists/listinfo/orclib-users
>>>>>
>>>>>
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Dive into the World of Parallel Programming! The Go Parallel Website,
>>> sponsored by Intel and developed in partnership with Slashdot Media, is
>>> your
>>> hub for all things parallel software development, from weekly thought
>>> leadership blogs to news, videos, case studies, tutorials and more. Take
>>> a
>>> look and join the conversation now. http://goparallel.sourceforge.net
>>> _______________________________________________
>>> Orclib-users mailing list
>>> Orc...@li...
>>> https://lists.sourceforge.net/lists/listinfo/orclib-users
>>>
>>>
>>
>