|
1
5 package org.h2.test.db;
6
7 import java.io.BufferedInputStream  ;
8 import java.io.InputStream ;
9 import java.sql.Blob ;
10 import java.sql.Connection ;
11 import java.sql.PreparedStatement ;
12 import java.sql.ResultSet ;
13 import java.sql.SQLException ;
14 import java.sql.Statement ;
15 import java.sql.Types ;
16
17 import org.h2.test.TestBase;
18 import org.h2.tools.SimpleResultSet;
19
20 public class TestFunctions extends TestBase {
21
22 Statement stat;
23
24 public void test() throws Exception {
25 deleteDb("functions");
26 Connection conn = getConnection("functions");
27 stat = conn.createStatement();
28 test("abs(null)", null);
29 test("abs(1)", "1");
30 test("abs(1)", "1");
31
32 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
33 stat.execute("CREATE ALIAS ADDROW FOR \"" + getClass().getName() + ".addRow\"");
34 ResultSet rs;
35 rs = stat.executeQuery("CALL ADDROW(1, 'Hello')");
36 rs.next();
37 check(rs.getInt(1), 1);
38 rs = stat.executeQuery("SELECT * FROM TEST");
39 rs.next();
40 check(rs.getInt(1), 1);
41 check(rs.getString(2), "Hello");
42 checkFalse(rs.next());
43
44 rs = stat.executeQuery("CALL ADDROW(2, 'World')");
45
46 stat.execute("CREATE ALIAS SEL FOR \"" + getClass().getName() + ".select\"");
47 rs = stat.executeQuery("CALL SEL('SELECT * FROM TEST ORDER BY ID')");
48 check(rs.getMetaData().getColumnCount(), 2);
49 rs.next();
50 check(rs.getInt(1), 1);
51 check(rs.getString(2), "Hello");
52 rs.next();
53 check(rs.getInt(1), 2);
54 check(rs.getString(2), "World");
55 checkFalse(rs.next());
56
57 rs = stat.executeQuery("SELECT NAME FROM SEL('SELECT * FROM TEST ORDER BY NAME') ORDER BY NAME DESC");
58 check(rs.getMetaData().getColumnCount(), 1);
59 rs.next();
60 check(rs.getString(1), "World");
61 rs.next();
62 check(rs.getString(1), "Hello");
63 checkFalse(rs.next());
64
65 rs = stat.executeQuery("SELECT SEL('SELECT * FROM TEST WHERE ID=' || ID) FROM TEST ORDER BY ID");
66 check(rs.getMetaData().getColumnCount(), 1);
67 rs.next();
68 check("((1, Hello))", rs.getString(1));
69 rs.next();
70 check("((2, World))", rs.getString(1));
71 checkFalse(rs.next());
72
73 rs = stat.executeQuery("SELECT SEL('SELECT * FROM TEST ORDER BY ID') FROM DUAL");
74 check(rs.getMetaData().getColumnCount(), 1);
75 rs.next();
76 check("((1, Hello), (2, World))", rs.getString(1));
77 checkFalse(rs.next());
78
79 try {
80 rs = stat.executeQuery("CALL SEL('ERROR')");
81 error("expected error");
82 } catch (SQLException e) {
83 check("42001", e.getSQLState());
84 }
85
86 stat.execute("CREATE ALIAS SIMPLE FOR \"" + getClass().getName() + ".simpleResultSet\"");
87 rs = stat.executeQuery("CALL SIMPLE(2, 1,1,1,1,1,1,1)");
88 check(rs.getMetaData().getColumnCount(), 2);
89 rs.next();
90 check(rs.getInt(1), 0);
91 check(rs.getString(2), "Hello");
92 rs.next();
93 check(rs.getInt(1), 1);
94 check(rs.getString(2), "World");
95 checkFalse(rs.next());
96
97 rs = stat.executeQuery("SELECT * FROM SIMPLE(1, 1,1,1,1,1,1,1)");
98 check(rs.getMetaData().getColumnCount(), 2);
99 rs.next();
100 check(rs.getInt(1), 0);
101 check(rs.getString(2), "Hello");
102 checkFalse(rs.next());
103
104 stat.execute("CREATE ALIAS ARRAY FOR \"" + getClass().getName() + ".getArray\"");
105 rs = stat.executeQuery("CALL ARRAY()");
106 check(rs.getMetaData().getColumnCount(), 2);
107 rs.next();
108 check(rs.getInt(1), 0);
109 check(rs.getString(2), "Hello");
110 checkFalse(rs.next());
111
112 stat.execute("CREATE ALIAS ROOT FOR \"" + getClass().getName() + ".root\"");
113 rs = stat.executeQuery("CALL ROOT(9)");
114 rs.next();
115 check(rs.getInt(1), 3);
116 checkFalse(rs.next());
117
118 stat.execute("CREATE ALIAS MAXID FOR \"" + getClass().getName() + ".selectMaxId\"");
119 rs = stat.executeQuery("CALL MAXID()");
120 rs.next();
121 check(rs.getInt(1), 2);
122 checkFalse(rs.next());
123
124 rs = stat.executeQuery("SELECT * FROM MAXID()");
125 rs.next();
126 check(rs.getInt(1), 2);
127 checkFalse(rs.next());
128
129 rs = stat.executeQuery("CALL CASE WHEN -9 < 0 THEN 0 ELSE ROOT(-9) END");
130 rs.next();
131 check(rs.getInt(1), 0);
132 checkFalse(rs.next());
133
134 stat.execute("CREATE ALIAS blob2stream FOR \"" + getClass().getName() + ".blob2stream\"");
135 stat.execute("CREATE ALIAS stream2stream FOR \"" + getClass().getName() + ".stream2stream\"");
136 stat.execute("CREATE TABLE TEST_BLOB(ID INT PRIMARY KEY, VALUE BLOB)");
137 stat.execute("INSERT INTO TEST_BLOB VALUES(0, null)");
138 stat.execute("INSERT INTO TEST_BLOB VALUES(1, 'edd1f011edd1f011edd1f011')");
139 rs = stat.executeQuery("SELECT blob2stream(VALUE) FROM TEST_BLOB");
140 while(rs.next()) {
141 }
142 rs.close();
143 rs = stat.executeQuery("SELECT stream2stream(VALUE) FROM TEST_BLOB");
144 while(rs.next()) {
145 }
147
148 stat.execute("CREATE ALIAS NULLRESULT FOR \"" + getClass().getName() + ".nullResultSet\"");
149 rs = stat.executeQuery("CALL NULLRESULT()");
150 check(rs.getMetaData().getColumnCount(), 1);
151 rs.next();
152 check(rs.getString(1), null);
153 checkFalse(rs.next());
154
155 conn.close();
156 }
157
158 void test(String sql, String value) throws Exception {
159 ResultSet rs = stat.executeQuery("CALL " + sql);
160 rs.next();
161 String s = rs.getString(1);
162 check(value, s);
163 }
164
165 public static BufferedInputStream blob2stream(Blob value) throws SQLException {
166 if(value == null) {
167 return null;
168 }
169 BufferedInputStream bufferedInStream = new BufferedInputStream (value.getBinaryStream());
170 return bufferedInStream;
171 }
172
173 public static BufferedInputStream stream2stream(InputStream value) throws SQLException {
174 if(value == null) {
175 return null;
176 }
177 BufferedInputStream bufferedInStream = new BufferedInputStream (value);
178 return bufferedInStream;
179 }
180
181 public static int addRow(Connection conn, int id, String name) throws SQLException {
182 conn.createStatement().execute("INSERT INTO TEST VALUES(" + id + ", '" + name + "')");
183 ResultSet rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM TEST");
184 rs.next();
185 int result = rs.getInt(1);
186 rs.close();
187 return result;
188 }
189
190 public static ResultSet select(Connection conn, String sql) throws SQLException {
191 return conn.createStatement().executeQuery(sql);
192 }
193
194 public static ResultSet selectMaxId(Connection conn) throws SQLException {
195 return conn.createStatement().executeQuery("SELECT MAX(ID) FROM TEST");
196 }
197
198 public static Object [] getArray() {
199 return new Object [] { new Integer (0), "Hello" };
200 }
201
202 public static ResultSet nullResultSet(Connection conn) throws SQLException {
203 PreparedStatement statement = conn.prepareStatement("select null from system_range(1,1)");
204 return statement.executeQuery();
205 }
206
207 public static ResultSet simpleResultSet(Integer count, int ip, boolean bp, float fp, double dp, long lp, byte byp, short sp) throws SQLException {
208 SimpleResultSet rs = new SimpleResultSet();
209 rs.addColumn("ID", Types.INTEGER, 10, 0);
210 rs.addColumn("NAME", Types.VARCHAR, 255, 0);
211 if (count == null) {
212 if (ip != 0 || bp || fp != 0.0 || dp != 0.0 || sp != 0 || lp != 0 || byp != 0) {
213 throw new Error ("params not 0/false");
214 }
215 }
216 if (count != null) {
217 if (ip != 1 || !bp || fp != 1.0 || dp != 1.0 || sp != 1 || lp != 1 || byp != 1) {
218 throw new Error ("params not 1/true");
219 }
220 if (count.intValue() >= 1) {
221 rs.addRow(new Object [] { new Integer (0), "Hello" });
222 }
223 if (count.intValue() >= 2) {
224 rs.addRow(new Object [] { new Integer (1), "World" });
225 }
226 }
227 return rs;
228 }
229
230 public static int root(int value) {
231 if (value < 0) {
232 TestBase.logError("function called but should not", null);
233 }
234 return (int) Math.sqrt(value);
235 }
236
237 }
238 |
Popular Tags |
Java API By Example, From Geeks To Geeks.