[DANSOL]

Hi,

In my MSSQL 2008 R2 installation I have a "linked server" linked to to my "IBM DB2 for i"(AS400) . It works perfectly.
...but running sql statement via executeQuery function it doesn't work.

I received this error:

select cdarma , dsarma from openquery (i5 , 'select cdarma , dsarma from mgart00f fetch first 10 rows only')exception 'SQLException' with message 'Could not execute query [Native Error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.]' in C:\Program Files (x86)\Zend\processmaker\gulliver\thirdparty\creole\drivers\mssql\MSSQLConnection.php:174 Stack trace: #0 C:\Program Files (x86)\Zend\processmaker\workflow\engine\classes\class.pmFunctions.php(246): MSSQLConnection->executeQuery('select cdarma ,...') #1 C:\Program Files (x86)\Zend\processmaker\workflow\engine\plugins\prova\provaApplication3.php(44): executeQuery('select cdarma ,...', '30313110350b5f9...') #2 C:\Program Files (x86)\Zend\processmaker\workflow\public_html\sysGeneric.php(680): require_once('C:\Program File...') #3 {main}

Same sql instruction works in MSSQL.

Capture.PNG (9.33 KiB) Viewed 6054 times

Who can help me to go deep into this in order to fix this issue?

Thanks.
Regards,
Daniele

[gbalbach]

Hi - did you ever find a solution to this?

[DANSOL]

Hi,
Unfortunately I didn't found a solution. At the end, I used odbc inside triggers, but I'm not so happy with this solution .
Lately I have modified a little the pm core in order to manage odbc connection( testing changes but seem working).
Let me know if you need more details.
Daniele

[gbalbach]

ok thanks - good luck with the modifications, I hope they work out well! I have been using ajax with javascript (json) calls out to a php file that lets me get info back that way - any updates I then do in triggers via odbc like you are.

[vcaceres]

Hi DANSOL,

Maybe you show me your trigger with odbc, because i need connect to DB2 AS 400 but not is succesfully from openquery in sql statment inside PM3.

thanks buddy.

[DANSOL]

Hello,
Sorry for delay ... I'm using something similar to the below code (even if now I have in place some custom code in order to support native ODBC support).
Hope this can help you.
Regards,
Daniele

Code: Select all

// you must install IBM client access odbc drivers on processmaker server
$dsn = 'Driver={Client Access ODBC Driver (32-bit)};System=YOUR SISTEM NAME OR IP ADDRESS;CCSID=1208;'; // for CCSID search info at ibm.com (but for me this works and it fixed UTF-8 issues

$con = odbc_connect($dsn , "your user name" , "your password");

$count=0;

// sample
$sqlFrom = "select COLUMNA , COLUMNB from SCHEMA.TABLE";
$res = odbc_exec($con , $query);

while( $row = odbc_fetch_array($res) ) {
	$records[$count]=array('A'=>$row['COLUMNA'] , 'B'=>$row['COLUMNB']);
    $count=$count+1;
} 

// dropdown sample using DBArray ( see doc on wiki) 
// other example here http://wiki.processmaker.com/3.0/Database_Connections#ODBC 
$_DBArray['YOUR NAME'] = $records;
$_SESSION['_DBArray'] = $_DBArray;