[soci-users] Second execute - fetch select cycle does not seem to return data

[Faik Uygur |SmartSoft]

Hello,

I am using soci version 3.2.1 with Oracle backend. I am using prepared statements. If a single row selecting statement is called with stmt->execute(true); - select after select works but if a multiple row returning select statement is used and I use "stmt->execute(false) while (stmt->fetch()) ..." the second run of execute-fetch cycle does not return any data. Am i missing something or is this a bug in Oracle backend. I looked for something like cursor reset, row reset if maybe needed but couldn't find anything related.

Here is some code that would help to understand the problem:

---

MyClass {
...
private:
shared_ptr<statement> _select;
...
}

void MyClass::setup() {
_select.reset(new statement((_session->prepare << "select "
"guid, status, lastupdated, "
"func_code, dst_host1, dst_port1 "
"from sys_cnn "
"where status = 1",
into(guid), into(status), into(lastupdated),
into(func_code), into(dst_host1), into(dst_port1)
)));
}

bool MyClass::load() {
_select->execute();
while (_select->fetch()) {
// use data returned from each fetch
}
return _session->got_data();
}

----

So first load() call works but second call does not return any rows.

Regards,
Faik Uygur



[cid:***@9636677b.4c9a90d9]


Faik Uygur |SmartSoft
TEKNİK DANIŞMAN, YAZILIM GELİŞTİRME
TECHNICAL CONSULTANT, SOFTWARE DEVELOPMENT

Ä°TÜ Ayazağa KampÃŒsÃŒ Teknokent ARI3 K:1 NO:102 34469
Maslak Ä°stanbul - TÃŒrkiye www.cardtek.com<http://www.cardtek.com/>
P [phone] F [phone] M


[cid:***@989d0225.4fa7cde2]




Bu e-posta ve muhtemel eklerinde verilen bilgiler kişiye özel ve gizli olup, yalnızca mesajda belirlenen alıcı ile ilgilidir.Size yanlışlıkla ulaşmışsa lÃŒtfen göndericiye bilgi veriniz, mesajı siliniz ve içeriğini başka bir kişiye açıklamayınız, herhangi bir ortama kopyalamayınız.Verilen tÃŒm bilgilerin doğruluğu ve bÃŒtÃŒnlÌğÌnÃŒn garantisi verilmemekte olup, önceden bildirilmeksizin değiştirilebilecektir.Bu mesajın içeriği Firmamızın resmi görÌşlerini yansıtmayabileceğinden Kartek Kart ve Bilişim Teknolojileri Tic.Ltd.Şti. ( SmartSoft ) hiçbir hukuki sorumluluğu kabul etmez.

[Faik Uygur |SmartSoft]

Hello again,

I have just tested that the same code just works for MySql backend. Calling the same cycle after and after always returns data. Do I have to call something else specific to Oracle backend after each execute - fetch cycle? Or is this a bug? Still looking...

Regards,
Faik Uygur

________________________________
Kimden: Faik Uygur |SmartSoft [***@cardtek.com]
Gönderildi: 19 Temmuz 2013 Cuma 10:47
Kime: soci-***@lists.sourceforge.net
Bilgi: Halil Ercikan |SmartSoft
Konu: [soci-users] Second execute - fetch select cycle does not seem to return data


Hello,

I am using soci version 3.2.1 with Oracle backend. I am using prepared statements. If a single row selecting statement is called with stmt->execute(true); - select after select works but if a multiple row returning select statement is used and I use "stmt->execute(false) while (stmt->fetch()) ..." the second run of execute-fetch cycle does not return any data. Am i missing something or is this a bug in Oracle backend. I looked for something like cursor reset, row reset if maybe needed but couldn't find anything related.

Here is some code that would help to understand the problem:

---

MyClass {
...
private:
shared_ptr<statement> _select;
...
}

void MyClass::setup() {
_select.reset(new statement((_session->prepare << "select "
"guid, status, lastupdated, "
"func_code, dst_host1, dst_port1 "
"from sys_cnn "
"where status = 1",
into(guid), into(status), into(lastupdated),
into(func_code), into(dst_host1), into(dst_port1)
)));
}

bool MyClass::load() {
_select->execute();
while (_select->fetch()) {
// use data returned from each fetch
}
return _session->got_data();
}

----

So first load() call works but second call does not return any rows.

Regards,
Faik Uygur



