Skip to content

Commit 9d986a5

Browse files
author
Robert
committed
Added table writing...
1 parent 9f47ee9 commit 9d986a5

File tree

3 files changed

+178
-21
lines changed

3 files changed

+178
-21
lines changed

‎Readme.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,5 +6,5 @@ A Java program to extract and insert data into MS SQL Server. Based on web servi
66
Password-protected databases not currently supported.
77

88

9-
Input: `database_url tbl_schema tbl_name filename`
9+
Input: `original_database_url original_tbl_schema original_tbl_name new_database_url new_tbl_schema new_tbl_name`
1010
Output: `filename.csv`

‎nbproject/project.properties‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -30,7 +30,7 @@ dist.javadoc.dir=${dist.dir}/javadoc
3030
endorsed.classpath=
3131
excludes=
3232
file.reference.commons-csv-1.4.jar=lib/commons-csv-1.4.jar
33-
file.reference.kotlin-runtime.jar=/home/robert/.netbeans/8.2/kotlinc/lib\\kotlin-runtime.jar
33+
file.reference.kotlin-runtime.jar=nulllib\\kotlin-runtime.jar
3434
file.reference.mssql-jdbc-6.2.1.jre8.jar=lib/mssql-jdbc-6.2.1.jre8.jar
3535
includes=**
3636
jar.compress=false
@@ -48,7 +48,7 @@ javac.target=1.8
4848
javac.test.classpath=\
4949
${javac.classpath}:\
5050
${build.classes.dir}:\
51-
${kotlinc.classpath}
51+
${kotlinc.classpath}:
5252
javac.test.processorpath=\
5353
${javac.test.classpath}
5454
javadoc.additionalparam=
@@ -71,7 +71,7 @@ platform.active=default_platform
7171
run.classpath=\
7272
${javac.classpath}:\
7373
${build.classes.dir}:\
74-
${kotlinc.classpath}
74+
${kotlinc.classpath}:
7575
# Space-separated list of JVM arguments used when running the project.
7676
# You may also define separate properties like run-sys-prop.name=value instead of -Dname=value.
7777
# To set system properties for unit tests define test-sys-prop.name=value:

‎src/sqlserver/copy/java/UtilityMain.java‎

Lines changed: 174 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -3,50 +3,61 @@
33
*/
44
package sqlserver.copy.java;
55

6+
import java.io.BufferedReader;
67
import java.io.BufferedWriter;
78
import java.io.File;
9+
import java.io.FileReader;
810
import java.io.FileWriter;
911
import java.io.IOException;
1012
import java.sql.Connection;
1113
import java.sql.DriverManager;
1214
import java.sql.PreparedStatement;
1315
import java.sql.ResultSet;
1416
import java.sql.SQLException;
17+
import java.sql.Statement;
1518
import java.util.ArrayList;
19+
import java.util.List;
20+
import java.util.logging.Level;
1621
import org.apache.commons.csv.CSVFormat;
22+
import org.apache.commons.csv.CSVParser;
1723
import org.apache.commons.csv.CSVPrinter;
24+
import org.apache.commons.csv.CSVRecord;
1825

