codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>36.2. Managing Database Connections</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="ecpg-concept.html" title="36.1. The Concept" /><link rel="next" href="ecpg-commands.html" title="36.3. Running SQL Commands" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">36.2. Managing Database Connections</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ecpg-concept.html" title="36.1. The Concept">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="ecpg.html" title="Chapter 36. ECPG — Embedded SQL in C">Up</a></td><th width="60%" align="center">Chapter 36. <span class="application">ECPG</span> — Embedded <acronym class="acronym">SQL</acronym> in C</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="ecpg-commands.html" title="36.3. Running SQL Commands">Next</a></td></tr></table><hr /></div><div class="sect1" id="ECPG-CONNECT"><div class="titlepage"><div><div><h2 class="title" style="clear: both">36.2. Managing Database Connections <a href="#ECPG-CONNECT" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="ecpg-connect.html#ECPG-CONNECTING">36.2.1. Connecting to the Database Server</a></span></dt><dt><span class="sect2"><a href="ecpg-connect.html#ECPG-SET-CONNECTION">36.2.2. Choosing a Connection</a></span></dt><dt><span class="sect2"><a href="ecpg-connect.html#ECPG-DISCONNECT">36.2.3. Closing a Connection</a></span></dt></dl></div><p>3 This section describes how to open, close, and switch database4 connections.5 </p><div class="sect2" id="ECPG-CONNECTING"><div class="titlepage"><div><div><h3 class="title">36.2.1. Connecting to the Database Server <a href="#ECPG-CONNECTING" class="id_link">#</a></h3></div></div></div><p>6 One connects to a database using the following statement:7</p><pre class="programlisting">8EXEC SQL CONNECT TO <em class="replaceable"><code>target</code></em> [<span class="optional">AS <em class="replaceable"><code>connection-name</code></em></span>] [<span class="optional">USER <em class="replaceable"><code>user-name</code></em></span>];9</pre><p>10 The <em class="replaceable"><code>target</code></em> can be specified in the11 following ways:12 13 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem">14 <code class="literal"><em class="replaceable"><code>dbname</code></em>[<span class="optional">@<em class="replaceable"><code>hostname</code></em></span>][<span class="optional">:<em class="replaceable"><code>port</code></em></span>]</code>15 </li><li class="listitem">16 <code class="literal">tcp:postgresql://<em class="replaceable"><code>hostname</code></em>[<span class="optional">:<em class="replaceable"><code>port</code></em></span>][<span class="optional">/<em class="replaceable"><code>dbname</code></em></span>][<span class="optional">?<em class="replaceable"><code>options</code></em></span>]</code>17 </li><li class="listitem">18 <code class="literal">unix:postgresql://localhost[<span class="optional">:<em class="replaceable"><code>port</code></em></span>][<span class="optional">/<em class="replaceable"><code>dbname</code></em></span>][<span class="optional">?<em class="replaceable"><code>options</code></em></span>]</code>19 </li><li class="listitem">20 an SQL string literal containing one of the above forms21 </li><li class="listitem">22 a reference to a character variable containing one of the above forms (see examples)23 </li><li class="listitem">24 <code class="literal">DEFAULT</code>25 </li></ul></div><p>26 27 The connection target <code class="literal">DEFAULT</code> initiates a connection28 to the default database under the default user name. No separate29 user name or connection name can be specified in that case.30 </p><p>31 If you specify the connection target directly (that is, not as a string32 literal or variable reference), then the components of the target are33 passed through normal SQL parsing; this means that, for example,34 the <em class="replaceable"><code>hostname</code></em> must look like one or more SQL35 identifiers separated by dots, and those identifiers will be36 case-folded unless double-quoted. Values of37 any <em class="replaceable"><code>options</code></em> must be SQL identifiers,38 integers, or variable references. Of course, you can put nearly39 anything into an SQL identifier by double-quoting it.40 In practice, it is probably less error-prone to use a (single-quoted)41 string literal or a variable reference than to write the connection42 target directly.43 </p><p>44 There are also different ways to specify the user name:45 46 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem">47 <code class="literal"><em class="replaceable"><code>username</code></em></code>48 </li><li class="listitem">49 <code class="literal"><em class="replaceable"><code>username</code></em>/<em class="replaceable"><code>password</code></em></code>50 </li><li class="listitem">51 <code class="literal"><em class="replaceable"><code>username</code></em> IDENTIFIED BY <em class="replaceable"><code>password</code></em></code>52 </li><li class="listitem">53 <code class="literal"><em class="replaceable"><code>username</code></em> USING <em class="replaceable"><code>password</code></em></code>54 </li></ul></div><p>55 56 As above, the parameters <em class="replaceable"><code>username</code></em> and57 <em class="replaceable"><code>password</code></em> can be an SQL identifier, an58 SQL string literal, or a reference to a character variable.59 </p><p>60 If the connection target includes any <em class="replaceable"><code>options</code></em>,61 those consist of62 <code class="literal"><em class="replaceable"><code>keyword</code></em>=<em class="replaceable"><code>value</code></em></code>63 specifications separated by ampersands (<code class="literal">&</code>).64 The allowed key words are the same ones recognized65 by <span class="application">libpq</span> (see66 <a class="xref" href="libpq-connect.html#LIBPQ-PARAMKEYWORDS" title="34.1.2. Parameter Key Words">Section 34.1.2</a>). Spaces are ignored before67 any <em class="replaceable"><code>keyword</code></em> or <em class="replaceable"><code>value</code></em>,68 though not within or after one. Note that there is no way to69 write <code class="literal">&</code> within a <em class="replaceable"><code>value</code></em>.70 </p><p>71 Notice that when specifying a socket connection72 (with the <code class="literal">unix:</code> prefix), the host name must be73 exactly <code class="literal">localhost</code>. To select a non-default74 socket directory, write the directory's pathname as the value of75 a <code class="varname">host</code> option in76 the <em class="replaceable"><code>options</code></em> part of the target.77 </p><p>78 The <em class="replaceable"><code>connection-name</code></em> is used to handle79 multiple connections in one program. It can be omitted if a80 program uses only one connection. The most recently opened81 connection becomes the current connection, which is used by default82 when an SQL statement is to be executed (see later in this83 chapter).84 </p><p>85 Here are some examples of <code class="command">CONNECT</code> statements:86</p><pre class="programlisting">87EXEC SQL CONNECT TO mydb@sql.mydomain.com;88 89EXEC SQL CONNECT TO tcp:postgresql://sql.mydomain.com/mydb AS myconnection USER john;90 91EXEC SQL BEGIN DECLARE SECTION;92const char *target = "mydb@sql.mydomain.com";93const char *user = "john";94const char *passwd = "secret";95EXEC SQL END DECLARE SECTION;96 ...97EXEC SQL CONNECT TO :target USER :user USING :passwd;98/* or EXEC SQL CONNECT TO :target USER :user/:passwd; */99</pre><p>100 The last example makes use of the feature referred to above as101 character variable references. You will see in later sections how C102 variables can be used in SQL statements when you prefix them with a103 colon.104 </p><p>105 Be advised that the format of the connection target is not106 specified in the SQL standard. So if you want to develop portable107 applications, you might want to use something based on the last108 example above to encapsulate the connection target string109 somewhere.110 </p><p>111 If untrusted users have access to a database that has not adopted a112 <a class="link" href="ddl-schemas.html#DDL-SCHEMAS-PATTERNS" title="5.9.6. Usage Patterns">secure schema usage pattern</a>,113 begin each session by removing publicly-writable schemas114 from <code class="varname">search_path</code>. For example,115 add <code class="literal">options=-c search_path=</code>116 to <code class="literal"><em class="replaceable"><code>options</code></em></code>, or117 issue <code class="literal">EXEC SQL SELECT pg_catalog.set_config('search_path', '',118 false);</code> after connecting. This consideration is not specific to119 ECPG; it applies to every interface for executing arbitrary SQL commands.120 </p></div><div class="sect2" id="ECPG-SET-CONNECTION"><div class="titlepage"><div><div><h3 class="title">36.2.2. Choosing a Connection <a href="#ECPG-SET-CONNECTION" class="id_link">#</a></h3></div></div></div><p>121 SQL statements in embedded SQL programs are by default executed on122 the current connection, that is, the most recently opened one. If123 an application needs to manage multiple connections, then there are124 three ways to handle this.125 </p><p>126 The first option is to explicitly choose a connection for each SQL127 statement, for example:128</p><pre class="programlisting">129EXEC SQL AT <em class="replaceable"><code>connection-name</code></em> SELECT ...;130</pre><p>131 This option is particularly suitable if the application needs to132 use several connections in mixed order.133 </p><p>134 If your application uses multiple threads of execution, they cannot share a135 connection concurrently. You must either explicitly control access to the connection136 (using mutexes) or use a connection for each thread.137 </p><p>138 The second option is to execute a statement to switch the current139 connection. That statement is:140</p><pre class="programlisting">141EXEC SQL SET CONNECTION <em class="replaceable"><code>connection-name</code></em>;142</pre><p>143 This option is particularly convenient if many statements are to be144 executed on the same connection.145 </p><p>146 Here is an example program managing multiple database connections:147</p><pre class="programlisting">148#include <stdio.h>149 150EXEC SQL BEGIN DECLARE SECTION;151 char dbname[1024];152EXEC SQL END DECLARE SECTION;153 154int155main()156{157 EXEC SQL CONNECT TO testdb1 AS con1 USER testuser;158 EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;159 EXEC SQL CONNECT TO testdb2 AS con2 USER testuser;160 EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;161 EXEC SQL CONNECT TO testdb3 AS con3 USER testuser;162 EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;163 164 /* This query would be executed in the last opened database "testdb3". */165 EXEC SQL SELECT current_database() INTO :dbname;166 printf("current=%s (should be testdb3)\n", dbname);167 168 /* Using "AT" to run a query in "testdb2" */169 EXEC SQL AT con2 SELECT current_database() INTO :dbname;170 printf("current=%s (should be testdb2)\n", dbname);171 172 /* Switch the current connection to "testdb1". */173 EXEC SQL SET CONNECTION con1;174 175 EXEC SQL SELECT current_database() INTO :dbname;176 printf("current=%s (should be testdb1)\n", dbname);177 178 EXEC SQL DISCONNECT ALL;179 return 0;180}181</pre><p>182 183 This example would produce this output:184</p><pre class="screen">185current=testdb3 (should be testdb3)186current=testdb2 (should be testdb2)187current=testdb1 (should be testdb1)188</pre><p>189 </p><p>190 The third option is to declare an SQL identifier linked to191 the connection, for example:192</p><pre class="programlisting">193EXEC SQL AT <em class="replaceable"><code>connection-name</code></em> DECLARE <em class="replaceable"><code>statement-name</code></em> STATEMENT;194EXEC SQL PREPARE <em class="replaceable"><code>statement-name</code></em> FROM :<em class="replaceable"><code>dyn-string</code></em>;195</pre><p>196 Once you link an SQL identifier to a connection, you execute dynamic SQL197 without an AT clause. Note that this option behaves like preprocessor198 directives, therefore the link is enabled only in the file.199 </p><p>200 Here is an example program using this option:201</p><pre class="programlisting">202#include <stdio.h>203 204EXEC SQL BEGIN DECLARE SECTION;205char dbname[128];206char *dyn_sql = "SELECT current_database()";207EXEC SQL END DECLARE SECTION;208 209int main(){210 EXEC SQL CONNECT TO postgres AS con1;211 EXEC SQL CONNECT TO testdb AS con2;212 EXEC SQL AT con1 DECLARE stmt STATEMENT;213 EXEC SQL PREPARE stmt FROM :dyn_sql;214 EXEC SQL EXECUTE stmt INTO :dbname;215 printf("%s\n", dbname);216 217 EXEC SQL DISCONNECT ALL;218 return 0;219}220</pre><p>221 222 This example would produce this output, even if the default connection is testdb:223</p><pre class="screen">224postgres225</pre><p>226 </p></div><div class="sect2" id="ECPG-DISCONNECT"><div class="titlepage"><div><div><h3 class="title">36.2.3. Closing a Connection <a href="#ECPG-DISCONNECT" class="id_link">#</a></h3></div></div></div><p>227 To close a connection, use the following statement:228</p><pre class="programlisting">229EXEC SQL DISCONNECT [<span class="optional"><em class="replaceable"><code>connection</code></em></span>];230</pre><p>231 The <em class="replaceable"><code>connection</code></em> can be specified232 in the following ways:233 234 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem">235 <code class="literal"><em class="replaceable"><code>connection-name</code></em></code>236 </li><li class="listitem">237 <code class="literal">CURRENT</code>238 </li><li class="listitem">239 <code class="literal">ALL</code>240 </li></ul></div><p>241 242 If no connection name is specified, the current connection is243 closed.244 </p><p>245 It is good style that an application always explicitly disconnect246 from every connection it opened.247 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ecpg-concept.html" title="36.1. The Concept">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ecpg.html" title="Chapter 36. ECPG — Embedded SQL in C">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="ecpg-commands.html" title="36.3. Running SQL Commands">Next</a></td></tr><tr><td width="40%" align="left" valign="top">36.1. The Concept </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 36.3. Running SQL Commands</td></tr></table></div></body></html>