### NCITransStart Usage Example

Source: https://docs.xugudb.com/content/development/nci/ref

Example showing how to start a new transaction.

```C
NCITransStart(ctx, err, 0, NCI_TRANS_NEW);
```

--------------------------------

### Complete OCI Example Setup

Source: https://docs.xugudb.com/content/development/oci/quick-start

The SQL table structure required for the complete OCI example.

```sql
CREATE TABLE tab_test (
    id      INT,
    name    VARCHAR(20),
    created DATE
);
```

--------------------------------

### SELECT GET_INSTALL_PATH(1) example

Source: https://docs.xugudb.com/content/reference/function/system-infos-functions/get_install_path

This example queries the installation directory path of node 1 in the current cluster. The output shows the result '/home/xugu/Server/BIN', assuming the database installation path is '/home/xugu/Server/'. If the input parameter is out of range, an exception E22008 is raised; if the specified node is in an abnormal state, an exception E22001 is raised.

```sql
-- 获取当前集群中1号节点的安装目录路径
SQL> SELECT GET_INSTALL_PATH(1) FROM dual;

+-----------------------+
|          EXPR1        |
+-----------------------+
| /home/xugu/Server/BIN |
+-----------------------+
```

--------------------------------

### Setup database tables and data

Source: https://docs.xugudb.com/content/reference/sql/expression/subquery

Create the employees and departments tables and insert initial data for subsequent examples.

```sql
-- 建表
SQL> CREATE TABLE employees (
       id INT PRIMARY KEY,
       name VARCHAR(50),
       department_id INT
     );

-- 建表
SQL> CREATE TABLE departments (
       id INT PRIMARY KEY,
       name VARCHAR(50)
     );

-- 插入数据
SQL> INSERT INTO employees VALUES
     (1, 'Alice', 1),
     (2, 'Bob', 1),
     (3, 'Charlie', 2),
     (4, 'Diana', 2),
     (5, 'Eve', 3);

-- 插入数据
SQL> INSERT INTO departments VALUES
     (1, 'HR'),
     (2, 'Engineering'),
     (3, 'Marketing');
```

--------------------------------

### OCITransStart example

Source: https://docs.xugudb.com/content/development/oci/ref

Example call to OCITransStart that starts a new transaction using OC I_TRANS_NEW flag.

```C
OCITransStart(ctx, err, 0, OCI_TRANS_NEW);
```

--------------------------------

### BeginTransaction Method Example

Source: https://docs.xugudb.com/content/development/nodejs

Example of starting a transaction.

```javascript
conn.connect();
conn.beginTransaction();
```

--------------------------------

### Install Xugu Go Driver with go get

Source: https://docs.xugudb.com/content/development/go/setup

Use this command to install the Xugu Go driver via go get. It fetches the latest version from the gitee.com/XuguDB repository.

```go
go get gitee.com/XuguDB/go-xugu-driver@latest
```

--------------------------------

### 存储文件映射配置 mount.ini 和 datafile.ini

Source: https://docs.xugudb.com/content/deployment/deploy-enterprise-version

Shows example contents of mount.ini (default generated) and datafile.ini (manually created) to plan file storage paths. The mapped paths must exist and be created in advance; if the default install is used, data and temp files are generated under the install path, so this step can be skipped.

```shell
[xugu@xukunqian236gu01 SETUP]# cat mount.ini
/XGLOG      ./XGLOG
/CATA      ./XHOME/CATA
/DATA      ./XHOME/DATA
/TEMP      ./XHOME/TEMP
/REDO      ./XHOME/REDO
/BACKUP      ./XHOME/BACKUP
/ARCH      ./XHOME/ARCH
/UNDO      ./XHOME/UNDO
/MODI      ./XHOME/XMODI

/DATA1         /data1/data
/TEMP1        /data1/temp
```

```shell
[xugu@kunqian236 SETUP]# cat datafile.ini
#DATA_FILES
/DATA1/DATA1.DBF
/DATA1/DATA2.DBF
/DATA1/DATA3.DBF
/DATA1/DATA4.DBF

#TEMP_FILES
/TEMP1/TEMP1.DBF
/TEMP1/TEMP2.DBF
```

--------------------------------

### Example Data Setup for <<->>

Source: https://docs.xugudb.com/content/spatial-database/system-function/geometry/operator/geometry_distance_centroid_nd

Creates test tables and inserts POINT, LINESTRING, POLYGON, and POLYHEDRALSURFACE data into the 'knn_recheck_geom_nd' table for KNN queries. Uses a stored procedure 'p_seris' to generate series values. Requires the series procedures and tables defined.