1926
/**
2027
* This program reads the data from SQL Server table, prints to CSV, then inserts CSV into new SQL Server table.
2128
*
2229
* @author Robert Streetman
2330
*/
2431
public class UtilityMain {
25-
private static String url;
26-
private static String tblName;
27-
private static String tblSchema;
28-
private static String fileName;
32+
private static String urlInput;
33+
private static String urlOutput;
34+
private static String tblNameInput;
35+
private static String tblNameOutput;
36+
private static String tblSchemaInput;
37+
private static String tblSchemaOutput;
38+
private static File tableFile;
2939

3040
/**
31-
* @param args url, tbl_schema, tbl_name, csv_file_name
41+
* @param args url_input, input_tbl_schema, input_tbl_name, url_output, output_tbl_schema, output_tbl_name
3242
*/
3343
public static void main(String[] args) {
3444
//These parameters were provided in original service
35-
url = args[0];
36-
tblSchema = args[1];
37-
tblName = args[2];
38-
fileName = args[3];
45+
urlInput = args[0];
46+
tblSchemaInput = args[1];
47+
tblNameInput = args[2];
48+
urlOutput = args[3];
49+
tblSchemaOutput = args[4];
50+
tblNameOutput = args[5];
3951

4052
readTable();
41-
//ToDo
53+
writeTable();
4254
}
4355

4456
private static void readTable() {
4557
try {
4658
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
4759

48-
try (Connection conn = DriverManager.getConnection(url)) {
49-
File tableFile;
60+
try (Connection conn = DriverManager.getConnection(urlInput)) {
5061
ArrayList<String> columnNames = new ArrayList();
5162
ArrayList<String> columnTypes = new ArrayList();
5263
ArrayList<String> columnNullable = new ArrayList();
@@ -66,8 +77,8 @@ private static void readTable() {
6677
+ "INFORMATION_SCHEMA.COLUMNS col LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE "
6778
+ "usage ON usage.TABLE_NAME=col.TABLE_NAME AND usage.COLUMN_NAME=col.COLUMN_NAME WHERE "
6879
+ "col.TABLE_NAME=? AND col.TABLE_SCHEMA=?;");
69-
pStmnt.setString(1, tblName);
70-
pStmnt.setString(2, tblSchema);
80+
pStmnt.setString(1, tblNameInput);
81+
pStmnt.setString(2, tblSchemaInput);
7182

7283
//Read column data from INFORMATION_SCHEMA
7384
try (ResultSet results = pStmnt.executeQuery()) {
@@ -113,7 +124,7 @@ private static void readTable() {
113124
}
114125

115126
//Create CSV filewriter
116-
tableFile = new File(fileName);
127+
tableFile = new File(tblNameInput + ".csv");
117128
CSVFormat csvFormat = CSVFormat.DEFAULT.withFirstRecordAsHeader();
118129
CSVPrinter csvWriter = new CSVPrinter(
119130
new BufferedWriter( new FileWriter(tableFile)), csvFormat);
@@ -146,8 +157,8 @@ private static void readTable() {
146157

147158
qryTable.append(" FROM ?.?;");
148159
pStmnt = conn.prepareStatement(qryTable.toString());
149-
pStmnt.setString(1, tblSchema);
150-
pStmnt.setString(2, tblName);
160+
pStmnt.setString(1, tblSchemaInput);
161+
pStmnt.setString(2, tblNameInput);
151162

152163
//Read table rows, print to CSV
153164
try (ResultSet results = pStmnt.executeQuery()) {
@@ -165,4 +176,150 @@ private static void readTable() {
165176
}
166177
}
167178

179+
private static void writeTable() {
180+
try {
181+
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
182+
183+
try (Connection conn = DriverManager.getConnection(urlOutput);
184+
Statement stmt = conn.createStatement()) {
185+
ArrayList<String> columnNames = new ArrayList();
186+
ArrayList<String> columnTypes = new ArrayList();
187+
ArrayList<String> columnConstraints = new ArrayList();
188+
189+
//Build string to delete old temp table, if exists
190+
PreparedStatement pStmnt = conn.prepareStatement("IF EXISTS (SELECT * FROM sysobjects WHERE id=object_id(N?) AND OBJECTPROPERTY(id, N'IsUserTable')=1) DROP TABLE ?;");
191+
pStmnt.setString(1, tblNameOutput);
192+
pStmnt.setString(2, tblNameOutput);
193+
194+
//Build CREATE statement for table
195+
StringBuilder qryCreate = new StringBuilder("CREATE TABLE ");
196+
qryCreate.append(tblNameOutput);
197+
qryCreate.append(" (");
198+
199+
//Read headers from file. First line may be blank
200+
BufferedReader br = new BufferedReader(new FileReader(tableFile));
201+
String line = br.readLine();
202+
203+
if (line == null || line.equals("\\s") || line.length() == 0) {
204+
line = br.readLine();
205+
}
206+
207+
br.close();
208+
String[] colNames = line.split(",");
209+
int numColumns = colNames.length;
210+
System.out.println("Number of columns: " + numColumns);
211+
212+
for (int i = 0; i < numColumns; i++) {
213+
columnNames.add(colNames[i]);
214+
}
215+
216+
//Create CSV file reader
217+
CSVFormat csvForm = CSVFormat.DEFAULT.withHeader(colNames).withDelimiter(',');
218+
CSVParser parser = new CSVParser(new FileReader(tableFile), csvForm);
219+
List records = parser.getRecords();
220+
int numRecords = records.size();
221+
222+
//Read data types
223+
CSVRecord tempRecord = (CSVRecord) records.get(1);
224+
225+
for (String column : columnNames) {
226+
columnTypes.add(tempRecord.get(column));
227+
}
228+
229+
//Read column constraints
230+
tempRecord = (CSVRecord) records.get(2);
231+
232+
for (String column : columnNames) {
233+
columnConstraints.add(tempRecord.get(column));
234+
}
235+
236+
//Build CREATE statement with headers, types & constraints
237+
for (int i = 0; i < numColumns; i++) {
238+
//qryCreate.append(columnNames.get(i));
239+
//qryCreate.append(" ");
240+
//qryCreate.append(columnTypes.get(i));
241+
//qryCreate.append(" ");
242+
//qryCreate.append(columnConstraints.get(i));
243+
qryCreate.append("? ? ?");
244+
245+
if (i < numColumns - 1) {
246+
qryCreate.append(", ");
247+
}
248+
}
249+
250+
qryCreate.append(");");
251+
pStmnt = conn.prepareStatement(qryCreate.toString());
252+
253+
//Set parameters in threes
254+
for (int i = 1; i < numColumns + 1; i++) {
255+
pStmnt.setString(i*3 - 2, columnNames.get(i));
256+
pStmnt.setString(i*3 - 1, columnTypes.get(i));
257+
pStmnt.setString(i*3, columnConstraints.get(i));
258+
}
259+
260+
pStmnt.executeUpdate();
261+
262+
//Read records (skip headers) from file, insert into new table
263+
//TODO: Switch to preparedstatement
264+
StringBuilder insertQry;
265+
for (int i = 3; i < numRecords; i++) {
266+
tempRecord = (CSVRecord) records.get(i);
267+
insertQry = new StringBuilder();
268+
insertQry.append("INSERT INTO ");
269+
insertQry.append(tblNameOutput);
270+
insertQry.append(" (");
271+
272+
for (int j = 0; j < numColumns; j++) {
273+
insertQry.append(columnNames.get(j));
274+
275+
if (j < numColumns - 1) {
276+
insertQry.append(", ");
277+
}
278+
}
279+
280+
insertQry.append(") VALUES (");
281+
282+
for (int j = 0; j < numColumns; j++) {
283+
String entry = tempRecord.get(columnNames.get(j));
284+
String type = columnTypes.get(j);
285+
286+
if (entry == null || entry.length() == 0 || entry.equals("")) {
287+
if (type.equals("text") || type.contains("char") || type.contains("varchar")
288+
|| type.contains("nchar") || type.contains("nvarchar")) {
289+
insertQry.append("''");
290+
} else {
291+
insertQry.append("NULL");
292+
}
293+
} else if (type.equals("text") || type.contains("char") || type.contains("varchar")
294+
|| type.contains("nchar") || type.contains("nvarchar")) {
295+
insertQry.append("'");
296+
insertQry.append(entry.replace("\'", "\'\'"));
297+
insertQry.append("'");
298+
} else {
299+
insertQry.append(entry);
300+
}
301+
302+
if (j < numColumns - 1) {
303+
insertQry.append(", ");
304+
}
305+
}
306+
307+
insertQry.append(");");
308+
System.out.println(insertQry.toString());
309+
stmt.execute(insertQry.toString());
310+
}
311+
312+
//TODO: Update data to new table BEFORE deleting old table to avoid unneccessary data loss
313+
/*
314+
stmt.execute(DBQueries.SVAP02Query02(tblNameOld));
315+
stmt.execute(DBQueries.SVAP02Query03(tblName, tblNameOld));
316+
*/
317+
} catch (SQLException | IOException ex) {
318+
319+
}
320+
} catch(ClassNotFoundException ex) {
321+
322+
}
323+
}
324+
168325
}

0 commit comments

Comments
 (0)