GBase8s dbaccess常用方式及技巧

GBase8s dbaccess常用方式及技巧

A.数据库servername： testserver

B.数据库名：testdb

C.SQL脚本文件：
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;

一.执行SQL脚本文件方式

1.调用dbaccess执行SQL脚本文件

A.简单执行SQL文件

 dbaccess testdb sqlfile.sql 或者  dbaccess testdb@testserver sqlfile.sql

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

B.执行输出打印执行语句及执行结果

 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.将执行结果重定向到文件(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.

二.交互模式执行语句

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

三.菜单模式执行

进入数据库

dbaccess 选择数据库

或者

dbaccess testdb

dbaccess testdb@testserver

按菜单操作执行SQL语句,其中可以使用vi进行脚本文件的编辑

四.几个小技巧

1.执行SQL脚本文件时，当出错时打印详细的错误信息

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.执行SQL脚本文件时，当出错时打印详细的错误信息并在出现第一个错误时，停止继续执行后续的语句

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.