[cid:***@9636677b.4c9a90d9]


Faik Uygur |SmartSoft
TEKNİK DANIŞMAN, YAZILIM GELİŞTİRME
TECHNICAL CONSULTANT, SOFTWARE DEVELOPMENT

Ä°TÜ Ayazağa KampÃŒsÃŒ Teknokent ARI3 K:1 NO:102 34469
Maslak Ä°stanbul - TÃŒrkiye www.cardtek.com<http://www.cardtek.com/>
P [phone] F [phone] M


[cid:***@989d0225.4fa7cde2]




Bu e-posta ve muhtemel eklerinde verilen bilgiler kişiye özel ve gizli olup, yalnızca mesajda belirlenen alıcı ile ilgilidir.Size yanlışlıkla ulaşmışsa lÃŒtfen göndericiye bilgi veriniz, mesajı siliniz ve içeriğini başka bir kişiye açıklamayınız, herhangi bir ortama kopyalamayınız.Verilen tÃŒm bilgilerin doğruluğu ve bÃŒtÃŒnlÌğÌnÃŒn garantisi verilmemekte olup, önceden bildirilmeksizin değiştirilebilecektir.Bu mesajın içeriği Firmamızın resmi görÌşlerini yansıtmayabileceğinden Kartek Kart ve Bilişim Teknolojileri Tic.Ltd.Şti. ( SmartSoft ) hiçbir hukuki sorumluluğu kabul etmez.



[cid:***@074f1242.4bb5e304]


Faik Uygur |SmartSoft
TEKNİK DANIŞMAN, YAZILIM GELİŞTİRME
TECHNICAL CONSULTANT, SOFTWARE DEVELOPMENT

Ä°TÜ Ayazağa KampÃŒsÃŒ Teknokent ARI3 K:1 NO:102 34469
Maslak Ä°stanbul - TÃŒrkiye www.cardtek.com<http://www.cardtek.com/>
P [phone] F [phone] M


[cid:***@0fa3a185.44a14615]

[Mateusz Loskot]

On 19 July 2013 08:47, Faik Uygur |SmartSoft <***@cardtek.com> wrote:

> Hello,
>
> I am using soci version 3.2.1 with Oracle backend. I am using prepared
> statements. If a single row selecting statement is called with
> stmt->execute(true); - select after select works but if a multiple row
> returning select statement is used and I use "stmt->execute(false) while
> (stmt->fetch()) ..." the second run of execute-fetch cycle does not return
> any data. Am i missing something or is this a bug in Oracle backend. I
> looked for something like cursor reset, row reset if maybe needed but
> couldn't find anything related.
>

There is statement::clean_up(), but you should not need to call it, in most
cases.


> Here is some code that would help to understand the problem:
>
> ---
>
> MyClass {
> ...
> private:
> shared_ptr<statement> _select;
> ...
> }
>
> void MyClass::setup() {
> _select.reset(new statement((_session->prepare << "select "
> "guid, status, lastupdated, "
> "func_code, dst_host1, dst_port1 "
> "from sys_cnn "
> "where status = 1",
> into(guid), into(status),
> into(lastupdated),
> into(func_code), into(dst_host1),
> into(dst_port1)
> )));
> }
>
> bool MyClass::load() {
> _select->execute();
> while (_select->fetch()) {
> // use data returned from each fetch
> }
> return _session->got_data();
> }
>
>

So, AFAIU, your use case is basically equivalent to re-execution of
statement as below:

T c;
statement st = (sql.prepare << "select c from t", into(c));
for (int j = 0; j < 2; ++j) // mimic two calls to load()
{
st.execute();
while (st.fetch())
{
// use c
}
}

This should work and it works for PostgreSQL backend.
I don't have access to Oracle at the moment.
I may be able to test it over the weekend or next week.

Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net

[Faik Uygur |SmartSoft]

Hello,

Thanks for the reply. I had tried clean_up call but the next execute segfaults for both Oracle and MySQL backends.

#0 0xb7e74259 in soci::details::statement_impl::execute(bool) () from /usr/local/lib/libsoci_core.so.3.2
#1 0xb7f4c02f in soci::statement::execute (this=0x810da88, withDataExchange=false) at /usr/local/include/soci/statement.h:170
...

load() {
_select->execute();
while (_select->fetch()) {
..
}
_select->clean_up();
}

I will try to find the cause if I can.

Regards,
Faik Uygur