```sql
CREATE TABLE seriesx(x INT);  
CREATE TABLE seriesy(y INT); 
CREATE TABLE seriesz(z INT); 
CREATE TABLE seriesi(i INT); 
CREATE OR REPLACE PROCEDURE p_seris(sta1 INT,endv1 INT,inc1 INT, tbn1 VARCHAR) IS
sta INT;
inc INT;
BEGIN
	sta:=sta1;
	inc:=inc1;
	LOOP
		EXECUTE IMMEDIATE 'INSERT INTO '||tbn1||' VALUES('||sta||')';
		sta:=sta+inc;
		IF sta > endv1 THEN
			EXIT;
		END IF;
	END LOOP;
END;

EXEC p_seris(-100,1000,7,'seriesx');
EXEC p_seris(-300,1000,9,'seriesy');
EXEC p_seris(1005,10000,5555,'szes';)
EXEC p_seris(0,10,1,'seriesi');

CREATE TABLE knn_recheck_geom_nd(gid INT primary key, geom GEOMETRY);

-- 3D POINTS
INSERT INTO knn_recheck_geom_nd(gid,geom)
	 SELECT ROW_NUMBER() OVER(ORDER BY x,y,z) AS gid, ST_MakePoint(x*0.777,y*0.987,z*1.05) As geom
	 FROM seriesx AS x, seriesy As y, seriesz As z ;

-- 3d LINES
INSERT INTO knn_recheck_geom_nd(gid, geom)
	 SELECT 50000 + i, ST_Translate('LINESTRING(-100 300 500, 500 700 600, 400 123 0, 500 10000 -1234, 1 1 5000)'::GEOMETRY, i*2000,0)
	 FROM seriesi i;

TRUNCATE TABLE seriesi;
EXEC p_seris(0,3,1,'seriesi');

-- 3d POLYGONS
INSERT INTO knn_recheck_geom_nd(gid, geom)
	 SELECT 500100 + i, ST_Translate('POLYGON((100 800 5678, 100 700 5678, 400 123 5678, 405 124 5678, 100 800 5678))'::GEOMETRY,0,i*2000)
	 FROM seriesi i;

-- polyhedral surface --
INSERT INTO knn_recheck_geom_nd(gid,geom)
	 SELECT 600000 + row_number() over(), ST_Translate(the_geom,100, 450,1000) As the_geom
	  FROM ( 
	  select ST_GeomFromText(
	 'POLYHEDRALSURFACE(
	 ((0 0 1, 1 0 1, 1 1 0, 0 1 0, 0 0 0)),
	 ((0 0 1, 0 1 0, 1 1 0, 1 1 0, 1 0 0, 0 0 0)),  ((0 0 1, 0 0 1, 1 0 1, 1 1 0, 0 0 0)),  ((0 0 1, 0 1 1, 0 1 0, 1 1 0, 0 0 0)),
	 ((0 1 0, 1 1 0, 1 1 1, 1 1 0, 0 1 0)),  ((0 0 1, 1 0 1, 1 1 1, 1 1 0, 0 0 1)) )')  AS the_geom UNION
	 select ST_GeomFromText(
	 'POLYHEDRALSURFACE(
	 ((0 0 1, 1 1 0, 1 1 1, 0 0 1)),
	 ((0 0 1, 0 1 0, 1 1 0, 1 0 0, 0 0 0)) )')  AS the_geom
	 ) As foo(the_geom) ;
```

--------------------------------

### XGCIExecDirect Usage Example

Source: https://docs.xugudb.com/content/development/xgci/ref

Example showing how to execute a SELECT query.

```C
const char* sql = "SELECT * FROM tab_test";
XGCIExecDirect(hstmt, (char*)sql, XGCI_NTS);
```

--------------------------------

### Get minimum point X coordinate using ST_BoundingDiagonal

Source: https://docs.xugudb.com/content/spatial-database/system-function/geometry/geometric-accessor/st_boundingdiagonal

Example query that buffers a point and extracts the X coordinate of the start point of the bounding diagonal.

```sql
SQL> SELECT ST_X(ST_StartPoint(ST_BoundingDiagonal(
  ST_Buffer(ST_Point(0,0),10)
)));

+---------------+
|     EXPR1     |
+---------------+
| -1.000000e+01 |
+---------------+
```

--------------------------------

### 使用 --start-ini 和 --init-setup 初始化配置

Source: https://docs.xugudb.com/content/deployment/start-parameter

结合 --start-ini 和 --init-setup 参数生成符合环境的 xugu.ini 配置文件并退出。

```shell
-- 初始化生成xugu.ini，并修改数据库服务端口为10578后退出;
./BIN/xugu_linux_x86_64_20250619 --start-ini="listen_port=10578" --init-setup
```

--------------------------------

### 初始化 SETUP 配置文件

Source: https://docs.xugudb.com/content/deployment/deploy-distributed-version

可选步骤，用于初始化 xugu.ini、mount.ini、types.ini 和 trust.ini 等配置文件。

```shell
./xugu_linux_x86_64_20250624 --init-setup
```

--------------------------------

### STARTS_WITH example

Source: https://docs.xugudb.com/content/reference/function/string-functions/starts_with

Example SQL query that checks if 'start_with' starts with 'start'. The output shows 'T' (true).

```sql
-- 检查'start_with'字符串是否以'start'字符串为前缀
SQL> SELECT STARTS_WITH('start_with', 'start') FROM dual;

+-------+
| EXPR1 |
+-------+
| T     |
+-------+
```

--------------------------------

### Complete demo.php example

Source: https://docs.xugudb.com/content/development/php/quick-start

A full example demonstrating connection, insertion, and querying in a single script.

```php
//demo.php
$pdo = new PDO("xugusql:ip=127.0.0.1;port=5138;db=SYSTEM;", "SYSDBA", "SYSDBA");

//执行插入
$res = $pdo->exec("INSERT INTO tab_test (id, name, created) VALUES(1, '张三', '2000-01-01')");
echo $res;

//执行查询
$stmt = $pdo->query("SELECT * FROM tab_test");
while($row = $stmt->fetch()) {
    print_r($row);
}
```

--------------------------------

### NCIDateSysDate Example

Source: https://docs.xugudb.com/content/development/nci/ref

Example usage of NCIDateSysDate to get the current system date.

```c
NCIDate date;
ret = NCIDateSysDate(NULL, &date);
```

--------------------------------

### OCIDateSysDate Example

Source: https://docs.xugudb.com/content/development/oci/ref

Example usage of OCIDateSysDate to get the current system date.

```c
OCIDate date;
ret = OCIDateSysDate(NULL, &date);
```

--------------------------------

### Enable Backup Plan Example

Source: https://docs.xugudb.com/content/reference/system-package/dbms_backup

Demonstrates querying the SYS_BACKUP_PLANS table, enabling the 'back_exp' plan using DBMS_BACKUP.ENABLE_PLAN, and verifying the status change.

