I don't see how this is an ODBC issue. Are you using the same user in your module as with psql? Have you tried the same query with isql (odbc equiv to psql)? Are you SURE you are connecting to the same db instance?<br>
<br><div class="gmail_quote">On Mon, Nov 15, 2010 at 2:01 PM, Jerry Richards <span dir="ltr"><<a href="mailto:jerry.richards@teotech.com">jerry.richards@teotech.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div lang="EN-US" link="blue" vlink="purple"><div><p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D">Hi Rupa,</span></p><p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D"> </span></p><p class="MsoNormal">
<span style="font-size:11.0pt;color:#1F497D">Yes, I'm using postgresql. When I run the C-module using the schema.table format it gets this error:</span></p><p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D"> </span></p>
<p class="MsoNormal"><span style="font-size:9.0pt;font-family:"Courier New";color:#1F497D">2010-11-15 11:11:18.431977 [ERR] switch_core_sqldb.c:806 SQL ERR: [select sip_presence.status, sip_presence.rpid from ts_sofia_internal.sip_presence where sip_presence.sip_user='1003'] no such table: ts_sofia_internal.sip_presence</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D"> </span></p><p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D">If I execute the select statement from the psql command line, it works as shown:</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D"> </span></p><p class="MsoNormal"><span style="font-size:9.0pt;font-family:"Courier New";color:#1F497D">teo=# select sip_presence.status, sip_presence.rpid from ts_sofia_internal.sip_presence where sip_presence.sip_user='1003';</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt;font-family:"Courier New";color:#1F497D">status | rpid</span></p><p class="MsoNormal"><span style="font-size:9.0pt;font-family:"Courier New";color:#1F497D">--------+------</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt;font-family:"Courier New";color:#1F497D">Away | away</span></p><p class="MsoNormal"><span style="font-size:9.0pt;font-family:"Courier New";color:#1F497D">(1 row)</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D"> </span></p><p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D">Could this have something to do with accessing the DB via ODBC? Do you have another suggestion?</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D"> </span></p><p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D">I did try adding 'ts_sofia_internal' to the search path (i.e. set search_path to ucm, ts_sofia_internal), but it also did not work.</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D"> </span></p><p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D">Thanks,</span></p><p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D">Jerry</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D"> </span></p><p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D"> </span></p><p class="MsoNormal"><span style="font-size:11.0pt;color:#1F497D"> </span></p>
<p class="MsoNormal" style="margin-left:.5in"><b><span style="font-size:10.0pt">From:</span></b><span style="font-size:10.0pt"> <a href="mailto:freeswitch-dev-bounces@lists.freeswitch.org" target="_blank">freeswitch-dev-bounces@lists.freeswitch.org</a> [mailto:<a href="mailto:freeswitch-dev-bounces@lists.freeswitch.org" target="_blank">freeswitch-dev-bounces@lists.freeswitch.org</a>] <b>On Behalf Of </b>Rupa Schomaker<br>
<b>Sent:</b> Monday, November 15, 2010 11:06 AM<br><b>To:</b> <a href="mailto:freeswitch-dev@lists.freeswitch.org" target="_blank">freeswitch-dev@lists.freeswitch.org</a><br><b>Subject:</b> Re: [Freeswitch-dev] Trouble Accessing DB Table in C-module Via ODBC</span></p>
<div><div></div><div class="h5"><p class="MsoNormal" style="margin-left:.5in"> </p><p class="MsoNormal" style="margin-left:.5in">What database engine? That looks like postgresql. If so, add the schema you are interested in working with into the search path or explicitly use the schema in your query.</p>
<div><p class="MsoNormal" style="margin-left:.5in"> </p></div><div><p class="MsoNormal" style="margin-left:.5in">schema.function(args) rather than just function(args)</p></div><div><p class="MsoNormal" style="margin-left:.5in">
</p></div><div><p class="MsoNormal" style="margin-right:0in;margin-bottom:12.0pt;margin-left:.5in">You can set the search path using alter user ...</p><div><p class="MsoNormal" style="margin-left:.5in">On Mon, Nov 15, 2010 at 10:56 AM, Jerry Richards <<a href="mailto:jerry.richards@teotech.com" target="_blank">jerry.richards@teotech.com</a>> wrote:</p>
<div><div><p class="MsoNormal" style="margin-left:.5in">Hello,</p><p class="MsoNormal" style="margin-left:.5in"> </p><p class="MsoNormal" style="margin-left:.5in">I need to access a database using two different schemas. During initialization the default schema is set as follows:</p>
<p class="MsoNormal" style="margin-left:.5in"> </p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:10.0pt;font-family:"Courier New"">set schema 'ucm';</span></p><p class="MsoNormal" style="margin-left:.5in">
</p><p class="MsoNormal" style="margin-left:.5in">Then later, I need to get data from a table in another schema 'ts_sofia_internal', so I tried the following, but it returns an error as shown:</p><p class="MsoNormal" style="margin-left:.5in">
</p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:10.0pt;font-family:"Courier New"">teo=# select * from sip_presence('ts_sofia_internal') where sip_presence.sip_user='1003';</span></p>
<p class="MsoNormal" style="margin-left:.5in"><span style="font-size:10.0pt;font-family:"Courier New"">ERROR: function sip_presence(unknown) does not exist</span></p><p class="MsoNormal" style="margin-left:.5in">
<span style="font-size:10.0pt;font-family:"Courier New"">LINE 1: select * from sip_presence('ts_sofia_internal') where sip_pr...</span></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:10.0pt;font-family:"Courier New""> ^</span></p>
<p class="MsoNormal" style="margin-left:.5in"><span style="font-size:10.0pt;font-family:"Courier New"">HINT: No function matches the given name and argument types. You might need to add explicit type casts.</span></p>
<p class="MsoNormal" style="margin-left:.5in"><span style="font-size:10.0pt;font-family:"Courier New"">teo=#</span></p><p class="MsoNormal" style="margin-left:.5in"> </p><p class="MsoNormal" style="margin-left:.5in">
Does anyone know how I can do this? By the way, I will actually implement the query in C and connect to the DB via ODBC.</p><p class="MsoNormal" style="margin-left:.5in"> </p><p class="MsoNormal" style="margin-left:.5in">
Thanks,</p><p class="MsoNormal" style="margin-left:.5in">Jerry</p><p class="MsoNormal" style="margin-left:.5in"><span style="color:#888888"> </span></p><p class="MsoNormal" style="margin-left:.5in"><span style="color:#888888"> </span></p>
<p class="MsoNormal" style="margin-left:.5in"><span style="color:#888888"> </span></p></div></div><p class="MsoNormal" style="margin-right:0in;margin-bottom:12.0pt;margin-left:.5in"><br>_______________________________________________<br>
FreeSWITCH-dev mailing list<br><a href="mailto:FreeSWITCH-dev@lists.freeswitch.org" target="_blank">FreeSWITCH-dev@lists.freeswitch.org</a><br><a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-dev" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-dev</a><br>
UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-dev" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-dev</a><br><a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a></p>
</div><p class="MsoNormal" style="margin-left:.5in"><br><br clear="all"><br>-- <br>-Rupa</p></div></div></div></div></div><br>_______________________________________________<br>
FreeSWITCH-dev mailing list<br>
<a href="mailto:FreeSWITCH-dev@lists.freeswitch.org">FreeSWITCH-dev@lists.freeswitch.org</a><br>
<a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-dev" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-dev</a><br>
UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-dev" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-dev</a><br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<br></blockquote></div><br><br clear="all"><br>-- <br>-Rupa<br>