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

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

Absolutely awesome, vincent. :-)

Thank you also, Luiz.

And again, my apologies for the lack of knowledge to solve the problem.
Thank you for everything.

Heres the full code for future reference:

PL/SQL:

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;


c++ code:

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);
st.Execute();
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 6:04 PM, vincent rogier <vin...@gm...>
wrote:

> 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
>