使用ODBC和ADO访问数据库

ODBC

ADO

 

ODBC

 

在AX中使用ODBC直接访问数据库前，必须在PC上先安装设置ODBC driver，本文末有安装配置ODBC driver的相关引用，查看请点击

 

在ODBC driver安装配置成功后，就可以在AX中使用ODBC来访问数据库了，代码演示如下：

 

LoginProperty loginProperty; OdbcConnection odbcConnection; Statement statement; ResultSet resultSet; str sqlStatement; SqlStatementExecutePermission perm; SysSQLSystemInfo systemInfo = SysSQLSystemInfo::construct();//获取当前AX使用的DB的信息 real real_value; str str_value; int int_value; date date_value; ; loginProperty = new LoginProperty(); loginProperty.setServer(systemInfo.getLoginServer()); loginProperty.setDSN(systemInfo.getloginDSN()); loginProperty.setDatabase(systemInfo.getloginDatabase()); odbcConnection = new OdbcConnection(loginProperty); if (odbcConnection) { try { sqlStatement = 'SELECT str_value, real_value, date_value, int_value ' + 'FROM tablename'; perm = new SqlStatementExecutePermission(sqlStatement); perm.assert(); statement = odbcConnection.createStatement(); resultSet = statement.executeQuery(sqlStatement); while ( resultSet && resultSet.next()) { // 必须安装顺序读取，每个列只能读一次 str_value = resultSet.getString(1); real_value = resultSet.getReal(2); date_value = resultSet.getDate(3); int_value = resultSet.getInt(4); } if (resultSet) { resultSet.close(); } if (statement) { statement.close(); } } catch { if (resultSet) { resultSet.close(); } if (statement) { statement.close(); } } } else { error("Failed to log on to the database through ODBC."); }

 

ADO

ADO连接DBF数据库, 引用自刘红超的日志，不保证代码正确，因为我自己没有运行过这段代码，原文地址：http://hi.erp100.com/space-13759-do-blog-id-13551442.html

static void ADOReadDbf(){ str adoSqlcmd; CCADORecordSet adoRecordSet; CCADOConnection ccConnection; CCADOFields adoFields; ; ccConnection=new CCADOConnection(); ccConnection.connectionString(strfmt("provider=msdasql;DRIVER=Microsoft Visual FoxPro Driver;UID=;Deleted=yes;Null=no;Collate=Machine;BackgroundFetch=no;Exclusive=No;SourceType=DBF;SourceDB=%1;",'C:/data')); adoSqlcmd =strfmt('select custNo,Name,address from %1','master.dbf'); adoRecordSet=new CCADORecordSet(); ccConnection.open(); adoRecordSet.open(adoSqlcmd,ccConnection); while(!adoRecordSet.EOF()) { adoFields=adoRecordSet.fields(); info(strfmt('%1',adoFields.itemName("custNo").value())); info(strfmt('%1',adoFields.itemName("name").value())); adoRecordSet.moveNext(); } adoRecordSet.close(); ccConnection.close(); }

 

安装配置ODBC driver，原文地址：http://www.xtelsio.com/hlp/en/client/and/windows_odbc-manager.htm

 

Tips to Windows ODBC manager "ODBC Data Source Administrator"Unfortunately, the Windows ODBC manager is not easy to use for the first time user. The following tips should make the entrance easier.
Function of Windows ODBC manager

• The Windows ODBC manager administers so-called DSNs (Data Source Names) which represent a data source (e.g., an Access database). A DSN contains all information which is needed for opening a data source, as for example: File path to the Access data base, user name and password, special data source parametres.
• The DSN to a data source should be created by the administrator once on a PC and can be used afterwards by applications.
  The DSN name can be freely chosen, as for example "My customer database ".
• Path to open the ODBC-Manger in Windows:
  [Control Panel > Administrative Tools > Data Sources (ODBC)]

Create a new DSN (example with Access)

...during setup of a new folder linking:

1. You are in the dialog "Select Data Source" of the ODBC manager.
2. Click on [Add...]
   (You do not need to enter a name in field "DSN name", the name is questioned later.)
3. Select in the following dialog the suitable driver to your data source, - "Microsoft Access driver (*.mdb)" for example.
   Hint: Some drivers can be listed several times in different language versions what has influence on possible error messages.
4. After [Next >] the DSN name is to be entered now.
   e.g., " My customer database "
   Click on [Next >] and [Finish].
5. The following dialog comes from the selected database driver.
   In the Access driver dialog, the the desired *.mdb file would be selected via [Select...] button.
6. With [OK] the settings are taken over and now the DSN name should be visible in the ODBC manager.
7. With a further [OK] the ODBC manager is closed. Now in the CTI-Client-Dialog "New Folder Linking" all tables of the Access database should be visible.

Create a new DSN via Control Panel:

1. Open the ODBC manager via: [Control Panel > Administrative Tools > Data Sources (ODBC)]
2. Select "File DSN".
3. Click on [Add ...]
4. To the further steps see point 3. and following above.