Connection Nesper to external database PgSQL/SQLite

Question

I'm trying to connect Nesper to an external database (originally sqlite) but nothing is able to resolve the type. I get an exception at the configure routine "com.espertech.esper.client.EPException: 'Unable to resolve type for driver 'PgSQL''" Seems to affect PgSQL as well. Any help on whats missing?

esper.xml config

<esper-configuration
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns="http://www.espertech.com/schema/esper">
  <database-reference name="db2">
    <driver type="PgSQL" connection-string="Host=nesper-pgsql-integ.local;Database=test;Username=esper;Password=3sp3rP@ssw0rd;"/>
    <connection-settings auto-commit="false" catalog="test" read-only="true" transaction-isolation="ReadCommitted"/>
    <connection-lifecycle value="retain"/>
    <lru-cache size="1000"/>
    <column-change-case value="uppercase"/>
    <metadata-origin value="metadata"/>
  </database-reference>


</esper-configuration>

Initialization routine throws an exception at config.Configure(url)

     static void InitializeEsper()
        {

            string url = @".\esper.xml";
            Configuration config = new Configuration();
            config.Configure(url); // Fails here 
            var serviceProvider = EPServiceProviderManager.GetDefaultProvider(config);
       
            _runtime = serviceProvider.EPRuntime;
            _administrator = serviceProvider.EPAdministrator;
          
            _administrator.Configuration.AddEventType<TradeEvent>();
            _administrator.Configuration.AddEventType<SampleEvent>();
            _administrator.Configuration.AddEventType<QueryEvent>();
          
        }

The exception message is: "Unable to resolve type for driver 'PgSQL'"

The stacktrace is:

" at com.espertech.esper.client.DbDriverFactoryConnection.ResolveDriverTypeFromName(String driverName)\r\n at com.espertech.esper.client.ConfigurationDBRef.SetDatabaseDriver(IContainer container, String driverName, Properties properties)\r\n at com.espertech.esper.client.ConfigurationParser.HandleDatabaseRefs(Configuration configuration, XmlElement element)\r\n at com.espertech.esper.client.ConfigurationParser.DoConfigure(Configuration configuration, XmlElement rootElement)\r\n at com.espertech.esper.client.ConfigurationParser.DoConfigure(Configuration configuration, Stream stream, String resourceName)\r\n at com.espertech.esper.client.Configuration.Configure(String resource)\r\n at SlidingWindow.Program.InitializeEsper() in C:\Users\esas\source\repos\esas\Nesper-Practice\SlidingWindow\Program.cs:line 116\r\n at SlidingWindow.Program.Main(String[] args) in C:\Users\esas\source\repos\esas\Nesper-Practice\SlidingWindow\Program.cs:line 33"

I was able to add a database in code (totally separate project)

        static void InitializeAndRun()
        {
            var container = ContainerExtensions.CreateDefaultContainer(false);
            container.RegisterDatabaseDriver(typeof(DbDriverSQLite)).InitializeDefaultServices().InitializeDatabaseDrivers();
           
            Configuration config = new Configuration(container);

            //Add database
            ConfigurationCommonDBRef dbref = new ConfigurationCommonDBRef();
            dbref.SetDatabaseDriver("DbDriverSQLite", "Data Source=.\\Db\\esperapp.db", new Properties());
            config.Common.AddDatabaseReference("esperdb", dbref);

            Console.WriteLine("Found Databases {0}", config.Common.DatabaseReferences.Count.ToString());

            _runtime = EPRuntimeProvider.GetDefaultRuntime(config);


            var statementText = "select * from " +
      "sql:esperdb [\"SELECT Timestamp,Source,Status FROM Events\"] output all";
          
            var statement = _runtime.DeployStatement(statementText);

            //Print Table contents
            var enumerator = statement.GetEnumerator();

            while (enumerator.MoveNext())
            {
                var e = enumerator.Current;
                Console.WriteLine("{0}, {1}, {2} ", e.Get("Timestamp"), e.Get("Source"), e.Get("Status"));
            }
        }

Answer 1

Below is the code that resolves the driver type i.e. method ResolveDriverTypeFromName of DbDriverConnectionHelper. It tries to resolve the name according to the steps below.

/// <summary>
/// Resolves the driver type from the name provided.  If the driver can not be
/// resolved, the method throws an exception to indicate that one could not
/// be found.  The method first looks for a class that matches the name of
/// the driver.  If one can not be found, it checks in the com.espertech.esper.epl.drivers
/// namespace to see if one can be found.  Lastly, if checks in the
/// com.espertech.espers.eql.drivers namespace with a variation of the given driver name.
/// </summary>
/// <param name="driverName">Name of the driver.</param>
/// <returns></returns>
public static Type ResolveDriverTypeFromName(string driverName)
{
    Type driverType;

    // Check for the type with no modifications
    if ((driverType = TypeHelper.ResolveType(driverName, false)) != null) {
        return driverType;
    }

    // Check for the type in the driverNamespace
    string specificName = $"{DriverNamespace}.{driverName}";
    if ((driverType = TypeHelper.ResolveType(specificName, false)) != null) {
        return driverType;
    }

    // Check for the type in the driverNamespace, but modified to include
    // a prefix to the name.
    string pseudoName = $"{DriverNamespace}.DbDriver{driverName}";
    if ((driverType = TypeHelper.ResolveType(pseudoName, false)) != null) {
        return driverType;
    }

    // Driver was not found, throw an exception
    throw new EPException("Unable to resolve type for driver '" + driverName + "'");
}