Why Migrate from Btrieve to PostgreSQL and other Relational Databases?
Introduction Many independent software vendors (ISV) and corporate users still rely on applications that use a category of database collective called...
ESQL is a powerful resource you can use with your DataFlex application using Mertech Drivers. You can, for example, change a loop to update a table using Lock, SaveRecord and Unlock to a simple ESQL block.
Example:
sql_set_stmt to 'update customer set balance = 100'
sql_prepare_stmt
sql_execute_stmt
Here is an Oracle SQL Script, sent to us by a Mertech Customer, with uses ORAFlex and helped their developers to write complex ESQL blocks to inserts.
The output script is a DataFlex program using Mertech commands.
The Oracle Script:
accept TABLE prompt 'Table Name: '
DECLARE
v_table_name user_tables.table_name%TYPE := '&&TABLE';
v_ObjName VARCHAR2(8192);
v_result VARCHAR2(80);
CURSOR c_Variables is
select (case
when data_type in ('VARCHAR2','DATE') then ('String s'|| column_name)
when data_type in ('NUMBER') then ('Number n'|| column_name)
else ('String s'|| column_name)
end)
from user_tab_columns
where table_name = v_table_name
order by column_id;
CURSOR c_Fields is
select (case when column_id > 1 then (','||column_name) else column_name end)
from user_tab_columns
where table_name = v_table_name
order by column_id;
CURSOR c_Values is
select
(case
when column_id >1 and data_type in ('VARCHAR2','DATE') then (q'|+",'"+s|'||column_name||q'|+"'"|')
when column_id >1 and data_type in ('NUMBER') then (q'|+","+n|'||column_name)
when column_id =1 and column_name = 'RECNUM' then (q'|s|'||column_name)
when column_id =1 and data_type in ('VARCHAR2','DATE') then (q'|"'"+s|'||column_name||q'|+"'"|')
when column_id =1 and data_type in ('NUMBER') then (q'|n|'||column_name)
else (q'|+",'"+s|'||column_name||q'|+"'"|')
end)
from user_tab_columns
where table_name = v_table_name
order by column_id;
BEGIN
--VARIAVEIS
dbms_output.put_line('// ESQL Variables');
Open c_Variables;
LOOP
FETCH c_Variables into v_result;
EXIT WHEN c_Variables%NOTFOUND;
IF (v_result like '%RECNUM%') THEN
dbms_output.put_line('String sRECNUM');
dbms_output.put_line('Move "'||v_table_name||'_DFSEQ.nextval" to sRECNUM');
ELSE
dbms_output.put_line(v_result);
END IF;
END LOOP;
Close c_Variables;
--INSERT
dbms_output.put_line('');
dbms_output.put_line('// ESQL Code Begin');
dbms_output.put_line('sql_set_stmt to "insert into '||v_table_name||' fields ("');
--FIELDS
v_ObjName := '';
Open c_Fields;
LOOP
FETCH c_Fields into v_result;
EXIT WHEN c_Fields%NOTFOUND;
v_ObjName :=(v_ObjName||v_result);
END LOOP;
dbms_output.put_line('sql_append_stmt to "'||v_ObjName||'"');
Close c_Fields;
-- Abre o Values
dbms_output.put_line('sql_append_stmt to ") values ("');
-- Monta Variaveis para o Values
v_ObjName := '';
Open c_Values;
LOOP
FETCH c_Values into v_result;
EXIT WHEN c_Values%NOTFOUND;
v_ObjName :=(v_ObjName||v_result);
END LOOP;
dbms_output.put_line('sql_append_stmt to ('||v_ObjName||')');
Close c_Values;
-- Fecha o Values
dbms_output.put_line('sql_append_stmt to ")"');
dbms_output.put_line('//------------------------------------------------------');
dbms_output.put_line('// ESQL Code End');
dbms_output.put_line('//------------------------------------------------------');
END;
Output Example Using ORDERHEA Table:
//---------------------------------------------------------
// Variables
//---------------------------------------------------------
String sRECNUM
Move "ORDERHEA_DFSEQ.nextval" to sRECNUM
Number nORDER_NUMBER
Number nCUSTOMER_NUMBER
String sORDER_DATE
String sTERMS
String sSHIP_VIA
String sORDERED_BY
String sSALESPERSON_ID
Number nORDER_TOTAL
Number nLAST_DETAIL_NUM
//---------------------------------------------------------
// Start
//---------------------------------------------------------
sql_set_stmt to "insert into ORDERHEA fields ("
sql_append_stmt to "RECNUM,ORDER_NUMBER,CUSTOMER_NUMBER,ORDER_DATE,TERMS,SHIP_VIA,ORDERED_BY,SALESPERSON_ID,ORDER_TOTAL,LAST_DETAIL_NUM"
sql_append_stmt to ") values ("
sql_append_stmt to (sRECNUM+","+nORDER_NUMBER+","+nCUSTOMER_NUMBER+",'"+sORDER_DATE+"'"+",'"+sTERMS+"'"+",'"+sSHIP_VIA+"'"+",'"+sORDERED_BY+"'"+",'"+sSALESPERSON_ID+"'"+","+nORDER_TOTAL+","+nLAST_DETAIL_NUM)
sql_append_stmt to ")"
//------------------------------------------------------
// End
//------------------------------------------------------
Introduction Many independent software vendors (ISV) and corporate users still rely on applications that use a category of database collective called...
COBOL applications are the foundation of numerous essential business functions, especially within the banking, insurance, and government sectors....
Imagine breaking free from the constraints of old, monolithic systems and embracing the agility and innovation of cloud-based solutions.