API Integraiton - SQL
SQL can be executed from within a function executed by Avgidea Function (adpy API document) to manipulate tables (DDL), update data (DML), and execute queries (SELECT).
Executing SQL (DDL)
resp = adpy.execute(name='orcl', statement='create table simple(id varchar2(64), num number, dt date)')
Function
execute
Parameters
name : Datasource name of the database to connect to
statement : DDL statement to execute (CREATE TABLE, DROP TABLE, TRUNCATE TABLE, etc.)
Return value
resp : DDL statement status and message
Executing SQL (DML)
sess = {
'NLS_DATE_FORMAT': 'YYYY/MM/DD HH:MI:SS'
}
resp = adpy.execute(name='orcl', statement='insert into simple values(:1, :2, SYSDATE)', args=['abcde', 1], session=sess)
Function
execute
Parameters
name : Datasource name of the database to connect to
statement : DML statements to execute (INSERT, UPDATE, DELETE, etc.)
Option
args : Binding variables
session : Parameters that affect the database session
NLS_DATE_FORMAT : Date format when inserting or updating data in Oracle
Return value
resp : DML statement status and message
Executing SQL (SELECT)
sess = {
'DATE_FORMAT' : '2006/01/02 15:04:05'
}
resp = adpy.query(name='orcl', querystr='select * from simple where id = :1', args=['abcde'],session=sess)
Funciton
query
Parameters
name : Datasource name of the database to connect to
querystr : SELECT statement to be executed
オプション
args : Binding variables
session : Parameters that affect the database session
DATE_FORMAT : Date format for data extraction
Return value
resp : SELECT statement status and JSON format records