1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
-- source windows.inc
-- source jdbconn.inc
SET GLOBAL time_zone='+0:00';
SET time_zone='+0:00';
let $MYSQLD_DATADIR= `select @@datadir`;
--copy_file $MTR_SUITE_DIR/std_data/girls.txt $MYSQLD_DATADIR/test/girls.txt
let $PORT= `select @@port`;
#
# This test is run against a local MariaDB server
#
CREATE DATABASE connect;
USE connect;
CREATE TABLE t2 (
id bigint not null,
msg varchar(500),
tm time,
dt date,
dtm datetime,
ts timestamp);
INSERT INTO t2 VALUES(455000000000, 'A very big number', '18:10:25', '2016-03-16', '1999-12-11 23:01:52', '2015-07-24 09:32:45');
SELECT * FROM t2;
--echo #
--echo # Testing JDBC connection to MySQL driver
--echo #
USE test;
--replace_result $PORT PORT
--eval CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME=t2 CONNECTION='jdbc:mysql://localhost:$PORT/connect?user=root&useSSL=false'
SELECT * FROM t1;
INSERT INTO t1 VALUES(786325481247, 'Hello!', '19:45:03', '1933-08-10', '1985-11-12 09:02:44', '2014-06-17 10:32:01');
SELECT * FROM t1;
DELETE FROM t1 WHERE msg = 'Hello!';
SELECT * FROM t1;
DROP TABLE t1;
--echo #
--echo # Testing JDBC view
--echo #
--replace_result $PORT PORT
--eval CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC SRCDEF='select id, msg, tm, dt from t2' CONNECTION='jdbc:mysql://localhost:$PORT/connect?user=root&useSSL=false'
SELECT * FROM t1;
SELECT msg, dt FROM t1;
DROP TABLE t1, connect.t2;
--echo #
--echo # Testing JDBC write operations
--echo #
USE connect;
--copy_file $MTR_SUITE_DIR/std_data/boys.txt $MYSQLD_DATADIR/connect/boys.txt
CREATE TABLE boys (
name CHAR(12) NOT NULL,
city CHAR(11),
birth DATE DATE_FORMAT='DD/MM/YYYY',
hired DATE DATE_FORMAT='DD/MM/YYYY' flag=36)
ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='boys.txt' ENDING=1;
SELECT * FROM boys;
USE test;
CREATE TABLE t3 (
name CHAR(12) NOT NULL,
city CHAR(12),
birth DATE,
hired DATE);
INSERT INTO t3 VALUES('Donald','Atlanta','1999-04-01','2016-03-31'),('Mick','New York','1980-01-20','2002-09-11');
SELECT * FROM t3;
--replace_result $PORT PORT
--eval CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME=boys CONNECTION='jdbc:mysql://localhost:$PORT/connect?user=root&useSSL=false' OPTION_LIST='scrollable=1'
SELECT * FROM t1;
UPDATE t1 SET city = 'Phoenix' WHERE name = 'Henry';
INSERT INTO t1 SELECT * FROM t3;
INSERT INTO t1 VALUES('Tom','Seatle','2002-03-15',NULL);
SELECT * FROM t1;
DROP TABLE t3;
--echo #
--echo # Testing JDBC join operations
--echo #
CREATE TABLE t3 (
name CHAR(9) NOT NULL,
city CHAR(12) NOT NULL,
age INT(2))
ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='girls.txt' ENDING=1;
SELECT g.name, b.name, g.city FROM t3 g STRAIGHT_JOIN connect.boys b where g.city = b.city;
SELECT g.name, b.name, g.city FROM t3 g STRAIGHT_JOIN t1 b where g.city = b.city;
DROP TABLE t1, t3, connect.boys;
--echo #
--echo # Testing MariaDB JDBC driver
--echo #
USE connect;
--copy_file $MTR_SUITE_DIR/std_data/employee.dat $MYSQLD_DATADIR/connect/employee.dat
CREATE TABLE emp (
serialno CHAR(5) NOT NULL,
name VARCHAR(12) NOT NULL FLAG=6,
sex TINYINT(1) NOT NULL,
title VARCHAR(15) NOT NULL FLAG=20,
manager CHAR(5) NOT NULL,
department CHAR(4) NOT NULL FLAG=41,
secretary CHAR(5) NOT NULL FLAG=46,
salary DOUBLE(8,2) NOT NULL FLAG=52)
ENGINE=connect TABLE_TYPE=fix FILE_NAME='employee.dat' ENDING=1;
SELECT * FROM emp;
--echo #
--echo # Option Driver is required to find the Driver class inside the executable jar file
--echo #
USE test;
--replace_result $PORT PORT
--eval CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME=emp CONNECTION='jdbc:mariadb://localhost:$PORT/connect?user=root' OPTION_LIST='Driver=org.mariadb.jdbc.Driver'
--replace_result $PORT PORT
--eval SHOW CREATE TABLE t1
SELECT * FROM t1;
SELECT name, title, salary FROM t1 WHERE sex = 1;
DROP TABLE t1, connect.emp;
#
# Testing remote command execution (Driver option is no more necessary)
#
--replace_result $PORT PORT
--eval CREATE TABLE t2 (command varchar(128) not null,number int(5) not null flag=1,message varchar(255) flag=2) ENGINE=CONNECT TABLE_TYPE=JDBC CONNECTION='jdbc:mariadb://localhost:$PORT/connect' OPTION_LIST='User=root,Execsrc=1'
SELECT * FROM t2 WHERE command='drop table tx1';
SELECT * FROM t2 WHERE command = 'create table tx1 (a int not null, b char(32), c double(8,2))';
SELECT * FROM t2 WHERE command in ('insert into tx1 values(1,''The number one'',456.12)',"insert into tx1(a,b) values(2,'The number two'),(3,'The number three')");
SELECT * FROM t2 WHERE command='update tx1 set c = 3.1416 where a = 2';
SELECT * FROM t2 WHERE command='select * from tx1';
SELECT * FROM t2 WHERE command='delete from tx1 where a = 2';
SELECT * FROM connect.tx1;
DROP TABLE t2;
--replace_result $PORT PORT
--eval CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC CATFUNC=tables CONNECTION='jdbc:mariadb://localhost:$PORT/connect' option_list='User=root,Maxres=50'
SELECT * FROM t1;
DROP TABLE t1;
DROP TABLE connect.tx1;
#
# Clean up
#
--remove_file $MYSQLD_DATADIR/connect/boys.txt
--remove_file $MYSQLD_DATADIR/connect/employee.dat
DROP DATABASE connect;
--remove_file $MYSQLD_DATADIR/test/girls.txt
SET GLOBAL time_zone=SYSTEM;
SET time_zone=SYSTEM;
-- source jdbconn_cleanup.inc
|
