Jump to content
Forums are read-only ×
Larry Ullman's Book Forums

sql-lover

Members
 View Profile  See their activity

  • Posts

    3

  • Joined

  • Last visited

sql-lover's Achievements

Newbie

Newbie (1/14)

1

Reputation

  1. sql-lover
    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.
  2. sql-lover
    Thanks Larry! Let me try that, appreciate the quick response!
  3. sql-lover
    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 ,,,
×
×
  • Create New...