Jump to content
Larry Ullman's Book Forums

Recommended Posts

Trying to put in practice what I've read in the book so far, and developed the following code ...

 

<!DOCTYPE html>
<html>
<head>
<title>Table Definition's Tool</title>
<style type="text/css">
	th { font-size: 110%; border-bottom: 2px solid black; }
	td { padding: 3px; border-bottom: 1px solid #aaa }
</style>
</head>
<body>
<h2>Table's definition (PRO database on Top)</h2>
<table>
<?php
//error_reporting (E_ALL ^ E_NOTICE);
require 'utils.php';
//Variables

$table_name=$_POST['table_name'];

// Connect via Windows authentication
$server = 'win1\i01';
//Connection info for PRO
$connectionInfoPRO = array(

  'Database' => 'adventureworks',
  'CharacterSet' => 'UTF-8'
);
$dbPRO = sqlsrv_connect($server, $connectionInfoPRO);
if ($dbPRO === false) {
exitWithSQLError('Database connection to PRO failed');
}

//Connection info for ITG
$connectionInfoITG = array(

  'Database' => 'adventureworksCOPY',
  'CharacterSet' => 'UTF-8'
);
$dbITG = sqlsrv_connect($server, $connectionInfoITG);
if ($dbITG === false) {
exitWithSQLError('Database connection to ITG failed');
}

/* Set up and execute the query. */
$query1 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME='$table_name'";

// Run PRO query
$qresult1 = sqlsrv_query($dbPRO, $query1);
if ($qresult1 === false) {
exitWithSQLError('Query of product data failed.');
}
echo '<tr><th>NAME</th><th>TYPE</th><th>POSITION</th><th>DEFAULT</th><th>LENGHT</th><th>IS NULLABLE</th></tr>';
// Retrieve individual rows from the result
while ($row1 = sqlsrv_fetch_array($qresult1,SQLSRV_FETCH_NUMERIC)) {
echo '<tr><td>', htmlspecialchars($row1['0']),
	 '</td><td>', htmlspecialchars($row1['1']),
	 '</td><td>', htmlspecialchars($row1['2']),
	 '</td><td>', htmlspecialchars($row1['3']),
	 '</td><td>', htmlspecialchars($row1['4']),
  '</td><td>', htmlspecialchars($row1['5']),
	 "</td></tr>\n";
// null == no further rows, false == error
if ($row1 === false) {
exitWithSQLError('Retrieving schema failed.');
}

//Run ITG query
$query2 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME='$table_name'";
$qresult2 = sqlsrv_query($dbITG, $query2);
if ($qresult2 === false) {
exitWithSQLError('Query of product data failed.');
}

echo '<tr><th>NAME</th><th>TYPE</th><th>POSITION</th><th>DEFAULT</th><th>LENGHT</th><th>IS NULLABLE</th></tr>';
// Retrieve individual rows from the result
while ($row2 = sqlsrv_fetch_array($qresult2,SQLSRV_FETCH_NUMERIC)) {
echo '<tr><td>', htmlspecialchars($row2['0']),
	 '</td><td>', htmlspecialchars($row2['1']),
	 '</td><td>', htmlspecialchars($row2['2']),
	 '</td><td>', htmlspecialchars($row2['3']),
	 '</td><td>', htmlspecialchars($row2['4']),
  '</td><td>', htmlspecialchars($row2['5']),
	 "</td></tr>\n";
}

// null == no further rows, false == error
if ($row2 === false) {
exitWithSQLError('Retrieving schema failed.');
}
// Share Release result liststatement resource and close connection
sqlsrv_free_stmt($qresult1);
sqlsrv_free_stmt($qresult2);
sqlsrv_close($dbPRO);
?>

</table>
</body>
</html>

 

I am using PHP with MS-SQL, so that's the reason for sqlsrv_fetch_array.

 

My problem is that I can't find a way to compare both results sets: $row1 against $row2. I probably need to put an array inside each loop maybe? But I don't know how.

 

The query returns a table's schema in MS-SQL so the idea is compare both results sets and if they are identical then both tables (schemas) are also the same.

 

By the way ... I can't use JOINS, UNIONS or pure T-SQL ... because the tables will reside on different databases and different SQL servers.

 

Help, please ....and thanks in advance ,,,

Link to post
Share on other sites

What you need to do is create two arrays in PHP. Start by creating a new array outside of each loop:

$table1 = array();

Then, within the loop, add the fetched row to the array:

$table1[] = $row1;

Do this for both tables (using different $table variables) and then you can compare $table1 to $table2.

  • Upvote 1
Link to post
Share on other sites

$table_name = $_POST['table_name'];

//Run ITG query
$query2 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME='$table_name'";

/* Set up and execute the query. */
$query1 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME='$table_name'";

This is very dangerous. I do not know if mysql(i)_real_escape works with other DB-solutions than MySQL(i), but you should find some way to sanitize your input.

 

-------------------------

 

To you problem:

 

Start with Larry's code. Then, how about trying a function like array_diff()? I'm thinking like this:

 

// It's now filled with data from both tables for the sake of argument
$table1_data = array();
$table2_data = array();

/** Find differences between arrays:
 * Array_diff() returns a new array with elements from the first argument. (table1_data) not present in argument 2 (table2_data.
 * We also make this comparison vica-verca. I think you have to check both ways to be 100% sure.
 */
$table1_data_not_present_in_table2 = array_diff($table1_data, $table2_data);
$table2_data_not_present_in_table1 = array_diff($table2_data, $table1_data);

// Check if both arrays are empty. (They must be for tables to be identical)
if ( empty($table1_data_not_present_in_table2) && empty($table1_data_not_present_in_table2) )
{
  // We have established table1 == table2 and table2 == table1.
  // They ARE the same.
}
else
{
  // table1_data_not_present_in_table2 == An array of elements from table 1 NOT FOUND in table 2
  // table2_data_not_present_in_table1 == An array of elements from table 2 NOT FOUND in table 1
}

 

Hope this gave you something. Tricky problem you have. :)

 

You might need to use some functions like trim(), but I think this is pretty close to a solution.

Link to post
Share on other sites

Yeah,

 

Good point Antonio.The security is horrible! lol ... but I was worried about the logic. I will inspect input later.

 

By the way, got ti! Found an elegant and nice solution. Here's the final answer (or my way) and hope it can help someone else later...

 

<!DOCTYPE html>
<html>
<head>
<title>Table Definition's Tool</title>
<style type="text/css">
	th { font-size: 110%; border-bottom: 2px solid black; }
	td { padding: 3px; border-bottom: 1px solid #aaa }
</style>
</head>
<body>
<h2>Table's definition (PRO database on Top)</h2>
<table>
<?php
//error_reporting (E_ALL ^ E_NOTICE);
require 'utils.php';
//Variables
$Server1 = Array();
$Server2 = Array();
$PROtable=$_POST['PROtable'];
$ITGtable=$_POST['ITGtable'];
// Connect via Windows authentication
$server = 'win1\i01';
//Connection info for PRO
$connectionInfoPRO = array(

  'Database' => 'adventureworks',
  'CharacterSet' => 'UTF-8'
);
$dbPRO = sqlsrv_connect($server, $connectionInfoPRO);
if ($dbPRO === false) {
exitWithSQLError('Database connection to PRO failed');
}

//Connection info for ITG
$connectionInfoITG = array(

  'Database' => 'adventureworksCOPY',
  'CharacterSet' => 'UTF-8'
);
$dbITG = sqlsrv_connect($server, $connectionInfoITG);
if ($dbITG === false) {
exitWithSQLError('Database connection to ITG failed');
}

/* Set up the query for PRO's table. */
$query1 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME='$PROtable'";

// Run PRO query
$qresult1 = sqlsrv_query($dbPRO, $query1);
if ($qresult1 === false) {
exitWithSQLError('Retrieving schema failed.');
}
echo '<tr><th>NAME</th><th>TYPE</th><th>POSITION</th><th>DEFAULT</th><th>LENGHT</th><th>IS NULLABLE</th></tr>';
// Retrieve individual rows from the result
while ($row1 = sqlsrv_fetch_array($qresult1,SQLSRV_FETCH_NUMERIC)) {
$Server1[$row1['0']] = $row1;
$Server1[$row1['1']] = $row1;
$Server1[$row1['2']] = $row1;
$Server1[$row1['3']] = $row1;
$Server1[$row1['4']] = $row1;

echo '<tr><td>', htmlspecialchars($row1['0']),
	 '</td><td>', htmlspecialchars($row1['1']),
	 '</td><td>', htmlspecialchars($row1['2']),
	 '</td><td>', htmlspecialchars($row1['3']),
	 '</td><td>', htmlspecialchars($row1['4']),
  '</td><td>', htmlspecialchars($row1['5']),
	 "</td></tr>\n";
}

// null == no further rows, false == error
if ($row1 === false) {
exitWithSQLError('Retrieving schema failed.');
}
/* Set up the query for ITG's table. */
$query2 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME='$ITGtable'";

$qresult2 = sqlsrv_query($dbITG, $query2);
if ($qresult2 === false) {
exitWithSQLError('Query of product data failed.');
}

echo '<tr><th>NAME</th><th>TYPE</th><th>POSITION</th><th>DEFAULT</th><th>LENGHT</th><th>IS NULLABLE</th></tr>';
// Retrieve individual rows from the result
while ($row2 = sqlsrv_fetch_array($qresult2,SQLSRV_FETCH_NUMERIC)) {
$Server2[$row2['0']] = $row2;
$Server2[$row2['1']] = $row2;
$Server2[$row2['2']] = $row2;
$Server2[$row2['3']] = $row2;
$Server2[$row2['4']] = $row2;
echo '<tr><td>', htmlspecialchars($row2['0']),
	 '</td><td>', htmlspecialchars($row2['1']),
	 '</td><td>', htmlspecialchars($row2['2']),
	 '</td><td>', htmlspecialchars($row2['3']),
	 '</td><td>', htmlspecialchars($row2['4']),
  '</td><td>', htmlspecialchars($row2['5']),
	 "</td></tr>\n";
}

// null == no further rows, false == error
if ($row2 === false) {
exitWithSQLError('Retrieving schema failed.');
}
//Comparing Arrays
foreach ($Server1 as $key => $value) {
if ($Server2[$key]!=$value) {
 echo "Warning! Schemas are different!<br /><br />\n";
 break;
}
}
$PROTableSize=sizeof($Server1);
$ITGTableSize=sizeof($Server2);
if ($PROTableSize==$ITGTableSize){
 echo "Schemas are identical!<br /><br />\n";
}

// Share Release result liststatement resource and close connection
sqlsrv_free_stmt($qresult1);
sqlsrv_free_stmt($qresult2);
sqlsrv_close($dbPRO);
sqlsrv_close($dbITG);
?>

</table>
</body>
</html>

 

exitWithSQLError is a function not noted above.

 

Thanks Larry! Your books really teach people how to program! You have a gift, the gift to explain difficult concepts into something people can apply and understand.

 

A little bit of background: above is my 1st PHP code! ;-) ... well, a serious one, not a "Hello World" stuff.

  • Upvote 1
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...