Re: loading jdbc Driver in servlet

[Dave Cramer]

My guess is it has something to do with your servlet classpath loader.
Which servlet engine are you using ?
Dave Cramer

da...@postgresintl.com
www.postgresintl.com


On Fri, 14 Dec 2018 at 16:04, Rob Sargent  wrote:

>
>
> On Dec 14, 2018, at 2:02 PM, Rob Sargent  wrote:
>
>
>
> On Dec 14, 2018, at 1:30 PM, Dave Cramer  wrote:
>
> Strange, I wouldn't think so, but then I haven't used a raw servlet for so
> long I have no idea.
>
>
> Dave Cramer
>
> da...@postgresintl.com
> www.postgresintl.com
>
>
> On Fri, 14 Dec 2018 at 13:29, Rob Sargent  wrote:
>
>> Using java 1.8, postgresql-42.1.4.jar, embedded tomcat 9
>>
>> It appears to me that I need to make the call
>> "Class.forName("org.postgresql.Driver)" when the entry is in a servlet.  Is
>> this expected, within a servlet, or is this just *post hoc ergo propter
>> hoc *at it finest and I changed something else (wittingly or not).  Same
>> code outside of servlet does not need the forced loading of the class and
>> the manual claims it's not need after java 1.6
>>
> I too am suspicious.  But the vagaries of javax are daunting.
> But if I comment it out, as just now, “No suitable driver found...”.  I
> was days playing with configuration and such think that this very specific
> error message was telling me my CLASSPATH was wrong.
>
> I wonder if I have an old javax installation (which I put in place just
> recently).
>
>
> I’m using javax.servlet-api-3.1.0.jar
>
>
>
>
>

Re: loading jdbc Driver in servlet

[Rob Sargent]

Tomcat version 9. Embedded in my main()

> On Dec 16, 2018, at 9:30 AM, Dave Cramer  wrote:
> 
> My guess is it has something to do with your servlet classpath loader. Which 
> servlet engine are you using ?
> Dave Cramer
> 
> da...@postgresintl.com
> www.postgresintl.com
> 
> 
>> On Fri, 14 Dec 2018 at 16:04, Rob Sargent  wrote:
>> 
>> 
 On Dec 14, 2018, at 2:02 PM, Rob Sargent  wrote:
 
 
 
 On Dec 14, 2018, at 1:30 PM, Dave Cramer  wrote:
 
 Strange, I wouldn't think so, but then I haven't used a raw servlet for so 
 long I have no idea.
 
 
 Dave Cramer
 
 da...@postgresintl.com
 www.postgresintl.com
 
 
> On Fri, 14 Dec 2018 at 13:29, Rob Sargent  wrote:
> Using java 1.8, postgresql-42.1.4.jar, embedded tomcat 9
> 
> It appears to me that I need to make the call 
> "Class.forName("org.postgresql.Driver)" when the entry is in a servlet.  
> Is this expected, within a servlet, or is this just post hoc ergo propter 
> hoc at it finest and I changed something else (wittingly or not).  Same 
> code outside of servlet does not need the forced loading of the class and 
> the manual claims it's not need after java 1.6
> 
>>> I too am suspicious.  But the vagaries of javax are daunting.
>>> But if I comment it out, as just now, “No suitable driver found...”.  I was 
>>> days playing with configuration and such think that this very specific 
>>> error message was telling me my CLASSPATH was wrong.
>>> 
>>> I wonder if I have an old javax installation (which I put in place just 
>>> recently).
>>> 
>>> 
>> I’m using javax.servlet-api-3.1.0.jar
>>> 
>>> 
>> 

Re: loading jdbc Driver in servlet

[Dave Cramer]

So you are starting up tomcat yourself ? Perhaps that is the difference ?
I have no idea what the tomcat wrapper does, but I'd be curious if the same
thing happens when stared normally

Dave Cramer

da...@postgresintl.com
www.postgresintl.com


