In addition to stored procedures, which use SQL control and condition-handling statements, input and output parameters, and local variables to provide applications with a server-based, precompiled procedural interface, Teradata Database supports external stored procedures.

You can write XSPs (eXternal Stored Procedures) in the C or C++ programming language, install them on the database, and then use the SQL CALL statement to call them like other stored procedures.

This article will show how to create a simple CXSP (C/C++ XSP), execute it in protected mode, and debug it locally or remotely using Eclipse CDT. In protected execution mode, the XSP runs as a separate process under 'tdatuser', a local operating system user that the Teradata Database installation process creates. The XSP can access the system resources for which tdatuser has privileges.

This CXSP example shows the C code for an external stored procedure that takes an INOUT string argument, strips off the first four characters, and returns the result.

Prerequisite for this Article

If you have not worked through the guide How to Set up a UDF / XSP Debugging Environment, please do it now before you continue.

Article Outline

In this article, we will do the following things to show how to create and debug a simple CXSP using Eclipse CDT.

  1. Create C Project in Eclipse
  2. Create CXSP Source File in C Project
  3. Install DDL in Database
  4. Run CXSP in Database
  5. Debug CXSP in Eclipse

1. Create C Project in Eclipse

1.1. C/C++ Perspective

Select the pull-down menu Window -> Open Perspective -> Other ...

An Open Perspective window is displayed. Select C/C++, and click the OK button.

1.2. Create C Project

Go to the Project Explorer in Eclipse and right click New -> C Project.

C Project window comes up.

Enter Project name cxsp_simple, select Project type Shared Library, and select Toolchains Linux GCC. Then click the Finish button.

A new C project cxsp_simple will be created.

1.3. Change Build Settings

1.3.1. Show Build Settings

Right click the project, and select Properties.

The next window shows the default Properties for cxsp_simple.

Click C/C++ Build -> Settings. The right of window shows default C/C++ build settings.

You can move the scrollbars at the right or bottom to see the part that is out of view.

1.3.2. Change Compiler Settings

Select GCC C Compiler -> Includes. Click the + button. An Add directory path window shows up. Enter Directory /usr/tdbms/etc.

The /usr/tdbms/etc path will be added into the right list.

Select Miscellaneous in GCC C Compiler. Check the flag Position Independent Code (-fPIC).

1.3.3. Change Linker Settings

Select GCC C Linker -> Libraries. Click the + button as below to add Library search path.

An Add directory path window shows up. Enter /usr/tdbms/lib in the text box and click OK.

The /usr/tdbms/lib path will be shown in the lower list.

Click OK to save the settings.

2. Create CXSP Source File in C Project

2.1. Create Source Folder

Right click the project, and select New -> Source Folder.

A New Source Folder window comes up. Please enter the source folder name src and click the Finish button.

The src source folder will be added to the project.

2.2. Create New Source File

Right click the src folder, select New -> Source File.

New Source File window is displayed. Enter Source file getregion.c. Click the Finish button.

A new source file getregion.c will be created. 

2.3. Edit The Source File

Edit the source file as shown below.

/*
 * This example shows the C code for an external stored procedure that takes an INOUT string
 * argument, strips off the first four characters, and returns the result.
 *
 *  Created on: Oct 10, 2012
 *      Author: root
 */
#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>
void getregion(VARCHAR_LATIN *region, char sqlstate[6]) {
	char tmp_string[64];
	if (strlen((const char *) region) > 4) {
		/* Strip off the first four characters */
		strcpy(tmp_string, (char *) region);
		strcpy((char *) region, &tmp_string[4]);
	}
}

3. Install DDL in Database

3.1. Logon Bteq

Open a console window, and execute the bteq logon command.

There are 2 cases, depending on whether the DBS is running on the local machine or not.

A. Local DBS

Logon to the DBS using the IP address 127.0.0.1 for the local host.

TDExpress14.0_Sles10:~ #bteq .logon 127.0.0.1/debugger,debugger

B. Remote DBS

Logon to the DBS  running in a remote machine. Its IP address is 192.168.85.128 in this example.

TDExpress14.0_Sles10:~ # bteq .logon 192.168.85.128/debugger,debugger

3.2 Create New CXSP

If this is to create a new CXSP GetRegionXSP, please execute the following command at the bteq prompt.

 BTEQ -- Enter your SQL request or BTEQ command:

CREATE PROCEDURE GetRegionXSP
(INOUT region VARCHAR(64))
LANGUAGE C
NO SQL
PARAMETER STYLE TD_GENERAL
EXTERNAL NAME 'D!CS!getregion!/usr/eclipse/workspace/cxsp_simple/src/getregion.c!F!getregion';

An output message will be displayed when the execution finishes.

 *** Procedure has been created.

3.3 Update Existing CXSP

If the GetRegionXSP XSP already exists, please execute the following command at the bteq prompt to replace the old GetRegionXSP.

 BTEQ -- Enter your SQL request or BTEQ command:

REPLACE PROCEDURE GetRegionXSP
(INOUT region VARCHAR(64))
LANGUAGE C
NO SQL
PARAMETER STYLE TD_GENERAL
EXTERNAL NAME 'D!CS!getregion!/usr/eclipse/workspace/cxsp_simple/src/getregion.c!F!getregion';

An output message will be displayed when the execution finishes.

 *** Procedure has been replaced.

4. Run CXSP

If there is no bteq prompt, please execute [3.1. Logon Bteq] section.

