nav confuse name of tables

bimo88

hi,
i'm using a php script to get data from nav natif base.in the nav 5.01 there is a new table Job N°8004160 which replace the old one N°167.when i execute this query "SELECT * FROM " im getting data from the old one.i've the same problem with job Task.
is there a solution ?
thanks

kine

The second table must have somehow different name than the first one. You cannot have two tables with same name.

bimo88

they dont have the same name.
the old one : _8004061 Job
the new one : Job

Luc_VanDyck

Can you upload a screenshot of the NAV Object Designer, showing the 2 tables? "_8004061 Job" is a strange name for a table.

bimo88

the old table job is not used anymore so they give it this name to explain that the new one is the table with the number 8004061.
so there is a explaination why nav odbc confuse the new one with the old one

Luc_VanDyck

Difficult to say. Maybe it's the space? Can you remove the space from the old table name and try again?

bimo88

i don't think its the space because i've the same problem with Job Task.

Luc_VanDyck

Then show us the PHP script.

bimo88

the problem is not coming from the php script because its perfectly working with Resource ,Resource Group and Resource / Resource Group.
here is the script
// this part is for connection
dsn = 'navision';//nom de la base de données
$DBUser = 'part';// nom de l'utilisateur de la base de données ciblée
$DBPwd = '';//passe de la base de données ciblée
$link2 = odbc_connect($dsn, $DBUser, $DBPwd ) or die(odbc_errormsg() );//connection à navision
$result = @odbc_data_source( $link2, SQL_FETCH_FIRST );
while($result)
{
if (strtolower($dsn) == strtolower($result)) {
echo $result . "<br>\n";
break;
}
else
$result = @odbc_data_source( $link2, SQL_FETCH_NEXT );
}
//this part its for getting data
$query = 'SELECT "No_","Type","Name","Address","City","Job Title","Status" FROM Resource WHERE (Status = 0 or Status = 1) and Type = 0';
$result = odbc_exec($link2,$query);// on test la connection
if (!$result)
{
print("Execution import resource failed:\n");
print(" State: ".odbc_error($link2)."\n");
print(" Error: ".odbc_errormsg($link2)."\n");
}
else
{
echo $result;
while(odbc_fetch_row($result))// on parcours la table employee sur nav
{
$num = odbc_result($result,'No_');
$ind = mysql_query("SELECT COUNT(*) as count FROM ressource WHERE Code_Ressource like '$num'");
if ($ind) $res = mysql_fetch_array($ind);
if($res == 0)// on teste si 0 nexiste po si 1 existe et donc on najoute po
{
$type = odbc_result($result,'Type');
$name = odbc_result($result,'Name');
$adr = odbc_result($result,'Address');
$ville = odbc_result($result,'City');
$job = odbc_result($result,'Job Title');
$statut = odbc_result($result,'Status');
mysql_query("insert into ressource values('$num','$name','$adr','$ville','$job','$type','$statut')");
}
}
}
//i'm using the exactly same script for Job and Job task
$query = 'SELECT * FROM Job ';//
$result = odbc_exec($link2,$query);// on test la connection
if (!$result)
{
print("Execution import affaire failed:\n");
print(" State: ".odbc_error($link2)."\n");
print(" Error: ".odbc_errormsg($link2)."\n");
}
else
{
echo $result;
while(odbc_fetch_array($result))// on parcours la table employee sur nav
{
$code = odbc_result($result,'No_');//Code affaire
$ind = mysql_query("SELECT COUNT(*) as count FROM affaire WHERE Code_Affaire like '$code' ");
if ($ind) $res = mysql_fetch_array($ind);
if($res == 0)// on teste si 0 nexiste po si 1 existe et donc on najoute po
{
$des = odbc_result($result,'Search Description');//code tache affaire
$des2 = odbc_result($result,'Description');//code tache affaire
$sub = odbc_result($result,'Subject');//code tache affaire
$sol = odbc_result($result,'Finished');//code tache affaire
$type = odbc_result($result,'Job Type');//description tache affaire
mysql_query("insert into affaire values('$code','$des','$des2','$sub','$sol','$type')");
}
}
}

Luc_VanDyck

I suggest you:

- write a script with only the minimal code needed to retrieve some fields from eg. the Item table
- then modify this script to retrieve some fields from your new Job table
- if this doesn't work, rename the old Job table (I hope you are using a separate development NAV database) to OldJobTable and try again
- if this doesn't work, delete the old Job table (you are working on a separate development database, are you?) and try again
- restart servers to prevent results from being cached

bimo88

i've tried what u said before.i renamed the old job table it didn't work but when i've renamed the new job table it works,
so that confirm that nav confuse the old and the new job table.
but i cant work with this solution i can not rename the job table.
is there a way to bypass this ?

there is a another point.when i access some tables it gaves me an error like this
non unique column reference.
i searched on internet and i founded that it may be caused by 2 fields having the same caption or by a tables which contain many fields.
i have no fields with the same captions.
i've deleted some fields and its works.
in the case too , i can't use this solution.
so i'm open to any suggestion

thanks a lot

bimo88

hi again,
for the confusion between names i've changed the captionML of job and i'm using it and its working.i dont know if its a good solution.can this be change cause some problems to nav ?

Luc_VanDyck

The NODBC-driver will probably using the TableCaptionML/FieldCaptionML properties to display the table/fieldnames in the proper language.

You can change these ML (=multilanguage) properties without problems. In the NAV code, all references are made through tablenames & fieldnames.

Glad you are finally finding a solution.

bimo88

i'm glad too.
but for the no unique column reference .is there a way to make it work without deleting fields from tables.because that what i found.its seems like the Nodbc driver can not handle more than 100 fields.