FNC_GeomGetResultWKTClob Function | C Library Functions | Teradata Vantage - 17.10 - FNC_GeomGetResultWKTClob - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)

Returns the LOB locator to a CLOB for an ST_Geometry that is defined to be the return value of a UDF/UDM or an INOUT or OUT parameter for an external stored procedure.

Syntax

void
FNC_GeomGetResultWKTClob(GEO_HANDLE            geoHandle,
                         LOB_RESULT_LOCATOR*   geoClob)

Syntax Elements

geoHandle
A handle to an ST_Geometry type that is defined to be a return value for a UDF/UDM or an INOUT/OUT parameter to an external stored procedure.
LOB_RESULT_LOCATOR geoClob
Pointer to the LOB locator that is used to set the ST_Geometry type return value.

Usage Notes

The FNC_GeomGetResultWKTClob function is used get a result LOB locator that is used to set the ST_Geometry return value. The ST_Geometry Handle geoHandle is passed as input, and a LOB locator geoClob is returned from the function. The user can then use LOB FNC routines to append to the CLOB value using the geoClob locator.

Use the Latin character set to append values to the CLOB. The Well-Known Text value should be written to the CLOB.

The FNC_GeomGetResultWKTClob function should be called only for LOB-based ST_Geometry values. If used with inline ST_Geometry values, a 7579 error is generated: FNC_GeomGetResultWKTClob can only be called on LOB-based ST_Geometry type.

Example: Using FNC_GeomGetResultWKTClob and FNC_GeomSetWKTClob to retrieve and set the value of a return ST_Geometry

The following function takes an ST_Geometry parameter and returns an ST_Geometry that has the same value. It first retrieves the Well-Known Text format from the input geometry. It then retrieves the LOB result locator from the return ST_Geometry, appends the WKT to the CLOB, and then sets the CLOB into the return ST_Geometry.

REPLACE FUNCTION geo_wkt(p1 ST_Geometry)
RETURNS ST_Geometry
SPECIFIC geo_wkt
NO SQL
PARAMETER STYLE SQL
DETERMINISTIC
LANGUAGE C
EXTERNAL NAME 'CS!geo_wkt!geo_wkt.c!F!geo_wkt';

#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>

void geo_wkt( GEO_HANDLE  *geo_in,
              GEO_HANDLE  *geo_out,
              int         *indicator_geo_in,
              int         *indicator_geo_out,
              char        sqlstate[6],
              SQL_TEXT    extname[129],
              SQL_TEXT    specific_name[129],
              SQL_TEXT    error_message[257])
{
    LOB_RESULT_LOCATOR geoClob_out;
    LOB_LOCATOR geoClob_in;
    BYTE buffer[64000];
    LOB_CONTEXT_ID id;
    FNC_LobLength_t actlen;
    int trunc_err = 0;
    FNC_LobLength_t clob_length;
    int srid = -1;
    int max_length, max_length2, numLobs, numLobs2;

    /* If input is null set return value to null. */
    if (*indicator_geo_in == -1 )
    {
        *indicator_geo_out = -1;
        return;
    }
    /* Get the Input Geometry Info */
     FNC_GetGeometryInfo(*geo_in,&max_length, &numLobs);

    /* Get the Output Geometry Info */
     FNC_GetGeometryInfo(*geo_out,&max_length2, &numLobs2);

    /* 
     * Copy geo_in ST_Geometry value to geo_out ST_Geometry value.
     *
     */
 
    if(numLobs ==1 && numLobs2==1)
    {
     /* Get the LOB_RESULT_LOCATOR of geo_out. */
      FNC_GeomGetResultWKTClob(*geo_out, &geoClob_out); 

     /* Get the LOB_LOCATOR and srid of the input ST_Geometry value. */
      FNC_GeomGetWKTClob(*geo_in, &geoClob_in, &srid); 

     FNC_LobOpen(geoClob_in, &id, 0, 0);

     clob_length = FNC_GetLobLength(geoClob_in);

     while (FNC_LobRead(id, buffer, clob_length, &actlen) == 0 && !trunc_err)
         trunc_err = FNC_LobAppend(geoClob_out, buffer, actlen, &actlen);

     FNC_LobClose(id);

     /* Set the return ST_Geometry value */
      FNC_GeomSetWKTClob(*geo_out, geoClob_out, srid);  
    }
}