libpq, PQExecParams and the inserting of binary data
5 stars based on
PostgreSQL is a powerful, open source, object-relational database system. It is a multi-user database management system. It supports international character sets, multibyte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting.
On a Debian-based system, we can install the PostgreSQL database from the packages using the above command. If we install the PostgreSQL database from packages, it is automatically added to the startup scripts of the operating system. If we are only learning to work with the database, it is unnecessary to start the database each time we boot the system.
The above command removes any system startup links for the PostgreSQL database. The above line shows how we can do it on Debian-based Linux.
In interfaces libpq pqexecparams and the inserting of binary data next interfaces libpq pqexecparams and the inserting of binary data, we are going to show how to start the PostgreSQL database, stop it, and query its status.
On Debian-based Linux, we can start the server with the service postgresql start command. At this moment, the service postgresql status command reports that the PostgreSQL database is down. We create a new role in the PostgreSQL system. We allow it to have ability to create new databases. A role is a user in a database world. Roles are separate from operating system users.
PostgreSQL often uses trust or peer authentication policies on local connections. In case of the trust authentication policy, PostgreSQL assumes that anyone who can connect to the server is authorized to access the database with whatever database user name they specify even superuser names. When making connections to the database, no password is required. The restrictions made in the database and user columns still apply.
The trust authentication is appropriate and very convenient for local connections on a single-user workstation. It is usually not appropriate on a multiuser machine. In case of the peer authentication policy, the database username must match the operating system username.
With the createdb command, we create a new database called testdb. Its owner is the new database user. This tutorial uses C MSVC does not support C In C99, we can mix declarations with code. In older C programs, we would need to separate this line into two lines. It is a set of library functions that allow client programs to interact with PostgreSQL. The PQlibVersion function returns the version of the libpq being used.
The example connects to the PostgreSQL database, executes a PQserverVersion function, prints the version, closes the connection, and cleans up. In this method, the database user name must match the operating system user name. No password is required to make a connection. Interfaces libpq pqexecparams and the inserting of binary data the PQconnectdb function, we make a connection to the database.
In the connection string, we provide the user name and the database name. The PQstatus function returns the status of the connection. The PQerrorMessage returns the most recently generated error message. It takes the connection object as a parameter. The PQfinish function closes the connection to the server and frees the memory used by the PGconn object. Next, we are going to authenticate to the database server with a password. In all other examples in this tutorial, we assume the peer or trust authentication mode.
In the example, we connect to the database with a password. We print the username, database name, and the password of the current database connection. The created table is called Cars and it has three columns: The PGresult encapsulates the result of a query. Our SQL command drops a table if it already exists. The PQresultStatus function should be called to check the return value for any errors. Every command result should be freed with the Interfaces libpq pqexecparams and the inserting of binary data function when interfaces libpq pqexecparams and the inserting of binary data is no longer needed.
The PQgetvalue function returns a single field value of one row of a PGresult. Prepared statements guard against SQL injections and increase performance. When using prepared statements, we use placeholders instead of directly writing the values into the statements.
The program takes a row Id as its argument. It fetches the data of the specified row and prints it. Since the program takes a value from a user, which cannot be trusted, we must sanitize the input data. A prepared statement is created with a PQexecParams function. The command line argument is stored in the rowId variable. We expect an integer value. The value is transformed into a string and passed to an array of characters.
The paramValues is a parameter of the PQexecParams function. This is our SQL statement, which returns one row of the Cars table. The PQexecParams function creates a prepared statement and executes it. The second parameter is the SQL statement. The third parameter is the number of parameters passed. Passing NULL to the fourth parameter means that the server should figure out the parameter types.
The fifth parameter is a pointer to an array of strings containing parameters. The next two parameters are only relevant with binary parameters. Passing 0 to the final parameter we obtain result in text format, 1 in binary format. Metadata is information about the data in the database. The following belongs to metadata: The PQnfields function returns the number of columns in the row of the query result.
The PQfname function returns the column name associated with the given column number. The PostgreSQL's information schema consists of a set of views that contain information about the objects defined in the current database. The tables view contains all tables and views defined in the current database. A transaction is an atomic unit of database operations against the data in one or more databases. SQL statements in a transaction can be either all committed to the database or all rolled back.
SQL statements are put into transactions for data safety and integrity. PostgreSQL operates in the autocommit mode. Every SQL statement is executed within a transaction: In the example, we update the price of a car and insert a new car.
The two operations are included in a single transaction. This means that either both operations are executed or none.