How to get UDT via EXEC SQL GET DESCRIPTOR ?
Hi all.
I have a question "How to get UDT via EXEC SQL GET DESCRIPTOR ?".
I modified dyn_sql.ec(it exists in $INFORMIXDIR/demo/esqlc) as bellow.
When execute dyn_sql, I get the -1260.
Any help ?
Here is background.
create table as bellow.
create table z_type(
id serial,
num integer,
str char(255),
vstr varchar(255),
lstr lvarchar,
hstr html
) lock mode row;
Insert data.
Execute dyn_sql
% dyn_sql db1
DYN_SQL Sample ESQL Program running.
Connected to db1
Enter a SELECT statement for the db1 database
(e.g. select * from customer;)
OR a ';' to terminate program:
>>select id,lstr,hstr from z_type;
Preparing statement (select id,lstr,hstr from z_tmx_type;)...
Declaring cursor 'sel_curs' for SELECT...
Allocating system-descriptor area...
Describing prepared SELECT...
Getting number of described values from system-descriptor area...
Opening cursor 'sel_curs'...
id : 1
lstr :
hstr :
id : 2
lstr : test_lvarchar
********Error encountered in GET DESCRIPTOR: LENGTH, NAME, EXTYPEID,
EXTYPELENGTH, EXTYPENAME fields
********
----------------------------------------------------------
SQLSTATE: IX000
SQLCODE: -1260
EXCEPTIONS: Number=1 More? N
- - - - - - - - - - - - - - - - - - - -
EXCEPTION 1: SQLSTATE=IX000
MESSAGE TEXT: It is not possible to convert between the specified types.
CLASS ORIGIN: IX
SUBCLASS ORIGIN: IX000
----------------------------------------------------------
Any help ?
Here is modified dyn_sql.ec .
/*
This program prompts the user to enter a SELECT statement
for the stores_demo database. It processes the statement using dynamic
sql
and system descriptor areas and displays the rows returned by the
database server.
*/
#include <stdio.h>
#include <stdlib.h>
#include <ctype.h>
EXEC SQL include sqltypes;
EXEC SQL include locator;
EXEC SQL include datetime;
EXEC SQL include decimal;
#define WARNNOTIFY 1
#define NOWARNNOTIFY 0
#define LCASE(c) (isupper(c) ? tolower(c) : (c))
#define BUFFSZ 256
extern char statement[80];
EXEC SQL BEGIN DECLARE SECTION;
loc_t lcat_descr;
loc_t lcat_picture;
EXEC SQL END DECLARE SECTION;
mint whenexp_chk();
main(argc, argv)
mint argc;
char *argv[];
{
int4 ret, getrow();
short data_found = 0;
EXEC SQL BEGIN DECLARE SECTION;
char ans[BUFFSZ], db_name[30];
char name[40];
mint sel_cnt, i;
short type;
EXEC SQL END DECLARE SECTION;
printf("DYN_SQL Sample ESQL Program running.\\n\\n");
EXEC SQL whenever sqlerror call whenexp_chk;
if (argc > 2) /* correct no. of args? */
{
printf("\\nUsage: %s [database]\\nIncorrect no. of argument(s)\\n",
argv[0]);
printf("\\nDYN_SQL Sample Program over.\\n\\n");
exit(1);
}
strcpy(db_name, "stores_demo");
if(argc == 2)
strcpy(db_name, argv[1]);
sprintf(statement,"CONNECT TO %s",db_name);
EXEC SQL connect to :db_name;
printf("Connected to %s\\n", db_name);
++argv;
while(1)
{
/* prompt for SELECT statement */
printf("\\nEnter a SELECT statement for the %s database",
db_name);
printf("\\n\\t(e.g. select * from customer;)\\n");
printf("\\tOR a ';' to terminate program:\\n>> ");
if(!getans(ans, BUFFSZ))
continue;
if(*ans == ';')
{
strcpy(statement, "DISCONNECT");
EXEC SQL disconnect current;
printf("\\nDYN_SQL Sample Program over.\\n\\n");
exit(1);
}
/* prepare statement id */
printf("\\nPreparing statement (%s)...\\n", ans);
strcpy(statement, "PREPARE sel_id");
EXEC SQL prepare sel_id from :ans;
/* declare cursor */
printf("Declaring cursor 'sel_curs' for SELECT...\\n");
strcpy(statement, "DECLARE sel_curs");
EXEC SQL declare sel_curs cursor for sel_id;
/* allocate descriptor area */
printf("Allocating system-descriptor area...\\n");
strcpy(statement, "ALLOCATE DESCRIPTOR selcat");
EXEC SQL allocate descriptor 'selcat' with max 20;
/* Ask the database server to describe the statement */
printf("Describing prepared SELECT...\\n");
strcpy(statement,
"DESCRIBE sel_id USING SQL DESCRIPTOR selcat");
EXEC SQL describe sel_id using sql descriptor 'selcat';
if(SQLCODE != 0)
{
printf("** Statement is not a SELECT.\\n");
free_stuff();
strcpy(statement, "DISCONNECT");
EXEC SQL disconnect current;
printf("\\nDYN_SQL Sample Program over.\\n\\n");
exit(1);
}
/* Determine the number of columns in the select list */
printf("Getting number of described values from ");
printf("system-descriptor area...\\n");
strcpy(statement, "GET DESCRIPTOR selcat: COUNT field");
EXEC SQL get descriptor 'selcat' :sel_cnt = COUNT;
/* open cursor; process select statement */
printf("Opening cursor 'sel_curs'...\\n");
strcpy(statement, "OPEN sel_curs");
EXEC SQL open sel_curs;
/*
* The following loop checks whether the cat_picture or
* cat_descr columns are described in the system descriptor area.
* If so, it initializes a locator structure to read the blob
* data into memory and sets the address of the locator structure
* in the system descriptor area.
*/
for(i = 1; i <= sel_cnt; i++)
{
strcpy(statement,
"GET DESCRIPTOR selcat: TYPE, NAME fields");
EXEC SQL get descriptor 'selcat' VALUE :i
:type = TYPE,
:name = NAME;
if(type == SQLTEXT && !strncmp(name, "cat_descr",
strlen("cat_descr")))
{
lcat_descr.loc_loctype = LOCMEMORY;
lcat_descr.loc_bufsize = -1;
lcat_descr.loc_oflags = 0;
strcpy(statement, "SET DESCRIPTOR selcat: DATA field");
EXEC SQL set descriptor 'selcat' VALUE :i
DATA = :lcat_descr;
}
if(type == SQLBYTES && !strncmp(name, @