Execute the following query to run the getregion CXSP.

 BTEQ -- Enter your SQL request or BTEQ command:

CALL GetRegionXSP('New York');

The result will be displayed when the execution finishes.

 *** Procedure has been executed.

5. Debug CXSP in Eclipse

There are 2 debugging types for CXSP, depending on DBS location.

  • Local CXSP debugging - Eclipse and DBS are running on the same machine.
  • Remote CXSP debugging - Eclipse and DBS are running on separate machines.

There is no difference between debugging a local CXSP and debugging a remote CXSP, except for the setup of the debug session.

5.1. Setup Source Lookup Path

Select the pull-down menu Window -> Preferences.

The Preferences window comes up.

Select C/C++ -> Debug -> Source Lookup Path. The Source Lookup Path settings will be shown at the right.

Please select the following values and click the Remove button to remove them one by one if they are in the Default Source Lookup Path list.

  • Absolute File Path
  • Program Relative File Path
  • Project

Click the Add button to add Project-Path Relative to Source Folders into the list.

Click the OK button to save the setting.

5.2. Switch to Debug Perspective

Select the pull-down menu Window -> Open Perspective -> Debug.

The Eclipse will switch to Debug perspective.

5.3. Set Breakpoint

For easy reference, we will show the line numbers in the text editor. (To do this, right click the left margin of the text editor, and select Show Line Number.)

Double click the line number where you want to set breakpoint. A blue point shows the breakpoint has been set at line 15 of the source code. Also a line has been added in the Breakpoints view.

To remove the breakpoint, just double click the blue point in the left margin.

5.4. Debug Configuration

Select the pull-down menu Run -> Debug Configurations....

Debug Configurations setting window is displayed. Double click C/C++ Attach to Application in the left list. A debug configuration cxsp_simple Debug is created automatically.

A. Local DBS

Click the Debug button, the Eclipse debugger will try to show all the processes in the VM. A Select Process window comes up.

Note: If we have not executed any CXSP since database startup, the udfsectsk process is not started. So please make sure you have executed a CXSP once before debugging. If there are more than one udfsectsk processes in the process list, we will need to  connect all of them because we cannot easily know which process will execute the next GetRegionXSP call.

Input our process name udfsectsk. The list will filter the items with input characters. There are 3 udfsectsk processes in the list. One of them is the udfsectsk process which executed GetRegionXSP earlier. The others are processes which executed other XSPs or UDFs.

Select the first udfsectsk process, and click the OK button to attach to udfsectsk - 15243 process. The process will be paused in debug mode. Click the Resume button or press F8 to continue.

Click the triangle button in the right of Debug button, and select cxsp_simple Debug which we just created above.

The Select Process window comes up. Input our process name like above. Select the second udfsectsk process and click the OK button to connect the udfsectsk - 15245 process. The second udfsectsk process will be paused in debug mode. Click the Resume button or press F8 to continue.

Connect the rest of the udfsectsk processes via the above procedure.

B. Remote DBS

Click the Debugger tab, and change the Debugger from gdb to gdbserver.

Click the Connection tab in Debugger Options and enter Host name or IP address.

To open a remote debug connection port, please submit the following command in a console window in client.

TDExpress14.0_Sles10:~ # ssh root@192.168.85.128 gdbserver --multi :10000

You will be prompted for the password of the root user on the remote DBS in order to connect.

In our case, enter DBS IP address 192.168.85.128 and the port 10000 which we just created in the remote DBS.

Click the Debug button to connect to remote DBS debug port. 

Click the Connect to process button, a Select Process window comes up. Enter process name udfsectsk and click the OK button.

A window comes up. Please find your compiled binary file and click the OK button. In our case, the Location should be /usr/eclipse/workspace/cxsp_simple/Debug/libcxsp_simple.so.

The remote connection will be established, and the remote udfsectsk process attached. That process will be paused in debug mode.

Click the Resume button or press F8 to continue.

Note: If there are more than one udfsectsk processes in the process list, please make sure you have connected all of them because we can not easily know which processes will execute our CXSP.  For each process, you need to specify a a different remote debug connection port number when running the ssh gdbserver command, and use this port number when establishing the connection.

5.5. Breakpoint Hit

If there is no bteq prompt, please execute [3.1. Logon Bteq] section.

Enter the following query at bteq prompt:

 BTEQ -- Enter your SQL request or BTEQ command:

CALL GetRegionXSP('New York');

The breakpoint will be hit in corresponding udfsectsk process.

We hit the breakpoint at line 15. The backtrace shows the thread was paused at getregion() at getregion.c: 15 0x2aaaac255d47.

Now we can see the variables in Variables view and check other information in corresponding view. Here we can see the input region value is New York.

5.6. Debugging

There are several debug buttons used for debugging.

  1. Resume (short-cut key: F8)
  2. Suspend
  3. Teminate
  4. Disconnect
  5. Step Into (short-cut key: F5)
  6. Step Over (short-cut key: F6)
  7. Step Return (short-cut key: F7)

Press Step Over button or press F6 twice. Thread execution now pauses at line 18. The value for region is changed to York.

Click the Resume button or press F8. The CXSP execution will be resumed.

5.7. Stop Debugging

To stop the debugging, select the process and click the Disconnect button.

The debugging session will be teminated, and the CXSP execution will be resumed.

Note: If you have connected other udfsectsk processes, please disconnect all of them. In that case, the other UDF or XSP executions will be interrupted.

For more tips and tricks, please refer to the following link.

Eclipse (Indigo) C/C++ Development User Guide