Its better to use the SET OPTION SQL statement. It allows you to enforce certain program options in the code itself rather than on the compile command which you might forget to do.
c/exec sql c+ set option commit=*none, c+ datfmt=*iso c/end-exec
Sample SQLRPGLE Program to Create Table on iSeries(AS400)
/free //Set SQL options exec sql SET OPTION commit=*none, datfmt=*iso; //Create the SQL table exec sql CREATE TABLE QGPL/MYDATA ( COMPANY FOR COLUMN D1CMP DEC ( 2, 0) NOT NULL WITH DEFAULT 0, EMPLOYEE_ID FOR COLUMN D1ID DEC ( 5, 0) NOT NULL WITH DEFAULT 0, EMPLOYEE_NAME FOR COLUMN D1NAME CHAR ( 30) NOT NULL WITH DEFAULT '', START_DATE FOR COLUMN D1SDAT DEC ( 8, 0) NOT NULL WITH DEFAULT 0, SALARY FOR COLUMN D1SALY DEC ( 11, 2) NOT NULL WITH DEFAULT 0, PRIMARY KEY (COMPANY, EMPLOYEE_ID)) RCDFMT MYDATAR; //Update description for SQL table exec sql LABEL on TABLE QGPL/MYDATA is 'My Employee Master Data'; //Update Column Heading for SQL table exec sql LABEL ON COLUMN QGPL/MYDATA ( COMPANY IS 'Company Number', EMPLOYEE_ID IS 'Employee Id', EMPLOYEE_NAME IS 'Employee Name', START_DATE IS 'Employee Start Date', SALARY IS 'Employee Salary'); //Update Column Text for SQL table exec sql LABEL ON COLUMN QGPL/MYDATA ( COMPANY TEXT IS 'Company Number', EMPLOYEE_ID TEXT IS 'Employee Id', EMPLOYEE_NAME TEXT IS 'Employee Name', START_DATE TEXT IS 'Employee Start Date', SALARY TEXT IS 'Employee Salary'); *inlr = *on; /end-free
Sample SQLRPGLE Program to Drop Table on iSeries(AS400)
/free //Set SQL options exec sql SET OPTION commit=*none, datfmt=*iso; //Create the SQL table exec sql DROP TABLE QGPL/MYDATA ; *inlr = *on; /end-free
CREATE TABLE statement
Create - To make a new database, table, index, or stored query. A CREATE statement in SQL creates an object inside of a relational database management system (RDBMS). The types of objects that can be created depends on which RDBMS is being used, but most support the creation of tables, indexes, users, synonyms and databases. The most common CREATE command is the CREATE TABLE command. The typical usage is:
CREATE [TEMPORARY] TABLE [table name] ( [column definitions] ) [table parameters].
Column Definitions: A comma-separated list consisting of any of the following
- Column definition: [column name] [data type] {NULL | NOT NULL} {column options}
- Primary key definition: PRIMARY KEY ( [comma separated column list] )
- CONSTRAINTS: {CONSTRAINT} [constraint definition]
- RDBMS specific functionality
DROP TABLE statement
Drop - To destroy an existing database, table, index, or view.
A DROP statement in SQL removes an object from a relational database management system (RDBMS). The types of objects that can be dropped depends on which RDBMS is being used, but most support the dropping of tables, users, and databases. The typical usage is simply:
DROP objecttype objectname.
ALTER TABLE statement
Alter - To modify an existing database object.
An ALTER statement in SQL changes the properties of an object inside of a relational database management system (RDBMS). The types of objects that can be altered depends on which RDBMS is being used. The typical usage is:
ALTER objecttype objectname parameters.
No comments:
Post a Comment
NO JUNK, Please try to keep this clean and related to the topic at hand.
Comments are for users to ask questions, collaborate or improve on existing.