pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PSQL = Yes ... JDBC = no ??
8+ messages / 5 participants
[nested] [flat]

* PSQL = Yes ... JDBC = no ??
@ 2023-09-03 16:00  Amn Ojee Uw <amnojeeuw@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Amn Ojee Uw @ 2023-09-03 16:00 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Hello!

This issue really puzzles me beyond intrigue.

Why would this schema:
*SELECT 'CREATE DATABASE jme_test_database' WHERE NOT EXISTS (SELECT 
FROM pg_database WHERE datname = 'jme_test_database')\gexec*
work when entered at the psql prompt, but not when passed as a parameter 
in a JDBC method?

The bellow snip demonstrates the the creation of a String object with 
same character string used in the PSQL#.
//CREATE DATABASE IF NOT EXISTS
//~~~~~~~~~~~~~~~~~~
*var s = new ***myString*(SELECT 'CREATE DATABASE jme_test_database' 
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 
'jme_test_database')\gexec);**
**
**stmt.executeUpdate(s.toString());*

However, this time PostgreSQL-15 complains with an error message saying :

*Exception in thread "main" org.postgresql.util.PSQLException: ERROR: 
syntax error at or near "\"**
**  Position: 122

*

For better clarification, I'd like to show the code to create the schema 
inside Java/JDBC.
---- snip ----

  */
    ...

     var sv = myString();
     ...

     public final myString getCreateDatabase(myString s){
         this.sv.setData("SELECT 'CREATE DATABASE ");
         this.sv.append(s.toString());
         this.sv.append("\'");
         this.sv.append("WHERE NOT EXISTS (SELECT FROM pg_database WHERE 
datname = ");
         this.sv.append("\'");
         this.sv.append(s.toString());
         this.sv.append("\'");
         this.sv.append(")\\gexec");

         return sv;
     }


Any help would be very much appreciated.

Thanks in advance.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PSQL = Yes ... JDBC = no ??
@ 2023-09-03 16:04  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Amn Ojee Uw <amnojeeuw@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Adrian Klaver @ 2023-09-03 16:04 UTC (permalink / raw)
  To: Amn Ojee Uw <amnojeeuw@gmail.com>; pgsql-general@lists.postgresql.org

On 9/3/23 09:00, Amn Ojee Uw wrote:
> Hello!
> 
> This issue really puzzles me beyond intrigue.
> 
> Why would this schema:
> *SELECT 'CREATE DATABASE jme_test_database' WHERE NOT EXISTS (SELECT 
> FROM pg_database WHERE datname = 'jme_test_database')\gexec*
> work when entered at the psql prompt, but not when passed as a parameter 
> in a JDBC method?
> 
> The bellow snip demonstrates the the creation of a String object with 
> same character string used in the PSQL#.
> //CREATE DATABASE IF NOT EXISTS
> //~~~~~~~~~~~~~~~~~~
> *var s = new ***myString*(SELECT 'CREATE DATABASE jme_test_database' 
> WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 
> 'jme_test_database')\gexec);**


\gexec is a psql specific meta-command.

See Meta-Commands here:

https://www.postgresql.org/docs/current/app-psql.html


> 
> Any help would be very much appreciated.
> 
> Thanks in advance.
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PSQL = Yes ... JDBC = no ??
@ 2023-09-03 22:25  Amn Ojee Uw <amnojeeuw@gmail.com>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 3 replies; 8+ messages in thread

From: Amn Ojee Uw @ 2023-09-03 22:25 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Thanks Adrian, I did read the link you suggested.
I was had at the Introduction title, since it makes this mention 
"/Alternatively, input can be from a file or from command line 
arguments./" since the input would be from the back end of the database, 
as I am using Java DBC to access the database.

However, I have not been able to discern the correlation that exist 
between the*JDBC statement *and the definition of *gexec* [/Sends the 
current query buffer to the server, then treats each column of each row 
of the query's output (if any) as an SQL statement to be executed./]

I guess it is that I am so new to the world of PG-15 that I just cannot 
understand or get your point. My reality is that esoteric definition go 
right over my head, since I am a newbie.

Would you be able to make the appropriate corrections to the 
PostgreSQL-15 schema passed to the JDBC Statement.executeUpdate() method?

Thanks in advance.

PD:

Are you saying that JDBC cannot handle or process \gexec, since it is 
PG-15 exclusive?

On 9/3/23 12:04 p.m., Adrian Klaver wrote:
> On 9/3/23 09:00, Amn Ojee Uw wrote:
>> Hello!
>>
>> This issue really puzzles me beyond intrigue.
>>
>> Why would this schema:
>> *SELECT 'CREATE DATABASE jme_test_database' WHERE NOT EXISTS (SELECT 
>> FROM pg_database WHERE datname = 'jme_test_database')\gexec*
>> work when entered at the psql prompt, but not when passed as a 
>> parameter in a JDBC method?
>>
>> The bellow snip demonstrates the the creation of a String object with 
>> same character string used in the PSQL#.
>> //CREATE DATABASE IF NOT EXISTS
>> //~~~~~~~~~~~~~~~~~~
>> *var s = new ***myString*(SELECT 'CREATE DATABASE jme_test_database' 
>> WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 
>> 'jme_test_database')\gexec);**
>
>
> \gexec is a psql specific meta-command.
>
> See Meta-Commands here:
>
> https://www.postgresql.org/docs/current/app-psql.html
>
>
>>
>> Any help would be very much appreciated.
>>
>> Thanks in advance.
>>
>

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PSQL = Yes ... JDBC = no ??
@ 2023-09-03 23:46  Maciek Sakrejda <m.sakrejda@gmail.com>
  parent: Amn Ojee Uw <amnojeeuw@gmail.com>
  2 siblings, 0 replies; 8+ messages in thread

