<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="FR" link="#0563C1" vlink="#954F72" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal">Hello Henning,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">The database I’m using is on a VM hosted on the same physical server as the Kamailio server, so the network overhead is small.<o:p></o:p></p>
<p class="MsoNormal">That said, I’m not sure I would gain much with prepared statements… but a DBA audit suggested it would help.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Anyway… thanks for your input, and for confirming that sqlops cannot support prepared statements. :)<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Regards,<o:p></o:p></p>
<p class="MsoNormal">Nicolas.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span style="mso-fareast-language:FR">De :</span></b><span style="mso-fareast-language:FR"> Henning Westerholt <hw@gilawa.com>
<br>
<b>Envoyé :</b> mardi 19 avril 2022 21:20<br>
<b>À :</b> Kamailio (SER) - Users Mailing List<br>
<b>Cc :</b> Chaigneau, Nicolas<br>
<b>Objet :</b> RE: PostgreSQL - module sqlops - working with prepared statements ?<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="DE" style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="DE">Hello,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="DE"> <o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">the sqlops module only support “standard SQL” queries from the configuration. And practically speaking, if you are executing your query against a remote server, the network overhead will probably substancially higher
than parsing the SQL statement on the server. So, I do not think there is actually a big benefit for using this.</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"> </span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">If you need the last bit of performance, you could always implement a C module and implement it there. From my experience you will gain most with statement batching, especially for write operations.</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"> </span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">Cheers,</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"> </span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">Henning</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"> </span><span lang="DE"><o:p></o:p></span></p>
<div>
<p class="MsoNormal"><span lang="EN-GB">-- </span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">Henning Westerholt – </span><span lang="DE"><a href="https://skalatan.de/blog/"><span lang="EN-GB">https://skalatan.de/blog/</span></a><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">Kamailio services – </span><span lang="DE"><a href="https://gilawa.com/"><span lang="EN-GB">https://gilawa.com</span></a><o:p></o:p></span></p>
</div>
<p class="MsoNormal"><span lang="EN-GB"> </span><span lang="DE"><o:p></o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="margin-left:35.4pt"><b><span lang="DE" style="mso-fareast-language:DE">From:</span></b><span lang="DE" style="mso-fareast-language:DE"> sr-users <<a href="mailto:sr-users-bounces@lists.kamailio.org">sr-users-bounces@lists.kamailio.org</a>>
<b>On Behalf Of </b>Chaigneau, Nicolas<br>
<b>Sent:</b> Tuesday, April 19, 2022 6:25 PM<br>
<b>To:</b> Kamailio (SER) - Users Mailing List <<a href="mailto:sr-users@lists.kamailio.org">sr-users@lists.kamailio.org</a>><br>
<b>Subject:</b> Re: [SR-Users] PostgreSQL - module sqlops - working with prepared statements ?</span><span lang="DE"><o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal" style="margin-left:35.4pt"><span lang="DE"> <o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">Hello,<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">Trying again, in case this got unnoticed the first time.<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">I hope that by doing so I’m not breaching the rules of the mailing list.<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">If nobody answers, then I’ll conclude that nobody knows about that, and shall not insist anymore. :)<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">Regards,<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">Nicolas.<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="margin-left:35.4pt"><b><span style="mso-fareast-language:FR">De :</span></b><span style="mso-fareast-language:FR"> sr-users <<a href="mailto:sr-users-bounces@lists.kamailio.org">sr-users-bounces@lists.kamailio.org</a>>
<b>De la part de</b> Chaigneau, Nicolas<br>
<b>Envoyé :</b> vendredi 8 avril 2022 17:01<br>
<b>À :</b> <a href="mailto:sr-users@lists.kamailio.org">sr-users@lists.kamailio.org</a><br>
<b>Objet :</b> [SR-Users] PostgreSQL - module sqlops - working with prepared statements ?</span><span lang="DE"><o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="mso-fareast-language:FR"> </span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">Hello,<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">I’m using sql_xquery from sqlops module to execute requests on a PostgreSQL database.<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">This work fine.<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">Now, for performance reasons, I would like to use prepared statements.<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">I’m wondering if this is possible using sql_xquery. I’ve looked at the code and documentation, it seems it is not supported.<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">Can you confirm ?<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">If not possible with sql_xquery, is it possible with another existing function ?<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">In the code, I’ve searched for « PQprepare » / « PQexecPrepared ».<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">These can be found only in « modules/db_postgres/pg_cmd.c », in function « pg_cmd_exec » which is exported from « modules/db_postgres/pg_mod.c » (see below).<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">I have no idea how to use these though… :/<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">Any ideas ?<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas">/*</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas">* Postgres module interface</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas">*/</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas">static cmd_export_t cmds[] = {</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas"> {"db_ctx", (cmd_function)NULL, 0, 0, 0, 0},</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas"> {"db_con", (cmd_function)pg_con, 0, 0, 0, 0},</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas"> {"db_uri", (cmd_function)pg_uri, 0, 0, 0, 0},</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas"> {"db_cmd", (cmd_function)pg_cmd, 0, 0, 0, 0},</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas"> {"db_put", (cmd_function)pg_cmd_exec, 0, 0, 0, 0},</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas"> {"db_del", (cmd_function)pg_cmd_exec, 0, 0, 0, 0},</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas"> {"db_get", (cmd_function)pg_cmd_exec, 0, 0, 0, 0},</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas"> {"db_upd", (cmd_function)pg_cmd_exec, 0, 0, 0, 0},</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas"> {"db_sql", (cmd_function)pg_cmd_exec, 0, 0, 0, 0},</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas"> {"db_res", (cmd_function)pg_res, 0, 0, 0, 0},</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas"> {"db_fld", (cmd_function)pg_fld, 0, 0, 0, 0},</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas"> {"db_first", (cmd_function)pg_cmd_first, 0, 0, 0, 0},</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas"> {"db_next", (cmd_function)pg_cmd_next, 0, 0, 0, 0},</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas"> {"db_setopt", (cmd_function)pg_setopt, 0, 0, 0, 0},</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas"> {"db_getopt", (cmd_function)pg_getopt, 0, 0, 0, 0},</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas"> {"db_bind_api", (cmd_function)db_postgres_bind_api, 0, 0, 0, 0},</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas"> {0, 0, 0, 0, 0, 0}</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:Consolas">};</span><span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">Regards,<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">Nicolas.<span lang="DE"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:35.4pt"> <span lang="DE"><o:p></o:p></span></p>
</div>
<span style="font-size: 9px; line-height: 10px;">This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message.</span></body>
</html>