```sql
-- 查询系统表SYS_BACKUP_PLANS，ENABLE字段为F
SQL> select * from sys_backup_plans;

+-------+-----------+-------+--------------------------+---------------+--------+---------+-----------+
| DB_ID | PLAN_NAME | PTYPE |         START_T          |    PERIOD     | ENABLE |  PATH   | CURR_ITEM |
+-------+-----------+-------+--------------------------+---------------+--------+---------+-----------+
| 1     | back_exp  | EXP   | 2025-06-30 10:10:10.000  |       1 00:00 | F      | /BACKUP | 1         |
+-------+-----------+-------+--------------------------+---------------+--------+---------+-----------+

-- 设置备份计划back_exp为活动态，允许其执行
SQL> DBMS_BACKUP.ENABLE_PLAN('back_exp',true);

-- 查询系统表SYS_BACKUP_PLANS，ENABLE字段变为T
SQL> select * from sys_backup_plans;

+-------+-----------+-------+--------------------------+---------------+--------+---------+-----------+
| DB_ID | PLAN_NAME | PTYPE |         START_T          |    PERIOD     | ENABLE |  PATH   | CURR_ITEM |
+-------+-----------+-------+--------------------------+---------------+--------+---------+-----------+
| 1     | back_exp  | EXP   | 2025-06-30 10:10:10.000  |       1 00:00 | T      | /BACKUP | 1         |
+-------+-----------+-------+--------------------------+---------------+--------+---------+-----------+
```

--------------------------------

### ST_SCROLL Example

Source: https://docs.xugudb.com/content/spatial-database/system-function/geometry/geometric-editor/st_scroll

Example demonstrating how to change the starting point of a closed linestring to its third vertex.

```sql
SQL> SELECT ST_ASEWKT(ST_SCROLL('SRID=4326;LINESTRING(0 0 0 1, 10 0 2 0, 5 5 4 2,0 0 0 1)', 'POINT(5 5 4 2)'));

EXPR1 |
------------------------------------------------------------------------------
SRID=4326;LINESTRING(5 5 4 2,0 0 0 1,10 0 2 0,5 5 4 2)|
```

--------------------------------

### NCIStmtPrepare Usage Example

Source: https://docs.xugudb.com/content/development/nci/ref

Example of preparing a SQL SELECT statement.

```C
cosnt char* sql = "SELECT * FROM tab_test WHERE id = ?";
ret = NCIStmtPrepare(stmt, err, (OraText*)sql, strlen(sql), NCI_NTV_SYNTAX, NCI_DEFAULT);
```

--------------------------------

### Get Current Time Example

Source: https://docs.xugudb.com/content/reference/function/date-and-time-functions/now

Example of using the NOW function to retrieve the current system time.

```sql
-- 获取当前时间
SQL>  SELECT NOW() FROM dual;

+--------------------------+
|          EXPR1           |
+--------------------------+
| 2025-06-19 15:05:13.290  |
+--------------------------+
```

--------------------------------

### XGCISessionBegin Usage Example

Source: https://docs.xugudb.com/content/development/xgci/ref

Example showing how to initiate a database session with credentials.

```C
int ret = XGCISessionBegin(hsession, (char*)"SYSDBA", (char*)"SYSDBA");
if(ret != XGCI_SUCCESS) {
    //XGCIErrors(...)
}
```

--------------------------------

### 启动程序命令

Source: https://docs.xugudb.com/content/ecosystem/orm/go/xorm

在 demo 根目录下打开命令行工具，执行 go run demo.go 启动程序。

```cmd
go run demo.go
```

--------------------------------

### PDOStatement::rowCount Example

Source: https://docs.xugudb.com/content/development/php/ref

Example showing how to get the number of rows affected by an INSERT statement.

```php
$pdo = new PDO("xugusql:ip=127.0.0.1;port=5138;db=SYSTEM", "SYSDBA", "SYSDBA");

$stmt = $pdo->prepare("INSERT INTO tab_test (name) VALUES (?)");
$stmt->bindValue(1, "Tom");
$stmt->execute();

echo $stmt->rowCount();
```

--------------------------------

### INITCAP Example

Source: https://docs.xugudb.com/content/reference/function/string-functions/initcap

Example showing the conversion of 'hELLO wORLD!' to 'Hello World!'.

```sql
-- 字符串中单词首字母转大写，其余转小写
SQL> SELECT INITCAP('hELLO wORLD!') FROM DUAL FROM dual;

+--------------+
|    EXPR1     |
+--------------+
| Hello World! |
+--------------+
```

--------------------------------

### 初始化系统配置

Source: https://docs.xugudb.com/content/deployment/start-parameter

使用 --init-setup 生成配置文件，若 SETUP 目录中存在同名配置文件将导致失败。目录结构输出显示生成的文件。

```Shell
$ ./xugu_linux_x86_64_20250619 --init-setup
Resource limit:
        File number:     hard limit=1048576,soft limit=10240
        File size(M):    hard limit=-1,soft limit=-1
        Data memory(M):  hard limit=-1,soft limit=-1
        Resident(M):     hard limit=-1,soft limit=-1
        Proc's total memory(M): hard limit=-1,soft limit=-1

```

```Shell
$ cd ..
$ tree
.
├── BIN
│   └── xugu_linux_x86_64_20250619
└── SETUP
    ├── mount.ini
    ├── trust.ini
    ├── types.ini
    └── xugu.ini
```

--------------------------------

### 切换数据库

Source: https://docs.xugudb.com/content/getting-started/basic-sql-operations

使用 USE 命令切换到指定的数据库 db_quickstart。

```sql
SQL> USE db_quickstart;
```

--------------------------------

### Get System Time Example

Source: https://docs.xugudb.com/content/reference/function/date-and-time-functions/systime

Example usage of the SYSTIME function to retrieve the current system time.

```sql
-- 获取系统当前时间。
SQL> SELECT SYSTIME() FROM dual;

+--------------+
|     EXPR1    |
+--------------+
| 11:42:25.948 |
+--------------+
```

--------------------------------

### INSERT Example

Source: https://docs.xugudb.com/content/reference/function/string-functions/insert

