Connect to Damon database in QT Create OCI mode

Connect to Dameng database in QT OCI mode

1, Background

The user uses QT Create to develop the integration software OCI to connect to the database. And adaptation Center https://eco.dameng.com/docs/zh-cn/app-dev/index.html There are no relevant examples, and the examples on the official website are old, so share them.

It should be noted that I am not a developer, so it is inevitable that I have some low-level operations in the thinking or use of C + + code. During debugging, I also refer to the posts sent by many friends on the Internet. If there are errors or modification suggestions, please correct them. thank you!

environment

Operating system: Window 10 64 bit

Database version: DM8 1.2.18

Development tool: QT 5.9.0 MinGw 5.3.0 32bit

OCI Library: the C compiler in QT environment is 32-bit, and the existing OCI64 bit is not matched. A 32-bit package needs to be applied separately. The version used this time is dm8_20211026_x86_win_32_ent_8.1.2.84_dmdci

It mainly shares QT Create software and uses OCI of C language to connect to Dameng database

2, Initialize environment

1. Create a new test SQL

DROP TABLE "SYSDBA"."PERSON" CASCADE;
 
CREATE TABLE "SYSDBA"."PERSON"
(
 "PERSONID" INT IDENTITY(1,1) NOT NULL,
 "SEX" CHAR(1) NOT NULL,
 "NAME" VARCHAR(50) NOT NULL,
 "EMAIL" VARCHAR(50) NULL,
 "PHONE" VARCHAR(25) NULL,
  CLUSTER PRIMARY KEY("PERSONID") ENABLE 
);
 
SET IDENTITY_INSERT "SYSDBA"."PERSON" ON;
INSERT INTO "SYSDBA"."PERSON"("PERSONID","SEX","NAME","EMAIL","PHONE") VALUES(1,'F','Li Li','lily@sina.com','02788548562');
INSERT INTO "SYSDBA"."PERSON"("PERSONID","SEX","NAME","EMAIL","PHONE") VALUES(2,'M','Wang Gang','','02787584562');
INSERT INTO "SYSDBA"."PERSON"("PERSONID","SEX","NAME","EMAIL","PHONE") VALUES(3,'M','Li Yong','','02782585462');
INSERT INTO "SYSDBA"."PERSON"("PERSONID","SEX","NAME","EMAIL","PHONE") VALUES(4,'F','Guo Yan','','02787785462');
INSERT INTO "SYSDBA"."PERSON"("PERSONID","SEX","NAME","EMAIL","PHONE") VALUES(5,'F','Sun Li','','13055173012');
SET IDENTITY_INSERT "SYSDBA"."PERSON" OFF;
 
COMMIT;

2. New project

  1. Open Qt Creator software -- > new project -- > rmpty qmake project

  1. Create a new project name and specify a directory. (note that the directory must not contain Chinese)

It is recommended to keep consistent with mine, especially to create paths, otherwise in Section 4 The relative path of pro file is wrong, so the file cannot be found.

  1. Determine the software and C compiler versions

3. Copy OCI Library

Copy the contents of the dmoci directory in the oci package to C:\Windows\SysWOW64

In my local words, D: \ DM8_ 20211026_ x86_ win_ 32_ ent_ 8.1.2.84_ Copy dmdci \ dmoci to C:\Windows\SysWOW64

explain

The 32-bit DLL is in the C:\Windows\SysWOW64 directory

The of 64 bit DLL is in C:\Windows\System32 directory

Note: during copying, two files will prompt whether to overwrite. Select no here

3, Debugging environment

1. Create a new master file

  1. To project – right click new add new file

  1. Create a new C file

  1. Edit name

  1. The directory structure has been changed after the next step

2. Debugging code

Put the following code into ocionnect Cpp file

/************************************************************************/
/* DCI Programming example (changed from DCI manual example)									*/
/************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <malloc.h>
#include "DCI.h"

// Declaration handle
DCIEnv* envhp;				/* Environment Handles  */
DCISvcCtx* svchp;			/* Service environment handle */
DCIServer* srvhp;			/* Server handle */
DCISession* authp;			/* conversation handle  */
DCIStmt* stmthp;			/* Statement Handle  */
DCIDescribe* dschp;			/* Description handle */
DCIError* errhp;			/* Error handle */
DCIDefine* defhp[3];		/* Define handle */
DCIBind* bidhp[4];			/* binding handle  */
sb2 ind[3];					/* indicator variable  */