________________________________
Kimden: Mateusz Loskot [***@loskot.net]
Gönderildi: 19 Temmuz 2013 Cuma 12:05
Kime: SOCI general discussion list
Bilgi: Halil Ercikan |SmartSoft
Konu: Re: [soci-users] Second execute - fetch select cycle does not seem to return data

On 19 July 2013 08:47, Faik Uygur |SmartSoft <***@cardtek.com<mailto:***@cardtek.com>> wrote:


[cid:***@609f65a0.4ca57386]


Faik Uygur |SmartSoft
TEKNİK DANIŞMAN, YAZILIM GELİŞTİRME
TECHNICAL CONSULTANT, SOFTWARE DEVELOPMENT

Ä°TÜ Ayazağa KampÃŒsÃŒ Teknokent ARI3 K:1 NO:102 34469
Maslak Ä°stanbul - TÃŒrkiye www.cardtek.com<http://www.cardtek.com/>
P [phone] F [phone] M


[cid:***@b2e469a7.42acf1fc]




Bu e-posta ve muhtemel eklerinde verilen bilgiler kişiye özel ve gizli olup, yalnızca mesajda belirlenen alıcı ile ilgilidir.Size yanlışlıkla ulaşmışsa lÃŒtfen göndericiye bilgi veriniz, mesajı siliniz ve içeriğini başka bir kişiye açıklamayınız, herhangi bir ortama kopyalamayınız.Verilen tÃŒm bilgilerin doğruluğu ve bÃŒtÃŒnlÌğÌnÃŒn garantisi verilmemekte olup, önceden bildirilmeksizin değiştirilebilecektir.Bu mesajın içeriği Firmamızın resmi görÌşlerini yansıtmayabileceğinden Kartek Kart ve Bilişim Teknolojileri Tic.Ltd.Şti. ( SmartSoft ) hiçbir hukuki sorumluluğu kabul etmez.


Hello,

I am using soci version 3.2.1 with Oracle backend. I am using prepared statements. If a single row selecting statement is called with stmt->execute(true); - select after select works but if a multiple row returning select statement is used and I use "stmt->execute(false) while (stmt->fetch()) ..." the second run of execute-fetch cycle does not return any data. Am i missing something or is this a bug in Oracle backend. I looked for something like cursor reset, row reset if maybe needed but couldn't find anything related.

There is statement::clean_up(), but you should not need to call it, in most cases.

Here is some code that would help to understand the problem:

---

MyClass {
...
private:
shared_ptr<statement> _select;
...
}

void MyClass::setup() {
_select.reset(new statement((_session->prepare << "select "
"guid, status, lastupdated, "
"func_code, dst_host1, dst_port1 "
"from sys_cnn "
"where status = 1",
into(guid), into(status), into(lastupdated),
into(func_code), into(dst_host1), into(dst_port1)
)));
}

bool MyClass::load() {
_select->execute();
while (_select->fetch()) {
// use data returned from each fetch
}
return _session->got_data();
}



So, AFAIU, your use case is basically equivalent to re-execution of statement as below:

T c;
statement st = (sql.prepare << "select c from t", into(c));
for (int j = 0; j < 2; ++j) // mimic two calls to load()
{
st.execute();
while (st.fetch())
{
// use c
}
}

This should work and it works for PostgreSQL backend.
I don't have access to Oracle at the moment.
I may be able to test it over the weekend or next week.

Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net

[Faik Uygur |SmartSoft]

Yep I have repeated the problem with your smaller testcase, again works for MySQL backend and not working for Oracle backend.

Also I would like to note that I am using instantclient version 12.1.0.1.0 if this helps.

Regards,
Faik Uygur

________________________________
Kimden: Mateusz Loskot [***@loskot.net]
Gönderildi: 19 Temmuz 2013 Cuma 12:05
Kime: SOCI general discussion list
Bilgi: Halil Ercikan |SmartSoft
Konu: Re: [soci-users] Second execute - fetch select cycle does not seem to return data

On 19 July 2013 08:47, Faik Uygur |SmartSoft <***@cardtek.com<mailto:***@cardtek.com>> wrote:


[cid:***@93c3efc0.4e928b41]


Faik Uygur |SmartSoft
TEKNİK DANIŞMAN, YAZILIM GELİŞTİRME
TECHNICAL CONSULTANT, SOFTWARE DEVELOPMENT

