Hi.
Using Windows 98 with ODBC to connect to a Microsoft SQL server V7.0. (Want to try ODBC rather than specific databases - still in development)
Connection OK.
As a test, I wanted to get a list of all the fields in the database.
This means (to me), getting a list of tables and then, for each table, getting a list of fields.
So.
The PHP code I have is (NOTE : This is my first ever PHP script!) ...
<HEAD>
<TITLE>Example of reading from MS SQL Server Database via ODBC</TITLE>
</HEAD>
<BODY>
Trying to connect to <B>MSSQLHR</B><BR>
<?php
error_reporting(2047);
if ($link = odbc_connect("MSSQLHR","",""))
{
echo "Connected to <B>MSSQLHR</B><BR>\n";
}
else
{
die("Cannot connect.");
}
echo "<TABLE BORDER><TH>Table Name</TH>";
$tablelist = odbc_tables($link);
while (odbc_fetch_row($tablelist))
{
if (odbc_result($tablelist, 4) == "TABLE")
{
$tablename = odbc_result($tablelist, 3);
echo "\t<TR VALIGN='TOP'><TD>" . $tablename ." </TD><TH>Field Name</TH><TH>Type</TH></TR>\n";
$columnslist = odbc_columns($link,"CarvalHR","%",$tablename,"%");
while (odbc_fetch_row($columnslist))
{
echo "\t<TR><TD></TD><TD>" . odbc_result($columnslist, 4) ."</TD><TD>" . odbc_result($columnslist, 6) ."</TD></TR>\n";
}
}
}
echo "</TABLE>";
?>
</BODY>
Instead of getting a nice HTML table of the tables and fields, I get errors.
Warning: SQL error: , SQL state 00000 in SQLColumns in C:/My Documents/WebSites/Human Resources Web/public_html/index.php on line 28
Warning: Supplied argument is not a valid ODBC result resource in C:/My Documents/WebSites/Human Resources Web/public_html/index.php on line 29
Lines 28 and 29 relate to getting the column details.
I rewrote this code to get a list of tables, hold them in an array and then using the array get a list of fields. That worked.
<HEAD>
<TITLE>Example of reading from MS Access Database via ODBC</TITLE>
</HEAD>
<BODY>
Trying to connect to <B>MSSQLHR</B><BR>
<?php
error_reporting(2047);
if ($link = odbc_connect("MSSQLHR","",""))
{
echo "Connected to <B>MSSQLHR</B><BR>\n";
}
else
{
die("Cannot connect.");
}
$tablelist = odbc_tables($link);
$tablecount = 0;
while (odbc_fetch_row($tablelist))
{
if (odbc_result($tablelist, 4) == "TABLE")
{
$tablename[++$tablecount] = odbc_result($tablelist, 3);
}
}
echo "<TABLE BORDER><TH>Table Name</TH>";
for ($tablecounter = 1 ; $tablecounter <= $tablecount ; $tablecounter++)
{
$columnslist = odbc_columns($link,"CarvalHR","%",$tablename[$tablecounter],"%");
echo "\t<TR VALIGN='TOP'><TD>" . $tablename[$tablecounter] ." </TD><TH>Field Name</TH><TH>Type</TH></TR>\n";
while (odbc_fetch_row($columnslist))
{
echo "\t<TR><TD></TD><TD>" . odbc_result($columnslist, 4) ."</TD><TD>" . odbc_result($columnslist, 6) ."</TD></TR>\n";
}
flush();
}
echo "</TABLE>";
?>
</BODY>
Can anyone suggest what is wrong with what I've done? I assume I can make odbc calls whilst I am handling others?
Or have I grossly missed the point?
From what I can tell, the first error says it was OK (From ODBC.CHM - Note: Although successful execution of a function is normally indicated by a return value of SQL_SUCCESS, the SQLSTATE 00000 also indicates success.).
The second error obviously means I've got SOMETHING wrong, but the same line of code works fine when not processing these things in a nested fashion.
Help will be GREATLY appreciated.
Regards,
Richard Quadling.
Using Windows 98 with ODBC to connect to a Microsoft SQL server V7.0. (Want to try ODBC rather than specific databases - still in development)
Connection OK.
As a test, I wanted to get a list of all the fields in the database.
This means (to me), getting a list of tables and then, for each table, getting a list of fields.
So.
The PHP code I have is (NOTE : This is my first ever PHP script!) ...
<HEAD>
<TITLE>Example of reading from MS SQL Server Database via ODBC</TITLE>
</HEAD>
<BODY>
Trying to connect to <B>MSSQLHR</B><BR>
<?php
error_reporting(2047);
if ($link = odbc_connect("MSSQLHR","",""))
{
echo "Connected to <B>MSSQLHR</B><BR>\n";
}
else
{
die("Cannot connect.");
}
echo "<TABLE BORDER><TH>Table Name</TH>";
$tablelist = odbc_tables($link);
while (odbc_fetch_row($tablelist))
{
if (odbc_result($tablelist, 4) == "TABLE")
{
$tablename = odbc_result($tablelist, 3);
echo "\t<TR VALIGN='TOP'><TD>" . $tablename ." </TD><TH>Field Name</TH><TH>Type</TH></TR>\n";
$columnslist = odbc_columns($link,"CarvalHR","%",$tablename,"%");
while (odbc_fetch_row($columnslist))
{
echo "\t<TR><TD></TD><TD>" . odbc_result($columnslist, 4) ."</TD><TD>" . odbc_result($columnslist, 6) ."</TD></TR>\n";
}
}
}
echo "</TABLE>";
?>
</BODY>
Instead of getting a nice HTML table of the tables and fields, I get errors.
Warning: SQL error: , SQL state 00000 in SQLColumns in C:/My Documents/WebSites/Human Resources Web/public_html/index.php on line 28
Warning: Supplied argument is not a valid ODBC result resource in C:/My Documents/WebSites/Human Resources Web/public_html/index.php on line 29
Lines 28 and 29 relate to getting the column details.
I rewrote this code to get a list of tables, hold them in an array and then using the array get a list of fields. That worked.
<HEAD>
<TITLE>Example of reading from MS Access Database via ODBC</TITLE>
</HEAD>
<BODY>
Trying to connect to <B>MSSQLHR</B><BR>
<?php
error_reporting(2047);
if ($link = odbc_connect("MSSQLHR","",""))
{
echo "Connected to <B>MSSQLHR</B><BR>\n";
}
else
{
die("Cannot connect.");
}
$tablelist = odbc_tables($link);
$tablecount = 0;
while (odbc_fetch_row($tablelist))
{
if (odbc_result($tablelist, 4) == "TABLE")
{
$tablename[++$tablecount] = odbc_result($tablelist, 3);
}
}
echo "<TABLE BORDER><TH>Table Name</TH>";
for ($tablecounter = 1 ; $tablecounter <= $tablecount ; $tablecounter++)
{
$columnslist = odbc_columns($link,"CarvalHR","%",$tablename[$tablecounter],"%");
echo "\t<TR VALIGN='TOP'><TD>" . $tablename[$tablecounter] ." </TD><TH>Field Name</TH><TH>Type</TH></TR>\n";
while (odbc_fetch_row($columnslist))
{
echo "\t<TR><TD></TD><TD>" . odbc_result($columnslist, 4) ."</TD><TD>" . odbc_result($columnslist, 6) ."</TD></TR>\n";
}
flush();
}
echo "</TABLE>";
?>
</BODY>
Can anyone suggest what is wrong with what I've done? I assume I can make odbc calls whilst I am handling others?
Or have I grossly missed the point?
From what I can tell, the first error says it was OK (From ODBC.CHM - Note: Although successful execution of a function is normally indicated by a return value of SQL_SUCCESS, the SQLSTATE 00000 also indicates success.).
The second error obviously means I've got SOMETHING wrong, but the same line of code works fine when not processing these things in a nested fashion.
Help will be GREATLY appreciated.
Regards,
Richard Quadling.