Esempio n. 1
0
        /// <summary>
        /// Sync column or table passed in the parameter
        /// </summary>
        /// <param name="table"></param>
        /// <param name="column"></param>
        /// <param name="noColumns">OUT parameter, returns 0 if table is being synched for the first time</param>
        /// <returns>string message</returns>
        public static string SyncColumn(MTable table, MColumn column, out int noColumns)
        {
            DatabaseMetaData md      = new DatabaseMetaData();
            String           catalog = "";
            String           schema  = DataBase.DB.GetSchema();

            //get table name
            string tableName = table.GetTableName();

            noColumns = 0;
            string sql = null;
            //get columns of a table
            DataSet dt = md.GetColumns(catalog, schema, tableName);

            md.Dispose();
            //for each column
            for (noColumns = 0; noColumns < dt.Tables[0].Rows.Count; noColumns++)
            {
                string columnName = dt.Tables[0].Rows[noColumns]["COLUMN_NAME"].ToString().ToLower();
                if (!columnName.Equals(column.GetColumnName().ToLower()))
                {
                    continue;
                }

                //check if column is null or not
                string dtColumnName = "is_nullable";
                string value        = "YES";
                //if database is oracle
                if (DatabaseType.IsOracle)
                {
                    dtColumnName = "NULLABLE";
                    value        = "Y";
                }
                bool notNull = false;
                //check if column is null
                if (dt.Tables[0].Rows[noColumns][dtColumnName].ToString() == value)
                {
                    notNull = false;
                }
                else
                {
                    notNull = true;
                }
                //............................

                //if column is virtual column then alter table and drop this column
                if (column.IsVirtualColumn())
                {
                    sql = "ALTER TABLE " + table.GetTableName()
                          + " DROP COLUMN " + columnName;
                }
                else
                {
                    sql = column.GetSQLModify(table, column.IsMandatory() != notNull);
                    noColumns++;
                    break;
                }
            }
            dt = null;
            //while (rs.next())
            //{
            //    noColumns++;
            //    String columnName = rs.getString ("COLUMN_NAME");
            //    if (!columnName.equalsIgnoreCase(column.getColumnName()))
            //        continue;

            //    //	update existing column
            //    boolean notNull = DatabaseMetaData.columnNoNulls == rs.getInt("NULLABLE");
            //    if (column.isVirtualColumn())
            //        sql = "ALTER TABLE " + table.getTableName()
            //            + " DROP COLUMN " + columnName;
            //    else
            //        sql = column.getSQLModify(table, column.isMandatory() != notNull);
            //    break;
            //}
            //rs.close();
            //rs = null;

            //	No Table
            if (noColumns == 0)
            {
                sql = table.GetSQLCreate();
            }
            //	No existing column
            else if (sql == null)
            {
                if (column.IsVirtualColumn())
                {
                    return("@IsVirtualColumn@");
                }
                sql = column.GetSQLAdd(table);
            }
            return(sql);
        }
