56、PostgreSQL Native Interface: Connection Options and SQL Execution

PostgreSQL Native Interface: Connection Options and SQL Execution

1. Overview

In the realm of PostgreSQL database management, efficient handling of connection options and execution of SQL statements are crucial. This blog will delve into various functions related to PostgreSQL native interface, including resetting connection options, removing options, saving data sources, setting options, testing connections, and executing SQL statements and scripts.

2. Connection Option Management Functions

2.1 reset

The reset function is used to reset PostgreSQL native interface connection options to their default values.

Syntax :

opts = reset(opts)

Description :
This function resets all connection options to their default values for all properties of the SQLConnectionOptions object. It also removes any additional driver - specific properties from the object.

Example :

% Create a PostgreSQL native interface data source
vendor = "PostgreSQL";
opts = databaseConnectionOptions("native",vendor);
% Configure the data source
opts = setoptions(opts, ...
    'DataSourceName',"PostgreSQLDataSource", ...
    'DatabaseName',"toystore_doc",'Server',"dbtb00", ...
    'PortNumber',5432);
% Test the database connection
username = "dbdev";
password = "matlab";
status = testConnection(opts,username,password);
% Reset the database connection options
opts = reset(opts);
% Configure the data source again with additional option
opts = setoptions(opts, ...
    "DataSourceName","PostgreSQLDataSource", ...
    "DatabaseName","toystore_doc", ...
    "Server","dbtb00","PortNumber",5432, ...
    "connect_timeout",20);
% Test the database connection again
status = testConnection(opts,username,password);
% Save the configured data source
saveAsDataSource(opts);

2.2 rmoptions

The rmoptions function is used to remove one or more specified connection options from the SQLConnectionOptions object.

Syntax :

opts = rmoptions(opts,option)

Description :
It removes the specified connection options from the SQLConnectionOptions object opts .

Example :

% Retrieve the existing PostgreSQL native interface data source
datasource = "PostgreSQLDataSource";
opts = databaseConnectionOptions(datasource);
% Add a driver - specific connection option
opts = setoptions(opts,"connect_timeout",20);
% Test the database connection
username = "dbdev";
password = "matlab";
status = testConnection(opts,username,password);
% Remove the driver - specific option
opts = rmoptions(opts,"connect_timeout");
% Test the database connection again
status = testConnection(opts,username,password);
% Save the data source
saveAsDataSource(opts);

2.3 saveAsDataSource

The saveAsDataSource function is used to save the PostgreSQL native interface data source specified by the SQLConnectionOptions object.

Syntax :

saveAsDataSource(opts)

Description :
It saves the data source defined by the SQLConnectionOptions object opts .

Example :

% Create a PostgreSQL native interface data source
vendor = "PostgreSQL";
opts = databaseConnectionOptions("native",vendor);
% Configure the data source
opts = setoptions(opts, ...
    'DataSourceName',"PostgreSQLDataSource", ...
    'DatabaseName',"toystore_doc",'Server',"dbtb00", ...
    'PortNumber',5432);
% Test the database connection
username = "dbdev";
password = "matlab";
status = testConnection(opts,username,password);
% Save the configured data source
saveAsDataSource(opts);

2.4 setoptions

The setoptions function is used to set connection options using the SQLConnectionOptions object.

Syntax :

opts = setoptions(opts,Option1,OptionValue1,...,OptionN,OptionValueN)

Description :
It sets connection options for the SQLConnectionOptions object opts using name - value pair arguments.

Example :

% Create a PostgreSQL native interface data source
vendor = "PostgreSQL";
opts = databaseConnectionOptions("native",vendor);
% Configure the data source
opts = setoptions(opts, ...
    'DataSourceName',"PostgreSQLDataSource", ...
    'DatabaseName',"toystore_doc",'Server',"dbtb00", ...
    'PortNumber',5432);
% Test the database connection
username = "dbdev";
password = "matlab";
status = testConnection(opts,username,password);
% Save the configured data source
saveAsDataSource(opts);

2.5 testConnection

The testConnection function is used to test the PostgreSQL native interface database connection.

Syntax :

