SAP Connectivity
Page tree
Skip to end of metadata
Go to start of metadata

Purpose

   The CTOS sample application found in the Open Server installation under the SYBASE root directory, under OCS-15_0\sample\srvlib is useful to the beginning programmer learning to use Open Server. It's a gateway connecting a client to an SAP Sybase ASE database server. The sample application contains source and functions to cover many facets of programming open server including sending language statements to the ASE and handling the retrieval of their result sets through Open Server. In some cases we want Open Server to parse the command and under certain commands we desire the Open Server to provide a result set instead of passing the command to the ASE. This article will explain how to do so with the ctos sample, modified slightly to demonstrate how to do such a thing.

Overview

This article will walk through the sample code, highlighting the sections relevant to this sort of processing.We start with sending the sample command "select 'yes'", and observe how the CTOS sample will parse and recognize the command.  It will call certain functions specifically for this command, and the open server code will provide a data format for the result set which is the character string "yes", bind the data to buffers internal to open server to handle the writing of the data to the network stream using TDS (Tabular Data Stream) which is done internally and then how the command is sent back to the client. Source code of the entire project is provided to the interested reader.

Receiving the command

I modified ctos Open Server sample to return the data, in character form, “yes” when CTOS receives the command “select ‘yes’”. I modified the code to “bypass” the send to an ASE database server, which normally services such requests. The CTOS sample code enables such requests. Here are the items of importance to make this happen.

 It all starts in the events.c file, with the call to ctos_language_handler(). This calls ctos_language_passthru(). This brings us to language.c. In here, the SQL command is processed. Since we are keying in on a specific SQL, “select ‘yes’”, the code will branch to different result set handling functions. In simplistic form, this is the code.  Details are in the actual source code:

strcpy(langyes, lang);
if (strncmp langyes, "select 'yes'", 12) == 0)
{
	// In code I am checking for errors
	ctos_results_yes(srvproc, CS_LANG_CMD, NIL(CS_DATAFMT **), rows);
		SRV_FREE(lang);
}
// send other commands to ASE
else
{
	ct_cmd_alloc(conn, &cmd);
	ct_command(cmd, CS_LANG_CMD, lang, len, CS_UNUSED);
	ctos_ctsend(srvproc, cmd);
	ctos_results(srvproc, cmd, CS_LANG_CMD, NIL(CS_DATAFMT **), rows);
	SRV_FREE(lang);
	ct_cmd_drop(cmd);
}

Don’t worry about the else section, that is just for sending commands to ASE - a worthwhile study in sending commands to TDS servers.

Formatting the Data

Now we’ll focus on the ctos_results_yes() function in results.c. This is where we’ll create data format structure, bind the information to the server proc, create our data in the data buffer and send it back to the client connected to CTOS that initiated the SQL command.

Notice there is no CS_COMMAND structure involved since we’re not sending it anywhere. The function we’re discussing calls two other functions, ctos_procrowfmt_yes() and ctos_procrows_yes(). The first handles the data formatting and the second handles the binding and sending the data to the client: 

