Document Type | Technical Information
Category | Tuning
Document Number | TTUTI025
Overview
This guide explains how to safely execute Embedded SQL (Embedded SQL, ESQL) in a multi-threaded environment.
To run Embedded SQL (Embedded SQL) in a multi-threaded environment, compilation options and settings within the source code are required. This allows safe use of ESQL across multiple threads.
Method
To execute Embedded SQL (Embedded SQL, ESQL) in a multi-threaded environment, you must add THREADS=YES to the esql precompile option during compilation.
Also, in the source code, you need to set EXEC SQL ENABLE THREADS; and EXEC SQL CONTEXT USE DEFAULT;.
Example Code 1
#include <stdio.h>
#include <string.h>
void esql_error(int line_no, int err_no, char *file)
{
printf("esql test esql_error at %d : %d in %s\n", line_no, err_no, file);
}
void
main(void)
{
char *uid = "tibero";
char *pwd = "tmax";
int balance = 0;
sql_context ctx1;
sql_context ctx2;
printf("***** thread context test start *****\n\n");
EXEC SQL WHENEVER SQLERROR DO esql_error(__LINE__, sqlca.sqlcode, __FILE__);
EXEC SQL ENABLE THREADS;
EXEC SQL CONTEXT USE DEFAULT;
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
EXEC SQL WHENEVER SQLERROR continue;
EXEC SQL DROP TABLE ACCOUNT;
EXEC SQL CREATE TABLE ACCOUNT (
ACCOUNT_ID CHAR(14),
BRANCH_CD CHAR(4),
CUSTOMER_ID CHAR(13),
ACCOUNT_TYPE CHAR(4),
BALANCE NUMBER(15, 5),
CREATE_DATE DATE,
EXPIRY_DATE DATE
);
EXEC SQL WHENEVER SQLERROR DO esql_error(__LINE__, sqlca.sqlcode, __FILE__);
EXEC SQL INSERT INTO account
VALUES ('ACCOUNT_ID_001', 'B003', 'CUSTOMER_ID_0',
'A002', 500000, '2003-10-5', '2005-10-5');
EXEC SQL INSERT INTO account
VALUES ('ACCOUNT_ID_002', 'B003', 'CUSTOMER_ID_1',
'A001', 10000, '2003-12-25', '2010-12-25');
EXEC SQL INSERT INTO account
VALUES ('ACCOUNT_ID_003', 'B001', 'CUSTOMER_ID_2',
'A003', 50000000, '1998-9-1', '2005-9-1');
EXEC SQL COMMIT WORK;
EXEC SQL CONTEXT ALLOCATE :ctx1;
EXEC SQL CONTEXT ALLOCATE :ctx2;
EXEC SQL CONTEXT USE :ctx1;
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
EXEC SQL SELECT BALANCE INTO :balance FROM ACCOUNT
WHERE ACCOUNT_ID = 'ACCOUNT_ID_001';
if (balance != 500000)
printf("expected : 500000, real : %d\n", balance);
EXEC SQL CONTEXT USE :ctx2;
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
EXEC SQL SELECT BALANCE INTO :balance FROM ACCOUNT
WHERE ACCOUNT_ID = 'ACCOUNT_ID_002';
if (balance != 10000)
printf("expected : 10000, real : %d\n", balance);
EXEC SQL CONTEXT FREE :ctx1;
EXEC SQL CONTEXT FREE :ctx2;
EXEC SQL CONTEXT USE DEFAULT;
EXEC SQL SELECT BALANCE INTO :balance FROM ACCOUNT
WHERE ACCOUNT_ID = 'ACCOUNT_ID_002';
if (balance != 10000)
printf("expected : 10000, real : %d\n", balance);
printf("***** thread context test end *****\n");
}
Example Code 2
int Fn_DRFT_SYNC_Make_LoadFile(char *p_exp_type)
{
FRM_ERR("=========LOAD START==========");
char s_load_path [ 255+1];
char s_load_msg [ 255+1];
char s_file_type [ 3+1];
char s_exp_type [ 1+1]; //EXPORT target TABLE distinction
char *conn_str = "msscm/msscm";
SQLRETURN n_ret = SQL_SUCCESS;
SQLINTEGER versionNumber = 1;
SQLCHAR dataFileName[256];
SQLCHAR actionString[256];
SQLCHAR msgFileName[256];
SQLCHAR fieldTerm[5];
SQLCHAR lineTerm[5];
SQLCHAR enclStart[5];
SQLCHAR enclEnd[5];
struct sqlca ca = {"\0", 0, 0, {0, "\0"}, "\0", {0, 0, 0, 0, 0, 0}, "\0", "\0"};
FRM_ERR("=========LOAD 1==========");
TBExportStruct exportStruct = {NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL};
TBExportIn exportIn = {{NULL, 0, NULL, 0, NULL, 0, NULL, 0, NULL, 0}};
TBExportOut exportOut = {0};
EXEC SQL ENABLE THREADS;
EXEC SQL CONTEXT USE DEFAULT;
EXEC SQL CONNECT :conn_str;
n_ret = TBConnect((SQLCHAR *)DNS_NAME, (SQLCHAR *)USER_NAME, (SQLCHAR *)PWD, &ca);
if(n_ret != SQL_SUCCESS) return -1;
//Distinguish the target TABLE for EXPORT - D:DATA table, S:SUM table
memset (s_exp_type , 0x00 , sizeof(s_exp_type));
strcpy (s_exp_type , p_exp_type);
//Define EXPORT FILE TYPE - Forced DEL type
//memset (s_file_type , 0x00 , sizeof(s_file_type));
//memcpy (s_file_type , "DEL" , sizeof(s_file_type)-1);
FRM_ERR("=========LOAD 2==========");
//Set EXPORT SQL
memset (g_export_sql, 0x00 , sizeof(g_export_sql ));
sprintf(g_export_sql, "SELECT * FROM %s", g_GLB_TABLE);
if ( s_exp_type[0] == 'D' ) {
//Set EXPORT DATA FILE PATH
memset (s_load_path, 0x00, sizeof( s_load_path ) );
sprintf(s_load_path, "%s/%.4s%.8s_%.2s.DEL", DRFT_LOAD_PATH , g_orgcode, g_sysdate,
g_sync_seq );
//Set EXPORT MSG PATH
memset (s_load_msg , 0x00, sizeof( s_load_msg ) );
sprintf(s_load_msg , "%s/%.4s%.8s_%.2s.MSG", DRFT_LOAD_PATH , g_orgcode, g_sysdate,
g_sync_seq );
}
else {
//Set EXPORT FILE PATH
memset (s_load_path, 0x00, sizeof( s_load_path ) );
sprintf(s_load_path,
"%s/%.4s%.8s_SUM_%.2s.DEL",
g_sysdate, g_sync_seq );
//Set EXPORT MSG PATH
memset (s_load_msg , 0x00, sizeof( s_load_msg ) );
sprintf(s_load_msg
,
DRFT_LOAD_PATH
,
g_orgcode,
"%s/%.4s%.8s_SUM_%.2s.MSG", DRFT_LOAD_PATH , g_orgcode,
g_sysdate, g_sync_seq );
}
strcpy((char *)dataFileName, s_load_path);
strcpy((char *)actionString, g_export_sql);
strcpy((char *)msgFileName, s_load_msg);
strcpy((char *)fieldTerm, ",");
strcpy((char *)lineTerm, "\n");
strcpy((char *)enclStart, "\"");
strcpy((char *)enclEnd, "\"");
FRM_ERR("=========LOAD 3==========");
/* setting data file name */
exportStruct.piDataFileName = dataFileName;
exportStruct.iDataFileNameLen = strlen((char *)dataFileName);
FRM_ERR("=========LOAD 4==========");
/* setting action String */
// exportStruct.piActionString = mfrm_cb_malloc(1024+sizeof(sqlstr));
exportStruct.piActionString = (struct sqlchar *)mfrm_cb_malloc( 1024 + sizeof( struct
sqlchar));
exportStruct.piActionString-data = actionString;
exportStruct.piActionString-length = strlen((char *)actionString);
/* setting file type : s_file_type DEL forced setting */
exportStruct.iFileType = SQL_DEL;
FRM_ERR("=========LOAD 5==========");
/* setting message file name */
exportStruct.piMsgFileName = msgFileName;
exportStruct.iMsgFileNameLen = strlen((char *)msgFileName);
/* setting field term, line term etc.. Needs to be set according to SQL_METH_D. */
exportIn.iMeta.fieldTerm = fieldTerm;
exportIn.iMeta.fieldTermLen = strlen((char *)fieldTerm);
exportIn.iMeta.lineTerm = lineTerm;
exportIn.iMeta.lineTermLen = strlen((char *)lineTerm);
exportIn.iMeta.enclStart = enclStart;
exportIn.iMeta.enclStartLen = strlen((char *)enclStart);
exportIn.iMeta.enclEnd = enclEnd;
exportIn.iMeta.enclEndLen = strlen((char *)enclEnd);
FRM_ERR("=========LOAD 6==========");
/* setting export input, output information */
exportStruct.piExportInfoIn = &exportIn;
exportStruct.poExportInfoOut = &exportOut;
FRM_ERR("=========LOAD 7==========");
FRM_ERR("n_ret : %d", n_ret);
/* setting file type */
n_ret = TBExport(versionNumber, &exportStruct, &ca);
FRM_ERR("=========LOAD 8==========");
if (n_ret != SQL_SUCCESS) {
FRM_ERR("[%s]EXPORT
ERROR!!![%d][%d]\n[%s]",
s_load_path,
n_ret,
SQLCODE,
g_export_sql );
g_sqlcode = SQLCODE;
return -1;
}
FRM_ERR("=========LOAD 9==========");
FRM_DSP("[%.4s][%.8s][%.2s][ROWS EXPORT]:[%ld]", g_orgcode, g_sysdate, g_sync_seq,
exportOut.oRowsExported );
/* disconnect */
n_ret = TBDisconnect((SQLCHAR *)DNS_NAME, &ca);
if(n_ret != SQL_SUCCESS) return -1;
EXEC SQL COMMIT WORK RELEASE;
return 0;
}
===========================================================
EXEC SQL ENABLE THREADS;
EXEC SQL CONTEXT USE DEFAULT;
EXEC SQL CONNECT :"[userid]/[pw]";