首页
学习
活动
专区
工具
TVP
发布
精选内容/技术社群/优惠产品,尽在小程序
立即前往

2.开始 | 2. Getting started

2.1设置

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

  • 您需要做的第一件事是确保您为要访问的数据库安装了ODBC驱动程序。计划运行erlang节点的客户端计算机和运行数据库的服务器计算机都需要ODBC驱动程序。(在某些情况下,客户端和服务器可能是同一台机器)。
  • 其次,您可能需要将环境变量和路径设置为适当的值。这在不同的操作系统,数据库和ODBC驱动程序之间可能会有很大差异。这是与第三方产品相关的配置问题,因此我们无法在本指南中为您提供标准解决方案。
  • Erlang ODBC应用程序由两者ErlangC代码组成。该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

代码语言:javascript
复制
1 > odbc:start().
     ok    

连接到数据库

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

创建一个表

代码语言:javascript
复制
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}    

插入一些数据

代码语言:javascript
复制
4 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(1, 'Jane', 'Doe', 'F')").
     {updated,1}    

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

代码语言:javascript
复制
5 > odbc:describe_table(Ref, "EMPLOYEE").
     {ok, [{"NR", sql_integer},
           {"FIRSTNAME", {sql_varchar, 20}},
           {"LASTNAME", {sql_varchar, 20}}
           {"GENDER", {sql_char, 1}}]}
   

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

代码语言:javascript
复制
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中的所有数据

代码语言:javascript
复制
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连接。返回结果集中的行数。

代码语言:javascript
复制
8 > odbc:select_count(Ref, "SELECT * FROM EMPLOYEE").
     {ok,8}     

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

代码语言:javascript
复制
9 > odbc:next(Ref).
     {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{1,"Jane","Doe","F"}]}
   
代码语言:javascript
复制
10 > odbc:next(Ref).
     {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{2,"John","Doe","M"}]}
   

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

代码语言:javascript
复制
11 > odbc:last(Ref).
     {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{8,"Louise","Lane","F"}]}    
代码语言:javascript
复制
12 > odbc:prev(Ref).
     {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{7,"Prue","Halliwell","F"}]}    
代码语言:javascript
复制
13 > odbc:first(Ref).
     {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{1,"Jane","Doe","F"}]}        
代码语言:javascript
复制
14 > odbc:next(Ref).
     {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{2,"John","Doe","M"}]}
   

把田里拿来FIRSTNAMENR所有女性雇员

代码语言:javascript
复制
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}]}     

领取领域FIRSTNAMENR所有女性员工,并在现场分类FIRSTNAME

代码语言:javascript
复制
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}]}
   

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

代码语言:javascript
复制
17 > odbc:select_count(Ref, "SELECT FIRSTNAME, NR FROM EMPLOYEE WHERE GENDER = 'F'").
     {ok,6}    

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

代码语言:javascript
复制
18 > odbc:select(Ref, {relative, 2}, 3).
   {selected,["FIRSTNAME","NR"],[{"Monica",3},{"Rachel",5},{"Piper",6}]}
   
代码语言:javascript
复制
19 > odbc:select(Ref, next, 2).
     {selected,["FIRSTNAME","NR"],[{"Prue",7},{"Louise",8}]}
   
代码语言:javascript
复制
20 > odbc:select(Ref, {absolute, 1}, 2).
     {selected,["FIRSTNAME","NR"],[{"Jane",1},{"Monica",3}]}
   
代码语言:javascript
复制
21 > odbc:select(Ref, next, 2).
   {selected,["FIRSTNAME","NR"],[{"Rachel",5},{"Piper",6}]}
   
代码语言:javascript
复制
22 > odbc:select(Ref, {absolute, 1}, 4). 
     {selected,["FIRSTNAME","NR"],
               [{"Jane",1},{"Monica",3},{"Rachel",5},{"Piper",6}]}
   

使用参数化查询选择。

代码语言:javascript
复制
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

代码语言:javascript
复制
24 > odbc:sql_query(Ref, "DROP TABLE EMPLOYEE").
     {updated,undefined}
   

关闭连接。

代码语言:javascript
复制
25 > odbc:disconnect(Ref).
     ok
   

关闭申请。

代码语言:javascript
复制
26 > odbc:stop().
   =INFO REPORT==== 7-Jan-2004::17:00:59 ===
   application: odbc
   exited: stopped
   type: temporary

   ok
   

扫码关注腾讯云开发者

领取腾讯云代金券