On Sun, 16 Dec 2018 at 12:20, Rob Sargent  wrote:

> Tomcat version 9. Embedded in my main()
>
> On Dec 16, 2018, at 9:30 AM, Dave Cramer  wrote:
>
> My guess is it has something to do with your servlet classpath loader.
> Which servlet engine are you using ?
> Dave Cramer
>
> da...@postgresintl.com
> www.postgresintl.com
>
>
> On Fri, 14 Dec 2018 at 16:04, Rob Sargent  wrote:
>
>>
>>
>> On Dec 14, 2018, at 2:02 PM, Rob Sargent  wrote:
>>
>>
>>
>> On Dec 14, 2018, at 1:30 PM, Dave Cramer  wrote:
>>
>> Strange, I wouldn't think so, but then I haven't used a raw servlet for
>> so long I have no idea.
>>
>>
>> Dave Cramer
>>
>> da...@postgresintl.com
>> www.postgresintl.com
>>
>>
>> On Fri, 14 Dec 2018 at 13:29, Rob Sargent  wrote:
>>
>>> Using java 1.8, postgresql-42.1.4.jar, embedded tomcat 9
>>>
>>> It appears to me that I need to make the call
>>> "Class.forName("org.postgresql.Driver)" when the entry is in a servlet.  Is
>>> this expected, within a servlet, or is this just *post hoc ergo propter
>>> hoc *at it finest and I changed something else (wittingly or not).
>>> Same code outside of servlet does not need the forced loading of the class
>>> and the manual claims it's not need after java 1.6
>>>
>> I too am suspicious.  But the vagaries of javax are daunting.
>> But if I comment it out, as just now, “No suitable driver found...”.  I
>> was days playing with configuration and such think that this very specific
>> error message was telling me my CLASSPATH was wrong.
>>
>> I wonder if I have an old javax installation (which I put in place just
>> recently).
>>
>>
>> I’m using javax.servlet-api-3.1.0.jar
>>
>>
>>
>>
>>

Re: new stored procedure with OUT parameters

[Anton Shen]

Thanks for the thoughts. The part I'm missing is that why procedures with
OUT param 'will not be called from SQL environments'?

Thanks,
Anton

On Sat, Dec 15, 2018 at 10:03 AM Pavel Stehule 
wrote:

> Hi
>
> út 11. 12. 2018 v 7:20 odesílatel Anton Shen <4175geo...@gmail.com>
> napsal:
>
>> Hi all,
>>
>> I was playing around with the stored procedure support in v11 and found
>> that pure OUT parameters are not supported. Is there any reason we only
>> support INOUT but not OUT parameters?
>>
>
> The procedure implementation in v11 is initial stage - only functionality
> with some simple implementation or without design issues was implemented.
>
> If I remember there was not clean what is correct and expected behave of
> usage of OUT variable when it is called from SQL environment, and when it
> is called from plpgsql.
>
> On Oracle - the OUT variables are part of procedure signature - you can
> write procedures P1(OUT a int), P1(OUT a text). Currently we have not a
> variables in SQL environment. So if Peter implemented OUT variables now then
>
> a) only IN parameters will be part of signature - like functions - but it
> is different than on Oracle, and we lost a possibility to use interesting
> feature
> b) the procedures with OUT variables will not be callable from SQL
> environment - that be messy for users.
> c) disallow it.
>
> I hope so PostgreSQL 12 will have schema variables, and then we can
> implement OUT variables. Now, it is not possible (do it most correct) due
> missing some other feature. INOUT parameters are good enough, and we have
> opened door for future correct design.
>
> Regards
>
> Pavel
>
>
>
>>
>> psql (11.0 (Homebrew petere/postgresql))
>> dev=# CREATE PROCEDURE test_sp(a OUT int) LANGUAGE plpgsql AS $$
>> dev$# BEGIN
>> dev$# a = 5;
>> dev$# END; $$;
>> ERROR:  procedures cannot have OUT arguments
>> HINT:  INOUT arguments are permitted.
>>
>> Thanks,
>> Anton
>>
>

