Basic PostgreSQL tutorial

Advanced PostgreSQL tutorial

PostgreSQL interface

PostgreSQL JAVA PostgreSQL Common Functions

PostgreSQL C/C++ interface

This tutorial will use the libpqxx library, which is the official C interface of PostgreSQL ++Client API. The source code of libpqxx is available under the BSD license, so you can download it for free, pass it on to others, make changes, sell it, include it in your own code, and share your changes with anyone you choose.

Installation

The latest version of libpqxx can be downloaded from the libpqxx download link (Libpqxx download). Therefore, please download the latest version and follow the steps below-

wget http://pqxx.org/download/software/libpqxx/libpqxx-4.0.tar.gz
tar xvfz libpqxx-4.0.tar.gz
cd libpqxx-4.0
./configure
make
make install

Before starting to use C / C ++ Before using the PostgreSQL interface, please find the pg_hba.conf file in the PostgreSQL installation directory and add the following lines-

# IPv4　local connections:
host　　　　all　　　　　　　all　　　　　　　　　127.0.0.1/32　　　　　　　　　　md5

If the postgres server is not running, you can use the following command to start it/Restart the postgres server-

[root@host]# service postgresql restart
Stopping postgresql service:　　[　　OK　　]
Starting postgresql service:　　[　　OK　　]

C/C++ interface APIs

The following are important interface routines that can meet your needs from C/C++The need for using PostgreSQL database in the program. If you are looking for a more complex application, you can check the official libpqxx documentation or use commercially available APIs.

S. No.	API & Description
1	pqxx::connection C( const std::string & dbstring ) This is a typedef that will be used to connect to the database. Here, dbstring provides the parameters required to connect to the database, such as: dbname = testdb user = postgres password=pass123 hostaddr=127.0.0.1 port=5432. If the connection is successfully established, it creates a connection between C and the connection object, providing various useful public functions.
2	C.is_open() The is_open() method is a public method of the connection object and returns a boolean value. If the connection is active, this method returns true; otherwise, it returns false.
3	C.disconnect() This method is used to disconnect from the opened database connection.
4	pqxx::work W( C ) This is a typedef used to create a transactional object using the connection C, which is finally used to execute SQL statements in transactional mode. If the transaction object is created successfully, it will be assigned to the variable W, which will be used to access the public methods related to the transactional object.
5	W.exec(const std::string & sql) This public method from the transactional object is used to execute SQL statements.
6	W.commit() This public method from the transactional object is used to commit the transaction.
7	W.abort() This public method of the transactional object is used to roll back the transaction.
8	pqxx::nontransaction N( C ) This is a typedef that will be used to create non-transactional objects using the connection C, which will finally be used to execute SQL statements in non-transactional mode. If the transaction object is created successfully, it will be assigned to the variable N, which will be used to access the public methods related to the non-transactional object.
9	N.exec(const std::string & sql) This public method from the non-transactional object is used to execute SQL statements and return a result object, which is actually an iterator that stores all the returned records.

Connecting to the database

The following C code section shows how to connect to the database on port5432the existing database running on the local computer. Here, I use the backslash \ to indicate the continuation of the line.

#include <iostream>
#include <pqxx/pqxx>　
using namespace std;
using namespace pqxx;
int main(int argc, char*　argv[]) {
　　　try {
　　　　　　connection C("dbname = testdb user = postgres password = cohondob \
　　　　　　hostaddr =　127.0.0.1　port =　5432");
　　　　　　if (C.is_open()) {
　　　　　　　　　cout << "Opened database successfully: " << C.dbname() << endl;
　　　　　　}
　　　　　　　　　cout << "Can't open database" << endl;
　　　　　　　　　return　1;
　　　　　　}
　　　　　　C.disconnect();
　　　} catch (const std::exception &e) {
　　　　　　cerr << e.what() << std::endl;
　　　　　　return　1;
　　　}
}

Now, let's compile and run the above program to connect to our database testdb, which is already available in your schema, and can be accessed using the user postgres and password pass123access.

