1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
|
<?php
/**
* Test GetUpdateSQL and GetInsertSQL.
*
* This file is part of ADOdb, a Database Abstraction Layer library for PHP.
*
* @package ADOdb
* @link https://adodb.org Project's web site and documentation
* @link https://github.com/ADOdb/ADOdb Source code and issue tracker
*
* The ADOdb Library is dual-licensed, released under both the BSD 3-Clause
* and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option,
* any later version. This means you can use it in proprietary products.
* See the LICENSE.md file distributed with this source code for details.
* @license BSD-3-Clause
* @license LGPL-2.1-or-later
*
* @copyright 2000-2013 John Lim
* @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
*/
error_reporting(E_ALL);
function testsql()
{
include('../adodb.inc.php');
include('../tohtml.inc.php');
global $ADODB_FORCE_TYPE;
//==========================
// This code tests an insert
$sql = "
SELECT *
FROM ADOXYZ WHERE id = -1";
// Select an empty record from the database
#$conn = ADONewConnection("mssql"); // create a connection
#$conn->PConnect("", "sa", "natsoft", "northwind"); // connect to MySQL, testdb
$conn = ADONewConnection("mysql"); // create a connection
$conn->PConnect("localhost", "root", "", "test"); // connect to MySQL, testdb
#$conn = ADONewConnection('oci8po');
#$conn->Connect('','scott','natsoft');
$connstr = "mysql:dbname=northwind";
$u = 'root';$p='';
$conn = ADONewConnection('pdo');
$conn->Connect($connstr, $u, $p);
//$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
$conn->debug=1;
$conn->Execute("delete from adoxyz where lastname like 'Smi%'");
$rs = $conn->Execute($sql); // Execute the query and get the empty recordset
$record = array(); // Initialize an array to hold the record data to insert
if (strpos($conn->databaseType,'mysql')===false) $record['id'] = 751;
$record["firstname"] = 'Jann';
$record["lastname"] = "Smitts";
$record["created"] = time();
$insertSQL = $conn->GetInsertSQL($rs, $record);
$conn->Execute($insertSQL); // Insert the record into the database
if (strpos($conn->databaseType,'mysql')===false) $record['id'] = 752;
// Set the values for the fields in the record
$record["firstname"] = 'anull';
$record["lastname"] = "Smith\$@//";
$record["created"] = time();
if (isset($_GET['f'])) $ADODB_FORCE_TYPE = $_GET['f'];
//$record["id"] = -1;
// Pass the empty recordset and the array containing the data to insert
// into the GetInsertSQL function. The function will process the data and return
// a fully formatted insert sql statement.
$insertSQL = $conn->GetInsertSQL($rs, $record);
$conn->Execute($insertSQL); // Insert the record into the database
$insertSQL2 = $conn->GetInsertSQL($table='ADOXYZ', $record);
if ($insertSQL != $insertSQL2) echo "<p><b>Walt's new stuff failed</b>: $insertSQL2</p>";
//==========================
// This code tests an update
$sql = "
SELECT *
FROM ADOXYZ WHERE lastname=".$conn->Param('var'). " ORDER BY 1";
// Select a record to update
$varr = array('var'=>$record['lastname'].'');
$rs = $conn->Execute($sql,$varr); // Execute the query and get the existing record to update
if (!$rs || $rs->EOF) print "<p><b>No record found!</b></p>";
$record = array(); // Initialize an array to hold the record data to update
// Set the values for the fields in the record
$record["firstName"] = "Caroline".rand();
//$record["lasTname"] = ""; // Update Caroline's lastname from Miranda to Smith
$record["creAted"] = '2002-12-'.(rand()%30+1);
$record['num'] = '';
// Pass the single record recordset and the array containing the data to update
// into the GetUpdateSQL function. The function will process the data and return
// a fully formatted update sql statement.
// If the data has not changed, no recordset is returned
$updateSQL = $conn->GetUpdateSQL($rs, $record);
$conn->Execute($updateSQL,$varr); // Update the record in the database
if ($conn->Affected_Rows() != 1)print "<p><b>Error1 </b>: Rows Affected=".$conn->Affected_Rows().", should be 1</p>";
$record["firstName"] = "Caroline".rand();
$record["lasTname"] = "Smithy Jones"; // Update Caroline's lastname from Miranda to Smith
$record["creAted"] = '2002-12-'.(rand()%30+1);
$record['num'] = 331;
$updateSQL = $conn->GetUpdateSQL($rs, $record);
$conn->Execute($updateSQL,$varr); // Update the record in the database
if ($conn->Affected_Rows() != 1)print "<p><b>Error 2</b>: Rows Affected=".$conn->Affected_Rows().", should be 1</p>";
$rs = $conn->Execute("select * from ADOXYZ where lastname like 'Sm%'");
//adodb_pr($rs);
rs2html($rs);
$record["firstName"] = "Carol-new-".rand();
$record["lasTname"] = "Smithy"; // Update Caroline's lastname from Miranda to Smith
$record["creAted"] = '2002-12-'.(rand()%30+1);
$record['num'] = 331;
$conn->AutoExecute('ADOXYZ',$record,'UPDATE', "lastname like 'Sm%'");
$rs = $conn->Execute("select * from ADOXYZ where lastname like 'Sm%'");
//adodb_pr($rs);
rs2html($rs);
}
testsql();
|
