Thread: PSQL = Yes ... JDBC = no ??

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.

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

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 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.

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

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

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

Thanks!!

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.

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!"