// Bind parameters of the select result set
text szpersonid[11];		/* Store personid column */
text szsex[2];				/* Store sex columns */
text szname[51];			/* Store name column */
text szemail[51];			/* Store mail columns */
text szphone[26];			/* Store phone columns */
char sql[256];				/* Store executed sql statements */


int main(int argc, char* argv[])
{
    char strServerName[50];
    char strUserName[50];
    char strPassword[50];
    int ret;
    text errbuf[100];

    // Set server, user name and password
    strcpy(strServerName, "localhost");
    strcpy(strUserName, "SYSDBA");
    strcpy(strPassword, "SYSDBA");

    // Initialize OCI application environment
    OCIInitialize(OCI_DEFAULT, NULL, NULL, NULL, NULL);

    // Initialize environment handle
    OCIEnvInit(&envhp, OCI_DEFAULT, 0, 0);

    // Allocation handle
    OCIHandleAlloc(envhp, (dvoid**)&svchp, OCI_HTYPE_SVCCTX, 0, 0);			/* Server environment handle */
    OCIHandleAlloc(envhp, (dvoid**)&srvhp, OCI_HTYPE_SERVER, 0, 0);			/* Server handle */
    OCIHandleAlloc(envhp, (dvoid**)&authp, OCI_HTYPE_SESSION, 0, 0);		/* conversation handle  */
    OCIHandleAlloc(envhp, (dvoid**)&errhp, OCI_HTYPE_ERROR, 0, 0);			/* Error handle */
    OCIHandleAlloc(envhp, (dvoid**)&dschp, OCI_HTYPE_DESCRIBE, 0, 0);		/* Descriptor handle  */

    // Connect server
    OCIServerAttach(srvhp, errhp, (text*)strServerName,
        (sb4)strlen(strServerName), OCI_DEFAULT);

    // Set user name and password
    OCIAttrSet(authp, OCI_HTYPE_SESSION, (text*)strUserName,
        (ub4)strlen(strUserName), OCI_ATTR_USERNAME, errhp);
    OCIAttrSet(authp, OCI_HTYPE_SESSION, (text*)strPassword,
        (ub4)strlen(strPassword), OCI_ATTR_PASSWORD, errhp);

    // Set server environment handle properties
    OCIAttrSet((dvoid*)svchp, (ub4)OCI_HTYPE_SVCCTX,
        (dvoid*)srvhp, (ub4)0, OCI_ATTR_SERVER, errhp);
    OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, (dvoid*)authp, 0,
        OCI_ATTR_SESSION, errhp);

    // Create and start a user session
    OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, OCI_DEFAULT);
    OCIHandleAlloc(envhp, (dvoid**)&stmthp, OCI_HTYPE_STMT, 0, 0);			/* Statement Handle  */

        /************************************************************************/
        /* Query person table															*/
        /************************************************************************/

    strcpy(sql, "select personid, name, phone from sysdba.person");

    // Prepare SQL statement
    OCIStmtPrepare(stmthp, errhp, (text*)sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);

    // Bind output column
    OCIDefineByPos(stmthp, &defhp[0], errhp, 1, (ub1*)szpersonid,
        sizeof(szpersonid), SQLT_STR, &ind[0], 0, 0, OCI_DEFAULT);
    OCIDefineByPos(stmthp, &defhp[1], errhp, 2, (ub1*)szname,
        sizeof(szname), SQLT_STR, &ind[1], 0, 0, OCI_DEFAULT);
    OCIDefineByPos(stmthp, &defhp[2], errhp, 3, (ub1*)szphone,
        sizeof(szphone), SQLT_STR, &ind[2], 0, 0, OCI_DEFAULT);

    // Execute SQL statement
    ret = OCIStmtExecute(svchp, stmthp, errhp, (ub4)0, 0, NULL, NULL, OCI_DEFAULT);
    if (ret != 0)
    {
        OCIErrorGet(errhp, 1, NULL, &ret, (OraText*)errbuf, sizeof(errbuf), OCI_HTYPE_ERROR);
        printf("\n%s\n", errbuf);
    }
    printf("%-10s%-10s%-10s\n", "PERSONID", "NAME", "PHONE");
    while ((OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)) != OCI_NO_DATA)
    {
        printf("%-10s", szpersonid);
        printf("%-10s", szname);
        printf("%-10s\n", szphone);
    }

    /************************************************************************/
    /* Insert a piece of data into the person table												*/
    /************************************************************************/

    memset(sql, 0, sizeof(sql));
    strcpy(sql, "insert into sysdba.person(sex, name, email, phone) values(:sex,:name,:email,:phone)");

    // Prepare SQL statement
    OCIStmtPrepare(stmthp, errhp, (text*)sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);

    // Set input parameters
    memset(szsex, 0, sizeof(szsex));
    memcpy(szsex, "M", strlen("M") + 1);
    memset(szname, 0, sizeof(szname));
    memcpy(szname, "Zhang San", strlen("Zhang San") + 1);
    memset(szemail, 0, sizeof(szemail));
    memcpy(szemail, "zhangsan@dameng.com", strlen("zhangsan@dameng.com") + 1);
    memset(szphone, 0, sizeof(szphone));
    memcpy(szphone, "02712345678", strlen("02712345678") + 1);

    // Bind input column
    const OraText col_sex[] = ":sex";
    const OraText col_name[] = ":name";
    const OraText col_email[] = ":email";
    const OraText col_phone[] = ":phone";
    OCIBindByName(stmthp, &bidhp[0], errhp, col_sex, 4, szsex,
        strlen((char*)szsex), SQLT_AFC, NULL, NULL, NULL, 0, NULL, 0);
    OCIBindByName(stmthp, &bidhp[1], errhp, col_name, 5, szname,
        strlen((char*)szname), SQLT_AFC, NULL, NULL, NULL, 0, NULL, 0);
    OCIBindByName(stmthp, &bidhp[2], errhp, col_email, 6, szemail,
        strlen((char*)szemail), SQLT_AFC, NULL, NULL, NULL, 0, NULL, 0);
    OCIBindByName(stmthp, &bidhp[3], errhp, col_phone, 6, szphone,
        strlen((char*)szphone), SQLT_AFC, NULL, NULL, NULL, 0, NULL, 0);

    // Execute SQL statement
    ret = OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0,
        (CONST OCISnapshot*) 0, (OCISnapshot*)0, (ub4)OCI_DEFAULT);
    if (ret != 0)
    {
        OCIErrorGet(errhp, 1, NULL, &ret, (OraText*)errbuf, sizeof(errbuf), OCI_HTYPE_ERROR);
        printf("\n%s\n", errbuf);
    }
    else
    {
        printf("\n Added name = Zhang San's record.\n");
    }

    // Submit to database
    OCITransCommit(svchp, errhp, OCI_DEFAULT);


    /************************************************************************/
    /* Update person table															*/
    /************************************************************************/

    memset(sql, 0, sizeof(sql));
    strcpy(sql, "update sysdba.person set sex='M',name='Liuhuan',email='12345678@qq.com',phone='13399990000' WHERE personid='1'");

    // Prepare SQL statement
    OCIStmtPrepare(stmthp, errhp, (text*)sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);

    // Execute SQL statement
    ret = OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0,
        (CONST OCISnapshot*)0, (OCISnapshot*)0, (ub4)OCI_DEFAULT);
    if (ret != 0)
    {
        OCIErrorGet(errhp, 1, NULL, &ret, (OraText*)errbuf, sizeof(errbuf), OCI_HTYPE_ERROR);
        printf("\n%s\n", errbuf);
    }
    else
    {
        printf("\n Updated personid = 1 Record of.\n");
    }

    // Submit to database
    OCITransCommit(svchp, errhp, OCI_DEFAULT);


    /************************************************************************/
    /* To delete the record with ID 5 in the person table, you must first exist the record in the database				*/
    /************************************************************************/

    memset(sql, 0, sizeof(sql));
    strcpy(sql, "delete from sysdba.person WHERE personid=:1");

    // Prepare SQL statement
    OCIStmtPrepare(stmthp, errhp, (text*)sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);

    // Binding input parameters
    memset(szpersonid, 0, sizeof(szpersonid));
    memcpy(szpersonid, "5", strlen("5") + 1);
    OCIBindByPos(stmthp, &bidhp[0], errhp, 1, szpersonid, strlen((char*)szpersonid),
        SQLT_AFC, NULL, NULL, NULL, 0, NULL, 0);

    // Execute SQL statement
    ret = OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0,
        (CONST OCISnapshot*) 0, (OCISnapshot*)0, (ub4)OCI_DEFAULT);
    if (ret != 0)
    {
        OCIErrorGet(errhp, 1, NULL, &ret, (OraText*)errbuf, sizeof(errbuf), OCI_HTYPE_ERROR);
        printf("\n%s\n", errbuf);
    }
    else
    {
        printf("\n Deleted personid = 5 Record of.\n");
    }

    // Submit to database
    OCITransCommit(svchp, errhp, OCI_DEFAULT);


    /************************************************************************/
    /* Query the person table again														*/
    /************************************************************************/

    strcpy(sql, "select personid, name, phone from sysdba.person");

    // Prepare SQL statement
    OCIStmtPrepare(stmthp, errhp, (text*)sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);

    // Bind output column
    OCIDefineByPos(stmthp, &defhp[0], errhp, 1, (ub1*)szpersonid,
        sizeof(szpersonid), SQLT_STR, &ind[0], 0, 0, OCI_DEFAULT);
    OCIDefineByPos(stmthp, &defhp[1], errhp, 2, (ub1*)szname,
        sizeof(szname), SQLT_STR, &ind[1], 0, 0, OCI_DEFAULT);
    OCIDefineByPos(stmthp, &defhp[2], errhp, 3, (ub1*)szphone,
        sizeof(szphone), SQLT_STR, &ind[2], 0, 0, OCI_DEFAULT);

    // Execute SQL statement
    ret = OCIStmtExecute(svchp, stmthp, errhp, (ub4)0, 0, NULL, NULL, OCI_DEFAULT);
    if (ret != 0)
    {
        OCIErrorGet(errhp, 1, NULL, &ret, (OraText*)errbuf, sizeof(errbuf), OCI_HTYPE_ERROR);
        printf("\n%s\n", errbuf);
    }
    printf("\n%-10s%-10s%-10s\n", "PERSONID", "NAME", "PHONE");
    while ((OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)) != OCI_NO_DATA)
    {
        printf("%-10s", szpersonid);
        printf("%-10s", szname);
        printf("%-10s\n", szphone);
    }


    //End session
    OCISessionEnd(svchp, errhp, authp, (ub4)0);

    //Disconnect from database
    OCIServerDetach(srvhp, errhp, OCI_DEFAULT);

    //Release OCI handle
    OCIHandleFree((dvoid*)dschp, OCI_HTYPE_DESCRIBE);
    OCIHandleFree((dvoid*)stmthp, OCI_HTYPE_STMT);
    OCIHandleFree((dvoid*)errhp, OCI_HTYPE_ERROR);
    OCIHandleFree((dvoid*)authp, OCI_HTYPE_SESSION);
    OCIHandleFree((dvoid*)svchp, OCI_HTYPE_SVCCTX);
    OCIHandleFree((dvoid*)srvhp, OCI_HTYPE_SERVER);
    system("pause");
    return 0;
}

