Commonly used methods of implementation and common techniques of Informix dbaccess

Assume:

A. Database Servername:testserver

B. Database name: TestDB

C.sql script file:

Sqlfile.sql

CREATE TABLE test_table (C1 integer);

INSERT into test_table (c1) VALUES (12);

Update test_table set c1=0;

Delete test_table where 1=1;

drop table test_table;

Sqlfile2.sql

CREATE TABLE test_table (C1 integer);

INSERT INTO test_table (C2) values (12);

INSERT into test_table (c1) VALUES (12);

Update test_table set c1=0;

Delete test_table where 1=1;

drop table test_table;

I. How to execute SQL script file

1. Call dbaccess Execute SQL script file

A. Simple execution of SQL files

dbaccess TestDB sqlfile.sql or dbaccess [email protected] Sqlfile.sql

Database selected.
Table created.
1 row (s) inserted.
1 row (s) updated.
1 row (s) deleted.
Table dropped.
Database closed.

B. Execute output Print execution statement and execution result

dbaccess-e TestDB sqlfile.sql

CREATE TABLE test_table (C1 integer);
Table created.
INSERT into test_table (c1) VALUES (12);
1 row (s) inserted.
Update test_table set c1=0;
1 row (s) updated.

Delete test_table where 1=1;
1 row (s) deleted.

drop table test_table;
Table dropped.
Database closed.

C. Redirecting execution results to a file (Shell:bash)

dbaccess-e testdb sqlfile.sql > Output.log > 2&1

Cat Output.log

CREATE TABLE test_table (C1 integer);
Table created.
INSERT into test_table (c1) VALUES (12);
1 row (s) inserted.
Update test_table set c1=0;
1 row (s) updated.

Delete test_table where 1=1;
1 row (s) deleted.

drop table test_table;
Table dropped.
Database closed.

Two. Interactive mode execution statement

Dbaccess--
> Database testdb;

Database selected.

> CREATE TABLE test_table (C1 integer);
Table created.

> INSERT into test_table (c1) VALUES (12);
1 row (s) inserted.
> Update test_table set c1=0;
1 row (s) updated.

> Delete test_table where 1=1;
1 row (s) deleted.

> drop table test_table;
Table dropped.

>^d

Three. Menu Mode execution

Enter the database

Dbaccess Selecting a Database

Or

Dbaccess TestDB

dbaccess [email protected]

Executes the SQL statement by menu operation, where you can edit the script file using VI

Four. A few tips

1. When executing a SQL script file, print verbose error messages when an error occurs

DBACCESS-E-M TestDB sqlfile2.sql

Database selected.

CREATE TABLE test_table (C1 integer);
Table created

INSERT INTO test_table (C2) values (1212);
217:column (C2) not found in any table in the query (or SLV is undefined).
Error in line 2
Near character position 26

INSERT into test_table (c1) VALUES (12);
1 row (s) inserted.
Update test_table set c1=0;
1 row (s) updated.
Delete test_table where 1=1;
1 row (s) deleted.
drop table test_table;
Table dropped.
Database closed.

2. When executing a SQL script file, print verbose error message when an error occurs and stop execution of subsequent statements when the first error is encountered

Dbaccess-e-m-a TestDB Sqlfile2.sql

Database selected.

CREATE TABLE test_table (C1 integer);
Table created.

INSERT INTO test_table (C2) values (1212);
217:column (C2) not found in any table in the query (or SLV is undefined).
Error in line 2
Near character position 26

Database closed.

Commonly used methods of implementation and common techniques of Informix dbaccess