You can use the user ID and password according to the database settings. Remember to keep-lpqxx and-lpq in the given order! Otherwise, the connector will consider the function starting with 'PQ' as missing.

$g++　test.cpp　-lpqxx　-lpq
$./a.out
Database opened successfully: testdb

Create table

The following C code segment will be used to create a table in the previously created database-

#include <iostream>
#include <pqxx/pqxx>　
using namespace std;
using namespace pqxx;
int main(int argc, char*　argv[]) {
　　　char　*　sql;
　　　
　　　try {
　　　　　　connection C("dbname = testdb user = postgres password = cohondob \
　　　　　　hostaddr =　127.0.0.1　port =　5432");
　　　　　　if (C.is_open()) {
　　　　　　　　　cout << "Opened database successfully: " << C.dbname() << endl;
　　　　　　}
　　　　　　　　　cout << "Can't open database" << endl;
　　　　　　　　　return　1;
　　　　　　}
　　　　　　/*　Create SQL statement　*/
　　　　　　sql = "CREATE TABLE COMPANY(" \
　　　　　　"ID INT PRIMARY KEY NOT NULL," \
　　　　　　"NAME TEXT NOT NULL," \
　　　　　　"AGE INT NOT NULL," \
　　　　　　"ADDRESS CHAR(50)," \
　　　　　　"SALARY REAL );";
　　　　　　/*　Create transaction object.　*/
　　　　　　work W(C);
　　　　　　
　　　　　　/*　Execute SQL query　*/
　　　　　　W.exec(sql);
　　　　　　W.commit();
　　　　　　cout << "Table created successfully" << endl;
　　　　　　C.disconnect();
　　　} catch (const std::exception &e) {
　　　　　　cerr << e.what() << std::endl;
　　　　　　return　1;
　　　}
　　　return 0;
}

When compiling and executing the given program above, it will create the COMPANY table in the testdb database and display the following statement-

Database opened successfully: testdb
Table created successfully

INSERT operation

The following C code segment shows how to create records in the company table created in the above example-

#include <iostream>
#include <pqxx/pqxx>　
using namespace std;
using namespace pqxx;
int main(int argc, char*　argv[]) {
　　　char　*　sql;
　　　
　　　try {
　　　　　　connection C("dbname = testdb user = postgres password = cohondob \
　　　　　　hostaddr =　127.0.0.1　port =　5432");
　　　　　　if (C.is_open()) {
　　　　　　　　　cout << "Opened database successfully: " << C.dbname() << endl;
　　　　　　}
　　　　　　　　　cout << "Can't open database" << endl;
　　　　　　　　　return　1;
　　　　　　}
　　　　　　/*　Create SQL statement　*/
　　　　　　sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "\
　　　　　　　　　"VALUES (1, 'Paul',　32, 'California',　20000.00 ); "\
　　　　　　　　　"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "\
　　　　　　　　　"VALUES (2, 'Allen',　25, 'Texas',　15000.00 ); "\
　　　　　　　　　"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
　　　　　　　　　"VALUES (3, 'Teddy',　23, 'Norway',　20000.00 );" \
　　　　　　　　　"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
　　　　　　　　　"VALUES (4, 'Mark',　25, 'Rich-Mond ',　65000.00 );";
　　　　　　/*　Create transaction object.　*/
　　　　　　work W(C);
　　　　　　
　　　　　　/*　Execute SQL query　*/
　　　　　　W.exec(sql);
　　　　　　W.commit();
　　　　　　cout << "Records created successfully" << endl;
　　　　　　C.disconnect();
　　　} catch (const std::exception &e) {
　　　　　　cerr << e.what() << std::endl;
　　　　　　return　1;
　　　}
　　　return 0;
}

When compiling and executing the given program above, it will create the given record in the COMPANY table and display the following two lines-

Database opened successfully: testdb
Records created successfully

SELECT operation

The following C code snippet shows how to retrieve and display records from the COMPANY table created in the above example

