221 lines
14 KiB
HTML
221 lines
14 KiB
HTML
<?xml version="1.0" encoding="UTF-8"?>
|
|
<!DOCTYPE html
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
|
<html lang="en-us" xml:lang="en-us">
|
|
<head>
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
|
<meta name="security" content="public" />
|
|
<meta name="Robots" content="index,follow" />
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
|
<meta name="DC.Type" content="reference" />
|
|
<meta name="DC.Title" content="Determine equivalent SQL and PL/I data types" />
|
|
<meta name="abstract" content="The precompiler determines the base SQLTYPE and SQLLEN of host variables based on the following table." />
|
|
<meta name="description" content="The precompiler determines the base SQLTYPE and SQLLEN of host variables based on the following table." />
|
|
<meta name="DC.subject" content="PL/I program, SQL data types, determining equivalent PL/I, data type, determining equivalent, PL/I, SQL data types, determining equivalent" />
|
|
<meta name="keywords" content="PL/I program, SQL data types, determining equivalent PL/I, data type, determining equivalent, PL/I, SQL data types, determining equivalent" />
|
|
<meta name="DC.Relation" scheme="URI" content="rzajpplone.htm" />
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
|
|
<meta name="DC.Format" content="XHTML" />
|
|
<meta name="DC.Identifier" content="rzajpequivsqlpli" />
|
|
<meta name="DC.Language" content="en-us" />
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
|
<!-- US Government Users Restricted Rights -->
|
|
<!-- Use, duplication or disclosure restricted by -->
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
|
<title>Determine equivalent SQL and PL/I data types</title>
|
|
</head>
|
|
<body id="rzajpequivsqlpli"><a name="rzajpequivsqlpli"><!-- --></a>
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
|
<h1 class="topictitle1">Determine equivalent SQL and PL/I data types</h1>
|
|
<div><p>The precompiler determines the base SQLTYPE and SQLLEN of host
|
|
variables based on the following table.</p>
|
|
<div class="section"><p>If a host variable appears with an indicator variable, the SQLTYPE
|
|
is the base SQLTYPE plus one.</p>
|
|
</div>
|
|
|
|
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" width="100%" frame="hsides" border="1" rules="all"><caption>Table 1. PL/I declarations mapped to
|
|
typical SQL data types</caption><thead align="left"><tr><th align="left" valign="bottom" width="40%" id="d0e44">PL/I data type</th>
|
|
<th align="left" valign="bottom" width="20%" id="d0e46">SQLTYPE of host variable</th>
|
|
<th align="left" valign="bottom" width="20%" id="d0e48">SQLLEN of host variable</th>
|
|
<th align="left" valign="bottom" width="20%" id="d0e50">SQL data type</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody><tr><td align="left" valign="top" width="40%" headers="d0e44 ">BIN FIXED(p) where p is in the range 1 to
|
|
15</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e46 ">500</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e48 ">2</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e50 ">SMALLINT</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="40%" headers="d0e44 ">BIN FIXED(p) where p is in the range 16 to
|
|
31</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e46 ">496</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e48 ">4</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e50 ">INTEGER</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="40%" headers="d0e44 ">DEC FIXED(p,s)</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e46 ">484</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e48 ">p in byte 1, s in byte 2</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e50 ">DECIMAL(p,s)</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="40%" headers="d0e44 ">BIN FLOAT(p) p is in the range 1 to 24</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e46 ">480</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e48 ">4</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e50 ">FLOAT (single precision)</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="40%" headers="d0e44 ">BIN FLOAT(p) p is in the range 25 to 53</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e46 ">480</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e48 ">8</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e50 ">FLOAT (double precision)</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="40%" headers="d0e44 ">DEC FLOAT(m) m is in the range 1 to 7</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e46 ">480</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e48 ">4</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e50 ">FLOAT (single precision)</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="40%" headers="d0e44 ">DEC FLOAT(m) m is in the range 8 to 16</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e46 ">480</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e48 ">8</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e50 ">FLOAT (double precision)</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="40%" headers="d0e44 ">PICTURE picture string (numeric)</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e46 ">488</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e48 ">p in byte 1, s in byte 2</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e50 ">NUMERIC (p,s)</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="40%" headers="d0e44 ">PICTURE picture string (sign leading separate)</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e46 ">504</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e48 ">p in byte 1, s in byte 2</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e50 ">No exact equivalent, use NUMERIC(p,s).</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="40%" headers="d0e44 ">CHAR(n)</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e46 ">452</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e48 ">n</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e50 ">CHAR(n)</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="40%" headers="d0e44 ">CHAR(n) VARYING </td>
|
|
<td align="left" valign="top" width="20%" headers="d0e46 ">448</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e48 ">n</td>
|
|
<td align="left" valign="top" width="20%" headers="d0e50 ">VARCHAR(n)</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
<div class="section"><p>The following table can be used to determine the PL/I data type
|
|
that is equivalent to a given SQL data type.</p>
|
|
</div>
|
|
|
|
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" width="100%" frame="hsides" border="1" rules="all"><caption>Table 2. SQL data types mapped to typical
|
|
PL/I declarations</caption><thead align="left"><tr><th align="left" valign="bottom" width="33.33333333333333%" id="d0e164">SQL data type</th>
|
|
<th align="left" valign="bottom" width="33.33333333333333%" id="d0e166">PL/I equivalent</th>
|
|
<th align="left" valign="bottom" width="33.33333333333333%" id="d0e168">Notes</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody><tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">SMALLINT</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">BIN FIXED(p)</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 ">p is a positive integer from 1 to 15.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">INTEGER</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">BIN FIXED(p)</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 ">p is a positive integer from 16 to 31.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">BIGINT</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">No exact equivalent</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 ">Use DEC FIXED(18).</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">DECIMAL(p,s) or NUMERIC(p,s)</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">DEC FIXED(p) or DEC FIXED(p,s) or PICTURE
|
|
picture-string</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 "><em>s</em> (the scale factor) and <em>p</em> (the
|
|
precision) are positive integers. <em>p</em> is a positive integer from 1 to
|
|
31. <em>s</em> is a positive integer from 0 to <em>p</em>.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">FLOAT (single precision)</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">BIN FLOAT(p) or DEC FLOAT(m)</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 "><em>p</em> is a positive integer from 1 to
|
|
24. <p><em>m</em> is a positive integer from 1 to 7.</p>
|
|
</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">FLOAT (double precision)</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">BIN FLOAT(p) or DEC FLOAT(m)</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 "><em>p</em> is a positive integer from 25 to
|
|
53. <p><em>m</em> is a positive integer from 8 to 16.</p>
|
|
</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">CHAR(n)</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">CHAR(n)</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 "><em>n</em> is a positive integer from 1 to
|
|
32766.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">VARCHAR(n)</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">CHAR(n) VARYING</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 "><em>n</em> is a positive integer from 1 to
|
|
32740.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">CLOB</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">None</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 ">Use SQL TYPE IS to declare a CLOB.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">GRAPHIC(n)</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">Not supported</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 ">Not supported.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">VARGRAPHIC(n)</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">Not supported</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 ">Not supported.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">DBCLOB</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">Not supported</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 ">Not supported</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">BINARY</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">None</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 ">Use SQL TYPE IS to declare a BINARY.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">VARBINARY</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">None</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 ">Use SQL TYPE IS to declare a VARBINARY.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">BLOB</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">None</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 ">Use SQL TYPE IS to declare a BLOB.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">DATE</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">CHAR(n)</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 ">If the format is *USA, *JIS, *EUR, or *ISO, <em>n</em> must
|
|
be at least 10 characters. If the format is *YMD, *DMY, or *MDY, <em>n</em> must
|
|
be at least 8 characters. If the format is *JUL, <em>n</em> must be at least
|
|
6 characters.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">TIME</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">CHAR(n)</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 "><em>n</em> must be at least 6; to include seconds, <em>n</em> must
|
|
be at least 8.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">TIMESTAMP</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">CHAR(n)</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 "><em>n</em> must be at least 19. To include
|
|
microseconds at full precision, <em>n</em> must be 26; if <em>n</em> is less than
|
|
26, truncation occurs on the microseconds part.</td>
|
|
</tr>
|
|
<tr><td align="left" valign="top" width="33.33333333333333%" headers="d0e164 ">DATALINK</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e166 ">Not supported</td>
|
|
<td align="left" valign="top" width="33.33333333333333%" headers="d0e168 ">Not supported</td>
|
|
</tr>
|
|
<tr><td valign="top" width="33.33333333333333%" headers="d0e164 ">ROWID</td>
|
|
<td valign="top" width="33.33333333333333%" headers="d0e166 ">None</td>
|
|
<td valign="top" width="33.33333333333333%" headers="d0e168 ">Use SQL TYPE IS to declare a ROWID.</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
<div>
|
|
<div class="familylinks">
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzajpplone.htm" title="This topic describes the unique application and coding requirements for embedding SQL statements in an iSeries PL/I program. Requirements for host structures and host variables are defined.">Code SQL statements in PL/I applications</a></div>
|
|
</div>
|
|
</div>
|
|
</body>
|
|
</html> |