Ä°TÜ Ayazağa KampÃŒsÃŒ Teknokent ARI3 K:1 NO:102 34469
Maslak Ä°stanbul - TÃŒrkiye www.cardtek.com<http://www.cardtek.com/>
P [phone] F [phone] M


[cid:***@7f5e8aa8.41b0382c]




Bu e-posta ve muhtemel eklerinde verilen bilgiler kişiye özel ve gizli olup, yalnızca mesajda belirlenen alıcı ile ilgilidir.Size yanlışlıkla ulaşmışsa lÃŒtfen göndericiye bilgi veriniz, mesajı siliniz ve içeriğini başka bir kişiye açıklamayınız, herhangi bir ortama kopyalamayınız.Verilen tÃŒm bilgilerin doğruluğu ve bÃŒtÃŒnlÌğÌnÃŒn garantisi verilmemekte olup, önceden bildirilmeksizin değiştirilebilecektir.Bu mesajın içeriği Firmamızın resmi görÌşlerini yansıtmayabileceğinden Kartek Kart ve Bilişim Teknolojileri Tic.Ltd.Şti. ( SmartSoft ) hiçbir hukuki sorumluluğu kabul etmez.


Hello,

I am using soci version 3.2.1 with Oracle backend. I am using prepared statements. If a single row selecting statement is called with stmt->execute(true); - select after select works but if a multiple row returning select statement is used and I use "stmt->execute(false) while (stmt->fetch()) ..." the second run of execute-fetch cycle does not return any data. Am i missing something or is this a bug in Oracle backend. I looked for something like cursor reset, row reset if maybe needed but couldn't find anything related.

There is statement::clean_up(), but you should not need to call it, in most cases.

Here is some code that would help to understand the problem:

---

MyClass {
...
private:
shared_ptr<statement> _select;
...
}

void MyClass::setup() {
_select.reset(new statement((_session->prepare << "select "
"guid, status, lastupdated, "
"func_code, dst_host1, dst_port1 "
"from sys_cnn "
"where status = 1",
into(guid), into(status), into(lastupdated),
into(func_code), into(dst_host1), into(dst_port1)
)));
}

bool MyClass::load() {
_select->execute();
while (_select->fetch()) {
// use data returned from each fetch
}
return _session->got_data();
}



So, AFAIU, your use case is basically equivalent to re-execution of statement as below:

T c;
statement st = (sql.prepare << "select c from t", into(c));
for (int j = 0; j < 2; ++j) // mimic two calls to load()
{
st.execute();
while (st.fetch())
{
// use c
}
}

This should work and it works for PostgreSQL backend.
I don't have access to Oracle at the moment.
I may be able to test it over the weekend or next week.

Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net

[Mateusz Loskot]

On 19 July 2013 10:32, Faik Uygur |SmartSoft <***@cardtek.com> wrote:

> Yep I have repeated the problem with your smaller testcase, again works
> for MySQL backend and not working for Oracle backend.
>
> Also I would like to note that I am using instantclient version 12.1.0.1.0
> if this helps.
>

Thanks for the confirmation. I guess there is a problem in the Oracle
backend, indeed.

Feel free to submit a bug report at
https://github.com/soci/soci/issue<https://github.com/soci/soci/issues?state=open>
s
so it won't be forgotten.

We need to add your minimal test case that is Oracle failing for you should
be
added to common-tests.h, so it can be checked across all backends.

Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net

[asif saeed]

Hi Faik,
On Fri, Jul 19, 2013 at 2:32 PM, Faik Uygur |SmartSoft <
***@cardtek.com> wrote:

> Also I would like to note that I am using instantclient version
> 12.1.0.1.0 if this helps.
>

Please note that using instant client will NEVER let OCI communicate with
Oracle backend - both of them are mutually exclusive. Using instant client,
you cannot use OCI - plain and simple. I also encountered this, perhaps, a
year ago and found out about this either-this-or-that scenario about
instant-client and OCI but could not find any solution. By the way, this is
very well documented in Oracle documentation for OCI, IIRC, as that's where
I came to know about this problem.

Best regards,
-Asif

[asif saeed]

Hi Faik,
On Fri, Jul 19, 2013 at 4:02 PM, asif saeed <***@gmail.com> wrote:

