This content will further demonstrate Halo's support for Oracle based on the Oracle parsing engine, performing simple basic operations on the Halo database by directly calling the Oracle Call Interface (OCI), and making a simple comparison with Oracle. (Full code at the end)
## Preparation Phase
There is a scott user in the Oracle database for familiarizing with database operations. We can also create a scott user in the Halo database and grant relevant permissions for learning:
create role scott password 'tiger' superuser;
alter role scott login;Import the OCI driver we provided into the Linux system according to the following operations:
1. Copy the libhalooci.so.10.2, libiconv.so.2, and libpq.so.5.11 files to the /usr/lib directory.
2. Create related soft links:
ln -s /usr/lib/libhalooci.so.10.2 /usr/lib/libhalooci.so
ln -s /usr/lib/libiconv.so.2 /usr/lib/libiconv.so
ln -s /usr/lib/libpq.so.5.11 /usr/lib/libpq.so
ln -s /usr/lib/libpq.so.5.11 /usr/lib/libpq.so.53. Prepare two virtual machines, one to start the Halo database service and the other to start the Oracle database service. Simply modify the halo_demo.c code. Since we previously created the scott user, we only need to change the database instance name here. Halo has a default halo0root database, so we can directly use this default database. Oracle can map the database instance name with the one in the configuration file.
text username = (text)"scott"; // Username
text passwd = (text)"tiger"; // User password
text server = (text)"//localhost:1921/halo0root"; // Database instance name4. Compile the example code, generate an executable file, and run it:
gcc halo_demo.c -o halo_demo -I ${ORACLE_HOME} -L /usr/lib -lhalooci -lpq -liconv -std=c99## Simple Comparison
The example program mainly performs the following operations: create t_test table, insert data, update data, query data, delete data, drop table. The complete execution results are as follows (left is Halo, right is Oracle):
### 1. Create t_test Table
After successful connection, execute the following code to create the t_test table, containing three fields: no, name, and age:
void create_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp)
{
OCIStmt *stmhp;
text *sql_statement =
(text *)"create table t_test \n (no number(10) primary key\n, name varchar2(40)\n, age number(10))";
// ...Full code, please see the end of the article
}Oracle execution result:
Halo execution result:
You can see that the t_test table has been successfully created in the database, and the field structure is consistent.
### 2. Insert Data into t_test Table
Insert three records: {1, "Zhang San", 23}, {2, "Li Si", 23}, {3, "Wang Wu", 21}:
void insert_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp)
{
OCIStmt *stmhp;
text *sql_statement =
(text *)"insert into t_test(no,name,age) values (:no,:name,:age)";
int no[3] = {1, 2, 3};
int age[3] = {23, 23, 21};
char *s_name[3] = {"Zhang San", "Li Si", "Wang Wu"};
// ...
}Oracle execution result:
Halo execution result:
### 3. Retrieve and Print t_test Table Data
void select_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp)
{
OCIStmt *stmhp;
text *sql_statement =
(text *)"select no,name,age from t_test";
// ...
}Oracle execution result:
Halo execution result:
### 4. Update t_test Table Data
Update the age field of the record with no=3 to 99:
void update_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp)
{
OCIStmt *stmhp;
text *sql_statement = (text *)"update t_test set age=:1 where no=:2";
int age = 99;
int id = 3;
// ...
}Oracle execution result:
Halo execution result:
### 5. Delete t_test Table Data
Delete the record with no=3:
void delete_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp)
{
OCIStmt *stmhp;
text *sql_statement =
(text *)"delete from t_test where no=:1";
int id = 3;
// ...
}Oracle execution result:
Halo execution result:
### 6. Drop t_test Table
void drop_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp)
{
OCIStmt *stmhp;
text *sql_statement = (text *)"drop table t_test";
// ...
}Oracle execution result:
Halo execution result:
## Conclusion
From the above comparison, it can be seen that the Halo database has good Oracle compatibility at the OCI interface level, can support common DDL and DML operations, and its behavior is highly consistent with Oracle.
## Issues Encountered
1. **Cannot find oci.h file**: Indicates that the ORACLE_HOME environment variable is not set or the file does not exist.
Solution:
su - root
find / -name oci.h
# If it exists, set the environment variable
export ORACLE_HOME=/path/to/oracle/sdk
# If it does not exist, download the Instant Client SDK from https://www.oracle.com/database/technologies/instant-client/downloads.html, extract it, and set ORACLE_HOME2. **error while loading shared libraries: libXXX.so.X: cannot open shared object file**: Indicates that the dynamic linker cannot find the specified library.
Solution:
echo '/usr/lib' >> /etc/ld.so.conf
ldconfig## Example Code
For more OCI usage, please refer to the official Oracle documentation: https://docs.oracle.com/en/database/oracle/oracle-database/19/lnoci/introduction.html#GUID-27645179-6957-4004-8BB8-38775266B038
#include <stdio.h>
#include <string.h>
#include "oci.h"
sb4 errcode;
text errbuf[512] = {0};
void create_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp)
{
OCIStmt *stmhp;
text *sql_statement =
(text *)"create table t_test \n (no number(10) primary key\n, name varchar2(40)\n, age number(10))";
int ret = OCIHandleAlloc(envhp, (dvoid **)&stmhp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
if (ret != OCI_SUCCESS)
{
return;
}
OCIStmtPrepare(stmhp, errhp, sql_statement, strlen((const char *)sql_statement), OCI_NTV_SYNTAX, OCI_DEFAULT);
ret = OCIStmtExecute(svchp, stmhp, errhp, (ub4)1, (ub4)0, NULL, NULL, OCI_DEFAULT);
if (ret != OCI_SUCCESS)
{
printf("FAILURE IN CREATING TABLE(S)\n");
OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);
printf ("%s", errbuf);
OCIHandleFree(stmhp, OCI_HTYPE_STMT);
return;
}
printf("Table(s) Successfully Creat\n");
OCIHandleFree(stmhp, OCI_HTYPE_STMT);
}
void drop_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp)
{
OCIStmt *stmhp;
text *sql_statement = (text *)"drop table t_test";
int ret = OCIHandleAlloc(envhp, (dvoid **)&stmhp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
if (ret != OCI_SUCCESS)
{
return;
}
OCIStmtPrepare(stmhp, errhp, sql_statement, strlen((const char *)sql_statement), OCI_NTV_SYNTAX, OCI_DEFAULT);
ret = OCIStmtExecute(svchp, stmhp, errhp, (ub4)1, (ub4)0, NULL, NULL, OCI_DEFAULT);
if (ret != OCI_SUCCESS)
{
printf("FAILURE IN DROPING TABLE(S)\n");
OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);
printf ("%s", errbuf);
OCIHandleFree(stmhp, OCI_HTYPE_STMT);
return;
}
printf("Table(s) Successfully Drop\n");
OCIHandleFree(stmhp, OCI_HTYPE_STMT);
}
void insert_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp)
{
OCIStmt *stmhp;
text *sql_statement =
(text *)"insert into t_test(no,name,age) values (:no,:name,:age)";
OCIBind *bind_no = (OCIBind *)0;
OCIBind *bind_name = (OCIBind *)0;
OCIBind *bind_age = (OCIBind *)0;
int no[3] = {1, 2, 3};
int age[3] = {23, 23, 21};
char *s_name[3] = {"Zhang San", "Li Si", "Wang Wu"};
int ret = OCIHandleAlloc(envhp, (dvoid **)&stmhp,OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
if (ret != OCI_SUCCESS)
{
return;
}
OCIStmtPrepare(stmhp, errhp, sql_statement,(ub4)strlen((char *)sql_statement),(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
for (int i = 0; i < 3; i++)
{
OCIBindByName(stmhp, &bind_no, errhp, (text *)":no",
-1, (dvoid *)&no[i],
sizeof(no[i]), SQLT_INT,
(dvoid *)0, 0, (ub2 *)0, (ub4)0,
(ub4 *)0, OCI_DEFAULT);
OCIBindByName(stmhp, &bind_name, errhp, (text *)":name",
-1, (dvoid *)s_name[i],
strlen(s_name[i]) + 1, SQLT_STR, (dvoid *)0, 0,
(ub2 *)0, (ub4)0, (ub4 *)0,
OCI_DEFAULT);
OCIBindByName(stmhp, &bind_age, errhp, (text *)":age",
-1, (dvoid *)&age[i], sizeof(age[i]),
SQLT_INT, (dvoid *)0, 0, (ub2 *)0,
(ub4)0, (ub4 *)0, OCI_DEFAULT);
ret = OCIStmtExecute(svchp, stmhp, errhp, (ub4)1, (ub4)0, NULL, NULL, OCI_DEFAULT);
if (ret != OCI_SUCCESS)
{
printf("FAILURE IN INSERTING DATA\n");
OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);
printf ("%s", errbuf);
OCIHandleFree(stmhp, OCI_HTYPE_STMT);
return;
}
}
OCITransCommit(svchp, errhp, (ub4)0);
printf("Table(s) Successfully Insert \n");
OCIHandleFree(stmhp, OCI_HTYPE_STMT);
}
void select_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp)
{
OCIStmt *stmhp;
OCIDefine *define;
int no = 0;
int age = 0;
char name[40] = {0};
ub4 fetched = 1;
text *sql_statement =
(text *)"select no,name,age from t_test";
int ret = OCIHandleAlloc(envhp, (dvoid **)&stmhp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
if (ret != OCI_SUCCESS)
{
return;
}
OCIStmtPrepare(stmhp, errhp, sql_statement, strlen((const char *)sql_statement), OCI_NTV_SYNTAX, OCI_DEFAULT);
OCIDefineByPos(stmhp, &define, errhp, (ub4)1, &no, sizeof(no), (ub2)SQLT_INT, NULL, 0, 0, OCI_DEFAULT);
OCIDefineByPos(stmhp, &define, errhp, (ub4)2, name, 40, (ub2)SQLT_STR, NULL, 0, 0, OCI_DEFAULT);
OCIDefineByPos(stmhp, &define, errhp, (ub4)3, &age, sizeof(age), (ub2)SQLT_INT, NULL, 0, 0, OCI_DEFAULT);
ret = OCIStmtExecute(svchp, stmhp, errhp,(ub4)1, (ub4)0, NULL, NULL, OCI_DEFAULT);
if (ret == OCI_NO_DATA)
{
fetched = 0;
}else if(ret != OCI_SUCCESS)
{
OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);
printf ("%s", errbuf);
OCIHandleFree(stmhp, OCI_HTYPE_STMT);
return;
}
if (fetched)
{
printf("no\t name\t age\t\r\n");
printf("%d\t %s\t %d\t\r\n", no, name, age);
while (1)
{
ret = OCIStmtFetch(stmhp, errhp,1, OCI_FETCH_NEXT, OCI_DEFAULT);
if (ret == OCI_NO_DATA)
{
break;
}
printf("%d\t %s\t %d\t\r\n", no, name, age);
}
}
printf("Table(s) Successfully Select \n");
OCIHandleFree(stmhp, OCI_HTYPE_STMT);
}
void update_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp)
{
OCIStmt *stmhp;
OCIBind *bindage;
OCIBind *bindid;
text *sql_statement = (text *)"update t_test set age=:1 where no=:2";
int age,id;
int ret = OCIHandleAlloc(envhp, (dvoid **)&stmhp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
if (ret != OCI_SUCCESS)
{
return;
}
OCIStmtPrepare(stmhp, errhp, sql_statement, strlen((const char *)sql_statement), OCI_NTV_SYNTAX, OCI_DEFAULT);
OCIBindByPos(stmhp, &bindage, errhp, (ub4)1, &age, sizeof(age),
SQLT_INT, NULL, NULL,NULL,0, NULL, OCI_DEFAULT);
OCIBindByPos(stmhp, &bindid, errhp, (ub4)2, &id, sizeof(id),
SQLT_INT, NULL, NULL,NULL,0, NULL, OCI_DEFAULT);
age = 99;
id = 3;
ret = OCIStmtExecute(svchp, stmhp, errhp, (ub4)1, (ub4)0, NULL, NULL, OCI_DEFAULT);
if (ret != OCI_SUCCESS)
{
OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);
printf("FAILURE IN UPDATE TABLE(S)\n");
printf ("%s", errbuf);
OCIHandleFree(stmhp, OCI_HTYPE_STMT);
return;
}
OCITransCommit(svchp, errhp, (ub4)0);
printf("Table(s) Successfully Update\n");
OCIHandleFree(stmhp, OCI_HTYPE_STMT);
}
void delete_table(OCISvcCtx *svchp, OCIError *errhp, OCIEnv *envhp)
{
OCIStmt *stmhp;
text *sql_statement =
(text *)"delete from t_test where no=:1";
OCIBind *bindid;
int id;
int ret = OCIHandleAlloc(envhp, (dvoid **)&stmhp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
if (ret != OCI_SUCCESS)
{
return;
}
OCIStmtPrepare(stmhp, errhp, sql_statement, strlen((const char *)sql_statement), OCI_NTV_SYNTAX, OCI_DEFAULT);
OCIBindByPos(stmhp, &bindid, errhp, (ub4)1, &id, sizeof(id),
SQLT_INT, NULL, NULL,NULL,0, NULL, OCI_DEFAULT);
id = 3;
if (OCIStmtExecute(svchp, stmhp, errhp, (ub4)1, (ub4)0, NULL, NULL, OCI_DEFAULT) != OCI_SUCCESS)
{
OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);
printf("FAILURE IN DELETE TABLE(S)\n");
printf ("%s", errbuf);
OCIHandleFree(stmhp, OCI_HTYPE_STMT);
return;
}
OCITransCommit(svchp, errhp, (ub4)0);
printf("Table(s) Successfully Delete\n");
OCIHandleFree(stmhp, OCI_HTYPE_STMT);
}
int main()
{
text username = (text *)"scott";
text passwd = (text *)"tiger";
text server = (text *)"//localhost:1921/halo0root";
/* Environment */
OCIEnv *envhp;
/* Error */
OCIError *errhp;
/* Service Context */
OCISvcCtx *svchp;
int ret = OCIEnvCreate(&envhp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL);
if (ret != OCI_SUCCESS && ret != OCI_SUCCESS_WITH_INFO)
{
return -1;
}
ret = OCIHandleAlloc(envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR, (size_t)0, NULL);
if (ret != OCI_SUCCESS && ret != OCI_SUCCESS_WITH_INFO)
{
OCIHandleFree(envhp, OCI_HTYPE_ENV);
return -1;
}
ret = OCILogon(envhp, errhp, &svchp, username, strlen((const char *)username),
passwd, strlen((const char *)passwd), server, strlen((const char *)server));
if (ret != OCI_SUCCESS && ret != OCI_SUCCESS_WITH_INFO)
{
OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);
printf ("%s", errbuf);
OCIHandleFree(errhp, OCI_HTYPE_ERROR);
OCIHandleFree(envhp, OCI_HTYPE_ENV);
return -1;
}
printf("Logged on as --> '%s'\n", username);
create_table(svchp, errhp, envhp);
insert_table(svchp, errhp, envhp);
select_table(svchp, errhp, envhp);
update_table(svchp, errhp, envhp);
select_table(svchp, errhp, envhp);
delete_table(svchp, errhp, envhp);
select_table(svchp, errhp, envhp);
drop_table(svchp, errhp, envhp);
OCILogoff(svchp,errhp);
OCIHandleFree(errhp, OCI_HTYPE_ERROR);
OCIHandleFree(envhp, OCI_HTYPE_ENV);
return 0;
}