cs-database/src/main/java/io/cloudslang/content/database/services/SQLQueryLobService.java
/*
* Copyright 2019-2024 Open Text
* This program and the accompanying materials
* are made available under the terms of the Apache License v2.0 which accompany this distribution.
*
* The Apache License is available at
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package io.cloudslang.content.database.services;
import io.cloudslang.content.database.utils.SQLInputs;
import io.cloudslang.content.database.utils.SQLUtils;
import org.apache.commons.io.input.ReaderInputStream;
import org.apache.commons.lang3.StringUtils;
import java.io.File;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import static org.apache.commons.io.FileUtils.*;
/**
* Created by victor on 13.01.2017.
*/
public class SQLQueryLobService {
public static boolean executeSqlQueryLob(SQLInputs sqlInputs) throws Exception {
if (StringUtils.isEmpty(sqlInputs.getSqlCommand())) {
throw new Exception("command input is empty.");
}
boolean isLOB = false;
ConnectionService connectionService = new ConnectionService();
try (final Connection connection = connectionService.setUpConnection(sqlInputs)) {
StringBuilder strColumns = new StringBuilder(sqlInputs.getStrColumns());
connection.setReadOnly(true);
Statement statement = connection.createStatement(sqlInputs.getResultSetType(), sqlInputs.getResultSetConcurrency());
statement.setQueryTimeout(sqlInputs.getTimeout());
ResultSet results = statement.executeQuery(sqlInputs.getSqlCommand());
ResultSetMetaData mtd = results.getMetaData();
int iNumCols = mtd.getColumnCount();
for (int i = 1; i <= iNumCols; i++) {
if (i > 1) strColumns.append(sqlInputs.getStrDelim());
strColumns.append(mtd.getColumnLabel(i));
}
sqlInputs.setStrColumns(strColumns.toString());
int nr = -1;
while (results.next()) {
nr++;
final StringBuilder strRowHolder = new StringBuilder();
for (int i = 1; i <= iNumCols; i++) {
if (i > 1) strRowHolder.append(sqlInputs.getStrDelim());
Object columnObject = results.getObject(i);
if (columnObject != null) {
String value;
if (columnObject instanceof java.sql.Clob) {
isLOB = true;
final File tmpFile = File.createTempFile("CLOB_" + mtd.getColumnLabel(i), ".txt");
copyInputStreamToFile(new ReaderInputStream(results.getCharacterStream(i), StandardCharsets.UTF_8), tmpFile);
if (sqlInputs.getLRowsFiles().size() == nr) {
sqlInputs.getLRowsFiles().add(nr, new ArrayList<String>());
sqlInputs.getLRowsNames().add(nr, new ArrayList<String>());
}
sqlInputs.getLRowsFiles().get(nr).add(tmpFile.getAbsolutePath());
sqlInputs.getLRowsNames().get(nr).add(mtd.getColumnLabel(i));
value = "(CLOB)...";
} else {
value = results.getString(i);
if (sqlInputs.isNetcool())
value = SQLUtils.processNullTerminatedString(value);
}
strRowHolder.append(value);
} else
strRowHolder.append("null");
}
sqlInputs.getLRows().add(strRowHolder.toString());
}
}
return isLOB;
}
}