Examples with DbRecordValue com.axway.ats.core.dbaccess.DbRecordValue used on opensource projects

Example 21 with DbRecordValue

use of com.axway.ats.core.dbaccess.DbRecordValue in project ats-framework by Axway.

the class OracleEnvironmentHandler method writeTableToFile.

@Override
protected void writeTableToFile(List<ColumnDescription> columns, DbTable table, DbRecordValuesList[] records, FileWriter fileWriter) throws IOException, ParseException {
    if (this.includeDeleteStatements) {
        fileWriter.write("DELETE FROM " + table.getTableName() + ";" + EOL_MARKER + LINE_SEPARATOR);
    }
    if (records.length > 0) {
        if (containsBinaryTypes(columns)) {
            /*
                 * If the binary value is too long the INSERT operation will fail.
                 * The workaround is to assign the binary value to a variable and then use it in the INSERT(or UPDATE) statement
                 * We have to use DECLARE/BEGIN/END anonymous block statement like this:
                 *
                   DECLARE
                      binValue_0 TABLE_NAME.BLOB_COLUMN_NAME%type;
                    BEGIN
                      binValue_0 := to_blob('3C3F786D6C2076657273696F6E3D22312E3022');
                      INSERT INTO TABLE_NAME(NAME,BLOB_COLUMN_NAME,DESCRIPTION) VALUES ('index.xhtml', binValue_0, 'Index file');
                    END; -- ATS EOL;
                 */
            final String INDENTATION = "  ";
            final String VAR_PREFIX = "binValue_";
            StringBuilder stmtBlockBuilder = new StringBuilder("DECLARE" + LINE_SEPARATOR);
            int variableIndex = 0;
            for (ColumnDescription column : columns) {
                if (column.isTypeBinary()) {
                    stmtBlockBuilder.append(INDENTATION + VAR_PREFIX + (variableIndex++) + " " + table.getTableName() + "." + column.getName() + "%type;" + LINE_SEPARATOR);
                }
            }
            stmtBlockBuilder.append("BEGIN" + LINE_SEPARATOR);
            int stmtBlockStart = stmtBlockBuilder.length();
            String insertBegin = INDENTATION + "INSERT INTO " + table.getTableName() + "(" + getColumnsString(columns) + ") VALUES (";
            String insertEnd = ");" + LINE_SEPARATOR;
            for (DbRecordValuesList record : records) {
                StringBuilder insertStatement = new StringBuilder();
                variableIndex = 0;
                for (int i = 0; i < record.size(); i++) {
                    ColumnDescription column = columns.get(i);
                    DbRecordValue recordValue = record.get(i);
                    // extract the value depending on the column type
                    String fieldValue = extractValue(column, (String) recordValue.getValue()).toString();
                    if (column.isTypeBinary()) {
                        String varName = VAR_PREFIX + (variableIndex++);
                        stmtBlockBuilder.append(INDENTATION + varName + " := " + fieldValue + ";" + LINE_SEPARATOR);
                        insertStatement.append(varName);
                    } else {
                        insertStatement.append(fieldValue);
                    }
                    insertStatement.append(",");
                }
                //remove the last comma
                insertStatement.delete(insertStatement.length() - 1, insertStatement.length());
                stmtBlockBuilder.append(insertBegin);
                stmtBlockBuilder.append(insertStatement.toString());
                stmtBlockBuilder.append(insertEnd);
                stmtBlockBuilder.append("END;" + EOL_MARKER + LINE_SEPARATOR);
                fileWriter.write(stmtBlockBuilder.toString());
                fileWriter.flush();
                // clear to block BEGIN tag
                stmtBlockBuilder.delete(stmtBlockStart, stmtBlockBuilder.length());
            }
        } else {
            StringBuilder insertStatement = new StringBuilder();
            String insertBegin = "INSERT INTO " + table.getTableName() + "(" + getColumnsString(columns) + ") VALUES (";
            String insertEnd = ");" + EOL_MARKER + LINE_SEPARATOR;
            for (DbRecordValuesList record : records) {
                insertStatement.append(insertBegin);
                for (int i = 0; i < record.size(); i++) {
                    DbRecordValue recordValue = record.get(i);
                    String fieldValue = (String) recordValue.getValue();
                    // extract specific values depending on their type
                    insertStatement.append(extractValue(columns.get(i), fieldValue));
                    insertStatement.append(",");
                }
                //remove the last comma
                insertStatement.delete(insertStatement.length() - 1, insertStatement.length());
                insertStatement.append(insertEnd);
            }
            fileWriter.write(insertStatement.toString());
        }
    }
// TODO : exclusive table locks END
}