Example demonstrating the replacement of three characters starting from the second position in the string 'abc1ABC1abcABcd' with 'WWWW'.

```sql
-- 使用'WWWW'替换'abc1ABC1abcABcd'的第二个字符开始的三个字符
SQL> SELECT INSERT('abc1ABC1abcABcd',2,3,'WWWW') FROM dual;

+------------------+
|      EXPR1       |
+------------------+
| aWWWWABC1abcABcd |
+------------------+
```

--------------------------------

### Create and Configure Logical Backup Plan

Source: https://docs.xugudb.com/content/reference/system-package/dbms_backup

Example demonstrating the creation of an EXP-type backup plan and adding a logical backup item for a database.

```sql
-- 创建一个EXP类型的备份计划back_exp
SQL> DBMS_BACKUP.CREATE_PLAN('back_exp','EXP','/BACKUP','2025-06-30 10:00:00',INTERVAL '1 0:0' DAY TO MINUTE);

-- 查询备份计划
SQL> SELECT * FROM SYS_BACKUP_PLANS where plan_name = 'back_exp';

+-------+-----------+-------+--------------------------+---------------+--------+---------+-----------+
| DB_ID | PLAN_NAME | PTYPE |         START_T          |    PERIOD     | ENABLE |  PATH   | CURR_ITEM |
+-------+-----------+-------+--------------------------+---------------+--------+---------+-----------+
| 1     | back_exp  | EXP   | 2025-06-30 10:00:00.000  |       1 00:00 | F      | /BACKUP | 1         |
+-------+-----------+-------+--------------------------+---------------+--------+---------+-----------+

-- 向这个备份计划中加入逻辑备份项(对数据库TEST_DB进行备份)
SQL> EXEC DBMS_BACKUP.ADD_EXPORT_ITEM('back_exp','TEST_DB','database','test_db.EXP',INTERVAL '0 0:0' DAY TO MINUTE);

-- 查询系统备份项
SQL> SELECT * FROM SYS_BACKUP_ITEMS where plan_name = 'back_exp';

+-------+-----------+---------+----------+----------+---------+-------------+---------+---------------+----------------+---------------+--------------------------+---------+----------+
| DB_ID | PLAN_NAME | ITEM_NO | OBJ_NAME | OBJ_TYPE | OP_TYPE |  FILE_NAME  | FILE_OP | FILE_KEEP_CNT | FILE_MOVE_PATH |   TIME_OFF    |        NEXT_RUN_T     | IPERIOD | ISTART_T |
+-------+-----------+---------+----------+----------+---------+-------------+---------+---------------+----------------+---------------+--------------------------+---------+----------+
| 1     | back_exp  | 1       | TEST_DB  | DATABASE | EXPORT  | test_db.EXP | <NULL>  | 0             | <NULL>         |       0 00:00 | 2025-06-30 10:00:00.000  | <NULL>  | <NULL>|
+-------+-----------+---------+----------+----------+---------+-------------+---------+---------------+----------------+---------------+--------------------------+---------+----------+
```

--------------------------------

### Execute Query and Fetch Results

Source: https://docs.xugudb.com/content/development/nci/quick-start

Example demonstrating how to prepare a query, define output positions, execute the statement, and fetch results in a loop.

```C
const char* sql = "SELECT id FROM tab_test";
NCIStmtPrepare(stmt, err, sql, strlen(sql), NCI_NTV_SYNTAX, NCI_DEFAULT);

NCIDefine* def;
int id;
NCIDefineByPos(stmt, &def, err, 1, &id, 4, SQLT_INT, NULL, NULL, NULL, NCI_DEFAULT);
NCIStmtExecute(ctx, stmt, err, 0, 0, NULL, NULL, NCI_DEFAULT);

while ((ret = NCIStmtFetch(stmt, err, 1, NCI_FETCH_NEXT, NCI_DEFAULT)) == NCI_SUCCESS) {
    printf("%d\n", id);
}
```

--------------------------------

### SUBSTR Usage Example

Source: https://docs.xugudb.com/content/reference/system-package/utl_raw

Example showing how to extract a 2-byte substring starting from position 1 of the RAW representation of 'hello'.

```sql
-- 将'hello'转换为RAW后，从起始位置1，提取2字节长度
SQL> SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.SUBSTR(UTL_RAW.CAST_TO_RAW('hello'),1,2)) FROM DUAL;

+-------+
| EXPR1 |
+-------+
| he    |
+-------+
```

--------------------------------

### NCIInitialize Usage Example

Source: https://docs.xugudb.com/content/development/nci/ref

Example showing how to call NCIInitialize with default parameters.

```C
NCIInitialize(NCI_DEFAULT, NULL, NULL, NULL, NULL);
```

--------------------------------

### SUBSTRB example: start at third byte

Source: https://docs.xugudb.com/content/reference/function/string-functions/substrb

Example query that extracts from the third byte of the string 'abcdef', returning 'cdef'.

```sql
-- 从第三个字节开始截取字符串'abcdef'
SQL> SELECT SUBSTRB('abcdef',3) FROM dual;

+-------+
| EXPR1 |
+-------+
| cdef  |
+-------+
```

--------------------------------

### 创建数据库

Source: https://docs.xugudb.com/content/getting-started/basic-sql-operations

使用 CREATE DATABASE 语句创建一个名为 db_quickstart 的数据库，并指定字符集为 UTF8。

```sql
SQL> CREATE DATABASE db_quickstart CHARACTER SET 'UTF8';
```

--------------------------------

### ST_LINESUBSTRING example with equal start and end fractions

Source: https://docs.xugudb.com/content/spatial-database/system-function/geometry/linear-reference/st_linesubstring

When startfraction and endfraction are equal, the result is a POINT, as shown in this example with both fractions set to 0.333.

