Mercurial > hg > monetdb-java

comparison tests/SQLcopyinto.java @ 251:2b1f650869d6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improved example program SQLcopyinto,java and added to tests directory for automatic testing
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 20 Sep 2018 17:31:00 +0200 (2018-09-20)
parents
children 96057ee68017 529b92d09fc6
comparison
equal deleted inserted replaced
250:3038e60bdca1 251:2b1f650869d6
1 /*
2 * This Source Code Form is subject to the terms of the Mozilla Public
3 * License, v. 2.0. If a copy of the MPL was not distributed with this
4 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
5 *
6 * Copyright 1997 - July 2008 CWI, August 2008 - 2018 MonetDB B.V.
7 */
8
9 import java.sql.*;
10 import java.io.*;
11 import java.util.*;
12 import nl.cwi.monetdb.mcl.net.MapiSocket;
13
14 /**
15 * This program demonstrates how the MonetDB JDBC driver can facilitate
16 * in performing COPY INTO ... FROM STDIN sequences.
17 * It shows how a data stream via MapiSocket to STDIN can be performed.
18 *
19 * @author Fabian Groffen, Martin van Dinther
20 */
21
22 public class SQLcopyinto {
23 final private static String tablenm = "exampleSQLCopyInto";
24
25 public static void main(String[] args) throws Exception {
26 System.out.println("SQLcopyinto started");
27 if (args.length == 0) {
28 System.err.println("Error: missing startup argument: the jdbc connection url !");
29 System.err.println("Usage: java -cp monetdb-jdbc-2.28.jar:. SQLcopyinto \"jdbc:monetdb://localhost:50000/demo?user=monetdb&password=monetdb\"");
30 System.exit(-1);
31 }
32 String jdbc_url = args[0];
33
34 // request a connection to MonetDB server via the driver manager
35 Connection conn = DriverManager.getConnection(jdbc_url);
36 System.out.println("Connected to MonetDB server");
37
38 Statement stmt = null;
39 ResultSet rs = null;
40 try {
41 stmt = conn.createStatement();
42 stmt.execute("CREATE TABLE IF NOT EXISTS " + tablenm + " (id int, val varchar(24))");
43
44 fillTableUsingCopyIntoSTDIN(conn);
45
46 // check content of the table populated via COPY INTO ... FROM STDIN
47 System.out.println("Listing uploaded data:");
48 rs = stmt.executeQuery("SELECT * FROM " + tablenm);
49 if (rs != null) {
50 while (rs.next()) {
51 System.out.println("Row data: " + rs.getString(1) + ", " + rs.getString(2));
52 }
53 rs.close();
54 rs = null;
55 }
56
57 stmt.execute("DROP TABLE " + tablenm);
58
59 System.out.println("SQLcopyinto completed");
60 } catch (SQLException e) {
61 System.err.println("SQLException: " + e.getMessage());
62 } catch (Exception e) {
63 System.err.println("Exception: " + e.getMessage());
64 } finally {
65 // free resources
66 if (rs != null)
67 rs.close();
68 if (stmt != null)
69 stmt.close();
70 // close the JDBC connection to MonetDB server
71 if (conn != null)
72 conn.close();
73 }
74 }
75
76 public static void fillTableUsingCopyIntoSTDIN(Connection mcon) throws Exception {
77 System.out.println();
78 System.out.println("CopyInto STDIN begin");
79
80 MapiSocket server = new MapiSocket();
81 try {
82 server.setLanguage("sql");
83 server.setSoTimeout(60);
84
85 // extract from MonetConnection object the used connection properties
86 String host = mcon.getClientInfo("host");
87 int port = Integer.parseInt(mcon.getClientInfo("port"));
88 String login = mcon.getClientInfo("user");
89 String passw = mcon.getClientInfo("password");
90 // System.out.println("host: " + host + " port: " + port + " login: " + login + " passwd: " + passw);
91
92 System.out.println("Before connecting to MonetDB server via MapiSocket");
93 List warning = server.connect(host, port, login, passw);
94 if (warning != null) {
95 for (Iterator it = warning.iterator(); it.hasNext(); ) {
96 System.out.println("Warning: " + it.next().toString());
97 }
98 }
99 System.out.println("Connected to MonetDB server via MapiSocket");
100
101 nl.cwi.monetdb.mcl.io.BufferedMCLReader mclIn = server.getReader();
102 nl.cwi.monetdb.mcl.io.BufferedMCLWriter mclOut = server.getWriter();
103
104 String error = mclIn.waitForPrompt();
105 if (error != null)
106 System.err.println("Received start error: " + error);
107
108 System.out.println("Before sending data to STDIN");
109
110 // the leading 's' is essential, since it is a protocol marker
111 // that should not be omitted, likewise the trailing semicolon
112 mclOut.write('s');
113 mclOut.write("COPY INTO " + tablenm + " FROM STDIN USING DELIMITERS ',','\\n';");
114 mclOut.newLine();
115 // now write the row data values as csv data lines to the STDIN stream
116 for (int i = 0; i < 40; i++) {
117 mclOut.write("" + i + ",val_" + i);
118 mclOut.newLine();
119 }
120 mclOut.writeLine(""); // need this one for synchronisation over flush()
121
122 error = mclIn.waitForPrompt();
123 if (error != null)
124 System.err.println("Received error: " + error);
125
126 mclOut.writeLine(""); // need this one for synchronisation over flush()
127
128 error = mclIn.waitForPrompt();
129 if (error != null)
130 System.err.println("Received finish error: " + error);
131
132 System.out.println("Completed sending data via STDIN");
133 } catch (Exception e) {
134 System.err.println("Mapi Exception: " + e.getMessage());
135 } finally {
136 // close connection to MonetDB server
137 server.close();
138 }
139
140 System.out.println("CopyInto STDIN end");
141 System.out.println();
142 }
143 }