status = testConnection(opts,username,password);
[status,message] = testConnection(opts,username,password);

Description :
The first syntax tests the database connection and returns a logical value indicating the connection status. The second syntax also returns the error message associated with the test.

Example :

% Create a PostgreSQL native interface data source
vendor = "PostgreSQL";
opts = databaseConnectionOptions("native",vendor);
% Configure the data source
opts = setoptions(opts, ...
    'DataSourceName',"PostgreSQLDataSource", ...
    'DatabaseName',"toystore_doc",'Server',"dbtb00", ...
    'PortNumber',5432);
% Test the database connection
username = "dbdev";
password = "matlab";
status = testConnection(opts,username,password);

3. SQL Execution Functions

3.1 execute

The execute function is used to execute an SQL query that contains a non - SELECT SQL statement using the PostgreSQL native interface database connection.

Syntax :

execute(conn,sqlquery)

Description :
It executes a non - SELECT SQL statement using the relational database connection.

Example :

% Connect to the database
datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);
% Load patient information
patients = readtable("patients.xls");
% Create the patients database table
tablename = "patients";
sqlwrite(conn,tablename,patients);
% Import the data from the patients database table
data = sqlread(conn,tablename);
% Delete the patients database table
sqlquery = strcat("DROP TABLE ",tablename);
execute(conn,sqlquery);
% Ensure that the table no longer exists
data = sqlfind(conn,tablename);
% Close the database connection
close(conn);

3.2 executeSQLScript

The executeSQLScript function is used to execute an SQL script on a PostgreSQL database.

Syntax :

results = executeSQLScript(conn,scriptfile);
results = executeSQLScript(conn,scriptfile,Name,Value);

Description :
The first syntax executes the SQL script and returns a structure array with results for each SQL SELECT statement. The second syntax allows specifying additional options using name - value pair arguments.

Example :

% Connect to the database
datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);
% Execute the SQL script
scriptfile = "compare_sales.sql";
results = executeSQLScript(conn,scriptfile);
% Display the first eight rows of imported data for the second SQL query
data = head(results(2).Data);
% Retrieve the variable names in the table
names = data.Properties.VariableNames;
% Determine the highest sales amount in January
max(data.jan_sales);
% Close the database connection
close(conn);

4. Input and Output Arguments Summary

Function	Input Arguments	Output Arguments
reset	opts (SQLConnectionOptions object)	opts (SQLConnectionOptions object)
rmoptions	opts (SQLConnectionOptions object), option (character vector, string scalar, cell array of character vectors, or string array)	opts (SQLConnectionOptions object)
saveAsDataSource	opts (SQLConnectionOptions object)	None
setoptions	opts (SQLConnectionOptions object), Option1,OptionValue1,...,OptionN,OptionValueN (name - value pair arguments)	opts (SQLConnectionOptions object)
testConnection	opts (SQLConnectionOptions object), username (character vector or string scalar), password (character vector or string scalar)	status (logical), message (character vector, optional)
execute	conn (connection object), sqlquery (character vector or string scalar)	None
executeSQLScript	conn (connection object), scriptfile (character vector or string scalar), Name - Value Pair Arguments (optional)	results (structure array)

5. Flowchart for PostgreSQL Native Interface Operations

graph TD;
    A[Create Data Source] --> B[Configure Data Source];
    B --> C[Test Connection];
    C -->|Success| D[Save Data Source];
    D --> E[Execute SQL Statements];
    E --> F[Execute SQL Script];
    C -->|Failure| G[Check Options];
    G --> B;

This flowchart shows the general workflow for working with the PostgreSQL native interface. First, a data source is created, then it is configured. After that, the connection is tested. If the test is successful, the data source is saved, and SQL statements and scripts can be executed. If the test fails, the options need to be checked and re - configured.

6. Detailed Analysis of Key Functions

6.1 reset Function

The reset function plays a crucial role in reverting the connection options of a PostgreSQL native interface to their default values. This is useful when you want to start fresh or undo any custom configurations.

