Trouble combining data from mysql and oracle datasources

[rpoyner]

We are building an app that combines local data stored in MySQL with upstream data from an Oracle data warehouse. I'm able to create datasources in visual builder that successfully pull data from the different DBs independantly, but when I try to combine them using a foreignkey and includefrom I get SQL errors. SmartClient seems to be using the MySQL driver and credentials to query the oracle database which of course fails. I get this error on several releases of SmartClient. So either this is a consistent bug, or I'm going at this wrong.

Versions:
SmartClient_v101p_2016-12-05_Pro
SmartClient_SNAPSHOT_v101d_2015-06-02_Pro
SmartClient_v110p_2016-11-25_Pro



Log messages. The first line shows smartclient querying an Oracle table with the mysql driver.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command denied to user 'personnelUser'@'rplindesk.che.wisc.edu' for table 'ID_CROSSWALK_UWMSN'
at sun.reflect.GeneratedConstructorAccessor24.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:389)
at com.mysql.jdbc.Util.getInstance(Util.java:372)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2531)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2489)
at com.mysql.jdbc.StatementImpl.exe<br><br>...(3024 bytes truncated - set isc.RPCManager.maxErrorMessageLength > 1000 to see more or check the Developer Console for full

Datasources as used with SmartClient_v110p_2016-11-25_Pro

<DataSource ID="idCrosswalkUWMSN" dataFormat="iscServer" serverType="sql" dataSourceVersion="1" dbName="InfoAccess" schema="UW" tableName="ID_CROSSWALK_UWMSN" quoteTableName="false" tableCode="841e47ab8c3db13a59b61566c5582f0c" allowAdvancedCriteria="false" generatedBy="v11.0p_2016-11-25/Pro Deployment 2016-11-25">
<fields>
<field name="PVI" type="text" length="9" primaryKey="true" columnCode="96e0c2de8af6f3574836252e72f42129">
<validators>
<Validator>
<type>isString</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
<defaultErrorMessage></defaultErrorMessage>
</Validator>
</validators>
</field>
<field name="ISIS_EMPLID" type="text" length="30" columnCode="a438413d627c281795d508f3d2fa9a41">
<validators>
<Validator>
<type>isString</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
<defaultErrorMessage></defaultErrorMessage>
</Validator>
</validators>
</field>
</fields>
</DataSource>


<DataSource ID="peopleInclude" serverType="sql" dataSourceVersion="1" dbName="personnel" tableName="people" quoteTableName="false" tableCode="12a032ce9179c32a6c7ab397b9d871fa" allowAdvancedCriteria="false" generatedBy="v11.0p_2016-11-25/Pro Deployment 2016-11-25">
<fields>
<field name="id" type="sequence" title="5-digit unique ID number" primaryKey="true" columnCode="b80bb7740288fda1f201890375a60c8f">
<validators>
<Validator>
<type>isInteger</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
<defaultErrorMessage>Must be a whole number.</defaultErrorMessage>
<resultingValue>9</resultingValue>
</Validator>
</validators>
</field>
<field name="uwPVI" type="text" length="9" title="Publicly Visible Identifier" foreignKey="idCrosswalkUWMSN.PVI" columnCode="5ec28c133fdd720ac4812f05a9815746">
<validators>
<Validator>
<type>isString</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
<defaultErrorMessage></defaultErrorMessage>
</Validator>
</validators>
</field>
<field name="emplID" includeFrom="idCrosswalkUWMSN.ISIS_EMPLID">
<type></type>
<title></title>
<primaryKey></primaryKey>
<valueXPath></valueXPath>
<required></required>
<length></length>
<canEdit></canEdit>
<hidden></hidden>
<detail></detail>
<foreignKey></foreignKey>
<rootValue></rootValue>
</field>
</fields>
<sequenceMode>jdbcDriver</sequenceMode>
<dataURL></dataURL>
<recordXPath></recordXPath>
</DataSource>

[Isomorphic]

Were you hoping for rough equivalent of a SQL join to be performed in memory in Java? Because you can't do a SQL join across two SQL databases for different vendors.

[rpoyner]

I was in fact hoping for a rough equivalent of a join. There will likely be 3 or more tables involved across 3 databases MySQL to Oracle1 to Oracle2

Are there examples or specific recommendations on how to simulate such a join?

[Blama]

Hi rpoyner,

you might be successful with a database link on Oracle level to the mysql database. SmartClient will then only connect to your Oracle Database.
The other way around should also be possible with federated tables.

Best regards
Blama

[rpoyner]

Is it possible to combine data from datasources of different vendors using a listGrid?

I'm attempting to do something similar to the "SQL Dynamic Field Include" example and failing with:

TypeError: Cannot read property 'includeFrom' of undefined
at _3.isc_Canvas_bindToDataSource [as bindToDataSource]

[Isomorphic]

We can't really do anything with so little information. It could be a bad DataSource definition or many other things.

See the FAQ for the kinds of information you need to include to make it possible for others to help you.