Cannot import from lowercase database in mySQL

[Former Member]

Hi,

I am using BODI 11.7.3.2, with a mySQL 5.0 database as a source.

Most of my databases are in uppercase, and I have never had a problem with it, but today I wanted to import tables from a new database, called dgwh, lowercase, and it gave an error which included a reference to the database 'DGWH' (uppercase). Since our installation of mySQL is case-sensitive, this did not work.

Looking for a solution I stumbled upon the following:

I created the datastore for the new database today, but another datastore on the same database server linking to a different database was created using version 11.5, and upgraded to 11.7.3.2. Using this alternative datastore, I could import the table without any problems!

Which led me to exporting both datastores and doing a compare. And yes, there was a difference: the line

"case_sensitive" = 'yes',

was included in the datastore imported from 11.5, but not at all present in the newly created datastore.

I figure that line is the one causing my problem. However, I don't see an option in my datastore to set any parameter concerning case-sensitivity. Is that option available?

Cheers

Tako Molanus

[Former Member · Accepted solution]

the case_sensitive option for datastore is determined automatically, this can't be set manually by the user

what is the Database type you are using in Datastore for MySQL database in 11.7? ODBC or MySQL

I don't think MySQL as database type for datastore is supported in 11.5.3, you might have used ODBC

try creating a new datastore in 11.7 for MySQL and use ODBC as database type and check whats the case_sensitive flag in that case

May be its not able to determine the case sensivity correctly if database type used is MySQL, may be a bug?

[Former Member · Accepted solution]

Manoj:

I was using ODBC as the database type, but I tried again with both mySQL (which I don't believe was there in version 11.5, otherwise we would have probably used it before) and ODBC, but without luck, the problems remains. In both cases I can't find an indicator saying anything about using upper- or lowercase names, so I could not try that part of your suggestion. But maybe I am missing something, where can I find that?

Still, it is strange that I am able to do this from the ATL, but not from the client.

We saw something similar in version 11.5: it was not possible to import a table from a different mySQL database than the one we logged in to in the datastore (we had a datastore for database STG, but wanted to look at a table from database DWH, so we wanted something like STG.DWH.DWHTable). If we changed this in the ATL however, it was no problem.

How come the ATl supports more than the client?

Hai:

Thanks for the teaser

I will suggest moving to version 12, but it is not my decision to make....

Edited by: Tako Molanus on Oct 28, 2008 8:06 AM

[Former Member · Accepted solution]

I tried this on 11.7.3.6, and I am getting the correct information for case-sensitivity of database (the case-sensitive option is yes in Datastore ATL). The database name gets imported in lower case

I have also tried importing a table which is on different database and the user that I defined in datastore has permission on that database. If I double click on Datastore in Designer it lists tables from both the databases.

I think you are trying to import tables from 2 databases (one is case sensitive and one is not case sensitive?) What is the case sensitivity of database that you have defined in System DSN and used that as datasource in Datastore

[Former Member · Accepted solution]

as you mentioned earlier in 11.7.3.2 export of datastore you don't see "case_sensitive" = 'yes', is this option missing in 11.7.3.2 ATL export or its value is "case_sensitive" = 'no' ? if you create a Datastore of any other database type is this option missing in that case also ?

if its missing then your repository is corrupted, its missing some data

check the output of following query select object_name,option_name from AL_OPTIONS WHERE OPTION_NAME LIKE 'case%'; in your repo

what is the database type of your repository ?

how did you create the 11.7 repo ? upgrade using repository manager from 11.5 or create a new repo in 11.7 and import the 11.5 ATL

try creating a new repository in 11.7.3.2 and define a new datastore in that and check if it works properly

[Former Member · Accepted solution]

Hi Manoj,

Yes, that seems to be it: if I try it in a newly created repository, it goes ok.

That seems to solve the issue.

Thanks.

Cheers

Tako

[werner_daehn · Accepted solution]

Excellent work!

[Former Member]

Hi,

I have seen this issue before and that it is an issue with the DI version 11.7.

I have tested this on version 12.0 of Data Services and this issue is resolved.

Cheers

Hai.