[Home]

Summary:ASTERISK-24364: PJSIP realtime configuration via res_odbc with Oracle backend - Binding variable to :0 is not allowed in oracle
Reporter:HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u)Labels:
Date Opened:2014-09-29 02:14:20Date Closed:2015-08-04 02:58:21
Priority:MajorRegression?
Status:Closed/CompleteComponents:pjproject/pjsip Resources/res_odbc Resources/res_pjsip
Versions:12.6.0 Frequency of
Occurrence		Constant
Related
Issues:
Environment:Attachments:( 0) asterisk_ora.png
( 1) bug.txt
( 2) odbc.log
( 3) registration_attempt_log.txt
( 4) related_configs_extraction.txt
Description:In case of using pjsip with oracle realtime over odbc.
Asterisk try to execute queryes like

{{SELECT ID FROM ps_endpoints WHERE id =:0;}}

Binding variable to :0 is not allowed in oracle (at least for version 10.2.0.5.x)
Query constantly return ORA-01403: no data found

After it realtime registrations functionality of pjsip completely stop to work.

PS: If to use binding variable names like
{{SELECT ID FROM ps_endpoints WHERE id =:var0;}}
oracle works ok.
It is possible bug in oracle of course, but i think that change of naming notation for odbc binding variables can solve a lot of compatibility issues with different databases.
Comments:By: Rusty Newton (rnewton) 2014-10-01 15:29:46.862-0500

Can you explain "After it realtime registrations functionality of pjsip completely stop to work." and provide Asterisk logs during the time this occurs?

When collecting the logs, please follow the [instructions linked here|https://wiki.asterisk.org/wiki/display/AST/Collecting+Debug+Information] and be sure that both VERBOSE and DEBUG messages are included in your log at the levels described.


By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2014-10-02 06:35:50.054-0500

{quote}
vpbxdev*CLI> pjsip list endpoints

Endpoint:  <Endpoint/CID.....................................>  <State.....>  <Channels.>
=========================================================================================

No objects found.

[Oct  2 13:30:02] DEBUG[54316]: res_config_odbc.c:113 custom_prepare: Skip: 0; SQL: SELECT * FROM ps_endpoints WHERE id LIKE ? ORDER BY id
[Oct  2 13:30:02] DEBUG[54316]: res_config_odbc.c:129 custom_prepare: Parameter 1 ('id LIKE') = '%'
[Oct  2 13:30:02] DEBUG[54316]: res_odbc.c:1062 odbc_release_obj2: odbc_release_obj2(0x26386e8) called (obj->txf = (nil))
[Oct  2 13:30:02] DEBUG[54316]: res_sorcery_realtime.c:119 sorcery_realtime_filter_objectset: Filtering out realtime field 'ID' from retrieval
[Oct  2 13:30:02] DEBUG[54316]: res_sorcery_realtime.c:119 sorcery_realtime_filter_objectset: Filtering out realtime field 'TRANSPORT' from retrieval
[Oct  2 13:30:02] DEBUG[54316]: res_sorcery_realtime.c:119 sorcery_realtime_filter_objectset: Filtering out realtime field 'AORS' from retrieval
[Oct  2 13:30:02] DEBUG[54316]: res_sorcery_realtime.c:119 sorcery_realtime_filter_objectset: Filtering out realtime field 'AUTH' from retrieval
[Oct  2 13:30:02] DEBUG[54316]: res_sorcery_realtime.c:119 sorcery_realtime_filter_objectset: Filtering out realtime field 'CONTEXT' from retrieval
[Oct  2 13:30:02] DEBUG[54316]: res_sorcery_realtime.c:119 sorcery_realtime_filter_objectset: Filtering out realtime field 'DISALLOW' from retrieval
[Oct  2 13:30:02] DEBUG[54316]: res_sorcery_realtime.c:119 sorcery_realtime_filter_objectset: Filtering out realtime field 'ALLOW' from retrieval
[Oct  2 13:30:02] DEBUG[54316]: res_sorcery_realtime.c:119 sorcery_realtime_filter_objectset: Filtering out realtime field 'DIRECT_MEDIA' from retrieval
{quote}




If i do same (SELECT * FROM ps_endpoints WHERE id LIKE '%' ORDER BY id) using ORACLE client i see that data exist in table.


By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2014-10-02 06:50:17.612-0500

ODBC trace log . Which show that SQL successfully executed and data selected from table.
Log done on version 13.0.0-beta2
By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2014-10-02 06:52:53.936-0500

Debug output from asterisk console (done on version 13.0.0-beta2)

By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2014-10-02 06:57:02.286-0500

Screenshot of table (ps_endpoints) contents
By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2014-10-02 07:01:50.094-0500

extconfig.conf  and sorcery.conf
By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2014-10-02 07:13:52.711-0500

Log with registration attempt
Data in oracle database were setup in accordance with description
in  https://wiki.asterisk.org/wiki/display/AST/Setting+up+PJSIP+Realtime
By: HZMI8gkCvPpom0tM (y2fbo4IevYm5Ve9u) 2015-08-04 02:58:21.390-0500

Fixed  indirectly with new released version.