3. Add Headers file

  1. Right click Headers – to display in Explorer

  1. Move the two files in the Include directory of OCi to the directory of the project

  1. Right click Add existing file to DCI H add in

  1. Note the file path DCI H has been introduced by this time

4. Modification pro file

  1. Click on the item pro file – > add library file

  1. Click external library

  1. Check oci32-bit lib library

  1. You'll find out pro file, the following appears

  1. Keep the contents of include and DEPENDPATH. And make adjustments

explain

  1. Add QT and qmake_ The contents of lflags and LIBS variables.
  2. QMAKE_LFLAGS is the decompression path of my oci, which needs to be replaced with its own
HEADERS += \
    oci_connect.h \
    DCI.h

SOURCES += \
    oci_connect.cpp

## Add the following
QT += network
QMAKE_LFLAGS=D:\dm8_20211026_x86_win_32_ent_8.1.2.84_dmdci\dmoci\dmoci.dll
INCLUDEPATH += $$PWD/../../dm8_20211026_x86_win_32_ent_8.1.2.84_dmdci/dmoci
DEPENDPATH += $$PWD/../../dm8_20211026_x86_win_32_ent_8.1.2.84_dmdci/dmoci
LIBS += -L$$PWD/../../dm8_20211026_x86_win_32_ent_8.1.2.84_dmdci/dmoci/dmoci.lib

4, Run project

Note: a black window will appear after running. After the window is closed, the following message will appear.

appendix

1. Qt Creator right-click to add library unresponsive solution

You want to add an external library to the project, but clicking Add library has no response

Solution

  1. Open pro file

  1. In the pro file interface, right-click and select add library

  1. Add library UI pop up

  1. summary
    Qt 5.9.0 Creator add library UI pop-up bug

Keywords: Database Qt DM8

Added by phpnewbie81 on Thu, 20 Jan 2022 04:10:39 +0200

Popular Keywords

©2024 Programming VIP