#include <iostream>
#include <pqxx/pqxx>　
using namespace std;
using namespace pqxx;
int main(int argc, char*　argv[]) {
　　　char　*　sql;
　　　
　　　try {
　　　　　　connection C("dbname = testdb user = postgres password = cohondob \
　　　　　　hostaddr =　127.0.0.1　port =　5432");
　　　　　　if (C.is_open()) {
　　　　　　　　　cout << "Opened database successfully: " << C.dbname() << endl;
　　　　　　}
　　　　　　　　　cout << "Can't open database" << endl;
　　　　　　　　　return　1;
　　　　　　}
　　　　　　/*　Create SQL statement　*/
　　　　　　sql = "SELECT　*　from COMPANY";
　　　　　　/*　Create non-transaction object.　*/
　　　　　　nontransaction N(C);
　　　　　　
　　　　　　/*　Execute SQL query　*/
　　　　　　result R(N.exec(sql));
　　　　　　
　　　　　　/*　List all records　*/
　　　　　　for (result::const_iterator c = R.begin(); c != R.end();　++c) {
　　　　　　　　　cout << "ID = " << c[0].as<int>() << endl;
　　　　　　　　　cout << "Name = " << c[1].as<string>() << endl;
　　　　　　　　　cout << "Age = " << c[2].as<int>() << endl;
　　　　　　　　　cout << "Address = " << c[3].as<string>() << endl;
　　　　　　　　　cout << "Salary = " << c[4].as<float>() << endl;
　　　　　　}
　　　　　　cout << "Operation done successfully" << endl;
　　　　　　C.disconnect();
　　　} catch (const std::exception &e) {
　　　　　　cerr << e.what() << std::endl;
　　　　　　return　1;
　　　}
　　　return 0;
}

When compiling and executing the above given program, it will produce the following results-

Database opened successfully: testdb
ID =　1
Name = Paul
Age =　32
Address = California
Salary =　20000
ID =　2
Name = Allen
Age =　25
Address = Texas
Salary =　15000
ID =　3
Name = Teddy
Age =　23
Address = Norway
Salary =　20000
ID =　4
Name = Mark
Age =　25
Address = Rich-Mond
Salary =　65000
Operation done successfully

UPDATE operation

The following C code snippet demonstrates how to use the UPDATE statement to update any record and then retrieve the updated records from the COMPANY table-Get and display the updated records

#include <iostream>
#include <pqxx/pqxx>　
using namespace std;
using namespace pqxx;
int main(int argc, char*　argv[]) {
　　　char　*　sql;
　　　
　　　try {
　　　　　　connection C("dbname = testdb user = postgres password = cohondob \
　　　　　　hostaddr =　127.0.0.1　port =　5432");
　　　　　　if (C.is_open()) {
　　　　　　　　　cout << "Opened database successfully: " << C.dbname() << endl;
　　　　　　}
　　　　　　　　　cout << "Can't open database" << endl;
　　　　　　　　　return　1;
　　　　　　}
　　　　　　
　　　　　　/*　Create transaction object.　*/
　　　　　　work W(C);
　　　　　　/*　Create SQL UPDATE statement　*/
　　　　　　sql = "UPDATE COMPANY set SALARY ="　25000.00 where ID=1";
　　　　　　/*　Execute SQL query　*/
　　　　　　W.exec(sql);
　　　　　　W.commit();
　　　　　　cout << "Records updated successfully" << endl;
　　　　　　
　　　　　　/*　Create SQL SELECT statement　*/
　　　　　　sql = "SELECT　*　from COMPANY";
　　　　　　/*　Create a non-transaction object.　*/
　　　　　　nontransaction N(C);
　　　　　　
　　　　　　/*　Execute SQL query　*/
　　　　　　result R(N.exec(sql));
　　　　　　
　　　　　　/*　List all records　*/
　　　　　　for (result::const_iterator c = R.begin(); c != R.end();　++c) {
　　　　　　　　　cout << "ID = " << c[0].as<int>() << endl;
　　　　　　　　　cout << "Name = " << c[1].as<string>() << endl;
　　　　　　　　　cout << "Age = " << c[2].as<int>() << endl;
　　　　　　　　　cout << "Address = " << c[3].as<string>() << endl;
　　　　　　　　　cout << "Salary = " << c[4].as<float>() << endl;
　　　　　　}
　　　　　　cout << "Operation done successfully" << endl;
　　　　　　C.disconnect();
　　　} catch (const std::exception &e) {
　　　　　　cerr << e.what() << std::endl;
　　　　　　return　1;
　　　}
　　　return 0;
}