Example 22 with DbRecordValue

use of com.axway.ats.core.dbaccess.DbRecordValue in project ats-framework by Axway.

the class Test_OracleEnvironmentHandler method createBackupNegativeSkippedColumnIsNotNullable.

@Test(expected = ColumnHasNoDefaultValueException.class)
public void createBackupNegativeSkippedColumnIsNotNullable() throws DatabaseEnvironmentCleanupException, DbException, IOException, ParseException {
    DbTable table1 = new DbTable("table1");
    List<String> columnsToSkip = new ArrayList<String>();
    columnsToSkip.add("name2");
    DbTable table2 = new DbTable("table2", columnsToSkip);
    //the columns meta data
    DbRecordValuesList column1MetaData = new DbRecordValuesList();
    column1MetaData.add(new DbRecordValue("", "COLUMN_NAME", "name1"));
    column1MetaData.add(new DbRecordValue("", "DATA_TYPE", "varchar(32)"));
    column1MetaData.add(new DbRecordValue("", "DATA_DEFAULT", true));
    DbRecordValuesList column2MetaData = new DbRecordValuesList();
    column2MetaData.add(new DbRecordValue("", "COLUMN_NAME", "name2"));
    column2MetaData.add(new DbRecordValue("", "DATA_TYPE", "varchar(32)"));
    column2MetaData.add(new DbRecordValue("", "DATA_DEFAULT", null));
    DbRecordValuesList column3MetaData = new DbRecordValuesList();
    column3MetaData.add(new DbRecordValue("", "COLUMN_NAME", "name3"));
    column3MetaData.add(new DbRecordValue("", "DATA_TYPE", "bit"));
    column3MetaData.add(new DbRecordValue("", "DATA_DEFAULT", 1));
    DbRecordValuesList[] columnsMetaData = new DbRecordValuesList[] { column1MetaData, column2MetaData, column3MetaData };
    DbRecordValuesList record1Value = new DbRecordValuesList();
    record1Value.add(new DbRecordValue("table1", "name1", "value1"));
    record1Value.add(new DbRecordValue("table1", "name2", null));
    record1Value.add(new DbRecordValue("table1", "name3", new String(new char[] { 1 })));
    DbRecordValuesList[] recordValues = new DbRecordValuesList[] { record1Value };
    OracleEnvironmentHandler envHandler = new OracleEnvironmentHandler(mockDbConnection, mockDbProvider);
    envHandler.addTable(table1);
    envHandler.addTable(table2);
    expect(mockDbConnection.getUser()).andReturn("myUserName").atLeastOnce();
    expect(mockDbProvider.select(isA(String.class))).andReturn(columnsMetaData);
    expect(mockDbProvider.select(isA(DbQuery.class), eq(DbReturnModes.ESCAPED_STRING))).andReturn(recordValues);
    expect(mockDbProvider.select(isA(String.class))).andReturn(columnsMetaData);
    expect(mockDbProvider.select(isA(DbQuery.class), eq(DbReturnModes.ESCAPED_STRING))).andReturn(recordValues);
    replay(mockDbConnection);
    replay(mockDbProvider);
    envHandler.writeBackupToFile(mockFileWriter);
    verify(mockDbConnection);
    verify(mockDbProvider);
}

Example 23 with DbRecordValue

use of com.axway.ats.core.dbaccess.DbRecordValue in project ats-framework by Axway.

the class Test_MysqlEnvironmentHandler method createBackupPositive.

