Navision - PHP/SQL search

hairyjim

Hi all.

Having succesfully accessed Navision via C/ODBC I have started testing queries I would like to run on tables.

Initially all seemed fine and SQL queries such as the below would work correctly and return correct results.

$sql="SELECT * FROM Contact WHERE Name LIKE 'James'";

BUT if I were to try the following statement no results would be returned:

$sql="SELECT * FROM Contact WHERE 'Contact No_' LIKE '%CT0000%'";

I expected this to return at least 9 records but nothing was returned. It would seem after some further testing that any field names that contain a space character and thus force me to use ' ' round them in the SQL statement do not work. For instance this does not work either:

$sql="SELECT * FROM Contact WHERE 'Address 2' = 'Tyndall Avenue'";

Has anyone got ideas on this? As I said I am pretty certain it has soemthing to do with field names with a space in them because all single word field names work completly fine.

Cheers
James

jesam

I don't know about C/ODBC, but in T-SQL this works :
SELECT * FROM Contact WHERE [Contact No.] LIKE '%CT0000%'

hairyjim

Thanks for the reply but this does not work.

Jim

Phenno

again, do you catch an error or it simply does not gives you any record?

is it on sql or native db?

hairyjim

This is on the native DB

Here is the code. It seems to be any query that requires the use of '' round the field name just returns no results. No errors are returned, I have full debug logging on my PHP setup.

I am also doing searches that I know there are results for.

<?php
$conn=odbc_connect("TestNavision","test","test");
if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="SELECT * FROM Contact WHERE 'Address 2' = 'Tyndall Avenue'";
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
echo "<table><tr>";
echo "<th align=\"left\">Contact No.</th>";
echo "<th align=\"left\">Company Name</th>";
echo "<th align=\"left\">Contact Name</th></tr>";
while (odbc_fetch_row($rs))
{
  $contno=odbc_result($rs,"Contact No_");
  $compname=odbc_result($rs,"Institution Name");
  $conname=odbc_result($rs,"Name");
  echo "<tr><td align=\"left\">$contno</td>";
  echo "<td align=\"left\">$compname</td>";
  echo "<td align=\"left\">$conname</td></tr>";
}
odbc_close($conn);
echo "</table>";
?>

Phenno

hm...

though it would give you an error if sql was wrong, you should try to exec fault sql query just to make sure that it will raise error.

try to switch quotation marks (double vs. single)

$sql="SELECT * FROM Contact WHERE 'Address 2' = 'Tyndall Avenue'"; 

$sql='SELECT * FROM Contact WHERE "Address 2" = "Tyndall Avenue"'; 

have you tried these kind of queries from any other place than php?

hairyjim

Phenno wrote:

hm...

$sql="SELECT * FROM Contact WHERE 'Address 2' = 'Tyndall Avenue'"; 

$sql='SELECT * FROM Contact WHERE "Address 2" = "Tyndall Avenue"'; 

have you tried these kind of queries from any other place than php?

Neither of the above work. The first just returns no results and the second provides a "Column not found: Tyndall Avenue" error.

As I said earlier a query will work if the field has no spaces in the name and thus does not require the single quotes.

Perhaps I try tweaking the DSN settings.

I have not tried SQL queries in any other app, I am able to import data via the dsn in excel.

Jim

Phenno

hairyjim wrote:

Phenno wrote:

hm...

$sql="SELECT * FROM Contact WHERE 'Address 2' = 'Tyndall Avenue'"; 

$sql='SELECT * FROM Contact WHERE "Address 2" = "Tyndall Avenue"'; 

have you tried these kind of queries from any other place than php?

Neither of the above work. The first just returns no results and the second provides a "Column not found: Tyndall Avenue" error.

As I said earlier a query will work if the field has no spaces in the name and thus does not require the single quotes.

Perhaps I try tweaking the DSN settings.

I have not tried SQL queries in any other app, I am able to import data via the dsn in excel.

Jim

Hehhh... Seems like he tried to compare values from two columns with double quotation.

Ok, try variations like this:

$sql="SELECT * FROM Contact WHERE "Address 2" = 'Tyndall Avenue'"; 

why this?

first (yours) query tried to compare two strings and ofcourse, results is null.

second (mine) tried to compare two columns but there is no column Tyndall Avenue.

this third could be the right one. please answer me on this in next few minutes, i have to go home.

hairyjim

You are a STAR!

That fixed the problem. You can go home now :P

Thanks so so so so much.

jim

EDIT: Just for any future reference for people.

Putitng column names in double quotes and 'escaping' the quotes with the backslash will work.

So the below code works for coumn names that require encasing in a quote.

$sql="SELECT * FROM Contact WHERE \"Address 2\" = 'Tyndall Avenue'";

Phenno

hairyjim wrote:

You are a STAR!

That fixed the problem. You can go home now :P

Thanks so so so so much.

jim

EDIT: Just for any future reference for people.

Putitng column names in double quotes and 'escaping' the quotes with the backslash will work.

So the below code works for coumn names that require encasing in a quote.

$sql="SELECT * FROM Contact WHERE "Address 2" = 'Tyndall Avenue'";

This is new to me too.
I used to use php with mysql and flip double and single quotes in combination that I like and nothing went wrong. Now we know that in Native db, it Does make difference. I'm, now, curious, does this happens on ms sql too?