Re: Best way for Postrgesql to pass info to java and back again? (PL/Java)

Hi Serguei,<br /><br /> Serguei Mokhov wrote:<br /><blockquote cite="mid:039f01c160b5$2b130b60$5dd9fea9@gunn"
type="cite"><prewrap="">----- Original Message ----- <br />From: Ashley Cambrell <a class="moz-txt-link-rfc2396E"
href="mailto:ash@freaky-namuh.com"><ash@freaky-namuh.com></a><br/>Sent: Monday, October 29, 2001 1:35 AM<br /><br
/></pre><blockquotetype="cite"><pre wrap="">I was just mulling over how hard it would to implement Java stored<br
/>procedures...<br /></pre></blockquote><pre wrap=""><br />This is an interesting proposal. I was thiniking of it
also<br/>some time ago when someone inquired whether PG has PL/Java or not...<br /><br /></pre><blockquote
type="cite"><prewrap="">(mulling being the operative word) and was thinking how to<br />best implement postgresql
<->java communications.  (ie shared memory<br />via JNI?)  I have read the past posts regarding possible
PL/Java<br/>implementations and it's basically stopped at "How to implement<br />Postrgesql <-> Java data
passing?".<br/></pre></blockquote><pre wrap=""><br />Maybe the same or a similar way as JDBC folks address
it?</pre></blockquote>I have looked at the JDBC drivers, and it seems to be all native Java code. They actually use
socketand standard buffered i/o readers to to the protcol transport.  (no JNI anywhere [I think])<br /><br /> The main
questionis still how to communicate low level (pre jdbc connection) to postgresql.  How could the waiting JVM (and
procedurerunner) get notified that a waiting procedure request was there.  Maybe  a very small network layer instead of
sharedmemory.  I single shared "notifiy" socket on postgresql's end that the JVM that a request is there [this request
containsenough info to dynamically load the base procedure class and start a thread], the JVM then opens another socket
for2 way comms (so as not to block the notify socket), it then gets the args for the procedure, runs the procedure and
thensends the results back done that comms socket.<br /><br /> ??<br /><blockquote
cite="mid:039f01c160b5$2b130b60$5dd9fea9@gunn"type="cite"><blockquote type="cite"><pre wrap="">I'm not sure if I'd have
timeto (nor the skill to) actually implement<br />anything, but ...<br /></pre></blockquote><pre wrap=""><br />Same
here,but besides having skilled people working on it one has<br />to initiate the idea first, that's what you did :)
andit might<br />turn out to something more tangeable after fair amount of discussion.</pre></blockquote> My thoughts
exactly:-)<br /><blockquote cite="mid:039f01c160b5$2b130b60$5dd9fea9@gunn" type="cite"><blockquote type="cite"><pre
wrap="">Insteadof using SPI to execute sql, the class would use jdbc to connect<br />to the server.  The PLJavaLoader
classcould cache JDBC connections to<br />reduce the connection overhead (?).<br /></pre></blockquote><pre wrap=""><br
/>I'mthinking not to use the JDBC directly, not only to reduce the<br />connection overhead but also calls to JDBC
layeritself. Instead,<br />one can possibly reuse some of the JDBC code, me thinks.<br /></pre></blockquote> The jdbc
codeis a very thin wrapper to a java implemented fe (?) protocol.  Stuff like:<br /><br /> /**<br />    * Sends an
integerto the back end<br />    *<br />    * @param val the integer to be sent<br />    * @param siz the length of the
integerin bytes (size of structure)<br />    * @exception IOException if an I/O error occurs<br />    */<br />   public
voidSendInteger(int val, int siz) throws IOException<br />   {<br />     byte[] buf = bytePoolDim1.allocByte(siz);<br
/><br/>     while (siz-- > 0)<br />       {<br />     buf[siz] = (byte)(val & 0xff);<br />     val >>=
8;<br/>       }<br />     Send(buf);<br />   }<br /><br /><br /> There doesn't seem to be any advantage to using
anythingunder the jdbc protocol.  It would only make it more complex.<br /><br /> Unless there was a reason to use SPI
thatI don't know about?<br /><blockquote cite="mid:039f01c160b5$2b130b60$5dd9fea9@gunn" type="cite"><pre wrap=""><br
/>Justa couple of quick thoughts...<br /></pre></blockquote> Thanks Serguei<br /><blockquote
cite="mid:039f01c160b5$2b130b60$5dd9fea9@gunn"type="cite"><pre wrap=""><br />-s<br /><br /><br /></pre></blockquote>
AshleyCambrell<br /><br />