MDBTools驱动程序不使用PHP MS-Access返回字符串值 [英] MDBTools driver not returning string values with PHP MS-Access

查看：80
本文介绍了MDBTools驱动程序不使用PHP MS-Access返回字符串值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我们有一个MS Access出勤数据库，该数据库由生物识别硬件更新.因此，没有任何方法可以替代MS Access.现在，我们需要在我们的Intranet网站上提供出勤信息，为此，我们试图定期读取Windows XP计算机上的MS-Access mdb文件，并通过php写入postgres数据库. PHP-Postgres-Apache在Ubuntu 10.04 Server上运行. html页面/报告将从服务器显示. 使用MDB工具从php连接到MS-Access mdb文件时，仅返回数字"和日期/时间"字段(尽管以字符串形式). 文本"字段返回NULL.

We have an MS Access attendance database, which is updated by biometric hardware. So there is no way to replace MS Access. Now we need to provide the attendance information on our intranet web, for which we are trying to periodically read the MS-Access mdb file on a Windows XP computer, and write to a postgres database through php. PHP - Postgres - Apache are running on Ubuntu 10.04 Server. html pages /reports are to be displayed from the server. When using MDB tools to connect to the MS-Access mdb file from php, only the Number and Date/Time fields are returned (though as String). The Text fields return NULL.

PHP代码如下:

$dbName = "/media/winshare/attEngine.mdb";
if (!file_exists($dbName)) 
    die("Could not find database file.");
$dbconn = new PDO("odbc:DRIVER=MDBTools; DBQ=$dbName; Uid=admin; Pwd=pswd;");
if ($dbconn) { 
    echo "mdb connection established.<br />\n";
} else {
    die ("mdb connection could not be established.<br />\n");
}
$qry = "SELECT transactionId, aDate, aDateTime, EmpCode, EmpName, ControllerNum FROM Transactions;"; 
$dbqryprep = $dbconn->prepare($qry);
$dbqryprep->execute();
$result = $dbqryprep->fetchall(PDO::FETCH_ASSOC);

echo "QRY RESULT (from Access):<pre>\n";
var_dump($result);
echo "\n</pre>\n";

此处:transactionId为Access中的自动编号； aDate，aDateTime是日期/时间； EmpCode是数字；和 EmpName和ControllerNum是Access中的文本"字段.

Here: transactionId is AutoNumber in Access; aDate, aDateTime are Date/Time; EmpCode is Number; and EmpName and ControllerNum are Text fields in Access.

当我们加载php时，其结果如下(仅显示前两个数组元素):

When we load the php, it gives result as follows (only first two array-elements shown):

mdb connection established.

QRY RESULT (from Access):

array(31986) {
  [0]=>   array(7) {
    ["transactionId"]=>     string(3) "341"
    ["aDate"]=>     string(17) "11/23/13 00:00:00"
    ["aDateTime"]=>     string(17) "11/23/13 13:01:07"
    ["EmpCode"]=>       string(1) "0"
    ["EmpName"]=>       NULL
    ["ControllerNum"]=>     NULL
  }
  [1]=>   array(7) {
    ["transactionId"]=>     string(3) "342"
    ["aDate"]=>     string(17) "11/23/13 00:00:00"
    ["aDateTime"]=>     string(17) "11/23/13 13:01:12"
    ["EmpCode"]=>       string(1) "0"
    ["EmpName"]=>       NULL
    ["ControllerNum"]=>     NULL
  }

实际上我有两个问题:

  1. 如上所述，我在使用MDBTools时可能会出现什么问题?

  1. What could be the problem in my using MDBTools as above?

还是在Windows计算机上运行/安排脚本以通过odbc连接到Access和postgres以及传输数据更好?如果是这样的话，最好的脚本是什么?

Or is it better to run / schedule scripts on the Windows computer to connect through odbc to Access and postgres, and transfer data? If so what are the best scripts for that?

推荐答案

这是对原始答案的修改:

经过几天的艰苦奋斗，我终于找到了适用于您的线程主题的解决方案( MDBTools驱动程序未使用PHP MS-Access返回字符串值)

After a days of hard struggles, I finally figured out a working solution for your Thread's subject (MDBTools driver not returning string values with PHP MS-Access)

除了我的旧答案(仅限于Text数据类型的127个字段大小)之外，这是我为解决方案所做的新尝试.

In addition to my old answer which is very limited to the 127 field size for the Text datatype, Here is my new attempt for a solution.

解决方案:

我建议不要在操作Access数据库时使用 PDO类使用 ODBC函数完成该工作.

Instead of using the PDO Class in manipulating the access db, I recommend using ODBC Functions to do the job.

示例:

在您的代码段中

$qry = "SELECT transactionId, aDate, aDateTime, EmpCode, EmpName, ControllerNum FROM Transactions"; 
$dbqryprep = $dbconn->prepare($qry);
$dbqryprep->execute();
$result = $dbqryprep->fetchall(PDO::FETCH_ASSOC);

将其更改为

$connection = odbc_connect("YourDSN","admin","pswd"); 
$sql = "SELECT transactionId, aDate, aDateTime, EmpCode, EmpName, ControllerNum FROM Transactions";
$result = odbc_exec($connection,$sql);
while($row = odbc_fetch_array($result))
{   echo "<pre>";
    print_r($row);
    echo "</pre>";
}

其中" YourDSN "是DSN(数据源名称)，需要在Ubuntu服务器上的 odbc.ini 文件中创建该文件，该文件可在/etc文件夹.在您的odbc.ini文件中输入下面的DSN格式.

Where "YourDSN" is a DSN(Data Source Name) that needs to be created in the odbc.ini file in your Ubuntu Server which can be found on the /etc folder. Type the DSN format below in your odbc.ini file.

DSN的格式如下:

[MyDataSource]

[MyDataSource]

描述=我的数据来源

Driver = MyDriver

Driver = MyDriver

ServerName = localhost

ServerName = localhost

数据库= DB文件的MyDatabase/完整路径

Database = MyDatabase/Complete path of your DB FIle

我的示例代码中:

[您的DSN]

描述=这是为您的访问数据库配置的DSN

Description = This is the configured DSN for your access db

Driver = MDBTools

Driver = MDBTools

ServerName = localhost

ServerName = localhost

数据库=/var/www/{您的dns}/{public_html}/.../.../media/winshare/attEngine.mdb

Database = /var/www/{your dns}/{public_html}/.../.../media/winshare/attEngine.mdb

^ Note(1) 数据库 必须是从根目录开始的完整目录(例如/var/www/...)

^Note(1) The Database must be the complete directory starting from the root(eg. /var/www/...)

^注意(2) 驱动程序 必须是MDBTools

^Note(2) The Driver must be MDBTools

就是这样！只需弄清楚DSN配置，您就可以开始了. 现在，您终于可以使用其最大字段大小来检索访问中的Text数据类型.希望对大家有帮助.如果您有任何澄清，请随时回复或发表评论.

That's it! Just figure out the DSN configuration and you are good to go. You can now finally retrieve Text datatypes in access with its max field size. I hope this helps everyone. Feel free to reply or comment if you have some clarifications.

旧答案:

OLD ANSWER:

这仅回答您的第一个问题和本主题 主题:我认为您使用的方式没有错 代码中的MDBTools.

This is to answer only your 1st question and the subject of this thread: I think there is nothing wrong with the way you use the MDBTools in your code.

经过数小时的网络搜索.我终于找到了一个线程 与我遇到的问题完全相同(MDBTools驱动程序 使用在Linux中运行的PHP使用MSACCESS不返回字符串值 os).也许这仅存在于在PHP中访问MS ACCESS的情况， 在LINUX操作系统上运行?我不知道.

After hours of searching the net. I finally found this one thread that is exactly the same as the problem that I was having(MDBTools driver not returning string values with MSACCESS using PHP running in a linux os). Maybe this only exists on accessing MS ACCESS in PHP which being run in a LINUX os? I don't know.

幸运的是，面对这个问题的我们，我似乎找到了工作 为此.

Luckily for us who faces this very problem, I have seem to find a work around for this.

代替使用PDO的准备和执行功能，请尝试 使用查询一个.

Instead of using the prepare and execute function of PDO, try to use the query one.

示例

更改以下代码行:

$qry = "SELECT transactionId, aDate, aDateTime, EmpCode, EmpName, ControllerNum FROM Transactions;"; 
$dbqryprep = $dbconn->prepare($qry);
$dbqryprep->execute();
$result = $dbqryprep->fetchall(PDO::FETCH_ASSOC);

对此:

$qry = "SELECT transactionId, aDate, aDateTime, EmpCode, EmpName, ControllerNum FROM Transactions;"; 
$result = $dbconn->query($qry)->fetchAll(PDO::FETCH_ASSOC);

然后:

在您的MSACCESS DB文件(.mdb，.accdb)中，只需更改字段大小 文本数据类型设置为 127 或较少.

In your MSACCESS DB file(.mdb, .accdb), Just change the Field Size of the Text Data Type to 127 or less.

请记住，仅当文本"列中的值达到最大值时，此解决方法才有效.仅127个字符.

Remember that this work around only works if the values in your Text columns have max. characters of 127 only.

因此，文本数据类型必须限制为127个字符，以便 MDBTools检索PHP中的文本.

Thus, the Text Datatype must be limited to 127 characters inorder for MDBTools to retrieve the text in PHP.

我不认为这是解决方案，而是发现的错误.一世 希望有人注意到这一点.这将对我们有很大帮助.特别是那些 将来会遇到这种情况.

I don't see this as a solution but rather than a bug discovered. I hope somebody notice this. It would help us a lot. Specially those who will encounter this in the future.

这篇关于MDBTools驱动程序不使用PHP MS-Access返回字符串值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
PHP最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