I am still trying to use AnyDAC and because of outstanding issues with version 1.10.1 I 'hacked' your code to find out, what's wrong... You remember: I have trouble with some tables, which have names with upper- and lowercase letters (to use only lowercase tablenames is no solution because of existing databases). I hope, my work will help you to find and fix the pieces of code. For me it's a serious problem and I am surprised that otherwise nobody seems to have the same problems.
First: MySQL I told you before, that there is a exception with MySQL-Dirver, but after that, the SELECT statement retrieves correct values. I was interested in that exception and modified daADPhysMySQL.pas / TADPhysMySQLConnection.DoQuery (line 465):
I recognize, that exception comes from 'SHOW INDEX FROM `testtbl´' - there must be 'SHOW INDEX FROM `TestTbl´' and that comes from TADPhysMySQLCommand.GenerateMetaInfoSQL (line 832):
mkIndexes,
mkIndexFields,
mkPrimaryKey,
mkPrimaryKeyFields:
begin
sSQL := 'SHOW INDEX';
if sWildcard <> '' then
sSQL := sSQL + ' LIKE ''' + sWildcard + '''';
sSQL := sSQL + ' FROM '; { knulo }
if GetMetaInfoKind in [mkIndexFields, mkPrimaryKeyFields] then
sSQL := sSQL + NormName(GetBaseObjectName)
else
sSQL := sSQL + NormName(GetCommandText);
end;
If I modify the line marked with knulo and write ' FROM TestTbl 'and remove the next four lines, it works without any exception.
The subsequent query statement 'SELECT * FROM TestTbl' comes with no modification of table name and works fine.
The MessageBox told me the table name in LOWERCASE letters and that is wrong, because the ODBC-connection points to a case sensitive database and the table name is 'TestTbl'.
Third: ORACLE Also some trouble with table names, I'm sure: same reason because ORACLE assumes table names in all uppercase.
Fourth: BCB5 compilation errors (please translate and send it to Sergej - thank you)
1) with 'mysql -V': mysql Ver 12.21 Distrib 4.0.15 for sus-linux (i686) and if I enter mysql: server version 4.0.15-Max I use InnoDB tables.
2) FNameCaseSensitive is set to FALSE Have I to edit the my.cnf and if YES: what changes are required (what section)? Currently there is no variable 'lower_case_table_names' in the configuration file.
Regards knulo
*edit1* Sorry, I realized in this moment, that I spelled your first name wrong all the time.
*edit 2* I asked MySQL documentation about 'lower_case_table_names':
If set to 1 table names are stored in lowercase on disk and table name comparisons are not case sensitive. This variable was added in MySQL 3.23.6. If set to 2 (new in 4.0.18), table names are stored as given but compared in lowercase. From MySQL 4.0.2, this option also applies to database names. From 4.1.1, it also applies to table aliases. See Section 9.2.2, “Identifier Case Sensitivity”. Note: If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase. You should not set this variable to 0 if you are running MySQL on a system that does not have case-sensitive filenames (such as Windows or Mac OS X). New in 4.0.18: If this variable is not set at startup and the filesystem on which the data directory is located does not have case-sensitive filenames, MySQL automatically sets lower_case_table_names to 2.
Well: I use InnoDB tables and the variable is not set, but otherwise I think, the default value in AnyDAC should be 0 for this query, like:
FNameCaseSensitive := QueryValue('SHOW VARIABLES LIKE ''lower_case_table_names''', 1) <> '1';
If you will explicitly set FNameCaseSensitive to True, then MySQL support in AnyDAC works well to you ? If yes, then I will search for better ways to detect is server case sensitive or not.
yes, if I set FNameCaseSensitive "manual" to TRUE, all things seems all right. Is the suggestion im my previous post (another default value) a solution (unfortunetly I cannot found, what MySQL assumes as default)?
But please, if you would have a look on the ORACLE and ODBC drivers first - these problems are more urgently, because the MySQL issue is more 'cosmetics' for my taste by that the query statements works (may be not optimal without indexes, but that is not so serious at the moment).
I know, I am importunately - that's why I want to use your objects absolutely and believe you've done and will do a great work.
now I use the lates release of AnyDAC (1.12.2) and the ORACLE issue seems to be fixed - thank you very much!
But ODBC and MySql are unchanged until now and compiler errors still exist. Is there a hope for fixing that if I buy priority support?
Meanwhile I worked out why access to MySQL via ODBC not worked in my environment (remember: I have case sensitive table names) [daADPhysODBC.pas, line 248]:
function TADPhysODBCConnection.InternalCreateMetadata: TObject;
begin
UpdateRDBMSKind;
case FRdbmsKind of
mkOracle: Result := TADPhysOraMetadata.Create({$IFDEF AnyDAC_MONITOR} GetMonitor, {$ENDIF} Self, 0, 0);
mkMSSQL: Result := TADPhysMSSQLMetadata.Create({$IFDEF AnyDAC_MONITOR} GetMonitor, {$ENDIF} Self);
mkMSAccess: Result := TADPhysMSAccMetadata.Create({$IFDEF AnyDAC_MONITOR} GetMonitor, {$ENDIF} Self);
mkMySQL: Result := TADPhysMySQLMetadata.Create({$IFDEF AnyDAC_MONITOR} GetMonitor, {$ENDIF} Self, 0, 0, True); {* knulo *}
mkDb2: Result := TADPhysDb2Metadata.Create({$IFDEF AnyDAC_MONITOR} GetMonitor, {$ENDIF} Self);
mkASA: Result := TADPhysASAMetadata.Create({$IFDEF AnyDAC_MONITOR} GetMonitor, {$ENDIF} Self);
mkADS: Result := TADPhysADSMetadata.Create({$IFDEF AnyDAC_MONITOR} GetMonitor, {$ENDIF} Self);
else Result := nil;
end;
Result := TADPhysODBCMetadata.Create({$IFDEF AnyDAC_MONITOR} GetMonitor, {$ENDIF}
Self, Self, TADPhysConnectionMetadata(Result));
end;
AnyDAC sets case sensitivity "hardcoded" to False for MySQL - that smells a little bit "straight on" for me and I adapted it for my needs (marked with 'knulo'). In general I think it's better to assume case sensivity, because in that way the user have to take care of right case for table and field names (se also my post 2007/03/16 12:41). What do you think about that?
I read about a redesign of ODBC parts in the forum (sorry, don't no where at the moment). It would be nice, if there where a similar way like in daADPhysMySql.pas, asking the database for case sensivity.
