Db Type = mssql ..column name truncated

Help
2008-03-10
2013-04-11
  • Philippe BOUSSAROQUE

    Hello,
    i use Open-Realty  an open source software, which use ADODBLITE for accesssing to database.
    By default they use dbtype=mysql and it works perfectly.
    I convert the mysql database to MS sql 2005 express. Conversion looks ok : comumn name are the same in the two versions.
    I modify the connection in the OR software from mysql to mssql.
    Open works ok
    ------------------------------
    $conn = &ADONewConnection($db_type);
    $db_type= "mssql";                                    $conn->PConnect($db_server, $db_user, $db_password, $db_database);
    -----------------------------------

    The problem is with when i query of one table whith long name for column name,

    --------------------------------------------------------
    $config["table_prefix_no_lang"] = "";
    $sql = "SELECT * FROM ".$config["table_prefix_no_lang"]."controlpanel";
    $recordSet = $conn->Execute($sql);
    // retreive the value  .... give field unknown
    $colValue = $recordSet->fields["controlpanel_automatic_update_check"];

    // retreive the value   with field name shortened give the value
    $colValue = $recordSet->fields["controlpanel_automatic_update_];

    I check whith this little script (sorry for my poor php (1st line key number, 2nd key name)
    // Loop throught Control Panel and save to Array
    foreach ((array)$recordSet->fields as $key => $value){
      if ($key <= "150"){
        print "cle: $key, Valeur :  ".urlencode($value);
       }
       else{
        print '<span style="color:red;">   ***'. $key."</span><br />";
       }
    }
    then i was able to check that many fiels name had been shortened

    As i am not fluent in php , i try to debug, but no results

    Do i a am missing some parameters  in adodbconfig.php file,  and which one?

    Thanks for your help, and sorry for my poor english (french people)

    Philippe BOUSSAROQUE
    ph.boussaroque@cia-it.com

     
    • Mark Dickenson

      Mark Dickenson - 2008-03-10

      This is not a problem with ADODB Lite or PHP.  It is a limitation of MSSQL.  Here is the information I found on the subject.

      "Note: In Windows, the DBLIB from Microsoft is used. Functions that
      return a column name are based on the dbcolname() function in DBLIB.
      DBLIB was developed for SQL Server 6.x where the max identifier length
      is 30. For this reason, the maximum column length is 30 characters."

      You will need to limit your field names to 30 characters or less to use MSSQL 2005.

       
      • Philippe BOUSSAROQUE

        hello Mark,

        thanks a lot for your rapid answer.
        I saw that the limitation was 30 , bu i did not thought it came from MS dblib library.

        As the software in question (Open Realty) is not from us , i guess we will stay running with  mysql database.

        Anyway congratulations for this interface software.

        Philippe

         

Log in to post a comment.