```sql
SQL> SELECT TO_CHAR(ST_AsText(ST_LineSubstring( 'LINESTRING(25 50, 100 125, 150 190)', 0.333, 0.333)));

+--------------------------------------------+
|                   EXPR1                    |
+--------------------------------------------+
| POINT(69.28469348539744 94.28469348539744) |
+--------------------------------------------+
```

--------------------------------

### 配置存储映射文件

Source: https://docs.xugudb.com/content/deployment/deploy-standard-version

在 SETUP 目录下配置 mount.ini 进行路径映射，并手动创建 datafile.ini 指定数据文件和临时文件路径。

```shell
[xugu@kunqian236 SETUP]# cat mount.ini
/XGLOG      ./XGLOG
/CATA      ./XHOME/CATA
/DATA      ./XHOME/DATA
/TEMP      ./XHOME/TEMP
/REDO      ./XHOME/REDO
/BACKUP      ./XHOME/BACKUP
/ARCH      ./XHOME/ARCH
/UNDO      ./XHOME/UNDO
/MODI      ./XHOME/XMODI

/DATA1         /data1/data
/TEMP1        /data1/temp

-- 在SETUP目录下手动创建文件datafile.ini
[xugu@kunqian236 SETUP]# cat datafile.ini
#DATA_FILES
/DATA1/DATA1.DBF
/DATA1/DATA2.DBF
/DATA1/DATA3.DBF
/DATA1/DATA4.DBF

#TEMP_FILES
/TEMP1/TEMP1.DBF
/TEMP1/TEMP2.DBF
```

--------------------------------

### SELECT SYSDATE() FROM dual example

Source: https://docs.xugudb.com/content/reference/function/date-and-time-functions/sysdate

Example query to get the system current time, with output showing a DATETIME value in the format specified.

```sql
-- 获取系统当前时间。
SQL> SELECT SYSDATE() FROM dual;

+--------------------------+
|          EXPR1           |
+--------------------------+
| 2025-07-01 10:03:03.008  |
+--------------------------+
```

--------------------------------

### ARRAY_LOWER example

Source: https://docs.xugudb.com/content/reference/function/array-functions/array_lower

Example query that gets the lower bound of a single-dimensional array [1, 2, 3] at the first dimension, returning 1.

```sql
-- 获取单维数组`[1, 2, 3]`在第一个维度上的下界
SQL> SELECT ARRAY_LOWER(ARRAY[1, 2, 3], 1);
+-------+
| EXPR1 |
+-------+
| 1     |
+-------+
```

--------------------------------

### Create Connection Example

Source: https://docs.xugudb.com/content/development/nodejs

Example of initializing a connection object with connection string parameters.

```javascript
const xugu = require("./xugudbjs");
let conn = xugu.createConnection("IP=127.0.0.1;Port=5138;Database=SYSTEM;USER=SYSDBA;PWD=SYSDBA;char_set=utf8");
```

--------------------------------

### Generate GUID with SYS_GUID()

Source: https://docs.xugudb.com/content/reference/function/uuid-functions/sys_guid

Example of generating a global unique identifier using SYS_GUID() in a SELECT query. The output shows the returned GUID value.

```sql
-- 生成全局唯一标识
SQL> SELECT SYS_GUID() FROM dual;

+------------------------------------+
|               EXPR1                |
+------------------------------------+
| <5FE0DC8CA6344554A9D8162BC9020537> |
+------------------------------------+
```

--------------------------------

### Complete XGCI Example

Source: https://docs.xugudb.com/content/development/xgci/quick-start

A full C program demonstrating how to connect to a XuguDB instance, insert data, and perform a query.

```C
// main.cpp
#include <stdio.h>
#include <string.h>
#include "xgci.h"

void printError(XGCIHANDLE handle)
{
    char errCode[7] = { 0 };
    char errMsg[256] = { 0 };
    int len = 0;
    XGCIErrors(handle, errCode, errMsg, &len);
    printf("[%s] %s\n", errCode, errMsg);
}

void insert(XGCIHANDLE hstmt)
{
    const char* sql = "INSERT INTO tab_test (name, created) VALUES('张三', '2000-01-01')";
    int ret = XGCIExecDirect(hstmt, (char*)sql, strlen(sql));
    if (ret == XGCI_SUCCESS)
        printf("insert successfully\n");
    else
        printError(hstmt);
    XGCIFreeStmt(hstmt, XG_RESET);
}

void query(XGCIHANDLE hstmt)
{
    const char* sql = "SELECT id, name, created FROM tab_test";
    int ret = XGCIExecDirect(hstmt, (char*)sql, XGCI_NTS);
    if (ret != XGCI_SUCCESS) {
        printError(hstmt);
    } else {
        int id;
        char name[20];
        DATE_STRUCT date;
        int rc[3];
        int len[3];
        XGCIDefineByPos(hstmt, 1, &id, 4, XG_C_INTEGER, &rc[0], &len[0]);
        XGCIDefineByPos(hstmt, 2, name, 20, XG_C_CHAR, &rc[1], &len[1]);
        XGCIDefineByPos(hstmt, 3, &date, 20, XG_C_DATE, &rc[2], &len[2]);
        while ((ret = XGCIFetch(hstmt)) == XGCI_SUCCESS) {
            printf("%d, %s, %d-%d-%d\n", id, name, date.year, date.month, date.day);
        }
    }
}

int main()
{
    XGCIHANDLE henv;
    XGCIHANDLE hserver;
    XGCIHANDLE hsession;
    XGCIHANDLE hstmt;

    unsigned short port = 5138;
    int ret;

    XGCIHandleAlloc(NULL, &henv, HT_ENV);
    XGCIHandleAlloc(henv, &hserver, HT_SERVER);
    XGCIHandleAttrSet(hserver, XGCI_ATTR_SRV_IP, "127.0.0.1", XGCI_NTS);
    XGCIHandleAttrSet(hserver, XGCI_ATTR_SRV_PORT, &port, 0);
    XGCIHandleAttrSet(hserver, XGCI_ATTR_SRV_DBNAME, "SYSTEM", XGCI_NTS);

    XGCIHandleAlloc(hserver, &hsession, HT_SESSION);
    XGCIHandleAlloc(hsession, &hstmt, HT_STATEMENT);

    ret = XGCISessionBegin(hsession, (char*)"SYSDBA", (char*)"SYSDBA");
    if (ret == XGCI_SUCCESS) {
        insert(hstmt);
        query(hstmt);
        XGCISessionEnd(hsession);
    } else {
        printError(hsession);
    }

    XGCIHandleFree(hstmt);
    XGCISessionEnd(hsession);
    XGCIHandleFree(hserver);
    XGCIHandleFree(henv);
    return ret;
}
```

