Force Select Query in Report

Ignition
1

I am trying to put the results of a SQL query into a table in a report but I am getting an error. The system thinks the the query is an update query. Is there a way to force it to think it is a Select query?

The code works in SQL Server Management Studio.

I have tried creating a Stored Procedure and get the same error.

SQL Query

declare @Previous_2_Days date
declare @Start_Time varchar(Max) 
declare @End_Time varchar(Max)
declare @Previous_day_date date
declare @Third_Shift_Start varchar(Max)
declare @Third_Shift_End varchar(Max)

Set @Start_Time = '18:30:00.000'
Set @End_Time = '05:29:59.999'
Set @Previous_2_Days = (SELECT Convert(date, dateadd(day,datediff(day,2,GETDATE()),0)));
Set @Previous_day_date = (SELECT Convert(date, dateadd(day,datediff(day,1,GETDATE()),0)));
Set @Third_Shift_Start = '' + convert(varchar, @Previous_2_Days) + ' ' + @Start_Time + ''
Set @Third_Shift_End = '' + convert(varchar, @Previous_day_date) + ' ' + @End_Time + ''

If Exists
	(
		Select QR_Code as 'QR Code', Count(QR_Code) as 'Number of Duplicates'
		From Laser_Marker_Success_Rate
		Where DT_stamp Between @Third_Shift_Start and @Third_Shift_End 
		Group by QR_Code
		Having Count(QR_Code) > 1
	)
Begin 
		Select QR_Code as 'QR Code', Count(QR_Code) as 'Number of Duplicates'
		From Laser_Marker_Success_Rate
		Where DT_stamp Between @Third_Shift_Start and @Third_Shift_End 
		Group by QR_Code
		Having Count(QR_Code) > 1
End

Else	
	
	Select 'No Duplicates Codes' as 'QR Code', 'Number of Duplicates' = 0

System Info.
Ignition version - Version: 7.9.5
OS - Windows Server 2012 R2

2

I think 7.9.5 has named queries - you could put your query into a named query, where you can force the query mode to select.

You should really update to the latest 7.9 version sooner, rather than later, by the way. 7.9.5 is quite old and has some serious bugs.

3

I will try that and see what happens. As for the update, we have purchased version 8.x but have not installed it yet. Waiting on a newer server to install it on.

4

That's largely because you aren't supplying a SQL query, but a SQL script. While some JDBC drivers tolerate this, it is not portable. Regardless, JDBC needs to know at the API level whether supplied SQL is a dataset-returning query or a result-code-returning statement. Ignition's bare query tools try to guess based on whether the SQL starts with SELECT.

Named Queries, which appear in 7.9.6, IIRC, allow you to specify which API to use, so the body of the SQL can be more complex.

As Paul notes, you are on an unsupported and buggy version. Some SQL script related bugs persist well into v8.1.x. You should avoid using SQL scripts in Ignition.

5

The named queries worked for me. Thank you for your help!

1 Like
6

Thank you for your response. I will get the opportunity to start over as soon as we get a server for our 8.x license. I will rethink how a lot of the things we do are done.

7

You would be correct. They were introduced in 7.9.4.

1 Like