MantisBT - TestLink
View Issue Details
0002282TestLinkIntegration with Other Systemspublic2009-03-26 01:392013-05-20 19:50
trickyarcher 
fman 
normalmajoralways
closedfixed 
1.7.5 
1.9.7 (2013 Q2 - bug fixing) 
Firefox
5.2.8
TBD
0002282: int_jira.php has a failing query when Jira is sitting on MS-SQL 2005
I modified TL 1.7.5 int_jira.php to solve query issues with MS-SQL 2005.

The first issue was the following:
0 - Invalid object name 'issuestatus'. - SELECT s.pname as issuestatus FROM issuestatus s, jiraissue i WHERE i.pkey='ims-3' AND i.issuestatus = s.ID

0 - Invalid object name 'jiraissue'. - SELECT summary FROM jiraissue WHERE pkey='ims-3'
Notice: Undefined variable: summary in C:\EasyPHP 3.0\testlink\lib\bugtracking\int_jira.php on line 149



To solve this issue I changed the following query at line 142:
$query = "SELECT summary FROM jiraschema.jiraissue WHERE pkey='$id'";

Using the same formula, I made modifications to the query on line 80:
// 20070818 - francisco.mancardi@gruppotesi.com
// $query = "SELECT issuestatus FROM jiraissue WHERE pkey='$id'";
// $query = "SELECT s.pname as issuestatus " .
// "FROM issuestatus s, jiraissue i " .
// "WHERE i.pkey='$id' AND i.issuestatus = s.ID";

        
$query = "SELECT * FROM jiraschema.issuestatus, jiraschema.jiraissue WHERE jiraschema.jiraissue.pkey='$id' AND jiraschema.jiraissue.issuestatus = jiraschema.issuestatus.ID";

Now I'm getting the following error when I look at the execute results:
0 - 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. - SELECT * FROM jiraschema.issuestatus, jiraschema.jiraissue WHERE jiraschema.jiraissue.pkey='ims-2' AND jiraschema.jiraissue.issuestatus = jiraschema.issuestatus.ID

I could probably comment out the query to solve the error message, but I'd rather have it work as designed( providing the current bug status into the testlink report)

Let me know if I can provide any more information to help.

Thanks,
D
Jira, MS, Server, SQL
jpg SP32-20090325-143545.jpg (112,061) 2009-03-26 01:39
http://mantis.testlink.org/file_download.php?file_id=882&type=bug
jpg

? Failed Test Cases.tif (62,114) 2009-03-26 03:01
http://mantis.testlink.org/file_download.php?file_id=883&type=bug
jpg SP32-20090330-165638.jpg (104,580) 2009-03-31 04:02
http://mantis.testlink.org/file_download.php?file_id=905&type=bug
jpg

log userlog3.log (75,654) 2009-03-31 04:03
http://mantis.testlink.org/file_download.php?file_id=906&type=bug
? int_jira.php (4,291) 2009-09-21 19:54
http://mantis.testlink.org/file_download.php?file_id=1227&type=bug
Issue History
2009-03-26 01:39trickyarcherNew Issue
2009-03-26 01:39trickyarcherFile Added: SP32-20090325-143545.jpg
2009-03-26 01:39trickyarcherBrowser => Firefox
2009-03-26 01:39trickyarcherPHP Version => 5.2.8
2009-03-26 03:01trickyarcherFile Added: Failed Test Cases.tif
2009-03-26 03:02trickyarcherTag Attached: Jira
2009-03-26 03:02trickyarcherTag Attached: MS
2009-03-26 03:02trickyarcherTag Attached: Server
2009-03-26 03:02trickyarcherTag Attached: SQL
2009-03-26 23:38mhavlatNote Added: 0006018
2009-03-26 23:38mhavlatStatusnew => feedback
2009-03-31 04:01trickyarcherNote Added: 0006079
2009-03-31 04:02trickyarcherFile Added: SP32-20090330-165638.jpg
2009-03-31 04:03trickyarcherFile Added: userlog3.log
2009-04-01 00:24trickyarcherNote Edited: 0006079
2009-04-01 00:31trickyarcherNote Added: 0006114
2009-06-08 18:32mhavlatNote Added: 0007202
2009-09-21 19:54ThemeeNote Added: 0007979
2009-09-21 19:54ThemeeFile Added: int_jira.php
2013-05-20 19:50fmanQA Team - Task Workflow Status => TBD
2013-05-20 19:50fmanStatusfeedback => closed
2013-05-20 19:50fmanAssigned To => fman
2013-05-20 19:50fmanResolutionopen => fixed
2013-05-20 19:50fmanFixed in Version => 1.9.7 (2013 Q2 - bug fixing)

Notes
(0006018)
mhavlat   
2009-03-26 23:38   
Are you aware that MSSQL2005 doesn't support UTF-8 and PHP doesn't support UCS2 charset? Check the last 1.8 Installation manual or web for workaround then.
(0006079)
trickyarcher   
2009-03-31 04:01   
(edited on: 2009-04-01 00:24)
Ok, I've upgraded to the official TL1.8 release. I still have to make modifications to int_jira.php for it to show the bug summary.

I am still having an issue pulling the status from my JiraDB:

ERROR ON exec_query() - database.class.php
0 - Incorrect syntax near '.'. - SELECT s.pname as jiraschema.issuestatus FROM jiraschema.issuestatus s, jiraschema.jiraissue i WHERE i.pkey='IMS-2' AND i.issuestatus = s.ID

THE MESSAGE :: SQL [1] executed [took 0.1175 secs][all took 0.1175 secs]: SELECT s.pname as jiraschema.issuestatus FROM jiraschema.issuestatus s, jiraschema.jiraissue i WHERE i.pkey='IMS-2' AND i.issuestatus = s.ID Query failed: errorcode[102] errormsg:Incorrect syntax near '.'.

What's wrong with the syntax

/**
     * Returns the status of the bug with the given id
     * this function is not directly called by TestLink.
     *
     * @return string returns the status of the given bug (if found in the db), or false else
     *
      * 2005119 - scs - fixed using of wrong index
     **/
    function getBugStatus($id)
    {
        if (!$this->isConnected())
            return false;

        $status = false;
        
        // 20070818 - francis
        // $query = "SELECT issuestatus FROM jiraissue WHERE pkey='$id'";
        $query = "SELECT s.pname as jiraschema.issuestatus " .
                 "FROM jiraschema.issuestatus s, jiraschema.jiraissue i " .
                 "WHERE i.pkey='$id' AND i.issuestatus = s.ID";
        
        $result = $this->dbConnection->exec_query($query);
        if ($result)
        {
            $status = $this->dbConnection->fetch_array($result);
            if ($status)
            {
                $status = $status['issuestatus'];
            }
            else
                $status = null;
        }
        return $status;
        
    }

(0006114)
trickyarcher   
2009-04-01 00:31   
UPDATE:
Needed to add SELECT permissions to the issuestatus table in JIRA.

Then updated the query in C:\EasyPHP 3.0\testlink_release_rep\lib\bugtracking\int_jira.php to the following:
[code] function getBugStatus($id)
    {
        if (!$this->isConnected())
            return false;

        $status = false;
        
        // 20070818 - francis@potesi.com
        // Original Query: $query = "SELECT issuestatus FROM jiraissue WHERE pkey='$id'";
        // First MOD: $query = "SELECT s.pname as jiraschema.issuestatus " .
        // "FROM jiraschema.issuestatus s, jiraschema.jiraissue i " .
        // "WHERE i.pkey='$id' AND i.issuestatus = s.ID";
        
            $query = "SELECT s.pname " .
                        "FROM jiraschema.issuestatus s, jiraschema.jiraissue i ".
                        "WHERE i.pkey = 'ims-3' AND i.issuestatus = s.ID";
            $result = $this->dbConnection->exec_query($query);
            if ($result)
            {
                $status = $this->dbConnection->fetch_array($result);
                if ($status)
                    $status = $status['issuestatus'];
                else
                    $status = null;
        }
        return $status;
        
    }
[/code]

Something in that bit of code is causing this error in the log, but no more errors are appearing on screen:
[>>][49d24b30c3b27498699995][DEFAULT][/testlink/lib/results/resultsByStatus.php][09/Mar/31 16:56:16]
    [09/Mar/31 16:56:16][WARNING][9d129b949e734a4f59525f40b98615f1][GUI]
        E_NOTICE
Undefined index: issuestatus - in C:\EasyPHP 3.0\testlink_release_rep\lib\bugtracking\int_jira.php - Line 87
    [09/Mar/31 16:56:17][WARNING][9d129b949e734a4f59525f40b98615f1][GUI]
        E_NOTICE
Undefined index: issuestatus - in C:\EasyPHP 3.0\testlink_release_rep\lib\bugtracking\int_jira.php - Line 87
    [09/Mar/31 16:56:17][ERROR][9d129b949e734a4f59525f40b98615f1][GUI]
        flushHttpHeader> Invalid format: 0
[<<][49d24b30c3b27498699995][DEFAULT][/testlink/lib/results/resultsByStatus.php][09/Mar/31 16:56:16][09/Mar/31 16:56:17][took 0.28871 secs]
(0007202)
mhavlat   
2009-06-08 18:32   
Could anybody who use JIRA confirm that the updated code still works with a non mssql JIRA ? Thanks.
(0007979)
Themee   
2009-09-21 19:54   
I had this same problem with JIRA using MS SQL 2005 and TestLink 1.8.3. I was able to debug the issue that caused "Undefined index" to be appear to logs. "SELECT s.pname " should be "SELECT s.pname as issuestatus ". Without the part where issuestatus is defined JIRA issue's status is never used correctly. This means that all issues seem to be open in TestLink while they might actually be resolved in JIRA.

Root cause of this issue seems to be that "jiraschema" that has to be set in some JIRA DB configurations (like SQL Server) but not in MySQL. See:

http://www.atlassian.com/software/jira/docs/v3.13/databases/sqlserver.html [^]

Perhaps there could be a variable in TestLink's JIRA configuration for this possible schema. Anyway I attached a modified version of the int_jira.php that works correctly with MS SQL 2005. With first try that version did not work with a JIRA installation that is using MySQL. I guess there is no "jirascheme" or my JIRA MySQL user is missing some permissions for this "jirascheme". I will check that later when I will get access to that MySQL database.

- Teemu / Ixonos