Esempio n. 2
0
        }//	prepare

        /// <summary>
        /// Alert table
        /// </summary>
        /// <returns>int</returns>
        ///
        override protected string DoIt()
        {
            string exception = "";

            log.Info("C_Column_ID=" + p_AD_Column_ID);
            if (p_AD_Column_ID == 0)
            {
                //    return "";
                throw new Exception("@No@ @AD_Column_ID@");
            }
            //IDbTransaction trx = ExecuteQuery.GerServerTransaction();
            MColumn column = new MColumn(GetCtx(), p_AD_Column_ID, Get_Trx());

            if (column.Get_ID() == 0)
            {
                throw new Exception("@NotFound@ @AD_Column_ID@" + p_AD_Column_ID);
            }

            MTable table = MTable.Get(GetCtx(), column.GetAD_Table_ID());

            if (table.Get_ID() == 0)
            {
                throw new Exception("@NotFound@ @AD_Table_ID@" + column.GetAD_Table_ID());
            }
            //	Find Column in Database

            DatabaseMetaData md      = new DatabaseMetaData();
            String           catalog = "";
            String           schema  = DataBase.DB.GetSchema();

            //get table name
            string tableName = table.GetTableName();


            int    noColumns;
            string sql = null;
            //get columns of a table
            DataSet dt = md.GetColumns(catalog, schema, tableName);

            md.Dispose();
            //for each column
            for (noColumns = 0; noColumns < dt.Tables[0].Rows.Count; noColumns++)
            {
                string columnName = dt.Tables[0].Rows[noColumns]["COLUMN_NAME"].ToString().ToLower();
                if (!columnName.Equals(column.GetColumnName().ToLower()))
                {
                    continue;
                }

                //check if column is null or not

                string dtColumnName = "is_nullable";
                string value        = "YES";
                //if database is oracle
                if (DatabaseType.IsOracle)
                {
                    dtColumnName = "NULLABLE";
                    value        = "Y";
                }
                bool notNull = false;
                //check if column is null
                if (dt.Tables[0].Rows[noColumns][dtColumnName].ToString() == value)
                {
                    notNull = false;
                }
                else
                {
                    notNull = true;
                }
                //............................

                //if column is virtual column then alter table and drop this column
                if (column.IsVirtualColumn())
                {
                    sql = "ALTER TABLE " + table.GetTableName()
                          + " DROP COLUMN " + columnName;
                }
                else
                {
                    sql = column.GetSQLModify(table, column.IsMandatory() != notNull);
                    noColumns++;
                    break;
                }
            }
            dt = null;

            //while (rs.next())
            //{
            //    noColumns++;
            //    String columnName = rs.getString ("COLUMN_NAME");
            //    if (!columnName.equalsIgnoreCase(column.getColumnName()))
            //        continue;

            //    //	update existing column
            //    boolean notNull = DatabaseMetaData.columnNoNulls == rs.getInt("NULLABLE");
            //    if (column.isVirtualColumn())
            //        sql = "ALTER TABLE " + table.getTableName()
            //            + " DROP COLUMN " + columnName;
            //    else
            //        sql = column.getSQLModify(table, column.isMandatory() != notNull);
            //    break;
            //}
            //rs.close();
            //rs = null;

            //	No Table
            if (noColumns == 0)
            {
                sql = table.GetSQLCreate();
            }
            //	No existing column
            else if (sql == null)
            {
                if (column.IsVirtualColumn())
                {
                    return("@IsVirtualColumn@");
                }
                sql = column.GetSQLAdd(table);
            }

            int no = 0;

            if (sql.IndexOf("; ") == -1)
            {
                //no =
                //ExecuteQuery.ExecuteNonQuery(sql, false, Get_Trx());
                try
                {
                    no = DataBase.DB.ExecuteQuery(sql, null, Get_Trx());

                    AddLog(0, DateTime.MinValue, Decimal.Parse(no.ToString()), sql);
                }
                catch (Exception ex)
                {
                    exception = ex.Message;
                }
                //addLog (0, null, new BigDecimal(no), sql);
            }
            else
            {
                //string ss = "; ";
                string[] statements = sql.Split(';');
                for (int i = 0; i < statements.Length; i++)
                {
                    int count = DataBase.DB.ExecuteQuery(statements[i].ToString(), null, Get_Trx());
                    AddLog(0, DateTime.MinValue, Decimal.Parse(count.ToString()), statements[i]);

                    no += count;
                    //ExecuteQuery.ExecuteNonQuery(statements[i].ToString());
                }
            }

            if (no == -1)
            {
                string        msg = "@Error@ ";
                ValueNamePair pp  = VAdvantage.Logging.VLogger.RetrieveError();
                if (pp != null)
                {
                    msg += exception + " - ";
                }
                msg += sql;
                throw new Exception(msg);
            }
            string r = createFK();

            return(sql + "; " + r);
        }       //	doIt