--------------------------------

### Example: SELECT POSTGIS_FULL_VERSION()

Source: https://docs.xugudb.com/content/spatial-database/system-function/geometry/version/postgis_full_version

Example query that calls POSTGIS_FULL_VERSION() and shows the returned version and build configuration string.

```sql
SQL> SELECT POSTGIS_FULL_VERSION();

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                             EXPR1                                                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| POSTGIS="3.5.0dev 3.5.0dev" [EXTENSION] PGSQL="161" GEOS="3.12.1-CAPI-1.18.1" SFCGAL="SFCGAL 1.5.0, CGAL 5.6.1, BOOST 1.85.0" PROJ="9.3.1 NETWORK_ENABLED=OFF URL_ENDPOINT= USER_WRITABLE_DIRECTORY=/root/.local/share/proj" GDAL="GDAL 3.8.5, released 2024/04/02 GDAL_DATA not found" LIBXML="2.9.10" LIBJSON="0.17" RASTER |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

```

--------------------------------

### CHECK_AUTH Usage Example

Source: https://docs.xugudb.com/content/reference/function/uncategorized-functions/check_auth

A comprehensive example demonstrating user setup, object creation, permission checking with CHECK_AUTH, and performing operations based on those permissions.

```sql
-- 新建用户和角色
SQL> CREATE USER user_test IDENTIFIED BY '123QWE$$&';

SQL> CREATE ROLE role_1;

SQL> GRANT ROLE role_1 TO user_test;

-- 新建表对象
SQL> CREATE TABLE tab_test_privilege(a INT);

-- 给角色授予 `SELECT, INSERT, UPDATE, DELETE` 权限
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE tab_test_privilege TO role_1 WITH GRANT OPTION;

-- 切换会话的用户为新建的 user_test 用户
SQL> $user_test

-- 查询新建的表对象 tab_test_privilege 的 ID
SQL> SELECT OBJ_ID, SCHEMA_ID , USER_ID FROM ALL_OBJECTS WHERE OBJ_NAME = 'tab_test_privilege';

+---------+-----------+---------+
| OBJ_ID  | SCHEMA_ID | USER_ID |
+---------+-----------+---------+
| 1048601 | 1         | 1       |
+---------+-----------+---------+

-- 检查 user_test 是否拥有表对象 tab_test_privilege 的 INSERT 权限
SQL> SELECT CHECK_AUTH(1048601, 1, 1 , 5, 1);

+-------+
| EXPR1 |
+-------+
| T     |
+-------+

SQL> SELECT * FROM SYSDBA.tab_test_privilege;

+---+
| A |
+---+
+---+

SQL> INSERT INTO SYSDBA.tab_test_privilege VALUES (1), (3), (4);

Total 3 records effected.
Use time:0 ms.

SQL> SELECT * FROM SYSDBA.tab_test_privilege;

+---+
| A |
+---+
| 1 |
| 3 |
| 4 |
+---+

-- 检查 user_test 是否拥有表对象 tab_test_privilege 的 UPDATE 权限
SQL> SELECT CHECK_AUTH(1048601, 1, 1 , 5, 2);

+-------+
| EXPR1 |
+-------+
| T     |
+-------+

SQL> UPDATE SYSDBA.tab_test_privilege SET a = 100 WHERE a < 4;

Total 2 records effected.
Use time:1 ms.

SQL> SELECT * FROM SYSDBA.tab_test_privilege;

+-----+
|  A  |
+-----+
| 100 |
| 100 |
| 4   |
+-----+

-- 检查 user_test 是否拥有表对象 tab_test_privilege 的 DROP 权限
SQL> SELECT CHECK_AUTH(1048601, 1, 1 , 5, 32);

+-------+
| EXPR1 |
+-------+
| F     |
+-------+

SQL> DROP TABLE SYSDBA.tab_test_privilege;

Error: [E18012] 权限不够
```

--------------------------------

### Complete XuguDB Usage Example

Source: https://docs.xugudb.com/content/development/nodejs

A full example demonstrating connecting to the database, creating a table, inserting data, querying the table, and disconnecting.

```javascript
const xugu = require("./xugudbjs");
let conn = xugu.createConnection("IP=127.0.0.1;Port=5138;Database=SYSTEM;USER=SYSDBA;PWD=SYSDBA;char_set=utf8");
try {
    conn.connect();
} catch (err) {
    console.log(err);
    return;
}

let sql = "CREATE TABLE tab_test(id INT, name VARCHAR(20), created DATE)";
conn.query(sql, (err) => {
    if(err != null) console.log(err);
});

sql = "INSERT INTO tab_test(id, name, created) VALUES(1, '张三', '2000-01-01')";
conn.query(sql, (err) => {
    if(err != null) console.log(err);
});

sql = "SELECT * FROM tab_test";
conn.query(sql, (err, results, fields) => {
    if(err != null) 
        console.log(err);
    else
        console.log(results);
});
conn.disconnect();
```

--------------------------------

### XGCITransBegin Example

Source: https://docs.xugudb.com/content/development/xgci/ref

Configures session attributes to disable auto-commit and starts a transaction.

```C
int flag = XGCI_AUTOCOMMIT_OFF;
XGCIHandleAttrSet(hsession, XGCI_ATTR_SESS_AUTO_COMMIT, &flag, 0);
// connect to database
XGCITransBegin(hsession);
```

