Re: Unable to convert database to NATIVE authentication

[Rick Hillegas]

On 11/17/17 6:37 PM, Bryan Pendleton wrote:
Afer that worked, I replaced "jdbc:derby:memory:db" with
"jdbc:derby://localhost:1527/demo4" and got the exception I'm pasting below
this. I don´t know what I'm configuring wrong.
Exception in thread "main" java.sql.SQLSyntaxErrorException:
'SYSCS_UTIL.SYSCS_CREATE_USER' no se reconoce como una función o
procedimiento.

Perhaps your Derby Network Server is running an old version of Derby?

You could have multiple copies of Derby on your system, and depending on the
CLASSPATH you are not running the same version of Derby in the different tests?

Here's how to find out what version of Derby you're running:

https://wiki.apache.org/db-derby/VersionInfo

I think SYSCS_CREATE_USER may have been added in Derby 10.9?

bryan

Attaching a revised version of the test program, which lets you specify 
the connection URL. If I bring up the network server, I get the 
following expected results:

$ java CreateNativeUser "jdbc:derby://localhost:8246/db"
DERBY SQL error: ERRORCODE: 45000, SQLSTATE: 08006, SQLERRMC: Database 
'db' shutdown.
Correctly failed to connect without credentials: Connection 
authentication failure occurred.  Reason: Userid or password invalid.
Successfully connected as user APP.

I think that Bryan has put his finger on the likeliest cause of your 
problem.

Hope this helps,

-Rick

import java.sql.*;

public class CreateNativeUser
{
  private static final String DEFAULT_DB_URL = "jdbc:derby:memory:db";

  /**
   * arg0 - Optional database url, e.g., "jdbc:derby:memory:db"
   */
  public static void main(String... args) throws Exception
  {
    String dbURL = DEFAULT_DB_URL;
    if ((args != null) && (args.length > 0)) { dbURL = args[0]; }
    
    {
      Connection conn = DriverManager.getConnection(dbURL + ";create=true");
      conn.prepareCall("call SYSCS_UTIL.SYSCS_CREATE_USER('app', 
'app')").execute();
    }

    // verify that user was created. bounce the database and re-connect
    try
    {
      DriverManager.getConnection(dbURL + ";shutdown=true");
    }
    catch (SQLException se) { println(se.getMessage()); }

    // should fail because no credentials are provided
    try
    {
      Connection conn = DriverManager.getConnection(dbURL);
      println("Hm, should not have been able to connect without credentials.");
    }
    catch (SQLException se)
    { println("Correctly failed to connect without credentials: " + 
se.getMessage()); }

    // now connect with correct credentials
    try
    {
      Connection conn = DriverManager.getConnection(dbURL + 
";user=app;password=app");
      println("Successfully connected as user APP.");
    }
    catch (SQLException se)
    { println("Did not expect to fail authentication: " + se.getMessage()); }
  }

  private static void println(String text) { System.out.println(text); }
}