• Default Values : The default values for the SQLConnectionOptions object include an empty DataSourceName , “PostgreSQL” as the Vendor , an empty DatabaseName , “localhost” as the Server , and PortNumber set to 5432.
• Driver - Specific Properties : It also removes any additional driver - specific properties that were added. For example, if you had set a connect_timeout option, it will be removed after calling reset .

6.2 executeSQLScript Function

The executeSQLScript function allows you to execute multiple SQL statements stored in a script file. This is beneficial when you have a series of related SQL operations to perform.

• Data Return Formats : You can specify different data return formats using the DataReturnFormat name - value pair argument. The available formats are “table” (default), “cellarray”, “numeric”, and “structure”.
  • Table : Returns the results as a table, which is easy to view and manipulate in MATLAB.
  • Cellarray : Returns the results as a cell array, which can be useful for more flexible data handling.
  • Numeric : Returns the results as a numeric matrix, suitable for numerical analysis.
  • Structure : Returns the results as a structure array, which can be convenient for accessing specific fields.
• Error Handling : The ErrorHandling name - value pair argument provides two options: “report” (default) and “store”.
  • Report : When an SQL statement fails, it stops the execution of the remaining statements and displays an error message at the command line.
  • Store : When an SQL statement fails, it stores the error message in the Message field of the results structure array, allowing you to handle the errors later.

7. Use Cases and Best Practices

7.1 Creating and Managing Data Sources

• Initial Setup : When creating a new data source, start by using databaseConnectionOptions to get the basic options. Then, use setoptions to configure the data source according to your needs, such as setting the DataSourceName , DatabaseName , Server , and PortNumber .
• Testing and Saving : Always test the connection using testConnection before saving the data source with saveAsDataSource . This ensures that the data source is properly configured and can be connected to.

7.2 Executing SQL Statements

• Non - SELECT Statements : Use the execute function to execute non - SELECT SQL statements, such as creating or deleting tables. For example, to delete a table, you can construct an SQL query like DROP TABLE tablename and pass it to the execute function.
• SQL Scripts : When you have multiple SQL statements to execute, it is more efficient to use executeSQLScript . Make sure the SQL script file follows the correct syntax, including proper commenting and statement termination.

8. Limitations of the Functions

8.1 executeSQLScript Limitations

• Script Format : The SQL script file must be a text file and can contain comments. Single - line comments start with -- , and multiline comments are enclosed in /*...*/ . However, continuous PL/SQL blocks with BEGIN and END , such as stored procedure definitions or trigger definitions, are not supported.
• Apostrophes and Nested Comments : Apostrophes that are not escaped, including those in comments, can cause issues. Also, nested comments can produce unexpected results.
• File Size : An SQL script containing more than 25,000 characters will cause the executeSQLScript function to return an error.

9. Summary of Key Points

Function	Purpose	Key Features
reset	Reset connection options to default values	Removes driver - specific properties
rmoptions	Remove specified connection options	Can remove multiple options at once
saveAsDataSource	Save the configured data source	Ensures the data source can be reused
setoptions	Set connection options	Uses name - value pair arguments
testConnection	Test the database connection	Returns connection status and optional error message
execute	Execute non - SELECT SQL statements	Works with relational database connections
executeSQLScript	Execute SQL script	Supports different data return formats and error handling

10. Conclusion

In conclusion, the functions related to the PostgreSQL native interface provide a comprehensive set of tools for managing database connections and executing SQL statements. By understanding the purpose and usage of each function, you can effectively create, configure, test, and execute operations on a PostgreSQL database. Whether you are a beginner or an experienced user, these functions can help you streamline your database management tasks and make the most of the PostgreSQL database in MATLAB.

11. Flowchart for executeSQLScript Function

graph TD;
    A[Connect to Database] --> B[Specify Script File];
    B --> C[Choose Data Return Format];
    C --> D[Choose Error Handling];
    D --> E[Execute SQL Script];
    E --> F{Success?};
    F -->|Yes| G[Process Results];
    F -->|No| H[Handle Errors];

This flowchart shows the steps involved in using the executeSQLScript function. First, you connect to the database, then specify the script file. After that, you choose the data return format and error handling method. The SQL script is then executed, and depending on the result, you either process the results or handle the errors.