ctos_procrowfmt_yes(SRV_PROC *srvproc, CS_DATAFMT **fmt)
{
	CS_DATAFMT	 *fmt;
	ctos_procrowfmt_yes(srvproc, &fmt);
	ctos_procrows_yes(srvproc, fmt, rows, CS_UNUSED);


The ctos_procrowfmt_yes() function provides the formatting and description of the data. The number of columns used is hard-coded to one. I also added a column name “c1” so when you receive the result you’ll know the command wasn’t sent to ASE but instead was generated internally by CTOS. There is some memory allocation to deal with. I also left in a for loop since I pass the CS_DATAFMT **fmt as an array of CS_DATAFMT’s. This is how the CTOS code handles the situation. Here’s the condensed version of the formatting code:

srv_bzero((CS_VOID *)fmtp, size);
fmtp[i].datatype = CS_CHAR_TYPE;
fmtp[i].maxlength = 255;
strcpy(fmtp[i].name, "c1");
fmtp[i].namelen = CS_NULLTERM;
srv_descfmt(srvproc, CS_SET, SRV_ROWDATA, (i+1), &(fmtp[i]));
*fmt = fmtp;

 

Binding the Data

Now the format is complete and it’s time to bind, copy data to the data buffer and send it to the client. This occurs in ctos_procrows_yes(). Data passes as bytes so I found it easier set each element of the data buffer to the byte value of the character.  In this case the word “yes” = 0x796573. The code will explain this much better. Only code relevant to sending this data is displayed here. Look in the ctos sample project provided for all the details:

ctos_procrows_yes(SRV_PROC *srvproc, CS_DATAFMT *fmt,
	      CS_INT *rows, CS_INT fetchcount)
{
	CS_SMALLINT	*ind;		/* Bind indication.		*/
	CS_INT	*len;		/* Length of column data.	*/
	CS_BYTE	*data;		/* Column data.		*/

	len = (CS_INT *)malloc(sizeof(CS_INT));
	memset(len, 0, 1);
	*len = 30;


	*len = 3;  // length of the data in the buffer we need for 'yes'
	srv_bind(srvproc, CS_SET, SRV_ROWDATA, i+1, &(fmt[i]), (CS_BYTE *)data, &(len[i]), &(ind[i]));

	data[0] = 0x79; data[1]=0x65; data[2]=0x73; // This is data for 'yes'
	srv_xferdata(srvproc, CS_SET, SRV_ROWDATA);

	// cleanup and return
}

Sending the Data and Testing CTOS

This returns to ctos_language_passthru() to send the final done to the client and then the whole process can repeat when language command is sent again to the Open Server. 

if ((rows > 0) || (!rows && (srvproc_ctx->os_sendcount == CS_TRUE)))
{
status |= SRV_DONE_COUNT;
}

/*
** Send a final done to the client to acknowledge the request
*/
srv_senddone(srvproc, status, srvproc_ctx->os_transtate, rows);

I start my CTOS server with this command line:

ctos ctos1570 -s pvw71572 -srvhdr -srvdata -srvmsgq -srvtoken -srvevent -srvdefqueue -srvattn -netparam -netreq -netdriver -netmem -netdata -netwake

When running the command the client output, as seen from ISQL, looks like this:

C:\...>isql -Usa -Psecret -Sctos1570
1> select 'yes'
2> go
 c1
----------------
 yes

(1 row affected)

My CTOS prints out the instrumentation I added - very simple print statements to let me know what is going on:

CTOS Server 'ctos1570' is running...
events: calling ctos_language_passthru.
YOU SENT COMMAND: select 'yes'.
language - received select 'yes' - don't send to ASE.
ctos_language_passthru: Calling ctos_results_yes.
procrowfmt_yes: Not calling ct_describe.
ctos_procrowfmt_yes: srv_descfmt.
Leaving ctos_procrowfmt_yes.
ctos_results_yes: nocols: 1.
ctos_procros_yes: len = 30.
ctos_procrows_yes: ind: 0. [after srv_bind]
ctos_procrows_yes: len: 3.
ctos_procrows_yes: after srv_bind: data: . [after srv_bind]
ctos_procrows_yes: data before srv_xferdata: yes.

Lastly, I like to look at the actual TDS between the client and CTOS. This open server log is useful for this purpose and other diagnostic reasons.

Jul 29 16:17:26 2013: Sybase Server-Library/15.7/P-EBF21007-21006 SP100/X64/BUILD1570-026/OPT/Sun Mar 24 08:34:18 2013Jul 29 16:17:26 2013: Sybase Client-Library/15.7/P-EBF21006 SP100/X64/BUILD1570-026/OPT/Sun Mar 24 08:32:57 2013

spid 8 - read: login, not eom, len=512, chan=0, pack=0, win=0, client
0x424a 0x434e 0x3030 0x3532 0x3631 0x3237 0x4100 0x0000 | BJCN00526127A
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0d73 |                s
0x6100 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 | a
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0002 0x7365 |               se
0x6372 0x6574 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 | cret
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0634 0x3234 |              424
0x3800 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 | 8
0x0000 0x0000 0x0000 0x0000 0x0000 0x0004 0x0301 0x060a |
0x0901 0x0100 0x0000 0x0000 0x0000 0x0000 0x6973 0x716c |             isql
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0463 0x746f 0x7331 |            ctos1
0x3537 0x3000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 | 570
0x0000 0x0000 0x0000 0x0000 0x0008 0x0006 0x7365 0x6372 |             secr
0x6574 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 | et
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0008 0x0500 0x0000 0x4354 |               CT
0x2d4c 0x6962 0x7261 0x7279 0x0a0f 0x0700 0x0a00 0x0d11 | -Library
0x0073 0x5f65 0x6e67 0x6c69 0x7368 0x0000 0x0000 0x0000 |  s_english
0x0000 0x0000 0x0000 0x0000 0x.... 0x.... 0x.... 0x.... |
===========================================================================
spid 8 - read: login, eom, len=107, chan=0, pack=0, win=0
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0800 0x0000 |
0x0000 0x0000 0x0069 0x736f 0x5f31 0x0000 0x0000 0x0000 |      iso_1
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0005 0x0035 0x3132 0x0000 0x0003 0x0000 0x0000 |      512
0xe220 0x0001 0x0e01 0x3ce2 0xe9b5 0x87e8 0x3b6d 0x7fff |       <     ;m
0xffff 0xfe02 0x0e00 0x0000 0x0000 0x1860 0x0000 0x0268 |            `   h
0x0000 0x00.. 0x.... 0x.... 0x.... 0x.... 0x.... 0x.... |
===========================================================================
Jul 29 16:17:38 2013: spid 8 - Calling the SRV_CONNECT event handler.
Jul 29 16:17:38 2013: Server Message: pvw71572 - 5704/10/1: Changed client character set setting to 'iso_1'.

Jul 29 16:17:38 2013: Server Message: pvw71572 - 5701/10/2: Changed database context to 'master'.

Jul 29 16:17:38 2013: Server Message: pvw71572 - 5703/10/1: Changed language setting to 'us_english'.

spid 8 - write: normal, eom, len=310, chan=0, pack=1, win=0
0xe54e 0x0048 0x1600 0x0001 0x0a05 0x5a5a 0x5a5a 0x5a00 |  N H      ZZZZZ
0x0000 0x3100 0x4368 0x616e 0x6765 0x6420 0x636c 0x6965 |   1 Changed clie
0x6e74 0x2063 0x6861 0x7261 0x6374 0x6572 0x2073 0x6574 | nt character set
0x2073 0x6574 0x7469 0x6e67 0x2074 0x6f20 0x2769 0x736f |  setting to 'iso
0x5f31 0x272e 0x0a08 0x7076 0x7737 0x3135 0x3732 0x0000 | _1'.  pvw71572
0x00e5 0x4300 0x4516 0x0000 0x020a 0x055a 0x5a5a 0x5a5a |   C E      ZZZZZ
0x0000 0x0026 0x0043 0x6861 0x6e67 0x6564 0x2064 0x6174 |    & Changed dat
0x6162 0x6173 0x6520 0x636f 0x6e74 0x6578 0x7420 0x746f | abase context to
0x2027 0x6d61 0x7374 0x6572 0x272e 0x0a08 0x7076 0x7737 |  'master'.  pvw7
0x3135 0x3732 0x0000 0x00e5 0x4700 0x4716 0x0000 0x010a | 1572    G G
0x055a 0x5a5a 0x5a5a 0x0000 0x002a 0x0043 0x6861 0x6e67 |  ZZZZZ   * Chang
0x6564 0x206c 0x616e 0x6775 0x6167 0x6520 0x7365 0x7474 | ed language sett
0x696e 0x6720 0x746f 0x2027 0x7573 0x5f65 0x6e67 0x6c69 | ing to 'us_engli
0x7368 0x272e 0x0a08 0x7076 0x7737 0x3135 0x3732 0x0000 | sh'.  pvw71572
0x00e3 0x0700 0x0404 0x3831 0x3932 0x00ad 0x1400 0x0505 |       8192
0x0000 0x000a 0x4f70 0x656e 0x5365 0x7276 0x6572 0x0f07 |     OpenServer
0x000a 0xe220 0x0001 0x0e01 0x20e0 0xe9b5 0x85e8 0x3a61 |               :a
0x7fff 0xffff 0xe602 0x0e00 0x0000 0x0003 0xd9fa 0xa781 |
0xf260 0x0000 0x00fd 0x0000 0x0000 0x0000 0x0000 0x.... |  `
===========================================================================
Jul 29 16:17:38 2013: spid 8 - Returned from SRV_CONNECT event handler.
spid 8 - read: normal, eom, len=27, chan=0, pack=0, win=0
0x210e 0x0000 0x0000 0x7365 0x6c65 0x6374 0x2027 0x7965 | !     select 'ye
0x7327 0x0a.. 0x.... 0x.... 0x.... 0x.... 0x.... 0x.... | s'
===========================================================================
Jul 29 16:17:40 2013: spid 8 - Calling the SRV_LANGUAGE event handler.
spid 8 - write: normal, eom, len=38, chan=0, pack=1, win=0
0xee0d 0x0001 0x0002 0x6331 0x0000 0x0000 0x002f 0xff00 |       c1     /
0xd103 0x7965 0x73fd 0x1000 0x0000 0x0100 0x0000 0x.... |   yes
===========================================================================
Jul 29 16:17:40 2013: spid 8 - Returned from SRV_LANGUAGE event handler.



 Conclusion

You can see there is a TDS_DONE with row count and it’s the final DONE for the call. The data is returned properly and the CTOS server is ready for more commands to process. It’s a simple way to enable Open Server to return specific data. The steps taken are (not including memory allocation of buffers and variables, error handling and cleanup):

Formatting using the CS_DATAFMT structure.

  • Set column name if needed
  • Set data type so it is handled in TDS
  • Set maximum data length
  • Set other attributes important to your application

Bind the data buffer and format to the server process.

  • Use the above format
  • Set data buffer to the data you want to send back

Send the data to the client.

  • In CTOS the srv_senddone is left to the language handler event