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

[vincent r. <vin...@gm...>]

use st.Execute() instead of st.Execute(sql)

Vincent

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

> (sorry for the duplicate, i keep clicking reply, instead of reply all)
>
> If i change that i get:
>
> "The statement must be described to perform this operation"
>
> full code:
>
> string sql = "declare rc SYS_REFCURSOR; begin listatipos(:rc); end;";
> //string sql = "begin listatipos(:1); end;";
> con.Open(connectString, user, passw);
> Statement st(con);
> Statement st2(con);
> st.Prepare(sql);
> st.Bind(":rc", st2, BindInfo::Out);
> //st.Bind(":1", st2, BindInfo::Out);
> 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();
>
>
> best regards,
> Miguel
>
> On Sat, Jan 3, 2015 at 5:56 PM, vincent rogier <vin...@gm...>
> wrote:
>
>> because you must add the semicolon in your sql statement :
>>
>> "declare rc SYS_REFCURSOR; begin listatipos(*:rc*); end;"
>>
>> Vincent
>>
>> On Sat, Jan 3, 2015 at 6:54 PM, Miguel Vaz <pag...@gm...> wrote:
>>
>>> 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
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>>
>> --
>> Vincent Rogier
>>
>
>


-- 
Vincent Rogier