[cosyxu]

Hi Amo,

I have a suggest control in my Dynaform, when I connect it to my MSSQL database, it only works for the tables in the Database, not working for the view tables in the database.

For example, under the database MYDB, there are "Tables" and "Views", why the query only works for the tables under "Tables" Catalogue not the "View" Catalogue, any ideas?

Thanks,
Yuan

[amosbatto]

Do the query with executeQuery() in a trigger and check if there is a message in the "__ERROR__" system variable that indicates what is the problem. You need to turn on Debug Mode when running a case to see it. Also, if you want a detailed error message you need to set in your env.ini file:

Code: Select all

debug = 1 

[cosyxu]

Hi Amo,

I have put the query in a trigger, and please find the following System variable __ERROR__,

Code: Select all

Could not rollback transaction [Native Error: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.]

Thanks,
Yuan

[cosyxu]

Do the query with executeQuery() in a trigger and check if there is a message in the "__ERROR__" system variable that indicates what is the problem. You need to turn on Debug Mode when running a case to see it. Also, if you want a detailed error message you need to set in your env.ini file:

Code: Select all

debug = 1 

And this is the trigger before the dynaform that I used,

Code: Select all

$msSQL = "23123213123213213213213213213"; //DB's id

$query = "SELECT   
				staff_id,        
            	staff_name         
      		FROM  view_timesheet
      		WHERE staff_id = '110' ";


$queryResult = executeQuery($query, $msSQL);		

if( is_array($queryResult) and count($queryResult) > 0 ){

	foreach($queryResult as $row){
		@@employee_Name  = $row['staff_name'];	
	}
	
}

Thanks,
Yuan

[amosbatto]

Test that same query directly in MSSQL to verify that it is correct.

If your staff_id field is an integer, then its value shouldn't be enclosed in quotation marks. Try it this way:

Code: Select all

$query = "SELECT  staff_id, staff_name FROM view_timesheet WHERE staff_id = 110";

[cosyxu]

Hi Amo,

I have confirmed that the staff_id is a string, like "uname1", "uname2".


Thanks,
Yuan

[cosyxu]

Hi Amo,

I have two tables, one is a table in the "Tables" category; the other is a view table in the "Views" category.

To test the connection, I have changed my query from a view to a table, and then it works.

So this makes me thinking that ProcessMake may not support to query a view within a trigger?

Thanks

Yuan

[amosbatto]

Yuan,
Please file a bug report about this issue at http://bugs.processmaker.com and post a link to your bug report here.

The Dev team usually ignores bug reports from the community, but this issue will also effect clients with support contracts, so they might address it.

[cosyxu]

Yuan,
Please file a bug report about this issue at http://bugs.processmaker.com and post a link to your bug report here.

The Dev team usually ignores bug reports from the community, but this issue will also effect clients with support contracts, so they might address it.

Hi Amo,

I have filled a bug here https://bugs.processmaker.com/view.php?id=25657

Thanks,
Yuan