Mantis Bugtracker          
testlink.org

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0002282TestLinkIntegration with Other Systemspublic2009-03-26 01:392013-05-20 19:50
Reportertrickyarcher 
Assigned Tofman 
PrioritynormalSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOSOS Version
Product Version1.7.5 
Fixed in Version1.9.7 (2013 Q2 - bug fixing) 
Summary0002282: int_jira.php has a failing query when Jira is sitting on MS-SQL 2005
DescriptionI 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
TagsJira, MS, Server, SQL
Database (MySQL,Postgres,etc)
BrowserFirefox
PHP Version5.2.8
TestCaseID
QA Team - Task Workflow StatusTBD
Attached Filesjpg file icon SP32-20090325-143545.jpg [^] (112,061 bytes) 2009-03-26 01:39


? file icon Failed Test Cases.tif [^] (62,114 bytes) 2009-03-26 03:01
jpg file icon SP32-20090330-165638.jpg [^] (104,580 bytes) 2009-03-31 04:02


log file icon userlog3.log [^] (75,654 bytes) 2009-03-31 04:03
? file icon int_jira.php [^] (4,291 bytes) 2009-09-21 19:54

- Relationships

-  Notes
(0006018)
mhavlat (reporter)
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 (reporter)
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 (reporter)
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 (reporter)
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 (reporter)
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

- Issue History
Date Modified Username Field Change
2009-03-26 01:39 trickyarcher New Issue
2009-03-26 01:39 trickyarcher File Added: SP32-20090325-143545.jpg
2009-03-26 01:39 trickyarcher Browser => Firefox
2009-03-26 01:39 trickyarcher PHP Version => 5.2.8
2009-03-26 03:01 trickyarcher File Added: Failed Test Cases.tif
2009-03-26 03:02 trickyarcher Tag Attached: Jira
2009-03-26 03:02 trickyarcher Tag Attached: MS
2009-03-26 03:02 trickyarcher Tag Attached: Server
2009-03-26 03:02 trickyarcher Tag Attached: SQL
2009-03-26 23:38 mhavlat Note Added: 0006018
2009-03-26 23:38 mhavlat Status new => feedback
2009-03-31 04:01 trickyarcher Note Added: 0006079
2009-03-31 04:02 trickyarcher File Added: SP32-20090330-165638.jpg
2009-03-31 04:03 trickyarcher File Added: userlog3.log
2009-04-01 00:24 trickyarcher Note Edited: 0006079
2009-04-01 00:31 trickyarcher Note Added: 0006114
2009-06-08 18:32 mhavlat Note Added: 0007202
2009-09-21 19:54 Themee Note Added: 0007979
2009-09-21 19:54 Themee File Added: int_jira.php
2013-05-20 19:50 fman QA Team - Task Workflow Status => TBD
2013-05-20 19:50 fman Status feedback => closed
2013-05-20 19:50 fman Assigned To => fman
2013-05-20 19:50 fman Resolution open => fixed
2013-05-20 19:50 fman Fixed in Version => 1.9.7 (2013 Q2 - bug fixing)



Copyright © 2000 - 2018 MantisBT Team
Powered by Mantis Bugtracker