PHP, ODBC, SQL Server Select Statement

[dkirkdrei]

I am having a problem getting results back from a SELECT statement
using the script below. The field names contain decimals and I am not
sure wether or not this is causing the problem or not?? I am
connecting to a SQL server db using ODBC, first time connecting to SQL
server but have been using ODBC to connect to Access db's for years. I
have entered the field names just as they are in the table and I have
tried replacing the spaces and decimals with underscores but still
recieve the same error. Anyway, my simple script and the error that I
am recieveing is below. Any help would be greatly appreciated..

$connect = odbc_connect('cabngsql', '', '');
if (!$connect)
{
echo "couldn't connect";
exit;
}
$query1 = "SELECT fol no FROM Protocol.folder";
$result1 = odbc_exec($connect, $query1);
while (odbc_fetch_row($result1)){
$folder = odbc_result($result1, 1);
echo "$folder";
}
?>

ERROR: SQL error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Invalid object name 'Protocol.folder'., SQL state S0002 in
SQLExecDirect

[SterLo]

Try $results1->Fields["fieldname"]->Value

[Toby A Inkster]

dkirkdrei wrote:

"SELECT fol no FROM Protocol.folder";

Do you mean that the table is actually *called* "Protocol.folder"? If so,
then that's a very poor choice of table name, as the dot is the standard
SQL schema operator.

That is, you can set up several schemas in your database, say, schema A
and schema B, and each schema can contain a table called "foobar". To
differentiate between them, the dot is used:

SELECT * FROM A.foobar;
SELECT * FROM B.foobar;

In Microsoft SQL Server, schemas are linked to users. So each user has
their own schema, thereby allowing multiple users to each create tables
with the same name in the same database. The default schema in MS SQL
Server is, IIRC, "dbo".

But anyway, I digress, although this is a poor choice of table naming, it
is possible to refer to tables with special characters in their names by
quoting them. That is, not:

SELECT * FROM Protocol.folder;

but:

SELECT * FROM "Protocol.folder";

If you also wanted to include a schema in there, you could use the
following query:

SELECT * FROM dbo."Protocol.folder";

In general, when choosing identifiers in SQL, I recommend using hostname
rules, except using underscores instead of hyphens. That is: start with a
letter; end with an alphanumeric; everything in between can be
alphanumeric or underscore. I'd also recommend using all lowercase and
avoiding tables and column names with SQL keywords like "order" and
"from".

--
Toby A Inkster BSc (Hons) ARCS
http://tobyinkster.co.uk/
Geek of ~ HTML/SQL/Perl/PHP/Python/Apache/Linux

[Chung Leong]

On May 9, 5:33 pm, [e-mail] wrote:

I am having a problem getting results back from a SELECT statement
using the script below. The field names contain decimals and I am not
sure wether or not this is causing the problem or not?? I am
connecting to a SQL server db using ODBC, first time connecting to SQL
server but have been using ODBC to connect to Access db's for years. I
have entered the field names just as they are in the table and I have
tried replacing the spaces and decimals with underscores but still
recieve the same error. Anyway, my simple script and the error that I
am recieveing is below. Any help would be greatly appreciated..

You need square brackets around the table name if it contains special
characters.

[Toby A Inkster]

Chung Leong wrote:

You need square brackets around the table name if it contains special
characters.

Square brackets are MS Access/SQL Server proprietary syntax. It is better
to use "double quotes" which Microsoft SQL Server supports along with
almost every other SQL database. [Notable exceptions: Microsoft Access,
MySQL (IIRC, version 5 has a setting to enable support for double-quoted
identifiers, but it's off by default)]

Even if you don't plan on porting this code to another platform, this is
not just a question of "code portability" but "brain portability". It's
best to be in the mindset of using standards-compliant SQL whenever it's
both possible and practical. Otherwise, you'll struggle when you're asked
to develop something for a different RDBMS.

(This is one of the many reasons I prefer PostgreSQL over MySQL: MySQL has
some fairly gratuitous deviations from the SQL standard in some fairly
major areas.)

--
Toby A Inkster BSc (Hons) ARCS
http://tobyinkster.co.uk/
Geek of ~ HTML/SQL/Perl/PHP/Python/Apache/Linux