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