> Hi Faik,
> On Fri, Jul 19, 2013 at 2:32 PM, Faik Uygur |SmartSoft <
> ***@cardtek.com> wrote:
>
>> Also I would like to note that I am using instantclient version
>> 12.1.0.1.0 if this helps.
>>
>
But you say that the the statement does return you the result set the
first time. It only fails subsequently. So, sorry for my previous post!

By the way, I sure would like to know how instant client can run in an
environment without causing any problem to OCI apps that are also running
as well in the same environment.

Best regards,
-Asif

[asif saeed]

Sorry, Faik. The problem was not about OCI, IIRC, but ORACLE_HOME. The
problem was running instant client in an environment where ORACLE_HOME
based apps were also running. Either one or the other could be run but not
both.

Best regards,
-Asif


On Fri, Jul 19, 2013 at 4:07 PM, asif saeed <***@gmail.com> wrote:

> Hi Faik,
> On Fri, Jul 19, 2013 at 4:02 PM, asif saeed <***@gmail.com> wrote:
>
>> Hi Faik,
>> On Fri, Jul 19, 2013 at 2:32 PM, Faik Uygur |SmartSoft <
>> ***@cardtek.com> wrote:
>>
>>> Also I would like to note that I am using instantclient version
>>> 12.1.0.1.0 if this helps.
>>>
>>
> But you say that the the statement does return you the result set the
> first time. It only fails subsequently. So, sorry for my previous post!
>
> By the way, I sure would like to know how instant client can run in an
> environment without causing any problem to OCI apps that are also running
> as well in the same environment.
>
> Best regards,
> -Asif
>

[Faik Uygur |SmartSoft]

I have created a pull request for this problem.

https://github.com/SOCI/soci/pull/173

Regards,
Faik Uygur

________________________________
Kimden: asif saeed [***@gmail.com]
Gönderildi: 19 Temmuz 2013 Cuma 14:35
Kime: SOCI general discussion list
Konu: Re: [soci-users] YNT: Second execute - fetch select cycle does not seem to return data

Sorry, Faik. The problem was not about OCI, IIRC, but ORACLE_HOME. The problem was running instant client in an environment where ORACLE_HOME based apps were also running. Either one or the other could be run but not both.

Best regards,
-Asif




[cid:***@f5632c15.49b451b7]


Faik Uygur |SmartSoft
TEKNİK DANIŞMAN, YAZILIM GELİŞTİRME
TECHNICAL CONSULTANT, SOFTWARE DEVELOPMENT

Ä°TÜ Ayazağa KampÃŒsÃŒ Teknokent ARI3 K:1 NO:102 34469
Maslak Ä°stanbul - TÃŒrkiye www.cardtek.com<http://www.cardtek.com/>
P [phone] F [phone] M


[cid:***@7cbe24cc.4a84beb4]




Bu e-posta ve muhtemel eklerinde verilen bilgiler kişiye özel ve gizli olup, yalnızca mesajda belirlenen alıcı ile ilgilidir.Size yanlışlıkla ulaşmışsa lÃŒtfen göndericiye bilgi veriniz, mesajı siliniz ve içeriğini başka bir kişiye açıklamayınız, herhangi bir ortama kopyalamayınız.Verilen tÃŒm bilgilerin doğruluğu ve bÃŒtÃŒnlÌğÌnÃŒn garantisi verilmemekte olup, önceden bildirilmeksizin değiştirilebilecektir.Bu mesajın içeriği Firmamızın resmi görÌşlerini yansıtmayabileceğinden Kartek Kart ve Bilişim Teknolojileri Tic.Ltd.Şti. ( SmartSoft ) hiçbir hukuki sorumluluğu kabul etmez.


On Fri, Jul 19, 2013 at 4:07 PM, asif saeed <***@gmail.com<mailto:***@gmail.com>> wrote:
Hi Faik,
On Fri, Jul 19, 2013 at 4:02 PM, asif saeed <***@gmail.com<mailto:***@gmail.com>> wrote:
Hi Faik,
On Fri, Jul 19, 2013 at 2:32 PM, Faik Uygur |SmartSoft <***@cardtek.com<mailto:***@cardtek.com>> wrote:

Also I would like to note that I am using instantclient version 12.1.0.1.0 if this helps.

But you say that the the statement does return you the result set the first time. It only fails subsequently. So, sorry for my previous post!

By the way, I sure would like to know how instant client can run in an environment without causing any problem to OCI apps that are also running as well in the same environment.

Best regards,
-Asif