@Test
public void createBackupPositive() throws DatabaseEnvironmentCleanupException, DbException, IOException, ParseException, SQLException {
    //the columns meta data
    DbRecordValuesList column1MetaData = new DbRecordValuesList();
    column1MetaData.add(new DbRecordValue("", "COLUMN_NAME", "name1"));
    column1MetaData.add(new DbRecordValue("", "COLUMN_TYPE", "varchar(32)"));
    column1MetaData.add(new DbRecordValue("", "COLUMN_DEFAULT", true));
    DbRecordValuesList column2MetaData = new DbRecordValuesList();
    column2MetaData.add(new DbRecordValue("", "COLUMN_NAME", "name2"));
    column2MetaData.add(new DbRecordValue("", "COLUMN_TYPE", "varchar(32)"));
    column2MetaData.add(new DbRecordValue("", "COLUMN_DEFAULT", true));
    DbRecordValuesList column3MetaData = new DbRecordValuesList();
    column3MetaData.add(new DbRecordValue("", "COLUMN_NAME", "name3"));
    column3MetaData.add(new DbRecordValue("", "COLUMN_TYPE", "bit"));
    column3MetaData.add(new DbRecordValue("", "COLUMN_DEFAULT", 1));
    DbRecordValuesList[] columnsMetaData = new DbRecordValuesList[] { column1MetaData, column2MetaData, column3MetaData };
    DbRecordValuesList record1Value = new DbRecordValuesList();
    record1Value.add(new DbRecordValue("table1", "name1", "value1"));
    record1Value.add(new DbRecordValue("table1", "name2", null));
    record1Value.add(new DbRecordValue("table1", "name3", "1"));
    DbRecordValuesList[] recordValues = new DbRecordValuesList[] { record1Value };
    expect(mockDbProvider.getConnection()).andReturn(mockConnection);
    expect(mockConnection.getMetaData()).andReturn(metaData);
    expect(mockDbConnection.getUser()).andReturn("myUserName").atLeastOnce();
    expect(metaData.getDriverMajorVersion()).andReturn(5);
    expect(metaData.getDriverMinorVersion()).andReturn(1);
    expect(mockDbProvider.select(isA(String.class))).andReturn(columnsMetaData);
    expect(mockDbProvider.select(isA(DbQuery.class), eq(DbReturnModes.ESCAPED_STRING))).andReturn(recordValues);
    expect(mockDbProvider.select(isA(String.class))).andReturn(columnsMetaData);
    expect(mockDbProvider.select(isA(DbQuery.class), eq(DbReturnModes.ESCAPED_STRING))).andReturn(recordValues);
    //expect the file writer calls
    //foreign keys check start
    mockFileWriter.write("SET FOREIGN_KEY_CHECKS = 0;" + EOL_MARKER + LINE_SEPARATOR);
    //table1
    mockFileWriter.write("LOCK TABLES `table1` WRITE;" + EOL_MARKER + LINE_SEPARATOR);
    mockFileWriter.write("DELETE FROM `table1`;" + EOL_MARKER + LINE_SEPARATOR);
    mockFileWriter.write("INSERT INTO `table1` (name1,name2,name3) VALUES('value1',NULL,0x1);" + EOL_MARKER + LINE_SEPARATOR);
    mockFileWriter.flush();
    mockFileWriter.write("UNLOCK TABLES;" + EOL_MARKER + LINE_SEPARATOR);
    mockFileWriter.write(LINE_SEPARATOR);
    //table2
    mockFileWriter.write("LOCK TABLES `table2` WRITE;" + EOL_MARKER + LINE_SEPARATOR);
    mockFileWriter.write("DELETE FROM `table2`;" + EOL_MARKER + LINE_SEPARATOR);
    mockFileWriter.write("INSERT INTO `table2` (name1,name2,name3) VALUES('value1',NULL,0x1);" + EOL_MARKER + LINE_SEPARATOR);
    mockFileWriter.flush();
    mockFileWriter.write("UNLOCK TABLES;" + EOL_MARKER + LINE_SEPARATOR);
    mockFileWriter.write(LINE_SEPARATOR);
    //foreign keys check end
    mockFileWriter.write("");
    replayAll();
    DbTable table1 = new DbTable("table1");
    DbTable table2 = new DbTable("table2");
    MysqlEnvironmentHandler envHandler = new MysqlEnvironmentHandler(mockDbConnection, mockDbProvider);
    envHandler.addTable(table1);
    envHandler.addTable(table2);
    envHandler.writeBackupToFile(mockFileWriter);
    verifyAll();
}