2.开始 | 2. Getting started

2开始

2.1设置

由于Erlang ODBC应用程序依赖于第三方产品，因此在启动和运行之前需要完成一些管理工作。

您需要做的第一件事是确保您为要访问的数据库安装了ODBC驱动程序。计划运行erlang节点的客户端计算机和运行数据库的服务器计算机都需要ODBC驱动程序。（在某些情况下，客户端和服务器可能是同一台机器）。

其次，您可能需要将环境变量和路径设置为适当的值。这在不同的操作系统，数据库和ODBC驱动程序之间可能会有很大差异。这是与第三方产品相关的配置问题，因此我们无法在本指南中为您提供标准解决方案。

Erlang ODBC应用程序由两者Erlang和C代码组成。该C代码作为商业版本中的windows，solaris和linux（SLES10）的预编译可执行文件提供。在开源发行版中，它的构建方式与使用configure和make的所有其他应用程序的构建方式相同。您可能希望使用--with-odbc = PATH提供ODBC库的路径。

注

Erlang ODBC应用程序应该运行在包括Linux，Windows 2000，Windows XP和NT在内的所有Unix方言上。但目前它仅针对Solaris，Windows 2000，Windows XP和NT进行了测试。

2.2使用ErlangAPI

Erlang shell中的以下对话框演示了Erlang ODBC接口的功能。该示例中使用的表与实际中存在的任何内容没有任何关联，它只是一个简单的示例。该示例是使用sqlserver 7.0 with servicepack 1数据库和ODBC驱动程序为sqlserver版本创建的2000.80.194.00。

1 > odbc:start().
     ok

连接到数据库

2 > {ok, Ref} = odbc:connect("DSN=sql-server;UID=aladdin;PWD=sesame", []).
     {ok,<0.342.0>}

创建一个表

3 > odbc:sql_query(Ref, "CREATE TABLE EMPLOYEE (NR integer,
     FIRSTNAME  char varying(20), LASTNAME  char varying(20), GENDER char(1),
     PRIMARY KEY(NR))").
     {updated,undefined}

插入一些数据

4 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(1, 'Jane', 'Doe', 'F')").
     {updated,1}

检查数据库分配给列的数据类型。希望这不是一个惊喜，有时它可以！这些是如果要执行参数化查询时应使用的数据类型。

5 > odbc:describe_table(Ref, "EMPLOYEE").
     {ok, [{"NR", sql_integer},
           {"FIRSTNAME", {sql_varchar, 20}},
           {"LASTNAME", {sql_varchar, 20}}
           {"GENDER", {sql_char, 1}}]}

使用参数化查询一次插入多行。

6 > odbc:param_query(Ref,"INSERT INTO EMPLOYEE (NR, FIRSTNAME, "
                 "LASTNAME, GENDER) VALUES(?, ?, ?, ?)",
                  [{sql_integer,[2,3,4,5,6,7,8]},
                   {{sql_varchar, 20},
                            ["John", "Monica", "Ross", "Rachel",
                            "Piper", "Prue", "Louise"]},
                  {{sql_varchar, 20},
                            ["Doe","Geller","Geller", "Green",
                             "Halliwell", "Halliwell", "Lane"]},
                  {{sql_char, 1}, ["M","F","M","F","F","F","F"]}]).
     {updated, 7}

获取表Employee中的所有数据

7> odbc:sql_query(Ref, "SELECT * FROM EMPLOYEE").
   {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],
         [{1,"Jane","Doe","F"},
          {2,"John","Doe","M"},
          {3,"Monica","Geller","F"},
          {4,"Ross","Geller","M"},
          {5,"Rachel","Green","F"},
          {6,"Piper","Halliwell","F"},
          {7,"Prue","Halliwell","F"},
          {8,"Louise","Lane","F"}]]}

关联包含整个表的结果集EMPLOYEE连接。返回结果集中的行数。

8 > odbc:select_count(Ref, "SELECT * FROM EMPLOYEE").
     {ok,8}

始终可以通过使用Next遍历结果集。

9 > odbc:next(Ref).
     {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{1,"Jane","Doe","F"}]}

10 > odbc:next(Ref).
     {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{2,"John","Doe","M"}]}

如果您的驱动程序支持可滚动游标，则您有更多的自由度，并且可以这样做。

11 > odbc:last(Ref).
     {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{8,"Louise","Lane","F"}]}

12 > odbc:prev(Ref).
     {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{7,"Prue","Halliwell","F"}]}

13 > odbc:first(Ref).
     {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{1,"Jane","Doe","F"}]}

14 > odbc:next(Ref).
     {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{2,"John","Doe","M"}]}

把田里拿来FIRSTNAME和NR所有女性雇员

15 > odbc:sql_query(Ref, "SELECT FIRSTNAME, NR FROM EMPLOYEE WHERE GENDER = 'F'").
    {selected,["FIRSTNAME","NR"],
         [{"Jane",1},
          {"Monica",3},
          {"Rachel",5},
          {"Piper",6},
          {"Prue",7},
          {"Louise",8}]}

领取领域FIRSTNAME和NR所有女性员工，并在现场分类FIRSTNAME。

16 > odbc:sql_query(Ref, "SELECT FIRSTNAME, NR FROM EMPLOYEE WHERE GENDER = 'F'
     ORDER BY FIRSTNAME").
   {selected,["FIRSTNAME","NR"],
         [{"Jane",1},
          {"Louise",8},
          {"Monica",3},
          {"Piper",6},
          {"Prue",7},
          {"Rachel",5}]}

将包含字段的结果集FIRSTNAME与NR所有女性员工的结果集关联到连接。返回结果集中的行数。

17 > odbc:select_count(Ref, "SELECT FIRSTNAME, NR FROM EMPLOYEE WHERE GENDER = 'F'").
     {ok,6}

当驱动程序支持可滚动游标时，还可以通过几种方法检索结果集的部分。请注意，即使不支持可滚动游标，Next也能工作。

18 > odbc:select(Ref, {relative, 2}, 3).
   {selected,["FIRSTNAME","NR"],[{"Monica",3},{"Rachel",5},{"Piper",6}]}

19 > odbc:select(Ref, next, 2).
     {selected,["FIRSTNAME","NR"],[{"Prue",7},{"Louise",8}]}

20 > odbc:select(Ref, {absolute, 1}, 2).
     {selected,["FIRSTNAME","NR"],[{"Jane",1},{"Monica",3}]}

21 > odbc:select(Ref, next, 2).
   {selected,["FIRSTNAME","NR"],[{"Rachel",5},{"Piper",6}]}

22 > odbc:select(Ref, {absolute, 1}, 4). 
     {selected,["FIRSTNAME","NR"],
               [{"Jane",1},{"Monica",3},{"Rachel",5},{"Piper",6}]}

使用参数化查询选择。

23 > odbc:param_query(Ref, "SELECT * FROM EMPLOYEE WHERE GENDER=?",
     [{{sql_char, 1}, ["M"]}]).
     {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],
               [{2,"John", "Doe", "M"},{4,"Ross","Geller","M"}]}

删除表格EMPLOYEE。

24 > odbc:sql_query(Ref, "DROP TABLE EMPLOYEE").
     {updated,undefined}

关闭连接。

25 > odbc:disconnect(Ref).
     ok

关闭申请。

26 > odbc:stop().
   =INFO REPORT==== 7-Jan-2004::17:00:59 ===
   application: odbc
   exited: stopped
   type: temporary

   ok