Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive
On this page

Show all

C-Structure SQLDA

Updated on 2024-05-07 GMT+08:00
SQLDA is a C language structure used to store a query result set. A structure stores a record of a result set.
EXEC SQL include sqlda.h; 
sqlda_t         *mysqlda;  
EXEC SQL FETCH 3 FROM mycursor INTO DESCRIPTOR mysqlda;
Note that the SQL keyword is omitted. The paragraphs about the use cases of the INTO and USING keywords in section Named SQLDA also apply here. In a DESCRIBE statement, if the INTO keyword is used, the DESCRIPTOR keyword can be omitted.
EXEC SQL DESCRIBE prepared_statement INTO mysqlda;
  • Procedure
    1. Prepare a query and declare a cursor for it.
    2. Declare an SQLDA for the result row.
    3. Declare SQLDA for input parameters, initialize parameters, and allocate memory.
    4. Open a cursor with the input SQLDA.
    5. Fetch rows from the cursor and store them in the output SQLDA.
    6. Read the value from the output SQLDA to the host variable.
    7. Close the cursor.
    8. Deallocate the memory allocated to the SQLDA.
  • There are three types of SQLDA data structures: sqlda_t, sqlvar_t, and struct sqlname.
    1. sqlda_t
      The definition of sqlda_t is as follows:
      struct sqlda_struct
      {
          char            sqldaid[8];
          long            sqldabc;
          short           sqln;
          short           sqld;
          struct sqlda_struct *desc_next;
          struct sqlvar_struct sqlvar[1];
      };
      typedef struct sqlda_struct sqlda_t;
      The structure members are described as follows:
      • sqldaid: contains a string "SQLDA".
      • sqldabc: contains the size (in bytes) of the allocated space.
      • sqln: contains the number of input parameters for a parameterized query in case it is passed into OPEN, DECLARE or EXECUTE statements using the USING keyword. When it is used as the output of a SELECT, EXECUTE, or FETCH statement, its value is the same as that of sqld.
      • sqld: contains the number of fields in a result set.
      • desc_next: If the query returns more than one record, multiple linked SQLDA structures are returned, and desc_next holds a pointer to the next SQLDA structure in the list.
      • sqlvar: indicates the array of the columns in the result set.
    2. sqlvar_t
      The structure type sqlvar_t holds a column value and metadata (such as type and length). The definition of this type is as follows:
      struct sqlvar_struct
      {
          short          sqltype;
          short          sqllen;
          char          *sqldata;
          short         *sqlind;
          struct sqlname sqlname;
      };
      typedef struct sqlvar_struct sqlvar_t;
      The structure members are described as follows:
      • sqltype: contains the type identifier of the field.
      • sqllen: contains the binary length of the field, for example, 4 bytes for ECPGt_int.
      • sqldata: points to the data. For details about the data format, see Type Mapping.
      • sqlind: points to a null indicator. The value 0 indicates not null, and the value –1 indicates null.
      • sqlname: indicates the name of the field.
    3. struct sqlname
      A struct sqlname structure holds a column name. It is treated as a member of the sqlvar_t structure. The definition of this type is as follows:
      #define NAMEDATALEN 64
      struct sqlname
      {
              short           length;
              char            data[NAMEDATALEN];
      };
      The structure members are described as follows:
      • length: contains the length of the field name.
      • data: contains the actual field name.
  • Use an SQLDA to retrieve a result set.
    The general procedure for retrieving a query result set through an SQLDA is as follows:
    1. Declare an sqlda_t structure to receive the result set.
    2. Execute the FETCH, EXECUTE, or DESCRIBE command to process a query for which an SQLDA has been declared.
    3. Check the number of records in the result set by looking at sqln, a member of the sqlda_t structure.
    4. Fetch the values of each column from sqlvar[0], sqlvar[1], ..., members of the sqlda_t structure.
    5. Go to next row (sqlda_t) by following the desc_next pointer, a member of the sqlda_t structure.
    6. Repeat the preceding steps as required.
    Example:
    /* Declare an sqlda_t structure to receive the result set. */
    sqlda_t *sqlda1;
    /* Next, specify an SQLDA in a command. This is an example of the FETCH command. */
    EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;
    /* Run a loop to retrieve rows along the linked list. */
    sqlda_t *cur_sqlda;
    for (cur_sqlda = sqlda1;
         cur_sqlda != NULL;
         cur_sqlda = cur_sqlda->desc_next)
    {
        ...
    }
    /* Inside the loop, run another loop to retrieve the data of each column in the row (sqlvar_t). */
    for (i = 0; i < cur_sqlda->sqld; i++)
    {
        sqlvar_t v = cur_sqlda->sqlvar[i];
        char *sqldata = v.sqldata;
        short sqllen  = v.sqllen;
        ...
    }
    /* To fetch the values of a column, check the value of the sqltype member of the sqlvar_t structure. Then, switch to an appropriate method based on the column type to copy data from the sqlvar field to a host variable. */
    char var_buf[1024];
    switch (v.sqltype)
    {
        case ECPGt_char:
            memset(&var_buf, 0, sizeof(var_buf));
            memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf) - 1 : sqllen));
            break;
    
        case ECPGt_int: 
            memcpy(&intval, sqldata, sqllen);
            snprintf(var_buf, sizeof(var_buf), "%d", intval);
            break;
        ...
    }
  • Use an SQLDA to pass query parameters.
    The general procedure for passing input parameters to a prepared query using an SQLDA is as follows:
    1. Create a prepared query (prepared statement).
    2. Declare an sqlda_t structure as an SQLDA.
    3. Allocate a memory area for the SQLDA.
    4. Set (copy) the input values in the allocated memory.
    5. Open a cursor declaring the SQLDA.
    Example:
    /* First, create a prepared statement. */
    EXEC SQL BEGIN DECLARE SECTION;
        char query[1024] = "SELECT d.oid, * FROM pg_database d, pg_stat_database s WHERE d.oid = s.datid AND (d.datname = ? OR d.oid = ?)";
    EXEC SQL END DECLARE SECTION;
        EXEC SQL PREPARE stmt1 FROM :query;
    
        /* Allocate memory for an SQLDA and set the number of input parameters in the sqln member variable of the sqlda_t structure.
        * When the prepared query requires two or more input parameters, the application must allocate extra memory space. The space size is calculated as follows: (Number of parameters – 1) x sizeof(sqlvar_t).
        * The example here shows how to allocate memory space for two input parameters.
         */
        sqlda_t *sqlda2;
        sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t));
        memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t));
        sqlda2->sqln = 2; /* Number of input variables */
        /* After memory allocation, store the parameter values into the sqlvar[] array. (This is same array used for retrieving column values when the SQLDA is receiving a result set.)
        * In this example, the input parameters are postgres (string type) and 1 (integer type). */
        sqlda2->sqlvar[0].sqltype = ECPGt_char;
        sqlda2->sqlvar[0].sqldata = "postgres";
        sqlda2->sqlvar[0].sqllen  = 8;
        int intval = 1;
        sqlda2->sqlvar[1].sqltype = ECPGt_int;
        sqlda2->sqlvar[1].sqldata = (char *) &intval;
        sqlda2->sqlvar[1].sqllen  = sizeof(intval);
        /* Input parameters are passed to the prepared statement by opening a cursor and declaring the SQLDA that has been created. */
        EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;
        /* Finally, the allocated memory must be explicitly released after you use the input SQLDA, which is different from the SQLDA used to receive query results. */
        free(sqlda2);

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback