/trunk/doc/developer_notes/database/access_todo.txt |
---|
6,13 → 6,28 |
The following things need to be addressed in order to make OIDplus compatible with Microsoft Access: |
- When *.accdb file is opened in Microsoft Access, and the page is loaded, then you get the message "Table oidplus_config" is missing |
(Because the system thinks that the "select" error comes from a missing table rather than a locking issue) |
- ODBC wrong datatype for prepared statements |
$res2 = OIDplus::db()->query("select username, severity from ###log_user ". |
"where log_id = ?", array($row['id'])); |
log_id = '50' ==> it should be 50, not '50' |
The prepared statements of PDO work correctly |
==> TODO: Fixed, but now we need to make sure that the data types in the query-array are correct: |
wrong: |
$res2 = OIDplus::db()->query("...", array($row['id'])); |
correct: |
$res2 = OIDplus::db()->query("...", array((int)$row['id'])); |
Also for booleans! |
- Add DSN example strings as example how to connect to Access |
C:\inetpub\wwwroot\oidplus\plugins\database\odbc\OIDplusDatabasePluginODBC.class.php |
C:\inetpub\wwwroot\oidplus\plugins\database\pdo\OIDplusDatabasePluginPDO.class.php |
OIDplus::baseConfig()->setValue('ODBC_DSN', 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\inetpub\wwwroot\oidplus\userdata\database\oidplus.accdb;'); |
OIDplus::baseConfig()->setValue('ODBC_DSN', 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\\inetpub\\wwwroot\\oidplus\\trunk\\userdata\\database\\oidplus.accdb;'); |
OIDplus::baseConfig()->setValue('ODBC_USERNAME', ''); |
OIDplus::baseConfig()->setValue('ODBC_PASSWORD', ''); |
OIDplus::baseConfig()->setValue('PDO_DSN', 'odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\inetpub\wwwroot\oidplus\userdata\database\oidplus.accdb;'); |
OIDplus::baseConfig()->setValue('PDO_DSN', 'odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\\inetpub\\wwwroot\\oidplus\\trunk\\userdata\\database\\oidplus.accdb;'); |
OIDplus::baseConfig()->setValue('PDO_USERNAME', ''); |
OIDplus::baseConfig()->setValue('PDO_PASSWORD', ''); |
20,9 → 35,10 |
* C:\inetpub\wwwroot\oidplus\setup\sql\struct_access.sql |
"Drop table if exists" |
Problem: Inside access, we need to execute each single CREATE TABLE command by hand! We cannot just copy-paste the whole code! |
Problem: Inside Access, we need to execute each single CREATE TABLE command by hand! We cannot just copy-paste the whole code! |
=> However, other tools like HickelSOFT DBTool can correctly send SQL queries to the ACCDB file via ADO |
* Should we give the user an empty *.accdb file? |
* Should we give the user an empty *.accdb file (as well as an empty SQLite3 file)? |
- Test if everything works, including Unicode and boolean flags (checkboxes etc.) |
/trunk/includes/classes/OIDplusObject.class.php |
---|
226,17 → 226,17 |
if (!OIDplus::baseConfig()->getValue('OBJECT_CACHING', true)) { |
if (!$ra) { |
$res = OIDplus::db()->query("select oChild.id as id, oChild.ra_email as child_mail, oParent.ra_email as parent_mail from ###objects as oChild ". |
$res = OIDplus::db()->query("select oChild.id as child_id, oChild.ra_email as child_mail, oParent.ra_email as parent_mail from ###objects as oChild ". |
"left join ###objects as oParent on oChild.parent = oParent.id"); |
$res->naturalSortByField('oChild.id'); |
while ($row = $res->fetch_array()) { |
if (!OIDplus::authUtils()->isRaLoggedIn($row['parent_mail']) && OIDplus::authUtils()->isRaLoggedIn($row['child_mail'])) { |
$x = self::parse($row['id']); // can be NULL if namespace was disabled |
$x = self::parse($row['child_id']); // can be NULL if namespace was disabled |
if ($x) $out[] = $x; |
} |
} |
} else { |
$res = OIDplus::db()->query("select oChild.id as id from ###objects as oChild ". |
$res = OIDplus::db()->query("select oChild.id as child_id from ###objects as oChild ". |
"left join ###objects as oParent on oChild.parent = oParent.id ". |
"where (".OIDplus::db()->getSlang()->isNullFunction('oParent.ra_email',"''")." <> ? and ". |
OIDplus::db()->getSlang()->isNullFunction('oChild.ra_email',"''")." = ?) or ". |
244,7 → 244,7 |
array($ra, $ra, $ra)); |
$res->naturalSortByField('oChild.id'); |
while ($row = $res->fetch_array()) { |
$x = self::parse($row['id']); // can be NULL if namespace was disabled |
$x = self::parse($row['child_id']); // can be NULL if namespace was disabled |
if ($x) $out[] = $x; |
} |
} |
/trunk/plugins/viathinksoft/adminPages/500_list_ras/OIDplusPageAdminListRAs.class.php |
---|
41,23 → 41,23 |
private function get_ralist(): array { |
$tmp = array(); |
if (OIDplus::db()->getSlang()->id() == 'mysql') { |
$res = OIDplus::db()->query("select distinct BINARY(email) as email from ###ra"); // "binary" because we want to ensure that 'distinct' is case sensitive |
$res = OIDplus::db()->query("select distinct BINARY(email) as distinct_email from ###ra"); // "binary" because we want to ensure that 'distinct' is case sensitive |
} else { |
$res = OIDplus::db()->query("select distinct email as email from ###ra"); // distinct in PGSQL is always case sensitive |
$res = OIDplus::db()->query("select distinct email as distinct_email from ###ra"); // distinct in PGSQL is always case sensitive |
} |
while ($row = $res->fetch_array()) { |
$tmp[$row['email']] = 1; |
$tmp[$row['distinct_email']] = 1; |
} |
if (OIDplus::db()->getSlang()->id() == 'mysql') { |
$res = OIDplus::db()->query("select distinct BINARY(ra_email) as ra_email from ###objects"); |
$res = OIDplus::db()->query("select distinct BINARY(ra_email) as distinct_ra_email from ###objects"); |
} else { |
$res = OIDplus::db()->query("select distinct ra_email as ra_email from ###objects"); |
$res = OIDplus::db()->query("select distinct ra_email as distinct_ra_email from ###objects"); |
} |
while ($row = $res->fetch_array()) { |
if (!isset($tmp[$row['ra_email']])) { |
$tmp[$row['ra_email']] = 0; |
if (!isset($tmp[$row['distinct_ra_email']])) { |
$tmp[$row['distinct_ra_email']] = 0; |
} else { |
$tmp[$row['ra_email']] = 2; |
$tmp[$row['distinct_ra_email']] = 2; |
} |
} |
ksort($tmp); |
/trunk/plugins/viathinksoft/adminPages/600_log/OIDplusPageAdminLogEvents.class.php |
---|
60,7 → 60,7 |
// --- |
$users = array(); |
$res2 = OIDplus::db()->query("select username, severity from ###log_user ". |
"where log_id = ?", array($row['id'])); |
"where log_id = ?", array((int)$row['id'])); |
while ($row2 = $res2->fetch_array()) { |
$users[] = $row2['username']; |
if ($row2['username'] == 'admin') $severity = $row2['severity']; |
69,7 → 69,7 |
// --- |
$objects = array(); |
$res2 = OIDplus::db()->query("select object, severity from ###log_object ". |
"where log_id = ?", array($row['id'])); |
"where log_id = ?", array((int)$row['id'])); |
while ($row2 = $res2->fetch_array()) { |
$objects[] = $row2['object']; |
} |
/trunk/plugins/viathinksoft/database/odbc/OIDplusDatabaseConnectionODBC.class.php |
---|
119,11 → 119,16 |
} else { |
$replace = $arg ? '1' : '0'; |
} |
} else if (is_int($arg)) { |
$replace = $arg; |
} else if (is_float($arg)) { |
$replace = number_format($arg, 10, '.', ''); |
} else { |
// TODO: More types? |
if ($this->slangDetectionDone) { |
$replace = "'".$this->getSlang()->escapeString($arg)."'"; // TODO: types |
$replace = "'".$this->getSlang()->escapeString($arg ?? '')."'"; |
} else { |
$replace = "'".str_replace("'", "''", $arg)."'"; // TODO: types |
$replace = "'".str_replace("'", "''", $arg)."'"; |
} |
} |
$pos = strpos($sql, $needle); |
195,6 → 200,7 |
if (!$this->conn) { |
$message = odbc_errormsg(); |
$message = vts_utf8_encode($message); // Make UTF-8 if it is NOT already UTF-8. Important for German Microsoft Access. |
throw new OIDplusConfigInitializationException(trim(_L('Connection to the database failed!').' '.$message)); |
} |
/trunk/plugins/viathinksoft/database/pdo/OIDplusDatabaseConnectionPDO.class.php |
---|
132,7 → 132,7 |
public function error(): string { |
$err = $this->last_error; |
if ($err == null) $err = ''; |
return $err; |
return vts_utf8_encode($err); |
} |
/** |
160,6 → 160,7 |
$this->conn = new \PDO($dsn, $username, $password, $options); |
} catch (\PDOException $e) { |
$message = $e->getMessage(); |
$message = vts_utf8_encode($message); // Make UTF-8 if it is NOT already UTF-8. Important for German Microsoft Access. |
throw new OIDplusConfigInitializationException(trim(_L('Connection to the database failed!').' '.$message)); |
} |
/trunk/plugins/viathinksoft/publicPages/093_rainfo/OIDplusPagePublicRaInfo.class.php |
---|
151,10 → 151,10 |
if (OIDplus::db()->getSlang()->id() == 'mysql') { |
$res = OIDplus::db()->query("select distinct BINARY(email) as email from ###ra"); // "binary" because we want to ensure that 'distinct' is case sensitive |
} else { |
$res = OIDplus::db()->query("select distinct email as email from ###ra"); // distinct in PGSQL is always case sensitive |
$res = OIDplus::db()->query("select distinct email as distinct_email from ###ra"); // distinct in PGSQL is always case sensitive |
} |
while ($row = $res->fetch_array()) { |
$out[] = 'oidplus:rainfo$'.$row['email']; |
$out[] = 'oidplus:rainfo$'.$row['distinct_email']; |
} |
} |
/trunk/plugins/viathinksoft/raPages/099_object_log/OIDplusPageRaObjectLog.class.php |
---|
83,7 → 83,7 |
while ($row = $res->fetch_array()) { |
$users = array(); |
$res2 = OIDplus::db()->query("select username, severity from ###log_user ". |
"where log_id = ?", array($row['id'])); |
"where log_id = ?", array((int)$row['id'])); |
while ($row2 = $res2->fetch_array()) { |
$users[] = $row2['username']; |
} |
/trunk/setup/sql/struct_access.sql |
---|
1,7 → 1,7 |
CREATE TABLE [config]( |
[name] varchar(50) NOT NULL, |
[value] text NOT NULL, |
[value] memo NOT NULL, |
[description] varchar(255) null, |
[protected] bit NOT NULL, |
[visible] bit NOT NULL, |
39,7 → 39,7 |
[id] varchar(255) NOT NULL, |
[parent] varchar(255) NULL, |
[title] varchar(255) NULL, |
[description] text NULL, |
[description] memo NULL, |
[ra_email] varchar(100) NULL, |
[confidential] bit NOT NULL, |
[created] datetime null, |
82,7 → 82,7 |
[id] AUTOINCREMENT, |
[unix_ts] long NOT NULL, |
[addr] varchar(45) NOT NULL, |
[event] text NOT NULL, |
[event] memo NOT NULL, |
primary key |
( |
[id] |