1// +build !future
2
3/*
4Copyright 2014 SAP SE
5
6Licensed under the Apache License, Version 2.0 (the "License");
7you may not use this file except in compliance with the License.
8You may obtain a copy of the License at
9
10    http://www.apache.org/licenses/LICENSE-2.0
11
12Unless required by applicable law or agreed to in writing, software
13distributed under the License is distributed on an "AS IS" BASIS,
14WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15See the License for the specific language governing permissions and
16limitations under the License.
17*/
18
19package driver
20
21import (
22	"bytes"
23	"database/sql"
24	"fmt"
25	"log"
26	"testing"
27)
28
29func TestCallEcho(t *testing.T) {
30	const procEcho = `create procedure %[1]s.%[2]s (in idata nvarchar(25), out odata nvarchar(25))
31language SQLSCRIPT as
32begin
33    odata := idata;
34end
35`
36
37	const txt = "Hello World!"
38
39	db, err := sql.Open(DriverName, TestDSN)
40	if err != nil {
41		t.Fatal(err)
42	}
43	defer db.Close()
44
45	procedure := RandomIdentifier("procEcho_")
46
47	if _, err := db.Exec(fmt.Sprintf(procEcho, TestSchema, procedure)); err != nil {
48		t.Fatal(err)
49	}
50
51	var out string
52
53	if err := db.QueryRow(fmt.Sprintf("call %s.%s(?, ?)", TestSchema, procedure), txt).Scan(&out); err != nil {
54		t.Fatal(err)
55	}
56
57	if out != txt {
58		t.Fatalf("value %s - expected %s", out, txt)
59	}
60
61}
62
63func TestCallBlobEcho(t *testing.T) {
64	const procBlobEcho = `create procedure %[1]s.%[2]s (in idata blob, out odata blob)
65language SQLSCRIPT as
66begin
67  odata := idata;
68end
69`
70
71	const txt = "Hello World!"
72
73	db, err := sql.Open(DriverName, TestDSN)
74	if err != nil {
75		t.Fatal(err)
76	}
77	defer db.Close()
78
79	procedure := RandomIdentifier("procBlobEcho_")
80
81	if _, err := db.Exec(fmt.Sprintf(procBlobEcho, TestSchema, procedure)); err != nil {
82		t.Fatal(err)
83	}
84
85	inlob := new(Lob)
86	inlob.SetReader(bytes.NewReader([]byte(txt)))
87
88	b := new(bytes.Buffer)
89	outlob := new(Lob)
90	outlob.SetWriter(b)
91
92	if err := db.QueryRow(fmt.Sprintf("call %s.%s(?, ?)", TestSchema, procedure), inlob).Scan(outlob); err != nil {
93		t.Fatal(err)
94	}
95
96	out := b.String()
97
98	if out != txt {
99		t.Fatalf("value %s - expected %s", out, txt)
100	}
101}
102
103type testTableData struct {
104	i int
105	x string
106}
107
108var testTableQuery1Data = []*testTableData{
109	&testTableData{0, "A"},
110	&testTableData{1, "B"},
111	&testTableData{2, "C"},
112	&testTableData{3, "D"},
113	&testTableData{4, "E"},
114}
115
116var testTableQuery2Data = []*testTableData{
117	&testTableData{0, "A"},
118	&testTableData{1, "B"},
119	&testTableData{2, "C"},
120	&testTableData{3, "D"},
121	&testTableData{4, "E"},
122	&testTableData{5, "F"},
123	&testTableData{6, "G"},
124	&testTableData{7, "H"},
125	&testTableData{8, "I"},
126	&testTableData{9, "J"},
127}
128
129var testTableQuery3Data = []*testTableData{
130	&testTableData{0, "A"},
131	&testTableData{1, "B"},
132	&testTableData{2, "C"},
133	&testTableData{3, "D"},
134	&testTableData{4, "E"},
135	&testTableData{5, "F"},
136	&testTableData{6, "G"},
137	&testTableData{7, "H"},
138	&testTableData{8, "I"},
139	&testTableData{9, "J"},
140	&testTableData{10, "K"},
141	&testTableData{11, "L"},
142	&testTableData{12, "M"},
143	&testTableData{13, "N"},
144	&testTableData{14, "O"},
145}
146
147func checkTableQueryData(t *testing.T, db *sql.DB, query string, data []*testTableData) {
148
149	rows, err := db.Query(query)
150	if err != nil {
151		t.Fatal(err)
152	}
153	defer rows.Close()
154
155	j := 0
156	for rows.Next() {
157
158		var i int
159		var x string
160
161		if err := rows.Scan(&i, &x); err != nil {
162			log.Fatal(err)
163		}
164
165		// log.Printf("i %d x %s", i, x)
166		if i != data[j].i {
167			t.Fatalf("value i %d - expected %d", i, data[j].i)
168		}
169		if x != data[j].x {
170			t.Fatalf("value x %s - expected %s", x, data[j].x)
171		}
172		j++
173	}
174	if err := rows.Err(); err != nil {
175		log.Fatal(err)
176	}
177}
178
179func TestCallTableOut(t *testing.T) {
180	const procTableOut = `create procedure %[1]s.%[2]s (in i integer, out t1 %[1]s.%[3]s, out t2 %[1]s.%[3]s, out t3 %[1]s.%[3]s)
181language SQLSCRIPT as
182begin
183  create local temporary table #test like %[1]s.%[3]s;
184  insert into #test values(0, 'A');
185  insert into #test values(1, 'B');
186  insert into #test values(2, 'C');
187  insert into #test values(3, 'D');
188  insert into #test values(4, 'E');
189  t1 = select * from #test;
190  insert into #test values(5, 'F');
191  insert into #test values(6, 'G');
192  insert into #test values(7, 'H');
193  insert into #test values(8, 'I');
194  insert into #test values(9, 'J');
195  t2 = select * from #test;
196  insert into #test values(10, 'K');
197  insert into #test values(11, 'L');
198  insert into #test values(12, 'M');
199  insert into #test values(13, 'N');
200  insert into #test values(14, 'O');
201  t3 = select * from #test;
202  drop table #test;
203end
204`
205
206	db, err := sql.Open(DriverName, TestDSN)
207	if err != nil {
208		t.Fatal(err)
209	}
210	defer db.Close()
211
212	tableType := RandomIdentifier("tt2_")
213	procedure := RandomIdentifier("procTableOut_")
214
215	if _, err := db.Exec(fmt.Sprintf("create type %s.%s as table (i integer, x varchar(10))", TestSchema, tableType)); err != nil {
216		t.Fatal(err)
217	}
218
219	if _, err := db.Exec(fmt.Sprintf(procTableOut, TestSchema, procedure, tableType)); err != nil {
220		t.Fatal(err)
221	}
222
223	var tableQuery1, tableQuery2, tableQuery3 string
224
225	rows, err := db.Query(fmt.Sprintf("call %s.%s(?, ?, ?, ?)", TestSchema, procedure), 1)
226	if err != nil {
227		t.Fatal(err)
228	}
229	defer rows.Close()
230
231	if !rows.Next() {
232		log.Fatal(rows.Err())
233	}
234	if err := rows.Scan(&tableQuery1, &tableQuery2, &tableQuery3); err != nil {
235		log.Fatal(err)
236	}
237
238	checkTableQueryData(t, db, tableQuery1, testTableQuery1Data)
239	checkTableQueryData(t, db, tableQuery2, testTableQuery2Data)
240	checkTableQueryData(t, db, tableQuery3, testTableQuery3Data)
241
242}
243