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

Host Variables with Non-Primitive Types

Updated on 2024-05-07 GMT+08:00

Non-primitive host variables can be arrays, typedefs, structures, and pointers.

  • Arrays

    There are two use cases for arrays as host variables. The first case is to store some text strings in char[] or VARCHAR[]. The second case is to retrieve multiple rows from a query result without using a cursor. Without an array, to process a query result consisting of multiple rows, it is required to use a cursor and the FETCH command. But with array host variables, multiple rows can be received at once. The length of the array has to be defined to be able to accommodate all rows, otherwise a buffer overflow will occur.

    For example, scan the pg_database system catalog and display the OIDs and names of all available databases.
    #include <stdlib.h>
    #include <stdio.h>
    #include <string.h>
    
    int main(void) 
    {
    EXEC SQL BEGIN DECLARE SECTION;
        int dbid[8];
        char dbname[8][16];
        int i;
    EXEC SQL END DECLARE SECTION;
    
        memset(dbname, 0, sizeof(char)* 16 * 8);
        memset(dbid, 0, sizeof(int) * 8);
        * Connect to the testdb database. The testdb database must be created in advance. */
        EXEC SQL CONNECT TO testdb; 
        /* Retrieve multiple rows to arrays at a time. */
        EXEC SQL SELECT oid,datname INTO :dbid, :dbname FROM pg_database; 
        for (i = 0; i < 8; i++) 
            printf("oid=%d, dbname=%s\n", dbid[i], dbname[i]); 
        EXEC SQL COMMIT; 
        EXEC SQL DISCONNECT ALL; 
        return 0; 
    }
    Example output (the exact value depends on the local environment):
    oid=1, dbname=template1 
    oid=11510, dbname=template0 
    oid=11511, dbname=postgres 
    oid=313780, dbname=testdb 
    oid=0, dbname= 
    oid=0, dbname= 
    oid=0, dbname=
  • Structures

    A structure whose member names match the column names of a query result, can be used to retrieve multiple columns at once. The structure enables handling multiple column values in a single host variable.

    The following example retrieves OIDs, names, and sizes of the available databases from the pg_database system catalog and using the pg_database_size() function. In this example, a structure variable dbinfo_t with members whose names match each column in the SELECT result is used to retrieve one result row without putting multiple host variables in the FETCH statement.

    EXEC SQL BEGIN DECLARE SECTION;
        typedef struct
        {
            int oid;
            char datname[65];
            long long int size;
        } dbinfo_t;
    
        dbinfo_t dbval;
    EXEC SQL END DECLARE SECTION;
        memset(&dbval, 0, sizeof(dbinfo_t));
    
        EXEC SQL DECLARE cur1 CURSOR FOR SELECT oid, datname, pg_database_size(oid) AS size FROM pg_database;     
        EXEC SQL OPEN cur1;
    
        /* Exit the while loop when the end of the result set is reached. */
        EXEC SQL WHENEVER NOT FOUND DO BREAK;
    
        while (1)
        {         
            /* Fetch multiple columns into one structure. */
            EXEC SQL FETCH FROM cur1 INTO :dbval;          
            /* Print the members of the structure. */
            printf("oid=%d, datname=%s, size=%lld\n", dbval.oid, dbval.datname, dbval.size);     
        }
        EXEC SQL CLOSE cur1;
    Example output (the exact value depends on the local environment):
    oid=1, datname=template1, size=4324580 
    oid=11510, datname=template0, size=4243460 
    oid=11511, datname=postgres, size=4324580 
    oid=313780, datname=testdb, size=8183012
    Structure host variables "absorb" as many columns in the query result as the structure columns. Additional columns can be allocated to other host variables. The above program could also be restructured like this, with the size variable outside the structure:
    EXEC SQL BEGIN DECLARE SECTION;     
        typedef struct     
        {        
            int oid;        
            char datname[65];     
        } dbinfo_t;   
       
        dbinfo_t dbval;     
        long long int size; 
    EXEC SQL END DECLARE SECTION;      
    
        memset(&dbval, 0, sizeof(dbinfo_t));      
    
        EXEC SQL DECLARE cur1 CURSOR FOR SELECT oid, datname, pg_database_size(oid) AS size FROM pg_database;     
        EXEC SQL OPEN cur1;      
    
        /* Exit the while loop when the end of the result set is reached. */
        EXEC SQL WHENEVER NOT FOUND DO BREAK;      
        while (1)     
        {         
            /* Fetch multiple columns into one structure. */
            EXEC SQL FETCH FROM cur1 INTO :dbval, :size;          
            /* Print the members of the structure. */
            printf("oid=%d, datname=%s, size=%lld\n", dbval.oid, dbval.datname, size);     
        }  
        
        EXEC SQL CLOSE cur1;
  • typedef
    Use the typedef keyword to map new types to existing types.
    EXEC SQL BEGIN DECLARE SECTION;
    typedef char mychartype[40];
    typedef long serial_t;
    EXEC SQL END DECLARE SECTION;
    You can also run the following command:
    EXEC SQL TYPE serial_t IS long;

    This declaration does not need to be part of a DECLARE section.

  • Pointers
    You can declare pointers to the most common types.
    EXEC SQL BEGIN DECLARE SECTION;
    int   *intp;
    char **charp;
    EXEC SQL END DECLARE SECTION;

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