When the above given program is compiled and executed, it will produce the following result-

Database opened successfully: testdb
Records updated successfully
ID =　2
Name = Allen
Age =　25
Address = Texas
Salary =　15000
ID =　3
Name = Teddy
Age =　23
Address = Norway
Salary =　20000
ID =　4
Name = Mark
Age =　25
Address = Rich-Mond
Salary =　65000
ID =　1
Name = Paul
Age =　32
Address = California
Salary =　25000
Operation done successfully

DELETE operation

The following C code snippet demonstrates how to use the DELETE statement to delete any record and then retrieve the remaining records from the COMPANY table-Display and show the remaining records from the table

#include <iostream>
#include <pqxx/pqxx>　
using namespace std;
using namespace pqxx;
int main(int argc, char*　argv[]) {
　　　char　*　sql;
　　　
　　　try {
　　　　　　connection C("dbname = testdb user = postgres password = cohondob \
　　　　　　hostaddr =　127.0.0.1　port =　5432");
　　　　　　if (C.is_open()) {
　　　　　　　　　cout << "Opened database successfully: " << C.dbname() << endl;
　　　　　　}
　　　　　　　　　cout << "Can't open database" << endl;
　　　　　　　　　return　1;
　　　　　　}
　　　　　　
　　　　　　/*　Create transaction object.　*/
　　　　　　work W(C);
　　　　　　/*　Create SQL DELETE statement　*/
　　　　　　sql = "DELETE from COMPANY where ID ="　2";
　　　　　　/*　Execute SQL query　*/
　　　　　　W.exec(sql);
　　　　　　W.commit();
　　　　　　cout << "Records deleted successfully" << endl;
　　　　　　
　　　　　　/*　Create SQL SELECT statement　*/
　　　　　　sql = "SELECT　*　from COMPANY";
　　　　　　/*　Create non-transaction object.　*/
　　　　　　nontransaction N(C);
　　　　　　
　　　　　　/*　Execute SQL query　*/
　　　　　　result R(N.exec(sql));
　　　　　　
　　　　　　/*　List all records　*/
　　　　　　for (result::const_iterator c = R.begin(); c != R.end();　++c) {
　　　　　　　　　cout << "ID = " << c[0].as<int>() << endl;
　　　　　　　　　cout << "Name = " << c[1].as<string>() << endl;
　　　　　　　　　cout << "Age = " << c[2].as<int>() << endl;
　　　　　　　　　cout << "Address = " << c[3].as<string>() << endl;
　　　　　　　　　cout << "Salary = " << c[4].as<float>() << endl;
　　　　　　}
　　　　　　cout << "Operation done successfully" << endl;
　　　　　　C.disconnect();
　　　} catch (const std::exception &e) {
　　　　　　cerr << e.what() << std::endl;
　　　　　　return　1;
　　　}
　　　return 0;
}

When compiling and executing the above given program, it will produce the following results-

Database opened successfully: testdb
Records deleted successfully
ID =　3
Name = Teddy
Age =　23
Address = Norway
Salary =　20000
ID =　4
Name = Mark
Age =　25
Address = Rich-Mond
Salary =　65000
ID =　1
Name = Paul
Age =　32
Address = California
Salary =　25000
Operation done successfully

PostgreSQL JAVA PostgreSQL Common Functions