Hi @Pontus Olausson 

I have an MySQL server I connect to with a ODBC provider. I can connect to it like so.

I do not have to state a driver.

If you create the data source, maps it to an Ingest Instance and tests the connection in TimeXtender Data Integration does it do the same then?

You will normally have to set up Advanced Settings for it to work properly.

Like I am doing here

You may not have to change the Prefix and Suffix, that is a MySQL specific change.

After setting up Advanced Settings, try to test the connection again.

Hi Thomas,

The server we are trying to connect to runs on Pervasive PSQL, now known as Actian Zen. Hence we are using the Pervasive ODBC Interface driver.

I have tried to create a data source connection in TimeXtender Data Integration, leaving the “Driver” empty to mimic your setup. When I test the connection I get the same error as before.

I have also mapped it to an Ingest Instance and tested the connection through the TimeXtender client with the same error message.

Hi @Pontus Olausson 

The important part is what I do in the last image. Is that possible, I mean changing the Schema name and View Name?

Hi @Thomas Lind 

I’ve tried changing the Schema Name and View Schema Name as you’ve done above, but didn’t see any different results when testing the connection.

Are these names specific to the source, i.e. MySQL vs. Pervasive PSQL? I haven’t been able to find any information about this or what values I am supposed to enter here.

Hi @Pontus Olausson ,

there is a standard for those but not all DBMS respect the standard. If your Pervasive SQL / Actian Zen database uses the Micro-kernel engine you will likely need to look up the metadata query in the documentation as that is a NoSQL-style system. If your database uses the relational engine it implements ANSI SQL-92 and you should be able to run: SELECT * FROM INFORMATION_SCHEMA.TABLES to get a list of tables. The column name you get back is the column name you should fill in in Thomas’ example iirc.

Hi @rory.smith 

We seem to be using the micro-kernel engine. The tables in the Pervasive SQL / Actian Zen database are stored as .btr files, which I guess indicates that it is based on Btrieve. I have managed to query the DDL files and cannot seem to find any logical schema name to use.

I’m not sure why, but the above error message doesn’t appear anymore and I am able to successfully test the connection. However, when synchronizing the data source I get ‘Completed with errors’: 

Error retrieving a table (unknown):
System.ArgumentException: Column 'TABLE_SCHEM' does not belong to table Tables.
   at System.Data.DataRow.GetDataColumn(String columnName)
   at DataSourceEngine.ADO.ADOSourceEngine.GenerateObjectList(DbConnection connection)

I’ve tried several different values for schema name and view schema name, including the database name and dbo. When querying the tables in PSQL Control Center we use SELECT * FROM “database name”.”table name”, and I can’t find anything like a schema in the DDL files.

Is there a way to configure TimeXtender to disregard schema names or leave it blank? Or do we need to define a schema name somehow?

Hi,

as this is not a relational database and does not support the usual structure, you will either need something like an ODBC driver or commercial driver that exposes the source as a relational-like system.

All I could find was this ancient post that may or may not refer to what you are using: https://communities.actian.com/s/question/0D53300003rfx30CAA/querying-for-a-list-of-table-column-names 

Hi @rory.smith 

I think I understand. We are using an ODBC driver built by Actian (Pervasive ODBC Interface version 12.11.33.00) and are in contact with our provider regarding documentation.

As a sidenote, since our no-sql data source doesn’t structure its data in a way that follow schemas in a relational-system sense, wouldn’t it make sense to be able to configure TimeXtender to use a default value for schemas instead of relying on the ODBC driver to supply schema names? Or is there something I’m missing that necessitates the ODBC driver supplying this?

Hi,

the main idea behind the ODBC standard is to allow for an abstraction away from the vagueries of individual DBMS. If your ODBC driver adheres to ODBC 1.0 at least, there should be a call implemented that returns the table metadata TimeXtender expects. As some of the field names have changed with different versions of ODBC compliance you may need to change the column names, this is why that functionality exists in TimeXtender.

If the ODBC driver does not implement this, then you may be able to find a different driver that exposes the Actian Zen data in a way that is consumable. I know CData delivers a product (not included in TX) that may be able to do this, but I don't know enough about this type of system to have much further info.

You can find some of the relevant ODBC information here: https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqltables-function?view=sql-server-ver16

Hi,

Thanks for the help and link!
After trying some combinations from the source you’ve provided I’ve managed to get it to work, using “TABLE_OWNER” as schema name and view schema name.

Thanks a lot!