From: Maciek Sakrejda @ 2023-09-03 23:46 UTC (permalink / raw)
  To: Amn Ojee Uw <amnojeeuw@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

On Sun, Sep 3, 2023, 16:25 Amn Ojee Uw <amnojeeuw@gmail.com> wrote:

> Are you saying that JDBC cannot handle or process \gexec, since it is
> PG-15 exclusive?
>

JDBC cannot handle our process \gexec since it is _psql_ exclusive. It's a
command interpreted and evaluated by that client specifically, not by the
Postgres server.

>

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PSQL = Yes ... JDBC = no ??
@ 2023-09-03 23:46  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Amn Ojee Uw <amnojeeuw@gmail.com>
  2 siblings, 0 replies; 8+ messages in thread

From: Adrian Klaver @ 2023-09-03 23:46 UTC (permalink / raw)
  To: Amn Ojee Uw <amnojeeuw@gmail.com>; pgsql-general@lists.postgresql.org

On 9/3/23 15:25, Amn Ojee Uw wrote:

> PD:
> 
> Are you saying that JDBC cannot handle or process \gexec, since it is 
> PG-15 exclusive?
> 


I am saying \gexec is a command unique to the client program psql, it 
has no meaning outside of the program. In other words it is not an SQL 
command and JDBC will not accept it.

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PSQL = Yes ... JDBC = no ??
@ 2023-09-04 00:12  David G. Johnston <david.g.johnston@gmail.com>
  parent: Amn Ojee Uw <amnojeeuw@gmail.com>
  2 siblings, 1 reply; 8+ messages in thread

From: David G. Johnston @ 2023-09-04 00:12 UTC (permalink / raw)
  To: Amn Ojee Uw <amnojeeuw@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Sunday, September 3, 2023, Amn Ojee Uw <amnojeeuw@gmail.com> wrote:
>
> Are you saying that JDBC cannot handle or process \gexec, since it is
> PG-15 exclusive?
>

psql is a client application present in all versions of PostgreSQL.  It has
a bunch of features related to executing SQL queries.  The stuff that it is
documented to do are only doable by it in many cases, and in all cases are
done locally, not by the server.  You cannot send those meta-commands to
the server, it has no clue what to do with them.  And since you are using
JDBC you by definition aren’t using psql.

David J.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PSQL = Yes ... JDBC = no ??
@ 2023-09-04 00:19  Amn Ojee Uw <amnojeeuw@gmail.com>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Amn Ojee Uw @ 2023-09-04 00:19 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

I see now, any suggestions as work around in JDBC?

Thanks!!

On 9/3/23 8:12 p.m., David G. Johnston wrote:
> On Sunday, September 3, 2023, Amn Ojee Uw <amnojeeuw@gmail.com> wrote:
>
>     Are you saying that JDBC cannot handle or process \gexec, since it
>     is PG-15 exclusive?
>
>
> psql is a client application present in all versions of PostgreSQL.  
> It has a bunch of features related to executing SQL queries.  The 
> stuff that it is documented to do are only doable by it in many cases, 
> and in all cases are done locally, not by the server.  You cannot send 
> those meta-commands to the server, it has no clue what to do with 
> them.  And since you are using JDBC you by definition aren’t using psql.
>
> David J.
>

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PSQL = Yes ... JDBC = no ??
@ 2023-09-04 06:15  Peter J. Holzer <hjp-pgsql@hjp.at>
  parent: Amn Ojee Uw <amnojeeuw@gmail.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Peter J. Holzer @ 2023-09-04 06:15 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 2023-09-03 20:19:44 -0400, Amn Ojee Uw wrote:
> I see now, any suggestions as work around in JDBC?

Implement the functionality in Java.

You could do it directly:

    execute
    SELECT 'CREATE DATABASE jme_test_database' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'jme_test_database')
    read the result
    for each row returned
        execute it as SQL

But that would be silly.

Instead you would do it like this:

    execute
    SELECT datname FROM pg_database WHERE datname = 'jme_test_database'
    read the result
    if the result is empty:
        execute
        CREATE DATABASE jme_test_database'

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2023-09-04 06:15 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-03 16:00 PSQL = Yes ... JDBC = no ?? Amn Ojee Uw <amnojeeuw@gmail.com>
2023-09-03 16:04 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-09-03 22:25   ` Amn Ojee Uw <amnojeeuw@gmail.com>
2023-09-03 23:46     ` Maciek Sakrejda <m.sakrejda@gmail.com>
2023-09-03 23:46     ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-09-04 00:12     ` David G. Johnston <david.g.johnston@gmail.com>
2023-09-04 00:19       ` Amn Ojee Uw <amnojeeuw@gmail.com>
2023-09-04 06:15         ` Peter J. Holzer <hjp-pgsql@hjp.at>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox