NCBI C++ ToolKit
bcp.c
Go to the documentation of this file.

Go to the SVN repository for this file.

Go to the SVN repository for this file.

Go to the SVN repository for this file.

Go to the SVN repository for this file.

Go to the SVN repository for this file.

Go to the SVN repository for this file.

Go to the SVN repository for this file.

Go to the SVN repository for this file.

Go to the SVN repository for this file.

1 #include "common.h"
2 #define TDSODBC_BCP
3 #include <odbcss.h>
4 
5 #include <common/test_assert.h>
6 
7 #ifdef UNICODE
9 #else
10 typedef char bcp_init_char_t;
11 #endif
12 
13 struct prefixed_int {
15  int value;
16 };
17 struct prefixed_str {
19  char value[64];
20 };
21 
22 /*
23  * Static data for insertion
24  */
25 static struct prefixed_int not_null_bit = {4, 1};
26 static struct prefixed_str not_null_char = {64, "a char"};
27 static struct prefixed_str not_null_varchar = {64, "a varchar"};
28 static struct prefixed_str not_null_datetime = {64, "2003-12-17 15:44:00.000"};
29 static struct prefixed_str not_null_smalldatetime = {64, "2003-12-17 15:44:00"};
30 static struct prefixed_str not_null_money = {64, "12.34"};
31 static struct prefixed_str not_null_smallmoney = {64, "12.34"};
32 static struct prefixed_str not_null_float = {64, "12.34"};
33 static struct prefixed_str not_null_real = {64, "12.34"};
34 static struct prefixed_str not_null_decimal = {64, "12.34"};
35 static struct prefixed_str not_null_numeric = {64, "12.34"};
36 static struct prefixed_int not_null_int = {4, 1234};
37 static struct prefixed_int not_null_smallint = {4, 1234};
38 static struct prefixed_int not_null_tinyint = {4, 123};
39 static struct prefixed_str not_null_nvarchar = {64, "a wide var"};
40 static ODBCINT64 null_prefix = -1;
41 
42 static const char *expected[] = {
43  "1",
44  "a char ","a varchar","2003-12-17 15:44:00.000","2003-12-17 15:44:00",
45  "12.34","12.34","12.34","12.3400002","12.34","12.34",
46  "1234","1234","123",
47  "a wide var",
48 };
49 static const int total_cols = 29;
50 
51 static const char *expected_special[] = {
52  "2015-03-14 15:26:53.000",
53  "2015-03-14 15:26:53.589793",
54  "3.141593000",
55  "3.141593", /* MS driver has "3141593" here. Bug? Should we be bug-compatible? */
56  "",
57 };
58 
59 static int tds_version;
60 
61 static void
62 init(void)
63 {
64  odbc_command("if exists (select 1 from sysobjects where type = 'U' and name = 'all_types_bcp_unittest') drop table all_types_bcp_unittest");
65  odbc_command("if exists (select 1 from sysobjects where type = 'U' and name = 'special_types_bcp_unittest') drop table special_types_bcp_unittest");
66 
67  odbc_command("CREATE TABLE all_types_bcp_unittest ("
68  " not_null_bit bit NOT NULL"
69  ""
70  ", not_null_char char(10) NOT NULL"
71  ", not_null_varchar varchar(10) NOT NULL"
72  ""
73  ", not_null_datetime datetime NOT NULL"
74  ", not_null_smalldatetime smalldatetime NOT NULL"
75  ""
76  ", not_null_money money NOT NULL"
77  ", not_null_smallmoney smallmoney NOT NULL"
78  ""
79  ", not_null_float float NOT NULL"
80  ", not_null_real real NOT NULL"
81  ""
82  ", not_null_decimal decimal(5,2) NOT NULL"
83  ", not_null_numeric numeric(5,2) NOT NULL"
84  ""
85  ", not_null_int int NOT NULL"
86  ", not_null_smallint smallint NOT NULL"
87  ", not_null_tinyint tinyint NOT NULL"
88  ", not_null_nvarchar nvarchar(10) NOT NULL"
89  ""
90  ", nullable_char char(10) NULL"
91  ", nullable_varchar varchar(10) NULL"
92  ""
93  ", nullable_datetime datetime NULL"
94  ", nullable_smalldatetime smalldatetime NULL"
95  ""
96  ", nullable_money money NULL"
97  ", nullable_smallmoney smallmoney NULL"
98  ""
99  ", nullable_float float NULL"
100  ", nullable_real real NULL"
101  ""
102  ", nullable_decimal decimal(5,2) NULL"
103  ", nullable_numeric numeric(5,2) NULL"
104  ""
105  ", nullable_int int NULL"
106  ", nullable_smallint smallint NULL"
107  ", nullable_tinyint tinyint NULL"
108  ", nullable_nvarchar nvarchar(10) NULL"
109  ")");
110 
111  if (tds_version < 0x703)
112  return;
113 
114  /* Excludes:
115  * binary
116  * image
117  * uniqueidentifier
118  * varbinary
119  * text
120  * timestamp
121  * nchar
122  * ntext
123  * nvarchar
124  */
125  odbc_command("CREATE TABLE special_types_bcp_unittest ("
126  "dt datetime not null,"
127  "dt2 datetime2(6) not null,"
128  "num decimal(19,9) not null,"
129  "numstr varchar(64) not null,"
130  "empty varchar(64) not null,"
131  "bitnull bit null"
132  ")");
133 }
134 
135 #define VARCHAR_BIND(x) \
136  bcp_bind( odbc_conn, \
137  (unsigned char *) (prefixlen == 0 ? (void*)&x.value : &x), \
138  prefixlen, (SQLINTEGER) strlen(x.value), NULL, termlen, \
139  BCP_TYPE_SQLVARCHAR, col++ )
140 
141 #define INT_BIND(x) \
142  bcp_bind( odbc_conn, (unsigned char *) (prefixlen == 0 ? (void*)&x.value : &x), prefixlen, SQL_VARLEN_DATA, NULL, termlen, BCP_TYPE_SQLINT4, col++ )
143 
144 #define NULL_BIND(x, type) \
145  bcp_bind( odbc_conn, (unsigned char *) (prefixlen == 0 ? (void*)&x.value : &null_prefix), prefixlen, prefixlen == 0 ? SQL_NULL_DATA : SQL_VARLEN_DATA, NULL, termlen, type, col++ )
146 
147 static void
148 test_bind(int prefixlen)
149 {
150  enum { termlen = 0 };
151 
152  RETCODE fOK;
153  int col=1;
154 
155  /* non nulls */
156  fOK = INT_BIND(not_null_bit);
157  assert(fOK == SUCCEED);
158 
160  assert(fOK == SUCCEED);
162  assert(fOK == SUCCEED);
163 
165  assert(fOK == SUCCEED);
167  assert(fOK == SUCCEED);
168 
170  assert(fOK == SUCCEED);
172  assert(fOK == SUCCEED);
173 
175  assert(fOK == SUCCEED);
177  assert(fOK == SUCCEED);
178 
180  assert(fOK == SUCCEED);
182  assert(fOK == SUCCEED);
183 
184  fOK = INT_BIND(not_null_int);
185  assert(fOK == SUCCEED);
187  assert(fOK == SUCCEED);
188  fOK = INT_BIND(not_null_tinyint);
189  assert(fOK == SUCCEED);
191  assert(fOK == SUCCEED);
192 
193  /* nulls */
194  assert(fOK == SUCCEED);
195  fOK = NULL_BIND(not_null_char, BCP_TYPE_SQLVARCHAR);
196  assert(fOK == SUCCEED);
197  fOK = NULL_BIND(not_null_varchar, BCP_TYPE_SQLVARCHAR);
198  assert(fOK == SUCCEED);
199 
200  fOK = NULL_BIND(not_null_datetime, BCP_TYPE_SQLVARCHAR);
201  assert(fOK == SUCCEED);
202  fOK = NULL_BIND(not_null_smalldatetime, BCP_TYPE_SQLVARCHAR);
203  assert(fOK == SUCCEED);
204 
205  fOK = NULL_BIND(not_null_money, BCP_TYPE_SQLVARCHAR);
206  assert(fOK == SUCCEED);
207  fOK = NULL_BIND(not_null_smallmoney, BCP_TYPE_SQLVARCHAR);
208  assert(fOK == SUCCEED);
209 
210  fOK = NULL_BIND(not_null_float, BCP_TYPE_SQLVARCHAR);
211  assert(fOK == SUCCEED);
212  fOK = NULL_BIND(not_null_real, BCP_TYPE_SQLVARCHAR);
213  assert(fOK == SUCCEED);
214 
215  fOK = NULL_BIND(not_null_decimal, BCP_TYPE_SQLVARCHAR);
216  assert(fOK == SUCCEED);
217  fOK = NULL_BIND(not_null_numeric, BCP_TYPE_SQLVARCHAR);
218  assert(fOK == SUCCEED);
219 
220  fOK = NULL_BIND(not_null_int, BCP_TYPE_SQLINT4);
221  assert(fOK == SUCCEED);
222  fOK = NULL_BIND(not_null_smallint, BCP_TYPE_SQLINT4);
223  assert(fOK == SUCCEED);
224  fOK = NULL_BIND(not_null_tinyint, BCP_TYPE_SQLINT4);
225  assert(fOK == SUCCEED);
226  fOK = NULL_BIND(not_null_nvarchar, BCP_TYPE_SQLVARCHAR);
227  assert(fOK == SUCCEED);
228 
229 }
230 
231 static void
232 set_attr(void)
233 {
234  SQLSetConnectAttr(odbc_conn, SQL_COPT_SS_BCP, (SQLPOINTER)SQL_BCP_ON, 0);
235 }
236 
237 static void
238 report_bcp_error(const char *errmsg, int line, const char *file)
239 {
240  odbc_stmt = NULL;
241  odbc_report_error(errmsg, line, file);
242 }
243 
244 static void normal_inserts(int prefixlen);
245 static void normal_select(void);
246 static void special_inserts(void);
247 static void special_select(void);
248 
249 static const char table_name[] = "all_types_bcp_unittest";
250 
251 int
252 main(int argc, char *argv[])
253 {
254  const char *s;
255 
257  odbc_connect();
258 
260 
261  init();
262 
263  normal_inserts(0);
264  if (tds_version >= 0x703)
265  special_inserts();
266  normal_select();
267  if (tds_version >= 0x703)
268  special_select();
269 
270  odbc_command("delete from all_types_bcp_unittest");
271  normal_inserts(8);
272  normal_select();
273 
274  if ((s = getenv("BCP")) != NULL && 0 == strcmp(s, "nodrop")) {
275  fprintf(stdout, "BCP=nodrop: '%s' kept\n", table_name);
276  } else {
277  fprintf(stdout, "Dropping table %s\n", table_name);
278  odbc_command("drop table all_types_bcp_unittest");
279  if (tds_version >= 0x703)
280  odbc_command("drop table special_types_bcp_unittest");
281  }
282 
283  odbc_disconnect();
284 
285  printf("Done.\n");
286  return 0;
287 }
288 
289 static void normal_inserts(int prefixlen)
290 {
291  int i;
292  int rows_sent;
293 
294  /* set up and send the bcp */
295  fprintf(stdout, "preparing to insert into %s ... ", table_name);
296  if (bcp_init(odbc_conn, (bcp_init_char_t *) T(table_name), NULL, NULL, BCP_DIRECTION_IN) == FAIL)
297  report_bcp_error("bcp_init", __LINE__, __FILE__);
298  fprintf(stdout, "OK\n");
299 
300  test_bind(prefixlen);
301 
302  fprintf(stdout, "Sending same row 10 times... \n");
303  for (i=0; i<10; i++)
304  if (bcp_sendrow(odbc_conn) == FAIL)
305  report_bcp_error("bcp_sendrow", __LINE__, __FILE__);
306 
307 #if 1
308  rows_sent = bcp_batch(odbc_conn);
309  if (rows_sent == -1)
310  report_bcp_error("bcp_batch", __LINE__, __FILE__);
311 #endif
312 
313  printf("OK\n");
314 
315  /* end bcp. */
316  rows_sent = bcp_done(odbc_conn);
317  if (rows_sent != 0)
318  report_bcp_error("bcp_done", __LINE__, __FILE__);
319  else
320  printf("%d rows copied.\n", rows_sent);
321 
322  printf("done\n");
323 }
324 
325 static void special_inserts(void)
326 {
327  int rows_sent;
328  SQL_TIMESTAMP_STRUCT timestamp;
329  DBDATETIME datetime;
330  SQL_NUMERIC_STRUCT numeric;
331 
332  printf("sending special types\n");
333  rows_sent = 0;
334 
335  if (bcp_init(odbc_conn, (bcp_init_char_t *) T("special_types_bcp_unittest"), NULL, NULL, BCP_DIRECTION_IN) == FAIL)
336  report_bcp_error("bcp_init", __LINE__, __FILE__);
337  printf("OK\n");
338 
339  datetime.dtdays = 42075;
340  datetime.dttime = 16683900;
341  timestamp.year = 2015;
342  timestamp.month = 3;
343  timestamp.day = 14;
344  timestamp.hour = 15;
345  timestamp.minute = 26;
346  timestamp.second = 53;
347  timestamp.fraction = 589793238;
348  memset(&numeric, 0, sizeof(numeric));
349  numeric.precision = 19;
350  numeric.scale = 6;
351  numeric.sign = 1;
352  numeric.val[0] = 0xd9;
353  numeric.val[1] = 0xef;
354  numeric.val[2] = 0x2f;
355  bcp_bind(odbc_conn, (unsigned char *) &datetime, 0, sizeof(datetime), NULL, 0, BCP_TYPE_SQLDATETIME, 1);
356  bcp_bind(odbc_conn, (unsigned char *) &timestamp, 0, sizeof(timestamp), NULL, 0, BCP_TYPE_SQLDATETIME2, 2);
357  bcp_bind(odbc_conn, (unsigned char *) &numeric, 0, sizeof(numeric), NULL, 0, BCP_TYPE_SQLDECIMAL, 3);
358  bcp_bind(odbc_conn, (unsigned char *) &numeric, 0, sizeof(numeric), NULL, 0, BCP_TYPE_SQLDECIMAL, 4);
359  bcp_bind(odbc_conn, (unsigned char *) "", 0, 0, NULL, 0, BCP_TYPE_SQLVARCHAR, 5);
360  bcp_bind(odbc_conn, (unsigned char *) &not_null_bit, 0, SQL_NULL_DATA, NULL, 0, BCP_TYPE_SQLINT4, 6);
361 
362  if (bcp_sendrow(odbc_conn) == FAIL)
363  report_bcp_error("bcp_sendrow", __LINE__, __FILE__);
364 
365  rows_sent = bcp_batch(odbc_conn);
366  if (rows_sent != 1)
367  report_bcp_error("bcp_batch", __LINE__, __FILE__);
368 
369  printf("OK\n");
370 
371  /* end bcp. */
372 
373  rows_sent = bcp_done(odbc_conn);
374  if (rows_sent != 0)
375  report_bcp_error("bcp_done", __LINE__, __FILE__);
376  else
377  printf("%d rows copied.\n", rows_sent);
378 
379  printf("done\n");
380 }
381 
382 static void normal_select(void)
383 {
384  int ok = 1, i;
385 
386  odbc_command("select * from all_types_bcp_unittest");
387  CHKFetch("SI");
388 
389  /* first columns have values */
390  for (i = 0; i < sizeof(expected)/sizeof(expected[0]); ++i) {
391  char output[128];
392  SQLLEN dataSize;
393  CHKGetData(i + 1, SQL_C_CHAR, output, sizeof(output), &dataSize, "S");
394  if (strcmp(output, expected[i]) || dataSize <= 0) {
395  fprintf(stderr, "Invalid returned col %d: '%s'!='%s'\n", i, expected[i], output);
396  ok = 0;
397  }
398  }
399  /* others are NULL */
400  for (; i < total_cols; ++i) {
401  char output[128];
402  SQLLEN dataSize;
403  CHKGetData(i + 1, SQL_C_CHAR, output, sizeof(output), &dataSize, "S");
404  if (dataSize != SQL_NULL_DATA) {
405  fprintf(stderr, "Invalid returned col %d: should be NULL'\n", i);
406  ok = 0;
407  }
408  }
409  if (!ok)
410  exit(1);
411  CHKCloseCursor("SI");
412 }
413 
414 static void special_select(void)
415 {
416  int ok = 1, i;
417 
418  odbc_command("select top 1 * from special_types_bcp_unittest");
419  CHKFetch("SI");
420  for (i = 0; i < sizeof(expected_special)/sizeof(expected_special[0]); ++i) {
421  char output[128];
422  SQLLEN dataSize;
423  CHKGetData(i + 1, SQL_C_CHAR, output, sizeof(output), &dataSize, "S");
424  if (strcmp(output, expected_special[i]) || (dataSize <= 0 && expected_special[i][0] != '\0')) {
425  fprintf(stderr, "Invalid returned col %d: '%s'!='%s'\n", i, expected_special[i], output);
426  ok = 0;
427  }
428  }
429  if (!ok)
430  exit(1);
431  CHKCloseCursor("SI");
432 }
#define CHKGetData(a, b, c, d, e, res)
Definition: common.h:136
#define odbc_command(cmd)
Definition: common.h:179
#define T(s)
Definition: common.h:230
#define CHKCloseCursor(res)
Definition: common.h:100
#define CHKFetch(res)
Definition: common.h:118
int main(int argc, char **argv)
Definition: bcp.c:166
#define NULL
Definition: ncbistd.hpp:225
RETCODE bcp_bind(DBPROCESS *dbproc, BYTE *varaddr, int prefixlen, DBINT varlen, BYTE *terminator, int termlen, int db_vartype, int table_column)
Bind a program host variable to a database column.
Definition: bcp.c:2032
DBINT bcp_done(DBPROCESS *dbproc)
Conclude the transfer of data from program variables.
Definition: bcp.c:1990
DBINT bcp_batch(DBPROCESS *dbproc)
Commit a set of rows to the table.
Definition: bcp.c:1963
RETCODE bcp_init(DBPROCESS *dbproc, const char *tblname, const char *hfile, const char *errfile, int direction)
Prepare for bulk copy operation on a table.
Definition: bcp.c:164
RETCODE bcp_sendrow(DBPROCESS *dbproc)
Write data in host variables to the table.
Definition: bcp.c:1339
exit(2)
FILE * file
int i
int strcmp(const char *str1, const char *str2)
Definition: odbc_utils.hpp:160
static void special_select(void)
Definition: bcp.c:414
static void report_bcp_error(const char *errmsg, int line, const char *file)
Definition: bcp.c:238
static void init(void)
Definition: bcp.c:62
static const char table_name[]
Definition: bcp.c:249
static struct prefixed_str not_null_smalldatetime
Definition: bcp.c:29
static struct prefixed_int not_null_int
Definition: bcp.c:36
#define NULL_BIND(x, type)
Definition: bcp.c:144
static struct prefixed_str not_null_numeric
Definition: bcp.c:35
static struct prefixed_str not_null_smallmoney
Definition: bcp.c:31
#define VARCHAR_BIND(x)
Definition: bcp.c:135
static struct prefixed_int not_null_smallint
Definition: bcp.c:37
char bcp_init_char_t
Definition: bcp.c:10
static const char * expected_special[]
Definition: bcp.c:51
static void special_inserts(void)
Definition: bcp.c:325
static struct prefixed_int not_null_bit
Definition: bcp.c:25
static struct prefixed_str not_null_money
Definition: bcp.c:30
static void normal_select(void)
Definition: bcp.c:382
static ODBCINT64 null_prefix
Definition: bcp.c:40
static struct prefixed_str not_null_char
Definition: bcp.c:26
static const int total_cols
Definition: bcp.c:49
static struct prefixed_str not_null_nvarchar
Definition: bcp.c:39
static void normal_inserts(int prefixlen)
Definition: bcp.c:289
static struct prefixed_str not_null_decimal
Definition: bcp.c:34
static struct prefixed_str not_null_varchar
Definition: bcp.c:27
#define INT_BIND(x)
Definition: bcp.c:141
static int tds_version
Definition: bcp.c:59
static void test_bind(int prefixlen)
Definition: bcp.c:148
static const char * expected[]
Definition: bcp.c:42
static struct prefixed_str not_null_real
Definition: bcp.c:33
static struct prefixed_int not_null_tinyint
Definition: bcp.c:38
static struct prefixed_str not_null_float
Definition: bcp.c:32
static struct prefixed_str not_null_datetime
Definition: bcp.c:28
static void set_attr(void)
Definition: bcp.c:232
HSTMT odbc_stmt
Definition: common.c:33
void odbc_report_error(const char *errmsg, int line, const char *file)
Definition: common.c:188
int odbc_disconnect(void)
Definition: common.c:290
void(* odbc_set_conn_attr)(void)
Definition: common.c:35
HDBC odbc_conn
Definition: common.c:32
int odbc_tds_version(void)
Definition: common.c:444
static SQLRETURN odbc_connect(TDS_DBC *dbc, TDSLOGIN *login)
Definition: odbc.c:356
#define SQLSetConnectAttr(h, n, p, t)
Definition: odbc.c:50
#define SQLLEN
Definition: odbc.h:52
static SQLCHAR output[256]
Definition: print.c:5
#define SQL_NULL_DATA
Definition: sql.h:29
#define SQL_C_CHAR
Definition: sqlext.h:511
#define ODBCINT64
Definition: sqltypes.h:401
void * SQLPOINTER
Definition: sqltypes.h:195
WCHAR SQLWCHAR
Definition: sqltypes.h:458
#define assert(x)
Definition: srv_diag.hpp:58
DBINT dtdays
Definition: sybdb.h:298
DBINT dttime
Definition: sybdb.h:299
int value
Definition: bcp.c:15
ODBCINT64 prefix
Definition: bcp.c:14
char value[64]
Definition: bcp.c:19
ODBCINT64 prefix
Definition: bcp.c:18
SQLCHAR val[16]
Definition: sqltypes.h:428
SQLUSMALLINT hour
Definition: sqltypes.h:309
SQLUINTEGER fraction
Definition: sqltypes.h:312
SQLUSMALLINT second
Definition: sqltypes.h:311
SQLSMALLINT year
Definition: sqltypes.h:306
SQLUSMALLINT month
Definition: sqltypes.h:307
SQLUSMALLINT day
Definition: sqltypes.h:308
SQLUSMALLINT minute
Definition: sqltypes.h:310
int RETCODE
Definition: sybdb.h:121
#define SUCCEED
Definition: sybdb.h:585
#define FAIL
Definition: sybdb.h:586
Modified on Tue Apr 09 07:59:55 2024 by modify_doxy.py rev. 669887
Modified on Wed Apr 10 07:35:16 2024 by modify_doxy.py rev. 669887
Modified on Thu Apr 11 15:20:05 2024 by modify_doxy.py rev. 669887
Modified on Fri Apr 12 17:22:30 2024 by modify_doxy.py rev. 669887
Modified on Sat Apr 13 11:49:51 2024 by modify_doxy.py rev. 669887
Modified on Sun Apr 14 05:29:06 2024 by modify_doxy.py rev. 669887
Modified on Tue Apr 16 20:14:23 2024 by modify_doxy.py rev. 669887
Modified on Wed Apr 17 13:10:41 2024 by modify_doxy.py rev. 669887
Modified on Sat Apr 20 12:21:41 2024 by modify_doxy.py rev. 669887