Re: new stored procedure with OUT parameters

[Pavel Stehule]

ne 16. 12. 2018 v 20:33 odesílatel Anton Shen <4175geo...@gmail.com> napsal:

> Thanks for the thoughts. The part I'm missing is that why procedures with
> OUT param 'will not be called from SQL environments'?
>

PostgreSQL, Oracle has function/procedure overloading. The function
signature - that is unique for any function is composed from all parameters
(on Oracle) or from only IN parameters (on PostgreSQL).

On Oracle I can have a procedures P(IN int, OUT varchar2), P(IN int, OUT
number) - it is not possible on Postgres, because OUT parameters are not
part of signature there. There is some workaround with passing fake
variable like P(IN int, IN varchar, OUT varchar), P(IN int, IN numeric, OUT
numeric). It is not nice, but there is not any hope to fix it due ensuring
compatibility.

The procedures are new - and now, there are time to define behave. You can
see, so INOUT parameters are passed to procedure similar to Oracle.

Because Peter didn't allow OUT variables, then he didn't need to solve a
question what parameters are part of signature. IN, INOUT parameters are
from signature perspective IN parameters - so it is nothing new.

SQL environment has not any variables (now). So you cannot to use any OUT
parameter from this environment - and then has not sense to use signature
with OUT parameters. But it is possible from PL/pgSQL - and it is not
consistent.




> Thanks,
> Anton
>
> On Sat, Dec 15, 2018 at 10:03 AM Pavel Stehule 
> wrote:
>
>> Hi
>>
>> út 11. 12. 2018 v 7:20 odesílatel Anton Shen <4175geo...@gmail.com>
>> napsal:
>>
>>> Hi all,
>>>
>>> I was playing around with the stored procedure support in v11 and found
>>> that pure OUT parameters are not supported. Is there any reason we only
>>> support INOUT but not OUT parameters?
>>>
>>
>> The procedure implementation in v11 is initial stage - only functionality
>> with some simple implementation or without design issues was implemented.
>>
>> If I remember there was not clean what is correct and expected behave of
>> usage of OUT variable when it is called from SQL environment, and when it
>> is called from plpgsql.
>>
>> On Oracle - the OUT variables are part of procedure signature - you can
>> write procedures P1(OUT a int), P1(OUT a text). Currently we have not a
>> variables in SQL environment. So if Peter implemented OUT variables now then
>>
>> a) only IN parameters will be part of signature - like functions - but it
>> is different than on Oracle, and we lost a possibility to use interesting
>> feature
>> b) the procedures with OUT variables will not be callable from SQL
>> environment - that be messy for users.
>> c) disallow it.
>>
>> I hope so PostgreSQL 12 will have schema variables, and then we can
>> implement OUT variables. Now, it is not possible (do it most correct) due
>> missing some other feature. INOUT parameters are good enough, and we have
>> opened door for future correct design.
>>
>> Regards
>>
>> Pavel
>>
>>
>>
>>>
>>> psql (11.0 (Homebrew petere/postgresql))
>>> dev=# CREATE PROCEDURE test_sp(a OUT int) LANGUAGE plpgsql AS $$
>>> dev$# BEGIN
>>> dev$# a = 5;
>>> dev$# END; $$;
>>> ERROR:  procedures cannot have OUT arguments
>>> HINT:  INOUT arguments are permitted.
>>>
>>> Thanks,
>>> Anton
>>>
>>

Re: new stored procedure with OUT parameters

[Adrian Klaver]

On 12/16/18 11:33 AM, Anton Shen wrote:
Thanks for the thoughts. The part I'm missing is that why procedures 
with OUT param 'will not be called from SQL environments'?


Pretty sure Pavel was referring to:

https://www.postgresql.org/docs/11/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS

"Notice that output parameters are not included in the calling argument 
list when invoking such a function from SQL. This is because PostgreSQL 
considers only the input parameters to define the function's calling 
signature. ..."



