Board index   FAQ   Search  
Register  Login
Board index php forum :: Database Other Database Engines

query a ntext value from mssql

Do you have questions regarding other database enginges (not MySQL) -- ask here!

Moderators: macek, egami, gesf

query a ntext value from mssql

Postby toonamo » Thu Sep 09, 2010 3:50 pm

I have linux setup as my frontend webserver. behind it is my mssql server.

i am looking for a way to get the text from a column set as ntext.

when i attempt to retrieve the value i get this error.
Warning: mssql_query() [function.mssql-query]: message: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. (severity 16) in /home/toonamo/Public_HTML/baha/item.php on line 216

Warning: mssql_query() [function.mssql-query]: Query failed in /home/toonamo/Public_HTML/baha/item.php on line 216

Warning: mssql_num_rows(): supplied argument is not a valid MS SQL-result resource in /home/toonamo/Public_HTML/baha/item.php on line 217


here is the script calling the ntext as well as other characters
Code: Select all
<?php
@include_once( "functions.php" );
$myServer = "192.168.15.3"; //MS Sql Host
$myUser = "sa";            //MS Sql User
$myPass = "********";      //MS Sql Password
$myDB = "efw";            //MS Sql Database
$rowIs = 0;               //Start Count For Tables
$trTriggered = "0";
//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer");

//select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
  or die("Couldn't open database $myDB");
 
session_start();

if (isset($_GET['item']))
  {
    $item=$_GET['item'];
  }
else
  {
    $item = 0;
  }

if (isset($_POST['webItem']))
  {
   $_SESSION['webItem'] = $_POST['webItem'];
   $webitem = $_SESSION['webItem'];
  }
elseif (isset($_SESSION['webItem']))
  {
   $webitem = $_SESSION['webItem'];
  }
else
  {
    $webitem = 1;
   $_SESSION['webItem'] = $webitem;
  }


// user Search Post 
if (isset($_POST['userSearch']))
  {
   $_SESSION['userSearch'] = $_POST['userSearch'];
   $userSearch = $_SESSION['userSearch'];
  }
elseif (isset($_SESSION['userSearch']))
  {
   $userSearch = $_SESSION['userSearch'];
  }
else
  {
    $userSearch = NULL;;
   $_SESSION['userSearch'] = $userSearch;
  }

// Category Post 
if (isset($_POST['cat_option']))
  {
   $_SESSION['cat_option'] = $_POST['cat_option'];
   $cat_option = $_SESSION['cat_option'];
  }
elseif (isset($_SESSION['cat_option']))
  {
   $cat_option = $_SESSION['cat_option'];
  }
else
  {
    $cat_option = 0;
   $_SESSION['cat_option'] = $cat_option;
  }

// Department Post 
if (isset($_POST['dept_option']))
  {
   $_SESSION['dept_option'] = $_POST['dept_option'];
   $dept_option = $_SESSION['dept_option'];
  }
elseif(isset($_SESSION['dept_option']))
  {
   $dept_option = $_SESSION['dept_option'];
  }
else
  {
    $dept_option = 0;
   $_SESSION['dept_option'] = $dept_option;
  } 

//Per Page Post Variable
if (isset($_POST['limit_option']))
  {
   $_SESSION['page'] = $_POST['limit_option'];
   $per_page = $_SESSION['page'];
  }
elseif (isset($_SESSION['page']))
  {
   $per_page = $_SESSION['page'];
  }
else
  {
    $per_page = 8;
   $_SESSION['page'] = $per_page;
  }

// Per Page Session
if ($dept_option == 0){
   $cat_option = 0;
   $_SESSION['cat_option'] = $cat_option;
}

