Oracle TABLE_COLUMN_SQL , UPPER

 Hi,

i was wondering if it wasn't good idea to replace:

private 

const string TABLE_COLUMN_SQL = "SELECT user, a.table_name, a.column_name, a.column_id, a.data_default, " +
" a.nullable, a.data_type, a.char_length, a.data_precision, a.data_scale " +
" FROM user_tab_columns a " +
" WHERE a.table_name = :tableName";

by:

just putting the upper. because sometimes i have tables called Table and sometimes i have TABLE, and when using New Query("table") doesn't work.

This is the OracleDataProvider.cs

I'll join the project soon, i promise! but for now i can't check'in this.

This wont work in all cases.  Depending on the Case settings, tablenames (and all objects for that matter) can be case sensitive.  Thus TABLE1 can be different than Table1.  This mostly has to do with whether quotation marks are used to declare the table.

I would hazard a guess that this would work 95% of the time, but you just know someone created a tables with the same name, different case somewhere along the line.

ooh! that i didn't know :(

I didn't know that we could have the same name to different tables.

So how can i solve this problem of calling new Query("table1") being the same as new Query("taBle1"), when my data isn't case sensitive?

Thanks, i've learned something already.

I've made some quick fix in OracleDataProvider.cs, so this can be managed. To who can i send so it'll be verified and approved to be in SubSonic's futures releases?

 public class OracleDataProvider : DataProvider
    {
        private bool _isOracleCaseSensitive = true;

        public bool IsOracleCaseSensitive {
            get { return _isOracleCaseSensitive; }
            set { _isOracleCaseSensitive = value; }
        }  (...)

(...)

  public override TableSchema.Table GetTableSchema(string tableName, TableType tableType)
        {
            TableSchema.TableColumnCollection columns = new TableSchema.TableColumnCollection();
            TableSchema.Table tbl = new TableSchema.Table(tableName, tableType, this);
            //tbl.ClassName = Convention.ClassName(tableName);
            //string sql = TABLE_COLUMN_SQL;
            QueryCommand cmd;
            if (IsOracleCaseSensitive)
                cmd = new QueryCommand(TABLE_COLUMN_SQL, Name);
            else
                cmd = new QueryCommand(TABLE_COLUMN_SQL_CASE_INSENSITIVE, Name);
           
            cmd.AddParameter(TABLE_NAME_PARAMETER, tableName, DbType.AnsiString);
            TableSchema.TableColumn column;

(...)

 private const string TABLE_COLUMN_SQL_CASE_INSENSITIVE = "SELECT user, a.table_name, a.column_name, a.column_id, a.data_default, " +
                                                "       a.nullable, a.data_type, a.char_length, a.data_precision, a.data_scale " +
                                                "  FROM user_tab_columns a " +
                                                " WHERE upper(a.table_name) = upper(:tableName)";

i know it's not a very nice approach, but i can't figure out another way, i'm a newbie to SubSonic, and only today i connected to SVN :p

Please post your opinion, i want to help, but also want to learn.