This content originally appeared on DEV Community and was authored by Özcan Kara
ssh root@EIP
wget https://dbs-download.obs.cn-north-1.myhuaweicloud.com/GaussDB/1642684986086/GaussDB_opengauss_client_tools.zip
unzip GaussDB_opengauss_client_tools.zip
cd /root/GaussDB_opengauss_client_tools/Euler2.5_X86_64
cp GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz /opt
cd /opt/
tar -zxvf GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz
source gsql_env.sh
gsql -h <IP address of the primary GaussDB node> -d postgres -p 8000 -U root -W yourpassword -r
CREATE DATABASE devdb ENCODING 'UTF8' template = template0;
\q
gsql -h <IP address of the primary GaussDB node> -d devdb -p 8000 -U root -W yourpassword -r
CREATE USER hccdp SYSADMIN IDENTIFIED BY "yourpassword";
\q
gsql -h <IP address of the primary GaussDB node> -d devdb -p 8000 -U hccdp -W yourpassword -r
CREATE TABLE bmsql_warehouse (
w_id integer NOT NULL,
w_ytd numeric(12,2),
w_tax numeric(4,4),
w_name character varying(10),
w_street_1 character varying(20),
w_street_2 character varying(20),
w_city character varying(20),
w_state character(2),
w_zip character(9)
);
ALTER TABLE bmsql_warehouse ADD CONSTRAINT bmsql_warehouse_pkey PRIMARY KEY (w_id);
CREATE TABLE bmsql_history (
hist_id serial NOT NULL,
h_c_id integer,
h_c_d_id integer,
h_c_w_id integer,
h_d_id integer,
h_w_id integer,
h_date timestamp without time zone,
h_amount numeric(6,2),
h_data character varying(24),
CONSTRAINT h_district_fkey FOREIGN KEY (h_w_id, h_d_id) REFERENCES bmsql_district(d_w_id, d_id),
CONSTRAINT h_customer_fkey FOREIGN KEY (h_c_w_id, h_c_d_id, h_c_id) REFERENCES bmsql_customer(c_w_id, c_d_id, c_id)
);
ALTER TABLE bmsql_history ADD CONSTRAINT bmsql_history_pkey PRIMARY KEY (hist_id);
CREATE TABLE bmsql_order (
o_w_id integer NOT NULL,
o_d_id integer NOT NULL,
o_id integer NOT NULL,
o_c_id integer,
o_carrier_id integer,
o_ol_cnt integer,
o_all_local integer,
o_entry_d timestamp without time zone,
CONSTRAINT o_customer_fkey FOREIGN KEY (o_w_id, o_d_id, o_c_id) REFERENCES bmsql_customer(c_w_id, c_d_id, c_id)
);
ALTER TABLE bmsql_order ADD CONSTRAINT bmsql_order_pkey PRIMARY KEY (o_w_id, o_d_id, o_id);
CREATE TABLE bmsql_new_order (
no_w_id integer NOT NULL,
no_d_id integer NOT NULL,
no_o_id integer NOT NULL,
CONSTRAINT no_order_fkey FOREIGN KEY (no_w_id, no_d_id, no_o_id) REFERENCES bmsql_order(o_w_id, o_d_id, o_id)
);
ALTER TABLE bmsql_new_order ADD CONSTRAINT bmsql_new_order_pkey PRIMARY KEY (no_w_id, no_d_id, no_o_id);
CREATE TABLE bmsql_item (
i_id integer NOT NULL,
i_name character varying(24),
i_price numeric(5,2),
i_data character varying(50),
i_im_id integer
);
ALTER TABLE bmsql_item ADD CONSTRAINT bmsql_item_pkey PRIMARY KEY (i_id);
CREATE TABLE bmsql_stock (
s_w_id integer NOT NULL,
s_i_id integer NOT NULL,
s_quantity integer,
s_ytd integer,
s_order_cnt integer,
s_remote_cnt integer,
s_data character varying(50),
s_dist_01 character(24),
s_dist_02 character(24),
s_dist_03 character(24),
s_dist_04 character(24),
s_dist_05 character(24),
s_dist_06 character(24),
s_dist_07 character(24),
s_dist_08 character(24),
s_dist_09 character(24),
s_dist_10 character(24),
CONSTRAINT s_item_fkey FOREIGN KEY (s_i_id) REFERENCES bmsql_item(i_id),
CONSTRAINT s_warehouse_fkey FOREIGN KEY (s_w_id) REFERENCES bmsql_warehouse(w_id)
);
ALTER TABLE bmsql_stock ADD CONSTRAINT bmsql_stock_pkey PRIMARY KEY (s_w_id, s_i_id);
CREATE TABLE bmsql_order_line (
ol_w_id integer NOT NULL,
ol_d_id integer NOT NULL,
ol_o_id integer NOT NULL,
ol_number integer NOT NULL,
ol_i_id integer NOT NULL,
ol_delivery_d timestamp without time zone,
ol_amount numeric(6,2),
ol_supply_w_id integer,
ol_quantity integer,
ol_dist_info character(24),
CONSTRAINT ol_stock_fkey FOREIGN KEY (ol_supply_w_id, ol_i_id) REFERENCES bmsql_stock(s_w_id, s_i_id),
CONSTRAINT ol_order_fkey FOREIGN KEY (ol_w_id, ol_d_id, ol_o_id) REFERENCES bmsql_order(o_w_id, o_d_id, o_id)
);
ALTER TABLE bmsql_order_line ADD CONSTRAINT bmsql_order_line_pkey PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number);
\d
mkdir /root/tpcc/
cd /root/tpcc
wget https://sandbox-expriment-files.obs.cn-north-1.myhuaweicloud.com/gaussdb/tpcc.tar.gz
tar -zxvf tpcc.tar.gz
mv /root/tpcc/home/user/rar/tpcc/bmsql_* .
cd /opt/
source gsql_env.sh
gsql -h <IP address of the primary GaussDB node> -d devdb -p 8000 -U hccdp -W yourpassword -r
\copy bmsql_warehouse from '/root/tpcc/bmsql_warehouse.csv' with ( FORMAT 'csv', DELIMITER ',', ENCODING 'utf8');
\copy bmsql_district from '/root/tpcc/bmsql_district.csv' with ( FORMAT 'csv', DELIMITER ',', ENCODING 'utf8');
\copy bmsql_customer from '/root/tpcc/bmsql_customer.csv' with ( FORMAT 'csv', DELIMITER ',', ENCODING 'utf8');
\copy bmsql_history from '/root/tpcc/bmsql_history.csv' with ( FORMAT 'csv', DELIMITER ',', ENCODING 'utf8');
\copy bmsql_order from '/root/tpcc/bmsql_oorder.csv' with ( FORMAT 'csv', DELIMITER ',', ENCODING 'utf8');
\copy bmsql_item from '/root/tpcc/bmsql_item.csv' with ( FORMAT 'csv', DELIMITER ',', ENCODING 'utf8');
\copy bmsql_new_order from '/root/tpcc/bmsql_new_order.csv' with ( FORMAT 'csv', DELIMITER ',', ENCODING 'utf8');
\copy bmsql_stock from '/root/tpcc/bmsql_stock.csv' with ( FORMAT 'csv', DELIMITER ',', ENCODING 'utf8');
\copy bmsql_order_line from '/root/tpcc/bmsql_order_line.csv' with ( FORMAT 'csv', DELIMITER ',', ENCODING 'utf8');
analyze bmsql_stock;
analyze bmsql_order_line;
analyze bmsql_warehouse;
analyze bmsql_district;
analyze bmsql_customer;
analyze bmsql_history;
analyze bmsql_order;
analyze bmsql_new_order;
analyze bmsql_new_order;
analyze bmsql_item;
SELECT count(*) FROM bmsql_customer;
CREATE UNIQUE INDEX bmsql_order_idx1 ON bmsql_order USING btree (o_w_id, o_d_id, o_carrier_id, o_id);
create index bmsql_customer_idx1 on bmsql_customer (c_w_id, c_d_id, c_last, c_first);
\di bmsql_customer_idx1
SELECT c_discount, c_last, c_credit, w_tax FROM bmsql_customer JOIN bmsql_warehouse ON (w_id = c_w_id) WHERE c_w_id = 1 AND c_d_id = 1 AND c_id = 114;
SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 1 AND d_id = 1 FOR UPDATE;
UPDATE bmsql_district SET d_next_o_id = d_next_o_id + 1 WHERE d_w_id = 1 AND d_id = 1;
INSERT INTO bmsql_order (o_id, o_d_id, o_w_id, o_c_id, o_entry_d,o_ol_cnt, o_all_local)VALUES (3001, 1, 1, 114,'2023-02-11 15:00' , 14, 1);
INSERT INTO bmsql_new_order (no_o_id, no_d_id, no_w_id)VALUES (3001, 1, 1);
SELECT s_quantity, s_data,s_dist_01, s_dist_02, s_dist_03, s_dist_04,s_dist_05, s_dist_06, s_dist_07, s_dist_08,s_dist_09, s_dist_10 FROM bmsql_stock WHERE s_w_id = 1 AND s_i_id = 1 FOR UPDATE;
UPDATE bmsql_stock SET s_quantity = 90, s_ytd = s_ytd + 4,s_order_cnt = s_order_cnt + 1,s_remote_cnt = s_remote_cnt + 1 WHERE s_w_id = 1 AND s_i_id = 1;
INSERT INTO bmsql_order_line (ol_o_id, ol_d_id, ol_w_id, ol_number,ol_i_id, ol_supply_w_id, ol_quantity,ol_amount, ol_dist_info)VALUES (3001, 1, 1, 16, 1, 1, 4, 0, 'j6vO1P7KCKKdP73IcBHkkkRQ');
SELECT w_name, w_street_1, w_street_2, w_city, w_state, w_zip FROM bmsql_warehouse WHERE w_id = 1;
SELECT d_name, d_street_1, d_street_2, d_city, d_state, d_zip FROM bmsql_district WHERE d_w_id = 1 AND d_id = 1;
SELECT c_id FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 1 AND c_last = 'BARBARPRI' ORDER BY c_first;
SELECT c_first, c_middle, c_last, c_street_1, c_street_2,c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 1 AND c_id = 4 FOR UPDATE;
SELECT c_data FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 1 AND c_id = 4;
UPDATE bmsql_warehouse SET w_ytd = w_ytd + 3 WHERE w_id = 1;
UPDATE bmsql_district SET d_ytd = d_ytd + 3 WHERE d_w_id = 1 AND d_id = 1;
UPDATE bmsql_customer SET c_balance = c_balance - 50, c_ytd_payment = c_ytd_payment + 50 ,c_payment_cnt = c_payment_cnt + 1 WHERE c_w_id = 1 AND c_d_id = 1 AND c_id = 4;
INSERT INTO bmsql_history(hist_id,h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id,h_date, h_amount, h_data)VALUES(300001,4, 1, 1, 1, 1, '2023-02-11', 50, 'succeed');
SELECT c_id FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 2 AND c_last = 'BAROUGHTPRES' ORDER BY c_first
SELECT c_first, c_middle, c_last, c_balance FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 2 AND c_id = 15;
SELECT o_id, o_entry_d, o_carrier_id FROM bmsql_order WHERE o_w_id = 1 AND o_d_id = 2 AND o_c_id = 15 AND o_id = (SELECT max(o_id) FROM bmsql_order WHERE o_w_id = 1 AND o_d_id = 2 AND o_c_id = 15);
SELECT ol_i_id, ol_supply_w_id, ol_quantity,ol_amount, ol_delivery_d FROM bmsql_order_line WHERE ol_w_id = 1 AND ol_d_id = 2 AND ol_o_id = 1229 ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number;
SELECT min(no_o_id) FROM bmsql_new_order WHERE no_w_id = 2 AND no_d_id = 2 ;
DELETE FROM bmsql_new_order WHERE no_w_id = 2 AND no_d_id = 2 AND no_o_id = 2101;
SELECT o_c_id FROM bmsql_order WHERE o_w_id =2 AND o_d_id = 2 AND o_id = 2101;
UPDATE bmsql_order SET o_carrier_id = 1 WHERE o_w_id = 2 AND o_d_id = 2 AND o_id = 2101;
SELECT sum(ol_amount) AS sum_ol_amount FROM bmsql_order_line WHERE ol_w_id = 2 AND ol_d_id = 2 AND ol_o_id = 2101;
UPDATE bmsql_order_line SET ol_delivery_d = '2023-2-11' WHERE ol_w_id = 2 AND ol_d_id = 2 AND ol_o_id = 2101;
UPDATE bmsql_customer SET c_balance = c_balance + 31547.84, c_delivery_cnt = c_delivery_cnt + 1 WHERE c_w_id = 2 AND c_d_id = 2 AND c_id = 2855;
SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 1 AND s_quantity < 60 AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 1 AND d_id = 1));
create user db_dev sysadmin password 'yourpassword';
mkdir -p /opt/expt/db/libs
cd /opt/expt/db/libs
wget https://sandbox-expriment-files.obs.cn-north-1.myhuaweicloud.com/20220525/opengaussjdbc.jar
wget https://labfiles-singapore.obs.ap-southeast-3.myhuaweicloud.com/OpenJDK11U-jdk_x64_linux_openj9_linuxXL_11.0.10_9_openj9-0.24.0.tar.gz
tar xzvf OpenJDK11U-jdk_x64_linux_openj9_linuxXL_11.0.10_9_openj9-0.24.0.tar.gz
mv jdk-11.0.10+9 /usr/lib/
ln -s /usr/lib/jdk-11.0.10+9/bin/java /usr/local/bin/java
ln -s /usr/lib/jdk-11.0.10+9/bin/javac /usr/local/bin/javac
java -version
javac -version
mkdir -p /opt/expt/db/basic
cd /opt/expt/db/basic
touch exptConnection.java
vi exptConnection.java
package expt.db.basic;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
public class exptConnection {
static final String JDBC_DRIVER = "com.huawei.opengauss.jdbc.Driver";
static final String DB_URL = "jdbc:opengauss://192.168.0.124:8000/demo";
static final String USER = "db_dev";
static final String PASS = "yourpassword";
static String connection_url = DB_URL + "?user=" + USER + "&password=" + PASS;
public static void main(String[] args) {
getConnect();
}
public static Connection getConnect() {
Connection conn = null;
// Register the driver.
try {
Class.forName(JDBC_DRIVER);
} catch (Exception e) {
e.printStackTrace();
return null;
}
// Connect to the database.
System.out.println("connecting database...");
try {
System.out.println("connection url is: " + connection_url);
conn = DriverManager.getConnection(connection_url);
System.out.println("connection successfully!");
return conn;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
}
javac -d . exptConnection.java
java -cp /opt/expt/db/libs/opengaussjdbc.jar:. expt.db.basic.exptConnection
static final String DB_URL = "jdbc:opengauss://192.168.0.124:8000,192.168.0.91:8000,192.168.0.117:8000/demo";
static final String PARM = "autoBalance=true";
static String connection_url = DB_URL + "?user=" + USER + "&password=" + PASS + "&" + PARM;
package expt.db.basic;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
public class exptConnection {
static final String JDBC_DRIVER = "com.huawei.opengauss.jdbc.Driver";
static final String DB_URL = "jdbc:opengauss://192.168.0.124:8000,192.168.0.91:8000,192.168.0.117:8000/demo";
static final String USER = "db_dev";
static final String PASS = "yourpassword";
static final String PARM = "autoBalance=true";
static String connection_url = DB_URL + "?user=" + USER + "&password=" + PASS + "&" + PARM;
public static void main(String[] args) {
getConnect();
}
public static Connection getConnect() {
Connection conn = null;
// Register the driver.
try {
Class.forName(JDBC_DRIVER);
} catch (Exception e) {
e.printStackTrace();
return null;
}
// Connect to the database.
System.out.println("connecting database...");
try {
System.out.println("connection url is: " + connection_url);
conn = DriverManager.getConnection(connection_url);
System.out.println("connection successfully!");
return conn;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
}
javac -d . exptConnection.java
java -cp /opt/expt/db/libs/opengaussjdbc.jar:. expt.db.basic.exptConnection
static final String PARM = "targetServerType=master";
cd /opt/expt/db/basic
touch exptCreateTable.java
vi exptCreateTable.java
package expt.db.basic;
import java.sql.*;
import java.util.Properties;
public class exptCreateTable {
static final String JDBC_DRIVER = "com.huawei.opengauss.jdbc.Driver";
static final String DB_URL = "jdbc:opengauss://192.168.0.124:8000,192.168.0.91:8000,192.168.0.117:8000/demo";
static final String USER = "db_dev";
static final String PASS = "yourpassword";
static final String PARM = "targetServerType=master";
static String connection_url = DB_URL + "?user=" + USER + "&password=" + PASS + "¤tSchema=db_dev" + "&" + PARM;
public static Connection getConnect() {
Connection conn = null;
// Register the driver.
try {
Class.forName(JDBC_DRIVER);
} catch (Exception e) {
e.printStackTrace();
return null;
}
// Connect to the database.
System.out.println("connecting database...");
try {
System.out.println("connection url is: " + connection_url);
conn = DriverManager.getConnection(connection_url);
System.out.println("connection successfully!");
return conn;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
// Table creation statement (to be modified)
public static void main(String[] args) throws SQLException {
// Execute SQL statements.
Connection conn = getConnect();
Statement statement = conn.createStatement();
statement.execute("create table test_table (id int, name varchar(10), destination varchar(20), uuid varchar(36))");
System.out.println("execute successfully!");
}
}
javac -d . exptCreateTable.java
java -cp /opt/expt/db/libs/opengaussjdbc.jar:. expt.db.basic.exptCreateTable
cd /opt/expt/db/basic
touch exptInsert.java
vi exptInsert.java
package expt.db.basic;
import java.sql.*;
import java.util.Properties;
import java.util.UUID;
public class exptInsert {
static final String JDBC_DRIVER = "com.huawei.opengauss.jdbc.Driver";
static final String DB_URL = "jdbc:opengauss://192.168.0.124:8000,192.168.0.91:8000,192.168.0.117:8000/demo";
static final String USER = "db_dev";
static final String PASS = "yourpassword";
static final String PARM = "targetServerType=master";
static String connection_url = DB_URL + "?user=" + USER + "&password=" + PASS + "¤tSchema=db_dev" + "&" + PARM;
public static Connection getConnect() {
Connection conn = null;
// Register the driver.
try {
Class.forName(JDBC_DRIVER);
} catch (Exception e) {
e.printStackTrace();
return null;
}
// Connect to the database.
System.out.println("connecting database...");
try {
System.out.println("connection url is: " + connection_url);
conn = DriverManager.getConnection(connection_url);
System.out.println("connection successfully!");
return conn;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
public static void main(String[] args) throws SQLException {
// Execute SQL statements.
Connection conn = getConnect();
Statement statement = conn.createStatement();
statement.execute("INSERT INTO test_table(id, name, destination, uuid) " + "values (2, 'zhangsan', 'hangzhou', 123456789)");
System.out.println("Number of rows affected: "+statement.getUpdateCount());
}
}
javac -d . exptInsert.java
java -cp /opt/expt/db/libs/opengaussjdbc.jar:. expt.db.basic.exptInsert
cd /opt/expt/db/basic
touch exptQuery.java
vi exptQuery.java
package expt.db.basic;
import java.sql.*;
import java.util.Properties;
import java.util.UUID;
public class exptQuery {
static final String JDBC_DRIVER = "com.huawei.opengauss.jdbc.Driver";
static final String DB_URL = "jdbc:opengauss://192.168.0.124:8000,192.168.0.91:8000,192.168.0.117:8000/demo";
static final String USER = "db_dev";
static final String PASS = "yourpassword";
static final String PARM = "targetServerType=master";
static String connection_url = DB_URL + "?user=" + USER + "&password=" + PASS + "¤tSchema=db_dev" + "&" + PARM;
public static Connection getConnect() {
Connection conn = null;
// Register the driver.
try {
Class.forName(JDBC_DRIVER);
} catch (Exception e) {
e.printStackTrace();
return null;
}
// Connect to the database.
System.out.println("connecting database...");
try {
System.out.println("connection url is: " + connection_url);
conn = DriverManager.getConnection(connection_url);
System.out.println("connection successfully!");
return conn;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
public static void closeConnect(Connection conn) {
System.out.println("closing connection...");
try {
conn.close();
System.out.println("connection closed!");
} catch (Exception e) {
e.printStackTrace();
}
}
public static void printAllRecords(ResultSet rs) {
try {
ResultSetMetaData metaData = rs.getMetaData();
for (int i = 0; i < metaData.getColumnCount(); i++) {
System.out.print(metaData.getColumnName(i + 1) + "\t");
}
System.out.println();
while (rs.next()) {
for (int i = 0; i < metaData.getColumnCount(); i++) {
System.out.print(rs.getString(i + 1) + "\t");
}
System.out.println();
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void printOneRecord(ResultSet rs) {
try {
ResultSetMetaData metaData = rs.getMetaData();
for (int i = 0; i < metaData.getColumnCount(); i++) {
System.out.print(metaData.getColumnName(i + 1) + "\t");
}
System.out.println();
for (int i = 0; i < metaData.getColumnCount(); i++) {
System.out.print(rs.getString(i + 1) + "\t");
}
System.out.println("\ncurrent row number: " + rs.getRow() + "\n");
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws SQLException {
// Execute SQL statements.
Connection conn = getConnect();
ResultSet resultSet = null;
PreparedStatement preparedStatement=conn.prepareStatement("select * from test_table where id=?;");
preparedStatement.setObject(1,2);
resultSet = preparedStatement.executeQuery();
printAllRecords(resultSet);
closeConnect(conn);
}
}
javac -d . exptQuery.java
java -cp /opt/expt/db/libs/opengaussjdbc.jar:. expt.db.basic.exptQuery
cd /opt/expt/db/basic
touch exptBatchInsert.java
vi exptBatchInsert.java
package expt.db.basic;
import java.sql.*;
import java.util.Properties;
import java.util.UUID;
public class exptBatchInsert {
static final String JDBC_DRIVER = "com.huawei.opengauss.jdbc.Driver";
static final String DB_URL = "jdbc:opengauss://192.168.0.124:8000,192.168.0.91:8000,192.168.0.117:8000/demo";
static final String USER = "db_dev";
static final String PASS = "yourpassword";
static final String PARM = "targetServerType=master";
static String connection_url = DB_URL + "?user=" + USER + "&password=" + PASS + "¤tSchema=db_dev" + "&" + PARM;
public static Connection getConnect() {
Connection conn = null;
// Register the driver.
try {
Class.forName(JDBC_DRIVER);
} catch (Exception e) {
e.printStackTrace();
return null;
}
// Connect to the database.
System.out.println("connecting database...");
try {
System.out.println("connection url is: " + connection_url);
conn = DriverManager.getConnection(connection_url);
System.out.println("connection successfully！");
return conn;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
public static void closeConnect(Connection conn) {
System.out.println("closing connection...");
try {
conn.close();
System.out.println("connection closed!");
} catch (Exception e) {
e.printStackTrace();
}
}
public static int insertRecordOnceATime(int begin, int count) {
PreparedStatement preparedStatement;
int index = begin;
try {
Connection conn = getConnect();
conn.setAutoCommit(true);
String targetQuery = "INSERT INTO test_table(id, name, destination, uuid) VALUES(?, ?, ?, ?)";
preparedStatement = conn.prepareStatement(targetQuery);
long start = System.currentTimeMillis();
for( ; index < begin+count; index++) {
preparedStatement.setInt(1, index);
preparedStatement.setString(2, "name-"+index);
preparedStatement.setString(3, "destination-"+index);
preparedStatement.setString(4, UUID.randomUUID().toString());
long startInternal = System.currentTimeMillis();
preparedStatement.executeUpdate();
System.out.println("each transaction time taken = " + (System.currentTimeMillis() - startInternal) + " ms");
}
long end = System.currentTimeMillis();
System.out.println("total time taken = " + (end - start) + " ms");
System.out.println("avg total time taken = " + (end - start)/ count + " ms");
preparedStatement.close();
closeConnect(conn);
} catch (SQLException ex) {
System.err.println("SQLException information");
while (ex != null) {
System.err.println("Error msg: " + ex.getMessage());
ex = ex.getNextException();
}
}
return index;
}
public static void insertRecordBatch(int begin, int count) {
PreparedStatement preparedStatement;
int index = begin;
try {
Connection conn = getConnect();
conn.setAutoCommit(true);
String targetQuery = "INSERT INTO test_table(id, name, destination, uuid) VALUES(?, ?, ?, ?)";
preparedStatement = conn.prepareStatement(targetQuery);
for( ; index < begin+count; index++) {
preparedStatement.setInt(1, index);
preparedStatement.setString(2, "name-"+index);
preparedStatement.setString(3, "destination-"+index);
preparedStatement.setString(4, UUID.randomUUID().toString());
preparedStatement.addBatch();
}
long start = System.currentTimeMillis();
int[] inserted = preparedStatement.executeBatch();
long end = System.currentTimeMillis();
System.out.println("total time taken to insert the batch = " + (end - start) + " ms");
System.out.println("total time taken = " + (end - start)/count + " s");
preparedStatement.close();
closeConnect(conn);
System.out.println("row influence number is: " + inserted.length);
} catch (SQLException ex) {
System.err.println("SQLException information");
while (ex != null) {
System.err.println("Error msg: " + ex.getMessage());
ex = ex.getNextException();
}
throw new RuntimeException("Error");
}
}
public static void main(String[] args) throws SQLException {
int current;
current = insertRecordOnceATime(1, 1000);
insertRecordBatch(current, 1000);
}
}
javac -d . exptBatchInsert.java
java -cp /opt/expt/db/libs/opengaussjdbc.jar:. expt.db.basic.exptBatchInsert
cd /opt/expt/db/basic
touch exptBatchInsert2.java
vi exptBatchInsert2.java
package expt.db.basic;
import java.sql.*;
import java.util.Properties;
import java.util.UUID;
public class exptBatchInsert2 {
static final String JDBC_DRIVER = "com.huawei.opengauss.jdbc.Driver";
static final String DB_URL = "jdbc:opengauss://192.168.0.124:8000,192.168.0.91:8000,192.168.0.117:8000/demo";
static final String USER = "db_dev";
static final String PASS = "yourpassword";
static final String PARM = "targetServerType=master";
static String connection_url = DB_URL + "?user=" + USER + "&password=" + PASS + "¤tSchema=db_dev" + "&" + PARM;
public static Connection getConnect() {
Connection conn = null;
// Register the driver.
try {
Class.forName(JDBC_DRIVER);
} catch (Exception e) {
e.printStackTrace();
return null;
}
// Connect to the database.
System.out.println("connecting database...");
try {
System.out.println("connection url is: " + connection_url);
conn = DriverManager.getConnection(connection_url);
System.out.println("connection successfully！");
return conn;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
public static void closeConnect(Connection conn) {
System.out.println("closing connection...");
try {
conn.close();
System.out.println("connection closed！");
} catch (Exception e) {
e.printStackTrace();
}
}
public static int insertRecordOnceATime(int begin, int count) {
PreparedStatement preparedStatement;
int index = begin;
try {
Connection conn = getConnect();
conn.setAutoCommit(false);
String targetQuery = "INSERT INTO test_table(id, name, destination, uuid) VALUES(?, ?, ?, ?)";
preparedStatement = conn.prepareStatement(targetQuery);
long start = System.currentTimeMillis();
for( ; index < begin+count; index++) {
preparedStatement.setInt(1, index);
preparedStatement.setString(2, "name-"+index);
preparedStatement.setString(3, "destination-"+index);
preparedStatement.setString(4, UUID.randomUUID().toString());
long startInternal = System.currentTimeMillis();
preparedStatement.executeUpdate();
System.out.println("each transaction time taken = " + (System.currentTimeMillis() - startInternal) + " ms");
}
conn. commit();
long end = System.currentTimeMillis();
System.out.println("total time taken = " + (end - start) + " ms");
System.out.println("avg total time taken = " + (end - start)/ count + " ms");
preparedStatement.close();
closeConnect(conn);
} catch (SQLException ex) {
System.err.println("SQLException information");
while (ex != null) {
System.err.println("Error msg: " + ex.getMessage());
ex = ex.getNextException();
}
}
return index;
}
public static void insertRecordBatch(int begin, int count) {
PreparedStatement preparedStatement;
int index = begin;
try {
Connection conn = getConnect();
conn.setAutoCommit(true);
String targetQuery = "INSERT INTO test_table(id, name, destination, uuid) VALUES(?, ?, ?, ?)";
preparedStatement = conn.prepareStatement(targetQuery);
for( ; index < begin+count; index++) {
preparedStatement.setInt(1, index);
preparedStatement.setString(2, "name-"+index);
preparedStatement.setString(3, "destination-"+index);
preparedStatement.setString(4, UUID.randomUUID().toString());
preparedStatement.addBatch();
}
long start = System.currentTimeMillis();
int[] inserted = preparedStatement.executeBatch();
long end = System.currentTimeMillis();
System.out.println("total time taken to insert the batch = " + (end - start) + " ms");
System.out.println("total time taken = " + (end - start)/count + " s");
preparedStatement.close();
closeConnect(conn);
System.out.println("row influence number is: " + inserted.length);
} catch (SQLException ex) {
System.err.println("SQLException information");
while (ex != null) {
System.err.println("Error msg: " + ex.getMessage());
ex = ex.getNextException();
}
throw new RuntimeException("Error");
}
}
public static void main(String[] args) throws SQLException {
int current;
current = insertRecordOnceATime(1, 1000);
insertRecordBatch(current, 1000);
}
}
javac -d . exptBatchInsert2.java
java -cp /opt/expt/db/libs/opengaussjdbc.jar:. expt.db.basic.exptBatchInsert2
yum install -y libtool
yum install -y libtool-ltdl-devel
yum install -y openssl-devel
mkdir -p /opt/odbc
cd /opt/odbc
wget https://udomain.dl.sourceforge.net/project/unixodbc/unixODBC/2.3.7/unixODBC-2.3.7pre.tar.gz
tar zxvf unixODBC-2.3.7pre.tar.gz
cd unixODBC-2.3.7pre
vim configure
LIB_VERSION="1:0:0"
./configure --enable-gui=no&&make
make install
cd /root
wget https://sandbox-expriment-files.obs.cn-north-1.myhuaweicloud.com/2434/GaussDB-Kernel-V500R002C10-EULER-64bit-Odbc.tar.gz
tar zxvf GaussDB-Kernel-V500R002C10-EULER-64bit-Odbc.tar.gz
cd /root/lib/
cp * /usr/local/lib
cd /root/odbc/lib/
cp * /usr/local/lib
vi /usr/local/etc/odbcinst.ini
[GaussMPP]
Driver64=/usr/local/lib/psqlodbcw.so
setup=/usr/local/lib/psqlodbcw.so
vi /usr/local/etc/odbc.ini
[MPPODBC]
Driver=GaussMPP
Servername=192.168.0.72
Database=demo
Username=db_dev
Password=yourpassword
Port=8000
vim ~/.bashrc
export LD_LIBRARY_PATH=/usr/local/lib/:$LD_LIBRARY_PATH
export ODBCSYSINI=/usr/local/etc
export ODBCINI=/usr/local/etc/odbc.ini
source ~/.bashrc
isql -v MPPODBC
mkdir -p /opt/odbc/db/basic
cd /opt/odbc/db/basic
touch DBtest.c
vi DBtest.c
// The following example shows how to obtain data from GaussDB through the ODBC interface.
// DBtest.c (compile with: libodbc.so)
include
include
include
ifdef WIN32
include
endif
SQLHENV V_OD_Env; // Handle ODBC environment
SQLHSTMT V_OD_hstmt; // Handle statement
SQLHDBC V_OD_hdbc; // Handle connection
char typename[100];
SQLINTEGER value = 100;
SQLINTEGER V_OD_erg,V_OD_buffer,V_OD_err,V_OD_id;
int main(int argc,char argv[])
{
// 1. Allocate an environment handle.
V_OD_erg = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env);
if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
{
printf("Error AllocHandle\n");
exit(0);
}
// 2. Set environment attributes (version information).
SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, (void)SQL_OV_ODBC3, 0);
// 3. Allocate a connection handle.
V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc);
if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
{
SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
exit(0);
}
// 4. Set connection attributes.
SQLSetConnectAttr(V_OD_hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_ON, 0);
// 5. Connect to a data source. userName and password indicate the username and password for connecting to the database. Set them as needed.
// If the username and password have been set in the odbc.ini file, you can retain "" for them. However, you are not advised to do so because the username and password will be disclosed if the permission for odbc.ini is abused.
V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "MPPODBC", SQL_NTS,
(SQLCHAR*) "", SQL_NTS, (SQLCHAR*) "", SQL_NTS);
if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
{
printf("Error SQLConnect %d\n",V_OD_erg);
SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
exit(0);
}
printf("Connected !\n");
// 6. Set statement attributes.
SQLSetStmtAttr(V_OD_hstmt,SQL_ATTR_QUERY_TIMEOUT,(SQLPOINTER *)3,0);
// 7. Allocate a statement handle.
SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &V_OD_hstmt);
// 8. Run SQL statements.
SQLExecDirect(V_OD_hstmt,"drop table IF EXISTS customer_t1",SQL_NTS);
SQLExecDirect(V_OD_hstmt,"CREATE TABLE customer_t1(c_customer_sk INTEGER, c_customer_name VARCHAR(32));",SQL_NTS);
SQLExecDirect(V_OD_hstmt,"insert into customer_t1 values(25,'LiLei')",SQL_NTS);
// 9. Prepare for execution.
SQLPrepare(V_OD_hstmt,"insert into customer_t1 values(?)",SQL_NTS);
// 10. Bind parameters.
SQLBindParameter(V_OD_hstmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,
&value,0,NULL);
// 11. Run prepared statements.
SQLExecute(V_OD_hstmt);
SQLExecDirect(V_OD_hstmt,"select id from testtable",SQL_NTS);
// 12. Obtain attributes of a specific column in the result set.
SQLColAttribute(V_OD_hstmt,1,SQL_DESC_TYPE,typename,100,NULL,NULL);
printf("SQLColAtrribute %s\n",typename);
// 13. Bind the result set.
SQLBindCol(V_OD_hstmt,1,SQL_C_SLONG, (SQLPOINTER)&V_OD_buffer,150,
(SQLLEN *)&V_OD_err);
// 14. Obtain data in the result set by executing SQLFetch.
V_OD_erg=SQLFetch(V_OD_hstmt);
// 15. Obtain and return data by executing SQLGetData.
while(V_OD_erg != SQL_NO_DATA)
{
SQLGetData(V_OD_hstmt,1,SQL_C_SLONG,(SQLPOINTER)&V_OD_id,0,NULL);
printf("SQLGetData ----ID = %d\n",V_OD_id);
V_OD_erg=SQLFetch(V_OD_hstmt);
};
printf("Done !\n");
// 16. Disconnect data source connections and release handles.
SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);SQLDisconnect(V_OD_hdbc);SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);return(0);
}
gcc -lodbc -g DBtest.c -o DBtest
./DBtest
isql -v MPPODBC
select * from customer_t1;
quit
create table t01(id int);
insert into t01 values(1);
insert into t01 values(2);
insert into t01 values(3);
select * from t01;
Step 1 Install the gsql client on the preset ECS.1. Log in to the ECS console and obtain the following information:IP Address: Obtain the ECS EIP.Port: 22User name: root (subject to the actual username)Password: Huawei@1234 (subject to the actual password)
wget https://sandbox-expriment-files.obs.cn-north-1.myhuaweicloud.com:443/20220525/GaussDB_opengauss_client_tools.zip
unzip GaussDB_opengauss_client_tools.zip
cd GaussDB_opengauss_client_tools/Euler2.8_arm_64/
tar -xvf GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz
source gsql_env.sh
gsql -h *Private IP address* -U root -d postgres -p 8000 -W yourpassword -r
\q
gsql --help
gsql -h *Private IP address* -U root -d postgres -p 8000 -W yourpassword -r
help
\copyright
\h
\help CREATE DATABASE
\?
\q
gsql -h *Private IP address* -U root -d postgres -p 8000 -W yourpassword -l
gsql -V
gsql -h *Private IP address* -U root -d postgres -p 8000 -W yourpassword -q
create table t_test (firstcol int);
insert into t_test values(200);
select * from t_test;
\q
gsql -h *Private IP address* -U root -d postgres -p 8000 -W yourpassword -S
select * from t_test;
select * from t_test
\q
gsql -h *Private IP address* -U root -d postgres -p 8000 -W yourpassword -r
select * from t_test;
select firstcol from t_test;
\q
gsql -h *Private IP address* -U root -d postgres -p 8000 -W yourpassword -r
\db
DROP TABLE IF EXISTS customer_t1;
CREATE TABLE customer_t1
(
c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
);
\d+;
\d+ customer_t1;
create index customer_t1_index1 on customer_t1(c_customer_id);
\di+ customer_t1_index1
DROP DATABASE IF EXISTS db_tpcc02;
CREATE DATABASE db_tpcc02;
\c db_tpcc02;
\q
This content originally appeared on DEV Community and was authored by Özcan Kara
Print
Share
Comment
Cite
Upload
Translate
Updates
There are no updates yet.
Click the Upload button above to add an update.
APA
MLA
Özcan Kara | Sciencx (2024-07-24T22:49:06+00:00) Demo. Retrieved from https://www.scien.cx/2024/07/24/demo/
" » Demo." Özcan Kara | Sciencx - Wednesday July 24, 2024, https://www.scien.cx/2024/07/24/demo/
HARVARDÖzcan Kara | Sciencx Wednesday July 24, 2024 » Demo., viewed ,<https://www.scien.cx/2024/07/24/demo/>
VANCOUVERÖzcan Kara | Sciencx - » Demo. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2024/07/24/demo/
CHICAGO" » Demo." Özcan Kara | Sciencx - Accessed . https://www.scien.cx/2024/07/24/demo/
IEEE" » Demo." Özcan Kara | Sciencx [Online]. Available: https://www.scien.cx/2024/07/24/demo/. [Accessed: ]
rf:citation » Demo | Özcan Kara | Sciencx | https://www.scien.cx/2024/07/24/demo/ |
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.