Plan zajęć
- Biblioteka libpq
- Połączenie z bazą danych PostgreSQL
- Zapytania do bazy danych
- Kursory w libpq
- Zapytania z parameterm
- Prepere statement
$ gcc -I/usr/include/postgresql -L/usr/local/pgsql/lib prog.c -o prog -lpq $ ./prog
#include <stdlib.h>
#include <stdio.h>
#include <libpq-fe.h>
int main()
{
PGconn *conn;
const char *connection_str = "host=localhost port=5432 dbname=*** user=*** password=***";
conn = PQconnectdb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr, "Connection to %s failed, %s",connection_str,PQerrorMessage(conn));
} else {
printf("Connected OK\n");
printf("PGDBNAME = %s\n",PQdb(conn));
printf("PGUSER = %s\n",PQuser(conn));
printf("PGPASSWORD = %s\n",PQpass(conn));
printf("PGHOST = %s\n",PQhost(conn));
printf("PGPORT = %s\n",PQport(conn));
printf("OPTIONS = %s\n",PQoptions(conn));
}
PQfinish(conn);
return EXIT_SUCCESS;
}
$ g++ -o prog prog.cpp -lpqxx -lpq $ ./prog
#include <iostream>
#include <sstream>
#include <string>
#include <pqxx/pqxx>
#include "lab10.h"
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
stringstream ss;
ss << "dbname = " << labdbname << " user = " << labdbuser << " password = " << labdbpass \
<< " host = " << labdbhost << " port = " << labdbport;
string s = ss.str();
try {
connection connlab(s);
if (connlab.is_open()) {
cout << "Successfully connection to: " << connlab.dbname() << endl;
} else {
cout << "Problem with connection to database" << endl;
return 1;
}
connlab.disconnect ();
} catch (const std::exception &e) {
cerr << e.what() << std::endl;
return 1;
}
}
#include <stdlib.h>
#include <libpq-fe.h>
void doSQL(PGconn *conn, char *command){
PGresult *result;
printf("------------------------------\n");
printf("%s\n", command);
result = PQexec(conn, command);
printf("status is : %s\n", PQresStatus(PQresultStatus(result)));
printf("#rows affected: %s\n", PQcmdTuples(result));
printf("result message: %s\n", PQresultErrorMessage(result));
switch(PQresultStatus(result)) {
case PGRES_TUPLES_OK:{
int n = 0, r = 0;
int nrows = PQntuples(result);
int nfields = PQnfields(result);
printf("number of rows returned = %d\n", nrows);
printf("number of fields returned = %d\n", nfields);
for(r = 0; r < nrows; r++) {
for(n = 0; n < nfields; n++)
printf(" %s = %s", PQfname(result, n),PQgetvalue(result,r,n));
printf("\n");
}
}/*end case*/
}/* end switch */
PQclear(result);
}
int main(){
PGresult *result;
PGconn *conn;
const char *connection_str = "host=localhost port=5432 dbname=*** user=*** password=***";
conn = PQconnectdb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr, "Connection to %s failed, %s", connection_str, PQerrorMessage(conn));
} else {
printf("Connected OK\n");
//doSQL(conn, "DROP TABLE lab10.person");
doSQL(conn, "CREATE TABLE lab10.person (id INTEGER PRIMARY KEY, fname VARCHAR, lname VARCHAR);");
doSQL(conn, "INSERT INTO lab10.person values(10, 'Adam', 'Dadacki')");
doSQL(conn, "INSERT INTO lab10.person values(29, 'Bogdan', 'Zawadzki')");
doSQL(conn, "INSERT INTO lab10.person values(31, 'Anna', 'Abacka')");
doSQL(conn, "INSERT INTO lab10.person values(66, 'Krystyna', 'Babacka')");
doSQL(conn, "SELECT * FROM lab10.person");
doSQL(conn, "UPDATE lab10.person SET fname = 'Zbigniew' WHERE id = 29");
doSQL(conn, "DELETE FROM lab10.person WHERE id = 29");
doSQL(conn, "SELECT * FROM lab10.person");
}
// else
// printf("connection failed: %s\n", PQerrorMessage(conn));
PQfinish(conn);
return EXIT_SUCCESS;
}
#include <stdlib.h>
#include <libpq-fe.h>
void doSQL(PGconn *conn, char *command){
PGresult *result;
printf("------------------------------\n");
printf("%s\n", command);
result = PQexec(conn, command);
printf("status is : %s\n", PQresStatus(PQresultStatus(result)));
printf("#rows affected: %s\n", PQcmdTuples(result));
printf("result message: %s\n", PQresultErrorMessage(result));
switch(PQresultStatus(result)) {
case PGRES_TUPLES_OK:{
int n = 0, r = 0;
int nrows = PQntuples(result);
int nfields = PQnfields(result);
printf("number of rows returned = %d\n", nrows);
printf("number of fields returned = %d\n", nfields);
for(r = 0; r < nrows; r++) {
for(n = 0; n < nfields; n++)
printf(" %s = %s", PQfname(result, n), PQgetvalue(result,r,n), PQgetlength(result,r,n));
printf("\n");
}
}/*end case*/
}/* end switch */
PQclear(result);
}
int main(){
PGresult *result;
PGconn *conn;
const char *connection_str = "host=localhost port=5432 dbname=*** user=*** password=***";
conn = PQconnectdb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr, "Connection to %s failed, %s", connection_str, PQerrorMessage(conn));
} else {
printf("Connected OK\n");
//doSQL(conn, "DROP TABLE lab10.person");
doSQL(conn, "CREATE TABLE lab10.person (id INTEGER PRIMARY KEY, fname VARCHAR, lname VARCHAR);");
doSQL(conn, "INSERT INTO lab10.person values(10, 'Adam', 'Dadacki')");
doSQL(conn, "INSERT INTO lab10.person values(29, 'Bogdan', 'Zawadzki')");
doSQL(conn, "INSERT INTO lab10.person values(31, 'Anna', 'Abacka')");
doSQL(conn, "INSERT INTO lab10.person values(66, 'Krystyna', 'Babacka')");
doSQL(conn, "SELECT * FROM lab10.person");
doSQL(conn, "UPDATE lab10.person SET fname = 'Zbigniew' WHERE id = 29");
doSQL(conn, "DELETE FROM lab10.person WHERE id = 29");
doSQL(conn, "SELECT * FROM lab10.person");
}
// else
// printf("connection failed: %s\n", PQerrorMessage(conn));
PQfinish(conn);
return EXIT_SUCCESS;
}
#include <iostream>
#include <sstream>
#include <string>
#include <pqxx/pqxx>
#include "lab10.h"
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
stringstream ss;
ss << "dbname = " << labdbname << " user = " << labdbuser << " password = " << labdbpass \
<< " host = " << labdbhost << " port = " << labdbport;
string s = ss.str();
try {
connection connlab(s);
if (connlab.is_open()) {
work trsxn{connlab};
result res { trsxn.exec("SELECT id_uczestnik, imie, nazwisko FROM uczestnik")};
for (auto row: res)
cout << row["id_uczestnik"].as<int>() << " " << row[2].c_str() << " " << row[1].c_str() << std::endl;
// dla polecenia select niekoniecznie
trsxn.commit();
} else {
cout << "Problem z connection " << endl;
return 3;
}
connlab.disconnect ();
} catch (pqxx::sql_error const &e) {
cerr << "SQL error: " << e.what() << std::endl;
cerr << "Polecenie SQL: " << e.query() << std::endl;
return 2;
} catch (const std::exception &e) {
cerr << e.what() << std::endl;
return 1;
}
}
#include <stdlib.h>
#include <libpq-fe.h>
void printTuples(PGresult *result) {
int n = 0, r = 0;
int nrows = PQntuples(result);
int nfields = PQnfields(result);
printf("number of rows returned = %d\n", nrows);
printf("number of fields returned = %d\n", nfields);
for(r = 0; r < nrows; r++) {
for(n = 0; n < nfields; n++)
printf(" %s = %s", PQfname(result, n), PQgetvalue(result,r,n), PQgetlength(result,r,n));
printf("\n");
}
}
void doSQL(PGconn *conn, char *command){
PGresult *result;
printf("------------------------------\n");
printf("%s\n", command);
result = PQexec(conn, command);
printf("status is : %s\n", PQresStatus(PQresultStatus(result)));
printf("#rows affected: %s\n", PQcmdTuples(result));
printf("result message: %s\n", PQresultErrorMessage(result));
switch(PQresultStatus(result)) {
case PGRES_TUPLES_OK:printTuples(result);
break;
}/* end switch */
PQclear(result);
}
int main(){
PGresult *result;
PGconn *conn;
int nTuples = 0;
const char *connection_str = "host=localhost port=5432 dbname=*** user=*** password=***";
conn = PQconnectdb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr, "Connection to %s failed, %s", connection_str, PQerrorMessage(conn));
} else {
printf("Connected OK\n");
doSQL(conn, "BEGIN work");
doSQL(conn, "DECLARE mcursor CURSOR FOR SELECT * FROM lab10.person");
do {
result = PQexec(conn, "FETCH 1 IN mcursor");
if ( PQresultStatus(result) == PGRES_TUPLES_OK) {
nTuples = PQntuples(result);
printTuples(result);
PQclear(result);
} else nTuples = 0;
} while (nTuples) ;
doSQL(conn, "CLOSE mcursor");
doSQL(conn, "COMMIT work");
}
// else
// printf("connection failed: %s\n", PQerrorMessage(conn));
PQfinish(conn);
return EXIT_SUCCESS;
}
#include <iostream>
#include <sstream>
#include <string>
#include <pqxx/pqxx>
#include "lab10.h"
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
stringstream ss;
ss << "dbname = " << labdbname << " user = " << labdbuser << " password = " << labdbpass \
<< " host = " << labdbhost << " port = " << labdbport;
string s = ss.str();
try {
connection connlab(s);
if (connlab.is_open()) {
work trsxn{connlab};
std::string selectStatement = "SELECT id_uczestnik, imie, nazwisko FROM uczestnik";
stateless_cursor<pqxx::cursor_base::read_only, pqxx::cursor_base::owned>
uczestnik_cursor(trsxn, selectStatement, "uczestnikCursor", false);
size_t idx = 0; // start from 0
size_t step = 3; // ilosc rekordow w porcji
result res;
do{
// fetch next cursor
res = uczestnik_cursor.retrieve( idx, idx + step );
idx += step;
size_t records = res.size();
for( pqxx::result::const_iterator row : res ){
cout << row["nazwisko"].as<std::string>()<< '\t' << row["imie"].as<std::string>()
<< '\t' << row["id_uczestnik"].as<int>() << endl;
}
} while( res.size() == step ); //jezeli the res.size() != step to jest to ostatnia petla
cout << " Sukces - wykonano" << endl;
// dla polecenia select niekoniecznie
trsxn.commit();
} else {
cout << "Problem z connection " << endl;
return 3;
}
connlab.disconnect ();
} catch (pqxx::sql_error const &e) {
cerr << "SQL error: " << e.what() << std::endl;
cerr << "Polecenie SQL: " << e.query() << std::endl;
return 2;
} catch (const std::exception &e) {
cerr << e.what() << std::endl;
return 1;
}
}
#include <stdlib.h>
#include <libpq-fe.h>
int main(){
PGresult *result;
PGconn *conn;
const char *connection_str = "host=localhost port=5432 dbname=*** user=*** password=***";
conn = PQconnectdb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr, "Connection to %s failed, %s", connection_str, PQerrorMessage(conn));
}
else {
result = PQexec(conn, "SELECT id as Nr, fname as Imie, lname as Nazwisko FROM lab10.person;");
{
PQprintOpt pqp;
pqp.header = 1;
pqp.align = 1;
pqp.html3 = 1;
pqp.expanded = 0;
pqp.pager = 0;
pqp.fieldSep = "";
pqp.tableOpt = "align=center";
pqp.caption = "Tabela person";
pqp.fieldName = NULL;
printf("<HTML><HEAD></HEAD><BODY>\n");
PQprint(stdout, result, &pqp);
printf("</BODY></HTML>\n");
}
}
PQfinish(conn);
return EXIT_SUCCESS;
}
#include <stdlib.h>
#include <libpq-fe.h>
#include <stdio.h>
/* for ntohl/htonl */
#include <netinet/in.h>
#include <arpa/inet.h>
int main(){
PGresult *result;
PGconn *conn;
const char *paramValues[1];
int paramLengths[1];
int paramFormats[1];
uint32_t binaryIntVal;
const char *connection_str = "host=localhost port=5432 dbname=*** user=*** password=***";
conn = PQconnectdb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr, "Connection to %s failed, %s", connection_str,PQerrorMessage(conn));
} else {
printf("Connected OK\n");
/* Set up parameter arrays for PQexecParams */
paramValues[0] = "Adam";
result = PQexecParams(conn, "SELECT * FROM lab10.person WHERE fname = $1",
1, /* one param */
NULL, /* let the backend deduce param type */
paramValues,
NULL, /* don't need param lengths since text */
NULL, /* default to all text params */
1); /* ask for binary results */
if (PQresultStatus(result) != PGRES_TUPLES_OK)
{
fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
PQclear(result);
} else {
int n = 0, r = 0;
int nrows = PQntuples(result);
int nfields = PQnfields(result);
printf("number of rows returned = %d\n", nrows);
printf("number of fields returned = %d\n", nfields);
for(r = 0; r < nrows; r++) {
for(n = 0; n < nfields; n++)
printf(" %s = %s", PQfname(result, n),PQgetvalue(result,r,n));
printf("\n");
}
}
PQfinish(conn);
return EXIT_SUCCESS;
}
}
#include <stdlib.h>
#include <libpq-fe.h>
#include <stdio.h>
/* for ntohl/htonl */
#include <netinet/in.h>
#include <arpa/inet.h>
int main(){
PGresult *result;
PGconn *conn;
const char *paramValues[1];
int paramLengths[1];
int paramFormats[1];
uint32_t binaryIntVal;
const char *connection_str = "host=localhost port=5432 dbname=*** user=*** password=***";
conn = PQconnectdb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr, "Connection to %s failed, %s", connection_str,PQerrorMessage(conn));
} else {
printf("Connected OK\n");
/* Convert integer value "31" to network byte order */
binaryIntVal = htonl((uint32_t) 31);
/* Set up parameter arrays for PQexecParams */
paramValues[0] = (char *) &binaryIntVal;
paramLengths[0] = sizeof(binaryIntVal);
paramFormats[0] = 1; /* binary */
result = PQexecParams(conn, "SELECT * FROM lab10.person WHERE id = $1::int4",
1, /* one param */
NULL, /* let the backend deduce param type */
paramValues,
paramLengths,
paramFormats,
0); /* ask for binary results */
if (PQresultStatus(result) != PGRES_TUPLES_OK)
{
fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
PQclear(result);
} else {
int n = 0, r = 0;
int nrows = PQntuples(result);
int nfields = PQnfields(result);
printf("number of rows returned = %d\n", nrows);
printf("number of fields returned = %d\n", nfields);
for(r = 0; r < nrows; r++) {
for(n = 0; n < nfields; n++)
printf(" %s = %s", PQfname(result, n),PQgetvalue(result,r,n));
printf("\n");
}
}
PQfinish(conn);
return EXIT_SUCCESS;
}
}
#include <stdlib.h>
#include <libpq-fe.h>
#include <stdio.h>
/* for ntohl/htonl */
#include <netinet/in.h>
#include <arpa/inet.h>
void printTuples(PGresult *result) {
int n = 0, r = 0;
int nrows = PQntuples(result);
int nfields = PQnfields(result);
printf("number of rows returned = %d\n", nrows);
printf("number of fields returned = %d\n", nfields);
for(r = 0; r < nrows; r++) {
for(n = 0; n < nfields; n++)
printf(" %s = %s", PQfname(result, n), PQgetvalue(result,r,n), PQgetlength(result,r,n));
printf("\n");
}
}
int main(){
PGresult *result;
PGconn *conn;
// Prepered statement
const char *stmtName ;
const char *query ;
int nParams ;
const char *paramValues[1];
int paramLengths[1];
int paramFormats[1];
uint32_t binaryIntVal;
const char *connection_str;
connection_str = "host=localhost port=5432 dbname=*** user=*** password=***";
stmtName = "SQL1" ;
query = "SELECT * FROM lab10.person WHERE fname = %1" ;
conn = PQconnectdb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr, "Connection to %s failed, %s", connection_str,PQerrorMessage(conn));
} else {
printf("Connected OK\n");
/* Prepere statement */
query = "SELECT * FROM lab10.person WHERE id= $1" ;
stmtName = "Query" ;
nParams = 1;
PGresult* pPreperedQuery = PQprepare(conn, stmtName, query, nParams, NULL);
/* Execute prepered statement id=31 */
/* Convert integer value "31" to network byte order */
binaryIntVal = htonl((uint32_t) 31);
/* Set up parameter arrays for PQexecParams */
paramValues[0] = (char *) &binaryIntVal;
paramLengths[0] = sizeof(binaryIntVal);
paramFormats[0] = 1; /* binary */
result = PQexecPrepared(conn, stmtName, nParams,
paramValues,
paramLengths,
paramFormats,
0); /* ask for binary results */
if (PQresultStatus(result) != PGRES_TUPLES_OK)
{
fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
PQclear(result);
} else printTuples(result) ;
/* Execute prepered statement id=10*/
/* Convert integer value "10" to network byte order */
binaryIntVal = htonl((uint32_t) 10);
/* Set up parameter arrays for PQexecParams */
paramValues[0] = (char *) &binaryIntVal;
paramLengths[0] = sizeof(binaryIntVal);
paramFormats[0] = 1; /* binary */
result = PQexecPrepared(conn, stmtName, nParams,
paramValues,
paramLengths,
paramFormats,
0); /* ask for binary results */
if (PQresultStatus(result) != PGRES_TUPLES_OK)
{
fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
PQclear(result);
} else printTuples(result) ;
/* Execute prepered statement id=66*/
/* Convert integer value "66" to network byte order */
binaryIntVal = htonl((uint32_t) 66);
/* Set up parameter arrays for PQexecParams */
paramValues[0] = (char *) &binaryIntVal;
paramLengths[0] = sizeof(binaryIntVal);
paramFormats[0] = 1; /* binary */
result = PQexecPrepared(conn, stmtName, nParams,
paramValues,
paramLengths,
paramFormats,
0); /* ask for binary results */
if (PQresultStatus(result) != PGRES_TUPLES_OK)
{
fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
PQclear(result);
} else printTuples(result) ;
PQfinish(conn);
return EXIT_SUCCESS;
}
}
#include <iostream>
#include <sstream>
#include <string>
#include <pqxx/pqxx>
#include "lab10.h"
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
int id = 101;
string fname ("And");
string lname ("Lem");
if( argc == 4 )
{
id = atoi(argv[1]);
fname = argv[2];
lname = argv[3];
}
stringstream ss;
ss << "dbname = " << labdbname << " user = " << labdbuser << " password = " << labdbpass \
<< " host = " << labdbhost << " port = " << labdbport;
string s = ss.str();
try {
connection connlab(s);
if (connlab.is_open()) {
work trsxn{connlab};
// konstrukcja polecenia insert
connlab.prepare("insert_uczestnik", "INSERT INTO uczestnik ( id_uczestnik, nazwisko, imie ) VALUES ($1, $2, $3)");
prepare::invocation insert_invocation = trsxn.prepared("insert_uczestnik")(id)(lname.c_str())(fname.c_str());
//dynamic array preparation
//prep_dynamic(ids, w_invocation);
// wykonanie
result res = insert_invocation.exec();
trsxn.commit();
} else {
cout << "Problem z connection " << endl;
return 3;
}
connlab.disconnect ();
} catch (pqxx::sql_error const &e) {
cerr << "SQL error: " << e.what() << std::endl;
cerr << "Polecenie SQL: " << e.query() << std::endl;
return 2;
} catch (const std::exception &e) {
cerr << e.what() << std::endl;
return 1;
}
}
#include <iostream>
#include <sstream>
#include <string>
#include <pqxx/pqxx>
#include "lab10.h"
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
int id = 101;
string fname ("Ant");
string lname ("Dyd");
if( argc == 4 )
{
id = atoi(argv[1]);
fname = argv[2];
lname = argv[3];
}
stringstream ss;
ss << "dbname = " << labdbname << " user = " << labdbuser << " password = " << labdbpass \
<< " host = " << labdbhost << " port = " << labdbport;
string s = ss.str();
try {
connection connlab(s);
if (connlab.is_open()) {
work trsxn{connlab};
// konstrukcja polecenia insert
connlab.prepare("update_uczestnik", "UPDATE uczestnik SET nazwisko = $1, imie = $2 WHERE id_uczestnik = $3");
prepare::invocation update_invocation = trsxn.prepared("update_uczestnik")(lname.c_str())(fname.c_str())(id);
// wykonanie
result res = update_invocation.exec();
trsxn.commit();
} else {
cout << "Problem z connection " << endl;
return 3;
}
connlab.disconnect ();
} catch (pqxx::sql_error const &e) {
cerr << "SQL error: " << e.what() << std::endl;
cerr << "Polecenie SQL: " << e.query() << std::endl;
return 2;
} catch (const std::exception &e) {
cerr << e.what() << std::endl;
return 1;
}
}
#include <iostream>
#include <sstream>
#include <string>
#include <pqxx/pqxx>
#include "lab10.h"
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
int id = 101;
if( argc == 2 )
{
id = atoi(argv[1]);
}
stringstream ss;
ss << "dbname = " << labdbname << " user = " << labdbuser << " password = " << labdbpass \
<< " host = " << labdbhost << " port = " << labdbport;
string s = ss.str();
try {
connection connlab(s);
if (connlab.is_open()) {
work trsxn{connlab};
// konstrukcja polecenia insert
connlab.prepare("delete_uczestnik", "DELETE FROM uczestnik WHERE id_uczestnik = $1");
prepare::invocation delete_invocation = trsxn.prepared("delete_uczestnik")(id);
// wykonanie
result res = delete_invocation.exec();
trsxn.commit();
} else {
cout << "Problem z connection " << endl;
return 3;
}
connlab.disconnect ();
} catch (pqxx::sql_error const &e) {
cerr << "SQL error: " << e.what() << std::endl;
cerr << "Polecenie SQL: " << e.query() << std::endl;
return 2;
} catch (const std::exception &e) {
cerr << e.what() << std::endl;
return 1;
}
}