--------------------------------

### Examples of Database Connection

Source: https://docs.xugudb.com/content/client-tool/xgconsole

Examples of connecting to a database and executing a command.

```shell
[root@localhost console]#./xgconsole -s nssl -h 127.0.0.1 -P 5138 -d SYSTEM -u SYSDBA -p SYSDBA

[root@localhost console]#./xgconsole -s nssl -h 127.0.0.1 -P 5138 -d SYSTEM -u SYSDBA -p SYSDBA -e "select 1 from dual ; "
```

--------------------------------

### BIT_AND example with table creation, insert, and select

Source: https://docs.xugudb.com/content/reference/function/aggregate-functions/bit_and

Demonstrates creating a table with a VARBIT column, inserting bit string values, and selecting the BIT_AND result. The output shows the aggregated bitwise AND result.

```sql
SQL> CREATE TABLE tab_bit_and(c1 VARBIT(7));

SQL> INSERT INTO tab_bit_and VALUES(b'1010101')(b'1011100')(b'0011100');

SQL> SELECT BIT_AND(c1) FROM tab_bit_and;

+------------+
|   EXPR1    |
+------------+
| b'0010100' |
+------------+
```

--------------------------------

### Get Job Next Runtime

Source: https://docs.xugudb.com/content/reference/object/job

Example of querying the next scheduled runtime for a job.

```sql
SQL> SELECT DBMS_SCHEDULER.JOB_NEXT_RUNTIME('job_doc','1970-01-01 00:00:00');

+--------------------------+
|          EXPR1           |
+--------------------------+
| 2025-06-18 13:42:13.000  |
+--------------------------+
```

--------------------------------

### Example: Get the next Sunday

Source: https://docs.xugudb.com/content/reference/function/date-and-time-functions/next_day

Retrieves the date of the nearest Sunday following the specified date.

```sql
-- 获取指定日期距离最近的星期日的日期
SQL> SELECT NEXT_DAY('2025-06-19 02:00:00',1) FROM dual;

+--------------------------+
|          EXPR1           |
+--------------------------+
| 2025-06-22 02:00:00.000  |
+--------------------------+
```

--------------------------------

### Create Table and Query Column Information

Source: https://docs.xugudb.com/content/reference/system-view/all/all_columns

This example demonstrates creating a custom object type and a table, then querying the ALL_COLUMNS view to inspect the column definitions for the newly created table.

```sql
SQL> CREATE TYPE type_object AS OBJECT(c1 INT, c2 VARCHAR);
     /

SQL> CREATE TABLE col_info(id INT,name CHAR,col1 VARCHAR(10),col2 NUMERIC(32,6),col3 INTERVAL YEAR TO MONTH,col4 type_object); 

SQL> SELECT t1.* FROM ALL_COLUMNS t1 JOIN ALL_TABLES t2 ON t1.db_id = t2.db_id and t1.table_id=t2.table_id and t2.table_name='col_info';
```

--------------------------------

### 初始化 trust.ini 配置

Source: https://docs.xugudb.com/content/reference/system-configuration-parameter/trust

使用 --init-setup 参数生成的初始 trust.ini 配置文件内容。

```sh
#Trusted db user ip setup:
# trust(or untrust)db_name(or everydb)user_name(or everyone) from ip1 to ip2 
  trust everydb everyone from anywhere
```

--------------------------------

### NEXTVAL example

Source: https://docs.xugudb.com/content/reference/object/sequence

Creates a sequence and uses NEXTVAL to get the next value. Shows the expected output of 100.

```sql
-- 创建序列值
SQL> CREATE SEQUENCE seq_test2 MINVALUE 1 MAXVALUE 1000 START WITH 100 INCREMENT BY 1;

-- 获取序列的下一个值
SQL> SELECT seq_test2.NEXTVAL FROM DUAL;
+-------+
| EXPR1 |
+-------+
| 100   |
+-------+

```

--------------------------------

### Create Database with Custom Configurations

Source: https://docs.xugudb.com/content/reference/system-dictionary/system-heap-table/others/sys_databases

Examples of creating databases with specific character sets, time zones, encryption settings, and comments, followed by a summary query.

```sql
-- 创库时带字符集
SQL> CREATE DATABASE db_gb18030 CHAR SET 'GB18030';

--  创库时带时区
SQL> CREATE DATABASE db_timezone TIME ZONE 'GMT+00:00';

-- 创库时带加密机
-- syssso用户登录，创建加密机
SQL> CREATE ENCRYPTOR 'encryptor_d1' BY 'encryptor_d1';
-- sysdba用户登录
SQL> CREATE DATABASE DB_ENCRY ENABLE encrypt;
-- syssso用户登录
SQL> USE DB_ENCRY;
Database switch to DB_ENCRY
SQL> ENCRYPT DATABASE BY 'encryptor_d1' cascade;

--sysdba用户登录
-- 用户库添加备注
SQL> CREATE DATABASE db_comment;
SQL> COMMENT ON DATABASE db_comment IS 'this is a db with comments';

-- 查询所有库
SQL> SELECT DB_ID, DB_NAME, USER_ID, CHAR_SET, TIME_ZONE, ENABLE_ENCRY, ENCRY_ID FROM SYS_DATABASES;
+-------+-------------+---------+----------------------+-----------+--------------+----------+
| DB_ID |   DB_NAME   | USER_ID |       CHAR_SET       | TIME_ZONE | ENABLE_ENCRY | ENCRY_ID |
+-------+-------------+---------+----------------------+-----------+--------------+----------+
| 1     | SYSTEM      | -1      | UTF8.UTF8_GENERAL_CI | GMT+08:00 | F            | 0        |
| 16    | DB_GB18030  | 1       | GB18030              | GMT+08:00 | F            | 0        |
| 17    | DB_TIMEZONE | 1       | GBK                  | GMT+00:00 | F            | 0        |
| 19    | DB_COMMENT  | 1       | GBK                  | GMT+08:00 | F            | 0        |
| 20    | DB_ENCRY    | 1       | GBK                  | GMT+08:00 | T            | 2        |
+-------+-------------+---------+----------------------+-----------+--------------+----------+
```