echo "<html>\n<head>\n";
echo "</head>
<style type='text/css'>
table.item
{
border: 0px;
border-collapse: collapse;
border-spacing: 0px;}
.title
{ border-bottom: 2px solid #d79900;
background-color: #fff2ba;
text-align: center;
font-family: Verdana;
font-weight: bold;
font-size: 11px;
color: #404040;}
.description
{ background-color: #fff;
padding: 10px;
text-align: left;
vertical-align: bottom;
}
.price
{ background-color: #f2f2f2;
padding: 4px;
text-align: center;
vertical-align: bottom;
}
</style>
<body";
echo ">\n";
//start of page
//search menu
echo "<form method='post' action='index.php";
if (isset($_GET['page']) && ($numRows > $per_page))
  {
   echo "?page=" . $page;
  }
echo "'>\n";

echo "<table width='100%' border='0'>\n";
echo "<tr>\n";
echo "<td align='left' width='200'>";
echo "Search:<input type='text' name='userSearch' VALUE='" . $userSearch . "'>";
echo "</td>\n<td width='125'>\n";

menu_dept();

if ($dept_option > 0){
   echo "</td>\n<td width='200'>\n";
   echo menu_cat($dept_option);
}
   


echo "</td>\n<td width='160'>\n";
//test_div();
echo "<select name='limit_option' onChange='document.myform.limit_option.value=this.value'>\n";
echo "<option";
  if($per_page == 2) echo " selected='selected'";
echo " value='2'>2 Items Per Page</option>\n";
echo "<option";
  if($per_page == 4) echo " selected='selected'";
echo " value='4'>4 Items Per Page</option>\n";
echo "<option";
  if($per_page == 8) echo " selected='selected'";
echo " value='8'>8 Items Per Page</option>\n";
echo "<option";
  if($per_page == 16) echo " selected='selected'";
echo " value='16'>16 Items Per Page</option>\n";
echo "<option";
  if($per_page == 32) echo " selected='selected'";
echo " value='32'>32 Items Per Page</option>\n";
echo "</select>";

echo "</td>\n<td width='175'>\n";

echo "<select name='webItem' onChange='document.myform.limit_option.value=this.value'>\n";
echo "<option";
  if($webitem == 1) echo " selected='selected'";
echo " value='1'>Show Only Web Items</option>\n";
echo "<option";
  if($webitem == 0) echo " selected='selected'";
echo " value='0'>Show All Items</option>\n";
echo "</select>";
echo "</td>\n<td width='50'>\n";
echo "<input type='submit' value='GO' name='post'>\n";
echo "</form>\n";
echo "</td>\n<td>\n";
echo "</td>\n";
echo "<td align='right' width='200'>\n";
echo "</td>\n</tr>\n</table>\n";
//end of search menu
if ($item == 0){
   echo "Opps There seems to be a problem";
}
else{
   $getItem = "SELECT ID, Description, WebItem, MSRP, DepartmentID, CategoryID, Quantity, QuantityCommitted, PictureName, ExtendedDescription ";
   $getItem .= "FROM Item ";
   $getItem .= "WHERE ID='" . $item . "'";
   $getItem .= " AND ";
   $getItem .= "WebItem='1'";
   $resultItem = mssql_query($getItem);
   $numRowsItem = mssql_num_rows($resultItem);
   if ($numRowsItem == 0){
      echo "Opps There seems to be a problem";
   }
   for($i = 0; $i < $numRowsItem; $i++){
        if (!mssql_data_seek($resultItem, $i)) {
              echo "Cannot lookup item: " . $item . "\n";
              continue;
              }
         if (!($itemData = mssql_fetch_assoc($resultItem))) {
              continue;
               }
        //echo $itemData["ID"];
        echo "<br>";
        echo "<center><table class='item' width='60%'>
        <tr>
        <td colspan ='3' class='title'><center><h1>";
        echo $itemData["Description"];
        echo "</h1></center></td>
        </tr>
        <tr>
        <td width='150'>";
        echo getPict($itemData["PictureName"]);
        echo "</td><td class='description'>";
        echo $itemData["Description"];
        echo "<br>\n";
        echo get_dept($itemData["DepartmentID"]);
        echo "<br>\n";
        echo get_cat($itemData["CategoryID"]);
        echo "<br>\n";
        echo $itemData["ExtendedDescription"];
        echo "</td><td class='price' width='150'>";
        //See how much we have left in stock
         $quantity = $itemData["Quantity"] - $itemData["QuantityCommitted"];
         //Check if item is in stock. If not show how many are coming. If none coming show out of stock.
           if ($quantity > 0){
              echo "<font color='red' size='+1'>In Stock: " . $quantity . "</font>\n";
           }
           elseif ((get_PO($itemData["Description"])+$quantity) > 0){
              echo "<font color='red' size='+1'>Prearrival: " . (get_PO($itemData["Description"])+$quantity) . "</font>\n";
          }
         else{
            echo "<font color='red' size='+1'>Item Out of Stock!</font>\n";
       echo "<br>\n";
         }
         if ($itemData["MSRP"] == '0'){
            echo "<font color='red' size='+2'>Call For Price</font>";
           }
           else{
            echo "<font color='red' size='+2'>$" . $itemData["MSRP"] . "</font>";
           }
        echo "</td>
        </tr>
        </table></center>";
        }




}
//end of page
echo "</body>\n</html>\n";

//close the connection
mssql_close($dbhandle);
session_write_close();
?>


i am running centos 5.5 with apache, php5, and mssql.
i would appreciate any help and if you could give me a example that would be great.
toonamo
New php-forum User
New php-forum User
 
Posts: 3
Joined: Thu Sep 09, 2010 3:40 pm

Re: query a ntext value from mssql

Postby toonamo » Thu Sep 09, 2010 4:16 pm

I was able to get it to work by changing
Code: Select all
$getItem = "SELECT ID, Description, WebItem, MSRP, DepartmentID, CategoryID, Quantity, QuantityCommitted, PictureName, ExtendedDescription ";


to:
Code: Select all
$getItem = "SELECT ID, Description, WebItem, MSRP, DepartmentID, CategoryID, Quantity, QuantityCommitted, PictureName, cast( ExtendedDescription AS TEXT )AS ExtendedDescription ";


Luckily my values are short enough that i don't run into length issues.
toonamo
New php-forum User
New php-forum User
 
Posts: 3
Joined: Thu Sep 09, 2010 3:40 pm


Return to Other Database Engines

Who is online

Users browsing this forum: No registered users and 3 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.