1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<!-- This document was generated using DocBuilder 3.3.3 -->
<HTML>
<HEAD>
<TITLE>Databases</TITLE>
<SCRIPT type="text/javascript" src="../../../../doc/erlresolvelinks.js">
</SCRIPT>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#FF00FF"
ALINK="#FF0000">
<CENTER>
<A HREF="http://www.erlang.se"><IMG BORDER=0 ALT="[Ericsson AB]" SRC="min_head.gif"></A>
</CENTER>
<A NAME="3"><!-- Empty --></A>
<H2>3 Databases</H2>
<A NAME="3.1"><!-- Empty --></A>
<H3>3.1 Databases</H3>
<P> If you need to access a relational database such as
<CODE>sqlserver</CODE>, <CODE>mysql</CODE>, <CODE>postgress</CODE>, <CODE>oracle</CODE>,
<CODE>cybase</CODE> etc. from your erlang application using the Erlang
ODBC interface is the way to go about it.
<P>
<P>The Erlang ODBC application should work for any relational
database that has an ODBC driver. But currently it is only
tested for <CODE>sqlserver</CODE> and <CODE>oracle</CODE>.
<A NAME="3.2"><!-- Empty --></A>
<H3>3.2 Database independence </H3>
<P> The Erlang ODBC interface is in principal database
independent, e.i. an erlang program using the interface could be
run without changes towards different databases. But as SQL is
used it is alas possible to write database dependent
programs. Even though SQL is an ANSI-standard meant to be
database independent, different databases have proprietary
extensions to SQL defining their own data types. If you keep to
the ANSI data types you will minimize the problem. But
unfortunately there is no guarantee that all databases actually
treats the ANSI data types equivalently. For instance an
installation of <CODE>Oracle Enterprise release 8.0.5.0.0 for
unix</CODE> will accept that you create a table column with the
ANSI data type <CODE>integer</CODE>, but when retrinving values from
this column the driver reports that it is of type
<CODE>SQL_DECIMAL(0, 38)</CODE> and not <CODE>SQL_INTEGER</CODE> as you may have
expected.
<P> Another obstacle is that some drivers do not support scrollable
cursors which has the effect that the only way to traverse the
result set is sequentially, with next, from the first row to the
last, and once you pass a row you can not go back. This means
that some functions in the interface will not work together with
certain drivers. A similar problem is that not all drivers
support "row count" for select queries, hence resulting in that
the function <CODE>select_count/[3,4]</CODE> will return <CODE>{ok,
undefined}</CODE> instead of <CODE>{ok, NrRows}</CODE> where
<CODE>NrRows</CODE> is the number of rows in the result set.
<A NAME="3.3"><!-- Empty --></A>
<H3>3.3 Data types </H3>
<P> The following is a list of the ANSI data types. For details
turn to the ANSI standard documentation. Usage of other data types
is of course possible, but you should be aware that this makes your
application dependent on the database you are using at the moment.
<P>
<UL>
<LI>
CHARACTER (size), CHAR (size)
</LI>
<LI>
NUMERIC (precision, scale), DECIMAL (precision, scale), DEC
(precision, scale ) precision - total number of digits, scale
- total number of decimal places
</LI>
<LI>
INTEGER, INT, SMALLINT
</LI>
<LI>
FLOAT (precision)
</LI>
<LI>
REAL
</LI>
<LI>
DOUBLE PRECISION
</LI>
<LI>
CHARACTER VARYING(size), CHAR VARYING(size)
</LI>
</UL>
<P> When inputing data using sql_query/[2,3] the values will
always be in string format as they are part of an SQL-query.
Example:
<PRE>
odbc:sql_query(Ref, "INSERT INTO TEST VALUES(1, 2, 3)").
</PRE>
<P>
<TABLE CELLPADDING=4>
<TR>
<TD VALIGN=TOP><IMG ALT="Note!" SRC="note.gif"></TD>
<TD>
<P> Note that when the value of the data to input is a string, it
has to be quoted with <CODE>'</CODE>. Example:
<PRE>
odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(1, 'Jane', 'Doe', 'F')").
</PRE>
</TD>
</TR>
</TABLE>
<P>You may also input data using <A HREF="odbc.html#param_query">param_query/[3,4]</A> and then
the input data will have the Erlang type corresponding to the
ODBC type of the column.<A HREF="#type"> See ODBC to
Erlang mapping</A>
<P> <A NAME="type"><!-- Empty --></A> When selecting data from a table, all data
types are returned from the database to the ODBC driver as an
ODBC data type. The tables below shows the mapping between those
data types and what is returned by the Erlang API.
<P>
<CENTER>
<TABLE CELLSPACING=0 CELLPADDING=2 BORDER=1>
<CAPTION ALIGN=BOTTOM><EM>Mapping of ODBC data types to the Erlang data types
returned to the Erlang application.</EM></CAPTION>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
ODBC Data Type
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
Erlang Data Type
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_CHAR(size)
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
String
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_NUMERIC(p,s) <BR>
when (p >= 0 and p <= 9 and s == 0)
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
Integer
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_NUMERIC(p,s) <BR>
when (p >= 10 and p <= 15 and s == 0)
or (s <= 15 and s > 0)
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
Float
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_NUMERIC(p,s)<BR>
when p >= 16
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
String
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_DECIMAL(p,s)<BR>
when (p >= 0 and p <= 9 and s == 0)
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
Integer
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_DECIMAL(p,s) <BR>
when (p >= 10 and p <= 15 and s == 0)
or (s <= 15 and s > 0)
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
Float
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_DECIMAL(p,s)<BR>
when p >= 16
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
String
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_INTEGER
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
Integer
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_SMALLINT
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
Integer
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_FLOAT
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
Float
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_REAL
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
Float
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_DOUBLE
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
Float
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_VARCHAR(size)
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
String
</TD>
</TR>
</TABLE>
</CENTER>
<P>
<CENTER>
<TABLE CELLSPACING=0 CELLPADDING=2 BORDER=1>
<CAPTION ALIGN=BOTTOM><EM>Mapping of extended ODBC data types to the Erlang data
types returned to the Erlang application.</EM></CAPTION>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
ODBC Data Type
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
Erlang Data Type
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_TYPE_DATE
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
String
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_TYPE_TIME
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
String
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_TYPE_TIMESTAMP
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
String
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_LONGVARCHAR
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
String
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_BINARY
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
String
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_VARBINARY
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
String
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_LONGVARBINARY
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
String
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_TINYINT
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
Integer
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
SQL_BIT
</TD>
<TD ALIGN="LEFT" VALIGN="MIDDLE">
Boolean
</TD>
</TR>
</TABLE>
</CENTER>
<P>
<TABLE CELLPADDING=4>
<TR>
<TD VALIGN=TOP><IMG ALT="Note!" SRC="note.gif"></TD>
<TD>
<P>To find out which data types will be returned for the
columns in a table use the function <A HREF="odbc.html#describe_table">describe_table/[2,3]</A> </TD>
</TR>
</TABLE>
<A NAME="3.4"><!-- Empty --></A>
<H3>3.4 Batch handling</H3>
<P>Grouping of SQL queries can be desirable in order to reduce
network traffic. Another benefit can be that the data source
sometimes can optimize execution of a batch of SQL queries.
<P>Explicit batches an procedures described below will result
in multiple results being returned from sql_query/[2,3].
while with parameterized queries only one result will be returned
from param_query/[2,3].<A NAME="3.4.1"><!-- Empty --></A>
<H4>3.4.1 Explicit batches</H4>
<P>The most basic form of a batch is created by semicolons
separated SQL queries, for example:
<PRE>
"SELECT * FROM FOO; SELECT * FROM BAR" or
"INSERT INTO FOO VALUES(1,'bar'); SELECT * FROM FOO"
</PRE>
<A NAME="3.4.2"><!-- Empty --></A>
<H4>3.4.2 Procedures </H4>
<P> Different databases may also support creating of procedures
that contains more than one SQL query. For example, the
following SQLServer-specific statement creates a procedure that
returns a result set containing information about employees
that work at the department and and a result set listing the
customers of that department.
<PRE>
CREATE PROCEDURE DepartmentInfo (@DepartmentID INT) AS
SELECT * FROM Employee WHERE department = @DepartmentID
SELECT * FROM Customers WHERE department = @DepartmentID
</PRE>
<A NAME="3.4.3"><!-- Empty --></A>
<H4>3.4.3 Parameterized queries</H4>
<P>To effectively perform a batch of similar queries, you can use
parameterized queries. This means that you in your SQL query
string will mark the places that usually would contain values
with question marks and then provide lists of values for each
parameter. For instance you can use this to insert multiple
rows into the <CODE>EMPLOYEE</CODE> table while executing only a
single SQL statement, for example code see <A HREF="getting_started.html#param_query"> "Using the Erlang
API"</A> section in the "Getting Started" chapter.<CENTER>
<HR>
<SMALL>
Copyright © 1991-2006
<A HREF="http://www.erlang.se">Ericsson AB</A><BR>
</SMALL>
</CENTER>
</BODY>
</HTML>
|