--------------------------------

### Get Current System Time

Source: https://docs.xugudb.com/content/reference/function/date-and-time-functions/sysdatetime

Example of retrieving the current system date and time using the SYSDATETIME function.

```sql
-- 获取系统当前时间。
SQL> SELECT SYSDATETIME() FROM dual;

+--------------------------+
|          EXPR1           |
+--------------------------+
| 2025-07-01 17:50:25.106  |
+--------------------------+
```

--------------------------------

### TO_BASE64 Usage Example

Source: https://docs.xugudb.com/content/reference/function/encoding-decoding-functions/to_base64

Example showing how to convert a string to binary and then encode it using base64.

```sql
-- 将字符串'123'转化为二进制后按base64编码规则进行编码
SQL> SELECT TO_BASE64(cast('123' as binary));

+-------+
| EXPR1 |
+-------+
| MTIz  |
+-------+
```

--------------------------------

### Example: Create table, insert data, backup, and query backup file type

Source: https://docs.xugudb.com/content/reference/function/system-management-functions/backfile_type

Demonstrates creating a table, inserting data, backing up the table, and using BACKFILE_TYPE to query the backup file type. The output shows 'tab_exp' for a table-level logical backup file.

```sql
SQL> CREATE TABLE tab_backup2(c1 INT,c2 VARCHAR);

SQL> INSERT INTO tab_backup2 VALUES (1, 'test1'), (2, 'test2'), (3, 'test3'), (4, 'test4');

SQL> BACKUP TABLE tab_backup2 TO '/BACKUP/TAB_BACKUP.EXP';

-- 查询表级备份文件的类型
SQL> SELECT BACKFILE_TYPE('/BACKUP/TAB_BACKUP.EXP');

+---------+
|  EXPR1  |
+---------+
| tab_exp |
+---------+
```

--------------------------------

### ARRAY_POSITION examples with output

Source: https://docs.xugudb.com/content/reference/function/array-functions/array_position

Two example queries showing ARRAY_POSITION usage. The first searches from the default index 1 and returns 2; the second starts from index 3 and returns 8. Both use a one-dimensional array of weekdays.

```sql
-- 从默认下标1开始查找指定元素`'mon'`第一次出现的位置
SQL> SELECT ARRAY_POSITION(ARRAY['sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat'], 'mon');
+-------+
| EXPR1 |
+-------+
| 2     |
+-------+

-- 从下标3开始查找指定元素`'mon'`第一次出现的位置
SQL> SELECT ARRAY_POSITION(ARRAY['sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat', 'mon'], 'mon', 3);
+-------+
| EXPR1 |
+-------+
| 8     |
+-------+
```

--------------------------------

### NCIAttrGet usage example with NCIParamGet

Source: https://docs.xugudb.com/content/development/nci/ref

Example showing how to retrieve parameter attributes using NCIAttrGet. It first calls NCIParamGet to get a parameter descriptor, then uses NCIAttrGet to fetch data type, column name, data size, and type name attributes. The example declares variables for the attribute values and lengths.

```C
NCIParam* para;\nret = NCIParamGet(stmt, NCI_HTYPE_STMT, err, &para, 1);\ub2 dataType;\ntext* colName;\nub4 dataSize;\ntext* typeName;\nub4 colNameLen;\nub4 typeNameLen;\nNCIAttrGet(para, NCI_DTYPE_PARAM, &dataType, NULL, NCI_ATTR_DATA_TYPE, err);\nNCIAttrGet(para, NCI_DTYPE_PARAM, &colName, &colNameLen, NCI_ATTR_NAME, err);\nNCIAttrGet(para, NCI_DTYPE_PARAM, &dataSize, NULL, NCI_ATTR_DATA_SIZE, err);\nNCIAttrGet(para, NCI_DTYPE_PARAM, &typeName, &typeNameLen, NCI_ATTR_TYPE_NAME, err);
```

--------------------------------

### Connect Method Example

Source: https://docs.xugudb.com/content/development/nodejs

Example of connecting to the database after creating the connection object.

```javascript
const xugu = require("./xugudbjs");
let conn = xugu.createConnection("IP=127.0.0.1;Port=5138;Database=SYSTEM;USER=SYSDBA;PWD=SYSDBA;char_set=utf8");
conn.connect();
```

--------------------------------

### Get Current System Time

Source: https://docs.xugudb.com/content/reference/function/date-and-time-functions/localtime

Example of using the LOCALTIME function to retrieve the current system time from the dual table.

```sql
-- 获取当前系统时间
SQL> SELECT LOCALTIME() FROM dual;

+--------------------------+
|          EXPR1           |
+--------------------------+
| 2025-06-19 11:14:14.699  |
+--------------------------+
```

--------------------------------

### cursor.getResultcolscale(int colno)

Source: https://docs.xugudb.com/content/development/python

Gets the scale of the specified column in the current result set. The column number starts from 1.

```APIDOC
## cursor.getResultcolscale(int colno)

### Description
Gets the scale of the specified column in the current result set.

### Method
Python method call

### Endpoint
cursor.getResultcolscale(int colno)

### Parameters
#### Path Parameters
- **colno** (int) - Required - Specifies the column number, starting from 1.

### Return Value
Returns the scale of the specified column.

### Example
```python
import xgcondb
conn = xgcondb.connect(host="192.168.2.216",port="5138",database="SYSTEM",user="SYSDBA", password="SYSDBA", charset='utf8')

cur = conn.cursor()
cur.execute("select 1 as col1 from dual")
print(cur.getResultcolscale(1))
# 0
```
```