Generic Datasource question

[m.ardito]

Hi,

Using standard ODBC (no teiid involved), I can create on each client pc a DSN which does not specify a database (it just has the mysql server address and user/pwd).

Then, from my apps, I can create several connections using that only saved DSN, even if there of course I have to specify a valid db name for the user, on that server.

In this way I can keep a single DSN but reuse it for many different db connections.

I was wondering if, in a similar way, it was possible in Teiid to create a similar "generic" datasource, so that I can then use it in a multisource XML vdb, each time specifying a valid db name for the user, on that server.

Why? If this can work, I can have one single DS for many databases on that server, and just the vdb will know which database it will use on that DS, greatly simplifying the management (I have many databases on that server).

It would work exactly like I am doing on my ODBC app, but through the teiid magic.

I tried and, while I can create the DS  like

<datasources>

    <datasource jta="false" jndi-name="java:/mysqlserver" pool-name="mysqlserver" enabled="true" use-ccm="false">

        <connection-url>jdbc:mysql://192.168.1.1:3306/</connection-url>

        <driver-class>com.mysql.jdbc.Driver</driver-class>

        <driver>mysql-connector-java-5.1.22-bin.jar</driver>

        <security>

            <user-name>user</user-name>

            <password>pass</password>

        </security>

        <validation>

            <validate-on-match>false</validate-on-match>

            <background-validation>false</background-validation>

        </validation>

        <statement>

            <share-prepared-statements>false</share-prepared-statements>

        </statement>

    </datasource>

</datasources>

and enable it. And I can connect both from the web consele, and from Squirrel .

But if I use it in the vdb with just the jndi name, like

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<vdb name="MyVDB" version="1">

    <description>Mysql server VDB</description>

    <property name="UseConnectorMetadata" value="true" />

     <model name="Testmysql">

        <property name="importer.useFullSchemaName" value="false"/>

        <source name="mysql5-connector" translator-name="mysql5" connection-jndi-name="java:/mysqlserver"/>

    </model>

</vdb>

it does not work, the vdb fails loading (as I expected...). I also tried using (guessing) a connection-jndi-name syntax like" java:/mysqlserver/mydb", which does not work either.

Is there a syntax that allows to use such DS in the VDB specifying the database each time?

If not, would it be possible/worth to implement such behaviour?

Marco

[shawkins]

> Is there a syntax that allows to use such DS in the VDB specifying the database each time?

Not through JNDI.  There are some potential hooks for this in JCA or Teiid.  There is a JDBC translator method getConnection that you can use to connect to the appropriate database based upon the user.  Similarly if the mapping of user to database is simple, you could use something like the connection validation sql to issue a statement to connect to the appropriate database when the connection is obtained from the pool.

> If not, would it be possible/worth to implement such behaviour?

There is [TEIID-3738] Add multi-tenant functionality - JBoss Issue Tracker to capture adding higher level support for connection based multi-tenant functionality.  However it seem like this will generally require plugging in some custom code, so using the getConnection translator extension may be fine for now.

[m.ardito]

Thanks for all infos,

I'll dig through. the docs.. but it seems this is not something I can set in a dynamic VDB XML, but only in java, perhaps?

Currently my apps are just ODBC clients, so I could have no other choice than create dozens of mysql DS, all identical but the DB name, and then make the VDBs to use them...

I hope this will not cause performance issues or any other trouble...

[shawkins]

> I'll dig through. the docs.. but it seems this is not something I can set in a dynamic VDB XML, but only in java, perhaps?

Using a Java based translator extension is likely the easiest / most flexible way yes.

The rough outline would be to create a subclass of the BaseDelegatingExecutionFactory and override the getConnection(F factory, ExecutionContext executionContext) method.  You will expect the factory to be the generic mysql DataSource and you'll use the user information on the ExecutionContext to issue a statement to the connection you obtain to connect to the desired database.  You'd also call ExecutionFactory.setScope(Scope.USER) to indicate that the results could be specific to each user.  This assumes there is a system account on the mysql data source that allows it use all of the target databases.

> I hope this will not cause performance issues or any other trouble...

The additional overhead for Teiid per vdb will depend upon how much metadata there is and how many internal materialized views are created.  Having separate pools will likely not be an issue until you are dealing with multiple hundreds of connections to a single instance.