PageRenderTime 52ms CodeModel.GetById 24ms RepoModel.GetById 0ms app.codeStats 0ms

/lib/yadal/class.Access.php

https://github.com/reshadf/Library
PHP | 615 lines | 254 code | 62 blank | 299 comment | 28 complexity | be53eef2558d3e942568d2b56d9b2cf6 MD5 | raw file
Possible License(s): LGPL-2.1
  1. <?php
  2. /**
  3. * Yadal interface for Microsoft Access databse
  4. *
  5. * This class works only on windows and is sometimes unstable (it wont run).
  6. * If it works, its just fine
  7. *
  8. * @package Yadal
  9. */
  10. /**
  11. * class Access
  12. *
  13. * Yadal - Yet Another Database Abstraction Layer
  14. * Microsoft Access database class.
  15. * This class works only on Windows!
  16. *
  17. * @author Teye Heimans
  18. * @package Yadal
  19. */
  20. include_once('class.Yadal.php');
  21. class Access extends Yadal
  22. {
  23. var $_cursor; // integer: what was the cursor position? (Used for recordCount)
  24. /**
  25. * Access::Access()
  26. *
  27. * Constructor
  28. *
  29. * @param string $db: The database to connect to
  30. * @return void
  31. * @access public
  32. * @author Teye Heimans
  33. */
  34. function Access( $db )
  35. {
  36. parent::Yadal( $db );
  37. $this->_nameQuote = array('[',']');
  38. }
  39. /**
  40. * Access::connect()
  41. *
  42. * Make a connection with the database and
  43. * select the database.
  44. *
  45. * @param string $connStr: Connection string data other then userid, password and datasource
  46. * @param string $username: the username which should be used to login
  47. * @param string $password: the password which should be used to login
  48. * @return resource: The connection resource or false on failure
  49. * @access public
  50. * @author Teye Heimans
  51. */
  52. function connect( $connStr = '', $username = '', $password = '' )
  53. {
  54. // make connection with the database
  55. $this->_conn = new COM('ADODB.Connection');
  56. if( !$this->_conn ) {
  57. die(
  58. 'Error, could not create ADODB connection with COM. <br />'.
  59. 'This only works on windows systems!'
  60. );
  61. }
  62. $this->_conn->Provider = 'Microsoft.Jet.OLEDB.4.0';
  63. $this->_conn->LockType = 3;
  64. // connect to the database
  65. $connStr .=
  66. 'Data Source='.$this->_db.';'.
  67. 'User Id='.$username.';'.
  68. 'Password='.$password.';';
  69. $this->_conn->Open( $connStr );
  70. // no error occoured and connection is open ?
  71. if( $this->_conn->Errors->Count == 0 && $this->_conn->State )
  72. {
  73. $this->_isConnected = true;
  74. // return the connection resource
  75. return $this->_conn;
  76. }
  77. return false;
  78. }
  79. /**
  80. * Access::close()
  81. *
  82. * Close the connection
  83. *
  84. * @return bool
  85. * @access public
  86. * @author Teye Heimans
  87. */
  88. function close()
  89. {
  90. if( $this->_isConnected )
  91. {
  92. $this->_isConnected = false;
  93. return $this->_conn->Close();
  94. }
  95. }
  96. /**
  97. * Access::query()
  98. *
  99. * Execute the query
  100. *
  101. * @param string $query: the query to execute
  102. * @return record set
  103. * @access public
  104. * @author Teye Heimans
  105. */
  106. function query( $query )
  107. {
  108. // save the last query...
  109. $this->_lastQuery = $query;
  110. $this->_cursor = 0;
  111. // execute the query
  112. $rs = $this->_conn->Execute( $query ) ;
  113. if( !$rs )
  114. {
  115. return false;
  116. }
  117. else
  118. {
  119. // request numer of columns (otherwise delete wont work :-S )
  120. if(!strtoupper(substr(trim($query), 0, 6)) == 'SELECT') {
  121. $rs->Fields->Count;
  122. }
  123. return $rs;
  124. }
  125. }
  126. /**
  127. * Access::getInsertId()
  128. *
  129. * Get the id of the last inserted record. Because MS Access
  130. * can't fetch the last inserted id we just fetch the highest id
  131. *
  132. * @param string $table: the table to fetch the last key from
  133. * @return int
  134. * @access public
  135. * @author Teye Heimans
  136. */
  137. function getInsertId( $table )
  138. {
  139. $keys = $this->getPrKeys( $table );
  140. $k = each( $keys );
  141. $rs = $this->query(
  142. 'SELECT MAX('. $this -> quote( $k[1] ).') AS id FROM '. $this -> quote( $table )
  143. );
  144. reset( $this->_keys );
  145. $result = (!$rs->EOF) ? $rs->Fields[0]->Value : -1;
  146. $rs->Close();
  147. $rs->Release();
  148. return $result;
  149. }
  150. /**
  151. * Access::getError()
  152. *
  153. * Return the last eror
  154. *
  155. * @return string
  156. * @access public
  157. * @author Teye Heimans
  158. */
  159. function getError()
  160. {
  161. // are there errors?
  162. $errc = $this->_conn->Errors;
  163. if ($errc->Count == 0)
  164. {
  165. return '';
  166. }
  167. // get the last error message
  168. $err = $errc->Item( $errc->Count-1 );
  169. // return the description
  170. return $err->Description;
  171. }
  172. /**
  173. * Access::recordCount()
  174. *
  175. * Public: return the number of records found by the query
  176. *
  177. * @param recordset $rs: The recordset where the records should be counted from
  178. * @return int
  179. * @access public
  180. * @author Teye Heimans
  181. */
  182. function recordCount( $rs )
  183. {
  184. // go to the first record
  185. if( !$rs->BOF )
  186. {
  187. $rs->MoveFirst();
  188. }
  189. // count the records
  190. $result = 0;
  191. while(!$rs->EOF)
  192. {
  193. $result++;
  194. $rs->MoveNext();
  195. }
  196. // go back to the record we where before calling this function
  197. if( !$rs->BOF ) $rs->MoveFirst();
  198. for($i = 0; $i < $this->_cursor; $i++ )
  199. {
  200. $rs->MoveNext();
  201. }
  202. return $result;
  203. }
  204. /**
  205. * Access::getFieldTypes()
  206. *
  207. * Return the types of the fields retrieved from the given table
  208. *
  209. * @param string $table
  210. * @return array
  211. * @access public
  212. * @author Teye Heimans
  213. */
  214. function getFieldTypes( $table )
  215. {
  216. return array(); // TODO!!
  217. }
  218. /**
  219. * Access::getRecord()
  220. *
  221. * Public: fetch a record in assoc mode and return it
  222. *
  223. * @param recordset $rs: $the recordset where we should get a record from
  224. * @return array
  225. * @access public
  226. * @author Teye Heimans
  227. */
  228. function getRecord( $rs )
  229. {
  230. // are we at the end of the records ?
  231. if( $rs->EOF ) {
  232. //$rs->Close();
  233. //$rs->Release();
  234. return false;
  235. }
  236. else
  237. {
  238. // save the record data in an array
  239. $result = array();
  240. for( $i = 0; $i < $rs->Fields->Count; $i++ )
  241. {
  242. $type = $rs->Fields[$i]->Type;
  243. $value = $rs->Fields[$i]->Value;
  244. switch( $type )
  245. {
  246. case 1: // null value
  247. $value = null;
  248. break;
  249. case 6: // currency is not supported properly;
  250. echo '<br /><b>'.$rs->Fields[$i]->Name.': currency type not supported by PHP</b><br />';
  251. $value = (float) $value;
  252. break;
  253. case 7: // adDate
  254. $value = date('Y-m-d H:i',(integer)$rs->Fields[$i]->Value);
  255. break;
  256. case 133:// A date value (yyyymmdd)
  257. $value = substr($value,0,4).'-'.substr($value,4,2).'-'.substr($value,6,2);
  258. break;
  259. }
  260. $result[ $rs->Fields[$i]->Name ] = trim( $value );
  261. }
  262. // move to the next record
  263. $rs->MoveNext();
  264. $this->_cursor++;
  265. // return the data
  266. return $result;
  267. }
  268. }
  269. /**
  270. * Access::getFieldNames()
  271. *
  272. * retrieve the field names used in the table
  273. *
  274. * @param string $table: table to retrieve the field names from
  275. * @return array of field names
  276. * @access public
  277. * @author Teye Heimans
  278. */
  279. function getFieldNames( $table )
  280. {
  281. $table = strtolower( $table );
  282. // return the data from the cache if it exists
  283. if( isset( $this->_cache['fields'][$table] ) )
  284. {
  285. return $this->_cache['fields'][$table];
  286. }
  287. // open schema 4: adSchemaColumns
  288. $rs = $this->_conn->OpenSchema( 4 );
  289. // get the fields..
  290. $tbl = $rs->Fields( 2 );
  291. $fld = $rs->Fields( 3 );
  292. $idx = $rs->Fields( 6 );
  293. // save the field names
  294. $result = array();
  295. while( !$rs->EOF )
  296. {
  297. if (strtolower($tbl->Value) == $table)
  298. {
  299. $result[$idx->Value-1] = $fld->Value;
  300. }
  301. $rs->MoveNext();
  302. }
  303. // close the schema
  304. $rs->Close();
  305. // sort the field names and return them
  306. ksort( $result );
  307. // save the result in the cache
  308. $this->_cache['fields'][$table] = $result;
  309. return $result;
  310. }
  311. /**
  312. * Access::getNotNullFields()
  313. *
  314. * Retrieve the fields that can not contain NULL
  315. *
  316. * @param string $table: The table which fields we should retrieve
  317. * @return array
  318. * @access public
  319. * @author Teye Heimans
  320. */
  321. function getNotNullFields ( $table )
  322. {
  323. $table = strtolower($table);
  324. // return the data from the cache if it exists
  325. if( isset( $this->_cache['notnull'][$table] ) )
  326. {
  327. return $this->_cache['notnull'][$table];
  328. }
  329. // open schema adSchemaColumns
  330. $rs = $this->_conn->OpenSchema( 4 );
  331. // the fields we are using
  332. $tbl = $rs->Fields( 2 );
  333. $null = $rs->Fields( 10 );
  334. // save the primary key fields in an array
  335. $result = array();
  336. while(!$rs->EOF)
  337. {
  338. // primary field data of the table we want to have ?
  339. if (strtolower($tbl->Value) == $table && (bool)$null->Value == false )
  340. {
  341. // get the field and index of the field
  342. $fld = $rs->Fields( 3 );
  343. $idx = $rs->Fields( 6 );
  344. $result[$idx->Value-1] = $fld->Value;
  345. }
  346. // go to the next record
  347. $rs->MoveNext();
  348. }
  349. // close the recordset
  350. $rs->Close();
  351. // sort the result and return it
  352. ksort( $result );
  353. // save the result in the cache
  354. $this->_cache['notnull'][$table] = $result;
  355. return $result;
  356. }
  357. /**
  358. * Access::getPrKeys()
  359. *
  360. * Get the primary keys from the table
  361. *
  362. * @param string $table: The table where we should fetch the primary keys from
  363. * @return array: primary keys
  364. * @access public
  365. * @author Teye Heimans
  366. */
  367. function getPrKeys( $table )
  368. {
  369. $table = strtolower( $table );
  370. // return the data from the cache if it exists
  371. if( isset( $this->_cache['keys'][$table] ) )
  372. {
  373. return $this->_cache['keys'][$table];
  374. }
  375. // open schema adSchemaPrimaryKeys
  376. $rs = $this->_conn->OpenSchema( 28 );
  377. // the fields we are using
  378. $tbl = $rs->Fields( 2 );
  379. $type = $rs->Fields( 7 );
  380. // save the primary key fields in an array
  381. $result = array();
  382. while(!$rs->EOF)
  383. {
  384. // primary field data of the table we want to have ?
  385. if (strtolower($tbl->Value) == $table && strtolower(substr($type->Value, 0, 10)) == 'primarykey')
  386. {
  387. // get the field and index of the field
  388. $fld = $rs->Fields( 3 );
  389. $idx = $rs->Fields( 6 );
  390. $result[$idx->Value-1] = $fld->Value;
  391. }
  392. // go to the next record
  393. $rs->MoveNext();
  394. }
  395. // close the recordset
  396. $rs->Close();
  397. // sort the result and return it
  398. ksort( $result );
  399. // save the result in the cache
  400. $this->_cache['keys'][$table] = $result;
  401. return $result;
  402. }
  403. /**
  404. * Access::dbDate()
  405. *
  406. * Convert the given date to the correct database format.
  407. *
  408. * @param string $y: The year of the date which should be converteds
  409. * @param string $m: The month of the date which should be converteds
  410. * @param string $d: The day of the date which should be converteds
  411. * @return string the date in the correct format or null when the date could not be converted
  412. * @access public
  413. * @author Teye Heimans
  414. */
  415. function dbDate( $y, $m, $d )
  416. {
  417. return " # $d-$m-$y # ";
  418. }
  419. /**
  420. * Access::escapeString()
  421. *
  422. * Escape the string we are going to save from dangerous characters
  423. *
  424. * @param string $string
  425. * @return string
  426. * @access public
  427. * @author Teye Heimans
  428. */
  429. function escapeString( $string )
  430. {
  431. return str_replace("'", "''", $string);
  432. }
  433. /**
  434. * Access::getUniqueFields()
  435. *
  436. * fetch the unique fields from the table
  437. *
  438. * @param string $table: The table to fetch the unique fields from
  439. * @return array
  440. * @access public
  441. * @author Teye Heimans
  442. */
  443. function getUniqueFields( $table )
  444. {
  445. // Access does not know unique fields... but primary key fields are also unique...
  446. return array('Primary Key' => $this->getPrKeys( $table ));
  447. }
  448. /**
  449. * Access::displaySchemas()
  450. *
  451. * Help function to display the schema's
  452. *
  453. * @param int $schema: the schema to display. leave blank to display all schemas
  454. * @return void
  455. * @access private
  456. * @author Teye Heimans
  457. */
  458. /*
  459. function displaySchemas( $schema = null) {
  460. for( $x = 1; $x <= 38; $x++ )
  461. {
  462. if (is_null($schema) || $schema == $x )
  463. {
  464. print_Var( $schema, $x );
  465. echo "SCHEMA $x\n";
  466. echo "<table border='1' style='border: 1px solid black'>\n";
  467. for( $i = 0; $i <= 50; $i++ ) {
  468. try {
  469. $rs = $this->_conn->OpenSchema( $x );
  470. echo
  471. " <tr>\n".
  472. " <td>".$i ."</td>\n";
  473. if($rs) {
  474. $record = @$rs->Fields ( $i );
  475. if($record) {
  476. while( !$rs->EOF ) {
  477. echo " <td>".($record->Value==''?'&nbsp;':$record->Value)."</td>\n";
  478. flush();
  479. $rs->MoveNext();
  480. }
  481. } else {
  482. break;
  483. }
  484. $rs->Close();
  485. $rs->Release();
  486. } else {
  487. echo "<td>Error.. $x failure</td>\n";
  488. break;
  489. }
  490. echo " </tr>";
  491. } catch ( Exception $e) {
  492. echo 'Caught exception: ', $e;
  493. break;
  494. }
  495. }
  496. echo "</table> <br />";
  497. }
  498. }
  499. return;
  500. }
  501. */
  502. }
  503. /*
  504. adSchemaCatalogs = 1,
  505. adSchemaCharacterSets = 2,
  506. adSchemaCollations = 3,
  507. adSchemaColumns = 4,
  508. adSchemaCheckConstraints = 5,
  509. adSchemaConstraintColumnUsage = 6,
  510. adSchemaConstraintTableUsage = 7,
  511. adSchemaKeyColumnUsage = 8,
  512. adSchemaReferentialContraints = 9,
  513. adSchemaTableConstraints = 10,
  514. adSchemaColumnsDomainUsage = 11,
  515. adSchemaIndexes = 12,
  516. adSchemaColumnPrivileges = 13,
  517. adSchemaTablePrivileges = 14,
  518. adSchemaUsagePrivileges = 15,
  519. adSchemaProcedures = 16,
  520. adSchemaSchemata = 17,
  521. adSchemaSQLLanguages = 18,
  522. adSchemaStatistics = 19,
  523. adSchemaTables = 20,
  524. adSchemaTranslations = 21,
  525. adSchemaProviderTypes = 22,
  526. adSchemaViews = 23,
  527. adSchemaViewColumnUsage = 24,
  528. adSchemaViewTableUsage = 25,
  529. adSchemaProcedureParameters = 26,
  530. adSchemaForeignKeys = 27,
  531. adSchemaPrimaryKeys = 28,
  532. adSchemaProcedureColumns = 29,
  533. adSchemaDBInfoKeywords = 30,
  534. adSchemaDBInfoLiterals = 31,
  535. adSchemaCubes = 32,
  536. adSchemaDimensions = 33,
  537. adSchemaHierarchies = 34,
  538. adSchemaLevels = 35,
  539. adSchemaMeasures = 36,
  540. adSchemaProperties = 37,
  541. adSchemaMembers = 38
  542. */
  543. ?>