1 #include <config.h>
2
3 #include <stdio.h>
4
5 #if HAVE_STDLIB_H
6 #include <stdlib.h>
7 #endif /* HAVE_STRING_H */
8
9 #if HAVE_STRING_H
10 #include <string.h>
11 #endif /* HAVE_STRING_H */
12
13 #include <ctpublic.h>
14 #include "common.h"
15
16 #include <common/test_assert.h>
17
18 #define MAX(X,Y) (((X) > (Y)) ? (X) : (Y))
19 #define MIN(X,Y) (((X) < (Y)) ? (X) : (Y))
20
21 static char software_version[] = "$Id: rpc_ct_param.c 487511 2015-12-17 20:11:40Z ucko $";
22 static void *no_unused_var_warn[] = { software_version, no_unused_var_warn };
23
24 CS_RETCODE ex_clientmsg_cb(CS_CONTEXT * context, CS_CONNECTION * connection, CS_CLIENTMSG * errmsg);
25 CS_RETCODE ex_servermsg_cb(CS_CONTEXT * context, CS_CONNECTION * connection, CS_SERVERMSG * errmsg);
26 static CS_INT ex_display_dlen(CS_DATAFMT * column);
27 static CS_RETCODE ex_display_header(CS_INT numcols, CS_DATAFMT columns[]);
28
29 typedef struct _ex_column_data
30 {
31 CS_SMALLINT indicator;
32 CS_CHAR *value;
33 CS_INT valuelen;
34 }
35 EX_COLUMN_DATA;
36
37 /* Testing: array binding of result set */
38 int
main(int argc,char * argv[])39 main(int argc, char *argv[])
40 {
41 CS_CONTEXT *ctx;
42 CS_CONNECTION *conn;
43 CS_COMMAND *cmd;
44 int verbose = 0;
45
46 CS_RETCODE ret;
47 CS_INT res_type;
48 CS_INT num_cols;
49
50 CS_CHAR cmdbuf[4096];
51
52 CS_DATAFMT datafmt;
53 CS_DATAFMT srcfmt;
54 CS_DATAFMT destfmt;
55 CS_INT intvar;
56 CS_SMALLINT smallintvar;
57 CS_FLOAT floatvar;
58 CS_MONEY moneyvar;
59 CS_BINARY binaryvar;
60 char moneystring[10];
61 char rpc_name[15];
62 CS_INT destlen;
63 CS_INT i;
64 CS_INT j;
65 CS_INT row_count = 0;
66 CS_INT rows_read;
67 CS_INT disp_len;
68 EX_COLUMN_DATA *coldata;
69 CS_DATAFMT *outdatafmt;
70 CS_SMALLINT msg_id;
71
72
73
74 fprintf(stdout, "%s: submit a stored procedure using ct_param \n", __FILE__);
75 if (verbose) {
76 fprintf(stdout, "Trying login\n");
77 }
78 ret = try_ctlogin(&ctx, &conn, &cmd, verbose);
79 if (ret != CS_SUCCEED) {
80 fprintf(stderr, "Login failed\n");
81 return 1;
82 }
83
84 ct_callback(ctx, NULL, CS_SET, CS_CLIENTMSG_CB, (CS_VOID *) ex_clientmsg_cb);
85
86 ct_callback(ctx, NULL, CS_SET, CS_SERVERMSG_CB, (CS_VOID *) ex_servermsg_cb);
87
88 /* do not test error */
89 ret = run_command(cmd, "IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'sample_rpc' AND type = 'P') DROP PROCEDURE sample_rpc");
90
91 strcpy(cmdbuf, "create proc sample_rpc (@intparam int, \
92 @sintparam smallint output, @floatparam float output, \
93 @moneyparam money output, \
94 @dateparam datetime output, @charparam char(20) output, @empty varchar(20) output, \
95 @binaryparam binary(20) output) \
96 as ");
97
98 strcat(cmdbuf, "select @intparam, @sintparam, @floatparam, @moneyparam, \
99 @dateparam, @charparam, @binaryparam \
100 select @sintparam = @sintparam + @intparam \
101 select @floatparam = @floatparam + @intparam \
102 select @moneyparam = @moneyparam + convert(money, @intparam) \
103 select @dateparam = getdate() \
104 select @charparam = \'The char parameters\' \
105 select @empty = \'\' \
106 select @binaryparam = @binaryparam \
107 print \'This is the message printed out by sample_rpc.\'");
108
109 ret = run_command(cmd, cmdbuf);
110
111 if (ret != CS_SUCCEED) {
112 fprintf(stderr, "create proc failed\n");
113 return 1;
114 }
115
116 /*
117 * Assign values to the variables used for parameter passing.
118 */
119
120 intvar = 2;
121 smallintvar = 234;
122 floatvar = 0.12;
123 binaryvar = (CS_BINARY) 0xff;
124 strcpy(rpc_name, "sample_rpc");
125 strcpy(moneystring, "300.90");
126
127 /*
128 * Clear and setup the CS_DATAFMT structures used to convert datatypes.
129 */
130
131 memset(&srcfmt, 0, sizeof(CS_DATAFMT));
132 srcfmt.datatype = CS_CHAR_TYPE;
133 srcfmt.maxlength = (CS_INT)strlen(moneystring);
134 srcfmt.precision = 5;
135 srcfmt.scale = 2;
136 srcfmt.locale = NULL;
137
138 memset(&destfmt, 0, sizeof(CS_DATAFMT));
139 destfmt.datatype = CS_MONEY_TYPE;
140 destfmt.maxlength = sizeof(CS_MONEY);
141 destfmt.precision = 5;
142 destfmt.scale = 2;
143 destfmt.locale = NULL;
144
145 /*
146 * Convert the string representing the money value
147 * to a CS_MONEY variable. Since this routine does not have the
148 * context handle, we use the property functions to get it.
149 */
150 if ((ret = ct_cmd_props(cmd, CS_GET, CS_PARENT_HANDLE, &conn, CS_UNUSED, NULL)) != CS_SUCCEED) {
151 fprintf(stderr, "ct_cmd_props() failed");
152 return 1;
153 }
154 if ((ret = ct_con_props(conn, CS_GET, CS_PARENT_HANDLE, &ctx, CS_UNUSED, NULL)) != CS_SUCCEED) {
155 fprintf(stderr, "ct_con_props() failed");
156 return 1;
157 }
158 ret = cs_convert(ctx, &srcfmt, (CS_VOID *) moneystring, &destfmt, &moneyvar, &destlen);
159 if (ret != CS_SUCCEED) {
160 fprintf(stderr, "cs_convert() failed");
161 return 1;
162 }
163
164 /*
165 * Send the RPC command for our stored procedure.
166 */
167 if ((ret = ct_command(cmd, CS_RPC_CMD, rpc_name, CS_NULLTERM, CS_NO_RECOMPILE)) != CS_SUCCEED) {
168 fprintf(stderr, "ct_command(CS_RPC_CMD) failed");
169 return 1;
170 }
171
172 /*
173 * Clear and setup the CS_DATAFMT structure, then pass
174 * each of the parameters for the RPC.
175 */
176 memset(&datafmt, 0, sizeof(datafmt));
177 strcpy(datafmt.name, "@intparam");
178 datafmt.namelen = CS_NULLTERM;
179 datafmt.datatype = CS_INT_TYPE;
180 datafmt.maxlength = CS_UNUSED;
181 datafmt.status = CS_INPUTVALUE;
182 datafmt.locale = NULL;
183
184 if ((ret = ct_param(cmd, &datafmt, (CS_VOID *) & intvar, CS_SIZEOF(CS_INT), 0)) != CS_SUCCEED) {
185 fprintf(stderr, "ct_param(int) failed");
186 return 1;
187 }
188
189 strcpy(datafmt.name, "@sintparam");
190 datafmt.namelen = CS_NULLTERM;
191 datafmt.datatype = CS_SMALLINT_TYPE;
192 datafmt.maxlength = 255;
193 datafmt.status = CS_RETURN;
194 datafmt.locale = NULL;
195
196 if ((ret = ct_param(cmd, &datafmt, (CS_VOID *) & smallintvar, CS_SIZEOF(CS_SMALLINT), 0)) != CS_SUCCEED) {
197 fprintf(stderr, "ct_param(smallint) failed");
198 return 1;
199 }
200
201 strcpy(datafmt.name, "@floatparam");
202 datafmt.namelen = CS_NULLTERM;
203 datafmt.datatype = CS_FLOAT_TYPE;
204 datafmt.maxlength = 255;
205 datafmt.status = CS_RETURN;
206 datafmt.locale = NULL;
207
208 if ((ret = ct_param(cmd, &datafmt, (CS_VOID *) & floatvar, CS_SIZEOF(CS_FLOAT), 0)) != CS_SUCCEED) {
209 fprintf(stderr, "ct_param(float) failed");
210 return 1;
211 }
212
213
214 strcpy(datafmt.name, "@moneyparam");
215 datafmt.namelen = CS_NULLTERM;
216 datafmt.datatype = CS_MONEY_TYPE;
217 datafmt.maxlength = 255;
218 datafmt.status = CS_RETURN;
219 datafmt.locale = NULL;
220
221 if ((ret = ct_param(cmd, &datafmt, (CS_VOID *) & moneyvar, CS_SIZEOF(CS_MONEY), 0)) != CS_SUCCEED) {
222 fprintf(stderr, "ct_param(money) failed");
223 return 1;
224 }
225
226 strcpy(datafmt.name, "@dateparam");
227 datafmt.namelen = CS_NULLTERM;
228 datafmt.datatype = CS_DATETIME4_TYPE;
229 datafmt.maxlength = 255;
230 datafmt.status = CS_RETURN;
231 datafmt.locale = NULL;
232
233 /*
234 * The datetime variable is filled in by the RPC so pass NULL for
235 * the data, 0 for data length, and -1 for the indicator arguments.
236 */
237 if ((ret = ct_param(cmd, &datafmt, NULL, 0, -1)) != CS_SUCCEED) {
238 fprintf(stderr, "ct_param(datetime4) failed");
239 return 1;
240 }
241 strcpy(datafmt.name, "@charparam");
242 datafmt.namelen = CS_NULLTERM;
243 datafmt.datatype = CS_CHAR_TYPE;
244 datafmt.maxlength = 60;
245 datafmt.status = CS_RETURN;
246 datafmt.locale = NULL;
247
248 /*
249 * The character string variable is filled in by the RPC so pass NULL
250 * for the data 0 for data length, and -1 for the indicator arguments.
251 */
252 if ((ret = ct_param(cmd, &datafmt, NULL, 0, -1)) != CS_SUCCEED) {
253 fprintf(stderr, "ct_param(char) failed");
254 return 1;
255 }
256
257 strcpy(datafmt.name, "@empty");
258 datafmt.namelen = CS_NULLTERM;
259 datafmt.datatype = CS_VARCHAR_TYPE;
260 datafmt.maxlength = 60;
261 datafmt.status = CS_RETURN;
262 datafmt.locale = NULL;
263
264 /*
265 * The character string variable is filled in by the RPC so pass NULL
266 * for the data 0 for data length, and -1 for the indicator arguments.
267 */
268 if ((ret = ct_param(cmd, &datafmt, NULL, 0, -1)) != CS_SUCCEED) {
269 fprintf(stderr, "ct_param(char) failed");
270 return 1;
271 }
272
273 strcpy(datafmt.name, "@binaryparam");
274 datafmt.namelen = CS_NULLTERM;
275 datafmt.datatype = CS_BINARY_TYPE;
276 datafmt.maxlength = 255;
277 datafmt.status = CS_RETURN;
278 datafmt.locale = NULL;
279
280 if ((ret = ct_param(cmd, &datafmt, (CS_VOID *) & binaryvar, CS_SIZEOF(CS_BINARY), 0)) != CS_SUCCEED) {
281 fprintf(stderr, "ct_param(binary) failed");
282 return 1;
283 }
284
285 /*
286 * Send the command to the server
287 */
288 if (ct_send(cmd) != CS_SUCCEED) {
289 fprintf(stderr, "ct_send(RPC) failed");
290 return 1;
291 }
292
293 /*
294 * Process the results of the RPC.
295 */
296 while ((ret = ct_results(cmd, &res_type)) == CS_SUCCEED) {
297 switch ((int) res_type) {
298 case CS_ROW_RESULT:
299 case CS_PARAM_RESULT:
300 case CS_STATUS_RESULT:
301 /*
302 * Print the result header based on the result type.
303 */
304 switch ((int) res_type) {
305 case CS_ROW_RESULT:
306 fprintf(stdout, "\nROW RESULTS\n");
307 break;
308
309 case CS_PARAM_RESULT:
310 fprintf(stdout, "\nPARAMETER RESULTS\n");
311 break;
312
313 case CS_STATUS_RESULT:
314 fprintf(stdout, "\nSTATUS RESULTS\n");
315 break;
316 }
317 fflush(stdout);
318
319 /*
320 * All three of these result types are fetchable.
321 * Since the result model for rpcs and rows have
322 * been unified in the New Client-Library, we
323 * will use the same routine to display them
324 */
325
326 /*
327 * Find out how many columns there are in this result set.
328 */
329 ret = ct_res_info(cmd, CS_NUMDATA, &num_cols, CS_UNUSED, NULL);
330 if (ret != CS_SUCCEED) {
331 fprintf(stderr, "ct_res_info(CS_NUMDATA) failed");
332 return 1;
333 }
334
335 /*
336 * Make sure we have at least one column
337 */
338 if (num_cols <= 0) {
339 fprintf(stderr, "ct_res_info(CS_NUMDATA) returned zero columns");
340 return 1;
341 }
342
343 /*
344 * Our program variable, called 'coldata', is an array of
345 * EX_COLUMN_DATA structures. Each array element represents
346 * one column. Each array element will re-used for each row.
347 *
348 * First, allocate memory for the data element to process.
349 */
350 coldata = (EX_COLUMN_DATA *) malloc(num_cols * sizeof(EX_COLUMN_DATA));
351 if (coldata == NULL) {
352 fprintf(stderr, "malloc coldata failed \n");
353 return 1;
354 }
355
356 outdatafmt = (CS_DATAFMT *) malloc(num_cols * sizeof(CS_DATAFMT));
357 if (outdatafmt == NULL) {
358 fprintf(stderr, "malloc outdatafmt failed \n");
359 return 1;
360 }
361
362 for (i = 0; i < num_cols; i++) {
363 ret = ct_describe(cmd, (i + 1), &outdatafmt[i]);
364 if (ret != CS_SUCCEED) {
365 fprintf(stderr, "ct_describe failed \n");
366 return 1;
367 }
368
369 outdatafmt[i].maxlength = ex_display_dlen(&outdatafmt[i]) + 1;
370 outdatafmt[i].datatype = CS_CHAR_TYPE;
371 outdatafmt[i].format = CS_FMT_NULLTERM;
372
373 coldata[i].value = (CS_CHAR *) malloc(outdatafmt[i].maxlength);
374 if (coldata[i].value == NULL) {
375 fprintf(stderr, "malloc coldata.value failed \n");
376 return 1;
377 }
378 coldata[i].value[0] = 0;
379
380 ret = ct_bind(cmd, (i + 1), &outdatafmt[i], coldata[i].value, &coldata[i].valuelen,
381 & coldata[i].indicator);
382 if (ret != CS_SUCCEED) {
383 fprintf(stderr, "ct_bind failed \n");
384 return 1;
385 }
386 }
387 if (ret != CS_SUCCEED) {
388 for (j = 0; j < i; j++) {
389 free(coldata[j].value);
390 }
391 free(coldata);
392 free(outdatafmt);
393 return 1;
394 }
395
396 ex_display_header(num_cols, outdatafmt);
397
398 while (((ret = ct_fetch(cmd, CS_UNUSED, CS_UNUSED, CS_UNUSED,
399 &rows_read)) == CS_SUCCEED) || (ret == CS_ROW_FAIL)) {
400 /*
401 * Increment our row count by the number of rows just fetched.
402 */
403 row_count = row_count + rows_read;
404
405 /*
406 * Check if we hit a recoverable error.
407 */
408 if (ret == CS_ROW_FAIL) {
409 fprintf(stdout, "Error on row %d.\n", row_count);
410 fflush(stdout);
411 }
412
413 /*
414 * We have a row. Loop through the columns displaying the
415 * column values.
416 */
417 for (i = 0; i < num_cols; i++) {
418 /*
419 * Display the column value
420 */
421 fprintf(stdout, "%s", coldata[i].value);
422 fflush(stdout);
423
424 /*
425 * If not last column, Print out spaces between this
426 * column and next one.
427 */
428 if (i != num_cols - 1) {
429 disp_len = ex_display_dlen(&outdatafmt[i]);
430 disp_len -= coldata[i].valuelen - 1;
431 for (j = 0; j < disp_len; j++) {
432 fputc(' ', stdout);
433 }
434 }
435 }
436 fprintf(stdout, "\n");
437 fflush(stdout);
438 }
439
440 /*
441 * Free allocated space.
442 */
443 for (i = 0; i < num_cols; i++) {
444 free(coldata[i].value);
445 }
446 free(coldata);
447 free(outdatafmt);
448
449 /*
450 * We're done processing rows. Let's check the final return
451 * value of ct_fetch().
452 */
453 switch ((int) ret) {
454 case CS_END_DATA:
455 /*
456 * Everything went fine.
457 */
458 fprintf(stdout, "All done processing rows.\n");
459 fflush(stdout);
460 break;
461
462 case CS_FAIL:
463 /*
464 * Something terrible happened.
465 */
466 fprintf(stderr, "ct_fetch returned CS_FAIL\n");
467 return 1;
468 break;
469
470 default:
471 /*
472 * We got an unexpected return value.
473 */
474 fprintf(stderr, "ct_fetch returned %d\n", ret);
475 return 1;
476 break;
477
478 }
479 break;
480
481 case CS_MSG_RESULT:
482 ret = ct_res_info(cmd, CS_MSGTYPE, (CS_VOID *) & msg_id, CS_UNUSED, NULL);
483 if (ret != CS_SUCCEED) {
484 fprintf(stderr, "ct_res_info(msg_id) failed");
485 return 1;
486 }
487 fprintf(stdout, "ct_result returned CS_MSG_RESULT where msg id = %d.\n", msg_id);
488 fflush(stdout);
489 break;
490
491 case CS_CMD_SUCCEED:
492 /*
493 * This means no rows were returned.
494 */
495 break;
496
497 case CS_CMD_DONE:
498 /*
499 * Done with result set.
500 */
501 break;
502
503 case CS_CMD_FAIL:
504 /*
505 * The server encountered an error while
506 * processing our command.
507 */
508 fprintf(stderr, "ct_results returned CS_CMD_FAIL.");
509 break;
510
511 default:
512 /*
513 * We got something unexpected.
514 */
515 fprintf(stderr, "ct_results returned unexpected result type.");
516 return CS_FAIL;
517 }
518 }
519
520 /*
521 * We're done processing results. Let's check the
522 * return value of ct_results() to see if everything
523 * went ok.
524 */
525 switch ((int) ret) {
526 case CS_END_RESULTS:
527 /*
528 * Everything went fine.
529 */
530 break;
531
532 case CS_FAIL:
533 /*
534 * Something failed happened.
535 */
536 fprintf(stderr, "ct_results failed.");
537 break;
538
539 default:
540 /*
541 * We got an unexpected return value.
542 */
543 fprintf(stderr, "ct_results returned unexpected result type.");
544 break;
545 }
546
547 run_command(cmd, "DROP PROCEDURE sample_rpc");
548
549 if (verbose) {
550 fprintf(stdout, "Trying logout\n");
551 }
552 ret = try_ctlogout(ctx, conn, cmd, verbose);
553 if (ret != CS_SUCCEED) {
554 fprintf(stderr, "Logout failed\n");
555 return 1;
556 }
557
558 return 0;
559 }
560
561 static CS_INT
ex_display_dlen(CS_DATAFMT * column)562 ex_display_dlen(CS_DATAFMT * column)
563 {
564 CS_INT len;
565
566 switch ((int) column->datatype) {
567 case CS_CHAR_TYPE:
568 case CS_VARCHAR_TYPE:
569 case CS_TEXT_TYPE:
570 case CS_IMAGE_TYPE:
571 len = MIN(column->maxlength, 1024);
572 break;
573
574 case CS_BINARY_TYPE:
575 case CS_VARBINARY_TYPE:
576 len = MIN((2 * column->maxlength) + 2, 1024);
577 break;
578
579 case CS_BIT_TYPE:
580 case CS_TINYINT_TYPE:
581 len = 3;
582 break;
583
584 case CS_SMALLINT_TYPE:
585 len = 6;
586 break;
587
588 case CS_INT_TYPE:
589 len = 11;
590 break;
591
592 case CS_REAL_TYPE:
593 case CS_FLOAT_TYPE:
594 len = 20;
595 break;
596
597 case CS_MONEY_TYPE:
598 case CS_MONEY4_TYPE:
599 len = 24;
600 break;
601
602 case CS_DATETIME_TYPE:
603 case CS_DATETIME4_TYPE:
604 len = 30;
605 break;
606
607 case CS_NUMERIC_TYPE:
608 case CS_DECIMAL_TYPE:
609 len = (CS_MAX_PREC + 2);
610 break;
611
612 default:
613 len = 12;
614 break;
615 }
616
617 return MAX((CS_INT) (strlen(column->name) + 1), len);
618 }
619
620 static CS_RETCODE
ex_display_header(CS_INT numcols,CS_DATAFMT columns[])621 ex_display_header(CS_INT numcols, CS_DATAFMT columns[])
622 {
623 CS_INT i;
624 CS_INT l;
625 CS_INT j;
626 CS_INT disp_len;
627
628 fputc('\n', stdout);
629 for (i = 0; i < numcols; i++) {
630 disp_len = ex_display_dlen(&columns[i]);
631 fprintf(stdout, "%s", columns[i].name);
632 fflush(stdout);
633 l = disp_len - (CS_INT)strlen(columns[i].name);
634 for (j = 0; j < l; j++) {
635 fputc(' ', stdout);
636 fflush(stdout);
637 }
638 }
639 fputc('\n', stdout);
640 fflush(stdout);
641 for (i = 0; i < numcols; i++) {
642 disp_len = ex_display_dlen(&columns[i]);
643 l = disp_len - 1;
644 for (j = 0; j < l; j++) {
645 fputc('-', stdout);
646 }
647 fputc(' ', stdout);
648 }
649 fputc('\n', stdout);
650
651 return CS_SUCCEED;
652 }
653
654 CS_RETCODE
ex_clientmsg_cb(CS_CONTEXT * context,CS_CONNECTION * connection,CS_CLIENTMSG * errmsg)655 ex_clientmsg_cb(CS_CONTEXT * context, CS_CONNECTION * connection, CS_CLIENTMSG * errmsg)
656 {
657 fprintf(stdout, "\nOpen Client Message:\n");
658 fprintf(stdout, "Message number: LAYER = (%ld) ORIGIN = (%ld) ", (long)CS_LAYER(errmsg->msgnumber), (long)CS_ORIGIN(errmsg->msgnumber));
659 fprintf(stdout, "SEVERITY = (%ld) NUMBER = (%ld)\n", (long)CS_SEVERITY(errmsg->msgnumber), (long)CS_NUMBER(errmsg->msgnumber));
660 fprintf(stdout, "Message String: %s\n", errmsg->msgstring);
661 if (errmsg->osstringlen > 0) {
662 fprintf(stdout, "Operating System Error: %s\n", errmsg->osstring);
663 }
664 fflush(stdout);
665
666 return CS_SUCCEED;
667 }
668
669 CS_RETCODE
ex_servermsg_cb(CS_CONTEXT * context,CS_CONNECTION * connection,CS_SERVERMSG * srvmsg)670 ex_servermsg_cb(CS_CONTEXT * context, CS_CONNECTION * connection, CS_SERVERMSG * srvmsg)
671 {
672 fprintf(stdout, "\nServer message:\n");
673 fprintf(stdout, "Message number: %ld, Severity %ld, ", (long) srvmsg->msgnumber, (long) srvmsg->severity);
674 fprintf(stdout, "State %ld, Line %ld\n", (long) srvmsg->state, (long) srvmsg->line);
675
676 if (srvmsg->svrnlen > 0) {
677 fprintf(stdout, "Server '%s'\n", srvmsg->svrname);
678 }
679
680 if (srvmsg->proclen > 0) {
681 fprintf(stdout, " Procedure '%s'\n", srvmsg->proc);
682 }
683
684 fprintf(stdout, "Message String: %s\n", srvmsg->text);
685 fflush(stdout);
686
687 return CS_SUCCEED;
688 }
689