[Sommaire] [Imprimer]

   1  <?php
   2  
   3  /** 
   4   * @version V4.50 6 July 2004 (c) 2000-2006 John Lim (jlim#natsoft.com.my). All rights reserved.
   5   * Released under both BSD license and Lesser GPL library license. 
   6   * Whenever there is any discrepancy between the two licenses, 
   7   * the BSD license will take precedence. 
   8   *
   9   * Set tabs to 4 for best viewing.
  10   * 
  11   * Latest version is available at http://php.weblogs.com
  12   *
  13   * Test GetUpdateSQL and GetInsertSQL.
  14   */
  15   
  16  error_reporting(E_ALL);
  17  function testsql()
  18  {
  19  
  20  
  21  include ('../adodb.inc.php');
  22  include ('../tohtml.inc.php');
  23  
  24  global $ADODB_FORCE_TYPE;
  25  
  26  
  27  //==========================
  28  // This code tests an insert
  29  
  30  $sql = "
  31  SELECT * 
  32  FROM ADOXYZ WHERE id = -1"; 
  33  // Select an empty record from the database 
  34  
  35  
  36  #$conn = &ADONewConnection("mssql");  // create a connection
  37  #$conn->PConnect("", "sa", "natsoft", "northwind"); // connect to MySQL, testdb
  38  
  39  $conn = &ADONewConnection("mysql");  // create a connection
  40  $conn->PConnect("localhost", "root", "", "test"); // connect to MySQL, testdb
  41  
  42  $conn =& ADONewConnection('oci8po');
  43  $conn->Connect('','scott','natsoft');
  44  //$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
  45  
  46  $conn->debug=1;
  47  $conn->Execute("delete from adoxyz where lastname like 'Smi%'");
  48  
  49  $rs = $conn->Execute($sql); // Execute the query and get the empty recordset
  50  $record = array(); // Initialize an array to hold the record data to insert
  51  
  52  if (strpos($conn->databaseType,'mysql')===false) $record['id'] = 751;
  53  $record["firstname"] = 'Jann';
  54  $record["lastname"] = "Smitts";
  55  $record["created"] = time();
  56  
  57  $insertSQL = $conn->GetInsertSQL($rs, $record);
  58  $conn->Execute($insertSQL); // Insert the record into the database
  59  
  60  if (strpos($conn->databaseType,'mysql')===false) $record['id'] = 752;
  61  // Set the values for the fields in the record
  62  $record["firstname"] = 'anull';
  63  $record["lastname"] = "Smith\$@//";
  64  $record["created"] = time();
  65  
  66  if (isset($_GET['f'])) $ADODB_FORCE_TYPE = $_GET['f'];
  67  
  68  //$record["id"] = -1;
  69  
  70  // Pass the empty recordset and the array containing the data to insert
  71  // into the GetInsertSQL function. The function will process the data and return
  72  // a fully formatted insert sql statement.
  73  $insertSQL = $conn->GetInsertSQL($rs, $record);
  74  $conn->Execute($insertSQL); // Insert the record into the database
  75  
  76  
  77  
  78  $insertSQL2 = $conn->GetInsertSQL($table='ADOXYZ', $record);
  79  if ($insertSQL != $insertSQL2) echo "<p><b>Walt's new stuff failed</b>: $insertSQL2</p>";
  80  //==========================
  81  // This code tests an update
  82  
  83  $sql = "
  84  SELECT * 
  85  FROM ADOXYZ WHERE lastname=".$conn->Param('var'). " ORDER BY 1"; 
  86  // Select a record to update 
  87  
  88  $varr = array('var'=>$record['lastname'].'');
  89  $rs = $conn->Execute($sql,$varr); // Execute the query and get the existing record to update
  90  if (!$rs || $rs->EOF) print "<p><b>No record found!</b></p>";
  91  
  92  $record = array(); // Initialize an array to hold the record data to update
  93  
  94  
  95  // Set the values for the fields in the record
  96  $record["firstName"] = "Caroline".rand();
  97  //$record["lasTname"] = ""; // Update Caroline's lastname from Miranda to Smith
  98  $record["creAted"] = '2002-12-'.(rand()%30+1);
  99  $record['num'] = '';
 100  // Pass the single record recordset and the array containing the data to update
 101  // into the GetUpdateSQL function. The function will process the data and return
 102  // a fully formatted update sql statement.
 103  // If the data has not changed, no recordset is returned
 104  
 105  $updateSQL = $conn->GetUpdateSQL($rs, $record);
 106  $conn->Execute($updateSQL,$varr); // Update the record in the database
 107  if ($conn->Affected_Rows() != 1)print "<p><b>Error1 </b>: Rows Affected=".$conn->Affected_Rows().", should be 1</p>";
 108  
 109  $record["firstName"] = "Caroline".rand();
 110  $record["lasTname"] = "Smithy Jones"; // Update Caroline's lastname from Miranda to Smith
 111  $record["creAted"] = '2002-12-'.(rand()%30+1);
 112  $record['num'] = 331;
 113  $updateSQL = $conn->GetUpdateSQL($rs, $record);
 114  $conn->Execute($updateSQL,$varr); // Update the record in the database
 115  if ($conn->Affected_Rows() != 1)print "<p><b>Error 2</b>: Rows Affected=".$conn->Affected_Rows().", should be 1</p>";
 116  
 117  $rs = $conn->Execute("select * from ADOXYZ where lastname like 'Sm%'");
 118  //adodb_pr($rs);
 119  rs2html($rs);
 120  
 121  $record["firstName"] = "Carol-new-".rand();
 122  $record["lasTname"] = "Smithy"; // Update Caroline's lastname from Miranda to Smith
 123  $record["creAted"] = '2002-12-'.(rand()%30+1);
 124  $record['num'] = 331;
 125  
 126  $conn->AutoExecute('ADOXYZ',$record,'UPDATE', "lastname like 'Sm%'");
 127  $rs = $conn->Execute("select * from ADOXYZ where lastname like 'Sm%'");
 128  //adodb_pr($rs);
 129  rs2html($rs);
 130  }
 131  
 132  
 133  testsql();
 134  ?>