From this commit:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e4128ee767df3c8c715eb08f8977647ae49dfb59

"SQL procedures

This adds a new object type "procedure" that is similar to a function
but does not have a return type and is invoked by the new CALL statement
instead of SELECT or similar.
...

While this commit is mainly syntax sugar around existing functionality,
future features will rely on having procedures as a separate object
type."

I read this to mean that since SQL functions don't have OUT in the 
signature at this time, SQL procedures do not either.




Thanks,
Anton

On Sat, Dec 15, 2018 at 10:03 AM Pavel Stehule > wrote:


Hi

út 11. 12. 2018 v 7:20 odesílatel Anton Shen <4175geo...@gmail.com
> napsal:

Hi all,

I was playing around with the stored procedure support in v11
and found that pure OUT parameters are not supported. Is there
any reason we only support INOUT but not OUT parameters?


The procedure implementation in v11 is initial stage - only
functionality with some simple implementation or without design
issues was implemented.

If I remember there was not clean what is correct and expected
behave of usage of OUT variable when it is called from SQL
environment, and when it is called from plpgsql.

On Oracle - the OUT variables are part of procedure signature - you
can write procedures P1(OUT a int), P1(OUT a text). Currently we
have not a variables in SQL environment. So if Peter implemented OUT
variables now then

a) only IN parameters will be part of signature - like functions -
but it is different than on Oracle, and we lost a possibility to use
interesting feature
b) the procedures with OUT variables will not be callable from SQL
environment - that be messy for users.
c) disallow it.

I hope so PostgreSQL 12 will have schema variables, and then we can
implement OUT variables. Now, it is not possible (do it most
correct) due missing some other feature. INOUT parameters are good
enough, and we have opened door for future correct design.

Regards

Pavel


psql (11.0 (Homebrew petere/postgresql))
dev=# CREATE PROCEDURE test_sp(a OUT int) LANGUAGE plpgsql AS $$
dev$# BEGIN
dev$# a = 5;
dev$# END; $$;
ERROR:  procedures cannot have OUT arguments
HINT:  INOUT arguments are permitted.

Thanks,
Anton




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: loading jdbc Driver in servlet

[Rob Sargent]

> On Dec 16, 2018, at 12:12 PM, Dave Cramer  wrote:
> 
> So you are starting up tomcat yourself ? Perhaps that is the difference ?
> I have no idea what the tomcat wrapper does, but I'd be curious if the same 
> thing happens when stared normally
> 
> Dave Cramer
> 
Whatever the is the difference, it undoes changes in the language since java 
1.6??!!

Re: loading jdbc Driver in servlet

[Rob Sargent]

> On Dec 16, 2018, at 5:55 PM, Martin Gainty  wrote:
> 
> 99% of the problems with 'isolated classloader' are solved using the class 
> e.g.
> 
Granted. But this seems to fly in the face of both Postgres docs and modern 
JDKs. 

Re: loading jdbc Driver in servlet

[Thomas Kellerer]

Rob Sargent schrieb am 14.12.2018 um 19:28:
> Using java 1.8, postgresql-42.1.4.jar, embedded tomcat 9
> 
> It appears to me that I need to make the call
> "Class.forName("org.postgresql.Driver)" when the entry is in a
> servlet.  Is this expected, within a servlet, or is this just /post
> hoc ergo propter hoc /at it finest and I changed something else
> (wittingly or not).  Same code outside of servlet does not need the
> forced loading of the class and the manual claims it's not need after
> java 1.6

Class.forName() is definitely not needed if the driver's JAR file is 
included in the classloader of the class requesting a connection. 

Where exactly did you put the JDBC driver's jar file? 
And what exactly is your main() method doing?

If you look at Tomcat's startup script (catalina.sh or catalina.bat), it's
obvious that setting up the claspath isn't that straightforward. 
My guess is, that that your main() method does something different 
and does not properly include the driver's jar in the classpath. 

Thomas