Upgraded from 8.1.17 to 8.1.26, now Named Query issue - cannot set parameter at position 1 (values was -1)

Ignition
,
1

Was trying to upgrade my gateway from 8.1.17 to 8.1.26 but it caused many of my SELECT Named Queries to break with the above error. Here's a singular example stack trace

WARN Vision.Binding.NamedQueryAdapter - Error running query:
com.inductiveautomation.factorypmi.application.gateway.NQuery@5be5d6e8@0ms
On: Invoices.Root Container.Table_Invoices.data
java.lang.Exception: Error running query:
com.inductiveautomation.factorypmi.application.gateway.NQuery@5be5d6e8@0ms
On: Invoices.Root Container.Table_Invoices.data
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:311)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: java.sql.SQLException: Could not set parameter at position 1 (values was -1)
Query - conn:196(M)  - "SELECT 
0 as 'Check',
`view_listInvoices`.`idx`,
`view_listInvoices`.`dateEntered`,
`view_listInvoices`.`flag`,
`view_listInvoices`.`autoFlag`,
`listCustomers`.`name` as 'Customer',
`listLocations`.`name` as 'Location',
`listAreas`.`name` as 'Area',
`listAssets`.`name` as 'Asset',
`listsalesorders`.`number` as 'soNumber',
`listsalesorders`.`name` as 'soName',
`view_listInvoices`.`number` as 'invNumber',
`view_listInvoices`.`name`,
CONCAT(`listUsers`.`firstName`,' ', `listUsers`.`lastName`) as 'Cust Contact',
(SELECT `name` FROM `listposoinvstatus` WHERE `idx` = `view_listInvoices`.`status`) as 'status',
#`view_listInvoices`.`parentProjectID`,
`view_listInvoices`.`individualId`,
(SELECT
	CASE
	WHEN
		`listsalesorders`.`state` IS NULL
	THEN
		"
	ELSE
		(SELECT description FROM descsostate WHERE descsostate.idx=listsalesorders.state)
	END) as 'SO State',
(SELECT 
		CASE 
		WHEN 
			(`view_listInvoices`.`state` IS NULL OR `view_listInvoices`.`state` IN (4, 5, 6, 7))
		THEN 
	        "
		ELSE
			(SELECT descriptio..."
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:351)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:325)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:278)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:954)
	at com.inductiveautomation.factorypmi.application.db.namedqueries.ClientNamedQueryManager.execute(ClientNamedQueryManager.java:32)
	at com.inductiveautomation.factorypmi.application.gateway.NQuery.execute(NQuery.java:70)
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.execute(QueryManager.java:668)
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:308)
	... 1 common frames omitted
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayResponse$GatewayThrowable: java.sql.SQLException: Could not set parameter at position 1 (values was -1)
Query - conn:196(M)  - "SELECT 
0 as 'Check',
`view_listInvoices`.`idx`,
`view_listInvoices`.`dateEntered`,
`view_listInvoices`.`flag`,
`view_listInvoices`.`autoFlag`,
`listCustomers`.`name` as 'Customer',
`listLocations`.`name` as 'Location',
`listAreas`.`name` as 'Area',
`listAssets`.`name` as 'Asset',
`listsalesorders`.`number` as 'soNumber',
`listsalesorders`.`name` as 'soName',
`view_listInvoices`.`number` as 'invNumber',
`view_listInvoices`.`name`,
CONCAT(`listUsers`.`firstName`,' ', `listUsers`.`lastName`) as 'Cust Contact',
(SELECT `name` FROM `listposoinvstatus` WHERE `idx` = `view_listInvoices`.`status`) as 'status',
#`view_listInvoices`.`parentProjectID`,
`view_listInvoices`.`individualId`,
(SELECT
	CASE
	WHEN
		`listsalesorders`.`state` IS NULL
	THEN
		"
	ELSE
		(SELECT description FROM descsostate WHERE descsostate.idx=listsalesorders.state)
	END) as 'SO State',
(SELECT 
		CASE 
		WHEN 
			(`view_listInvoices`.`state` IS NULL OR `view_listInvoices`.`state` IN (4, 5, 6, 7))
		THEN 
	        "
		ELSE
			(SELECT descriptio..."
	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:407)
	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:173)
	at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:92)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.NamedQueryFunctions.execute(NamedQueryFunctions.java:49)
	at jdk.internal.reflect.GeneratedMethodAccessor71.invoke(null)
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(null)
	at java.lang.reflect.Method.invoke(null)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:228)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:434)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:523)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:590)
	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)
	at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1410)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)
	at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1665)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:527)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:578)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1570)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1383)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1543)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1305)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:301)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:51)
	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:141)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
	at org.eclipse.jetty.server.Server.handle(Server.java:563)
	at org.eclipse.jetty.server.HttpChannel.lambda$handle$0(HttpChannel.java:505)
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:762)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:497)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:282)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)
	at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:416)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:385)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:272)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.lambda$new$0(AdaptiveExecutionStrategy.java:140)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:411)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:934)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1078)
	at java.lang.Thread.run(null)

And the named query definition - all parameters are used in the WHERE clause for filtering -

SELECT 
0 as 'Check',
`listSalesOrders`.`idx`,
`listSalesOrders`.`dateEntered`,
`listCustomers`.`name` as 'Customer',
`listLocations`.`name` as 'Location',
`listAreas`.`name` as 'Area',
`listAssets`.`name` as 'Asset',
`listSalesOrders`.`number` as 'soNumber',
`listSalesOrders`.`name` as 'soName',

IFNULL(`view_listinvoices`.`number`,'') as 'invNumber',
IFNULL(`view_listinvoices`.`name`,'') as 'invName',

CONCAT(`cu`.`firstName`,' ', `cu`.`lastName`) as 'Cust Contact',
(SELECT `name` FROM `listposoinvstatus` WHERE `idx` = `listSalesOrders`.`status`) as 'status',

(SELECT label FROM descsotype WHERE value = `listSalesOrders`.`type`) as 'type',
`listSalesOrders`.`billThisMonth` as 'billThisMonth',
`listSalesOrders`.`parentProjectID`,
`listSalesOrders`.`individualId`,
`listSalesOrders`.`taxable`,
`listSalesOrders`.`totalPreTaxAmt`,
`listSalesOrders`.`totalTax`,
`view_salesordersalreadybilled`.`totalamountleft` as 'Amount Left',
`listSalesOrders`.`totalMargin`,
`listSalesOrders`.`totalWeeks`,
`listSalesOrders`.`leftMargin`,
`listSalesOrders`.`leftWeeks`,
(Case (`listSalesOrders`.`leftWeeks`) WHEN 0 THEN 0 ELSE (`listSalesOrders`.`leftMargin`)/(`listSalesOrders`.`leftWeeks`) END ) as 'Margin per Week',
`listProjects`.`idx` as 'parentProjectId',
`listProjects`.`number` as 'ProjectNumber',
`listAssets`.`idx` as 'parentAssetId',
`listAreas`.`idx` as 'parentAreaId',
`listLocations`.`idx` as 'parentLocationId',
`listCustomers`.`idx` as 'parentCustomerId',
`view_salesorderscostallocated`.`costAllocatedTotal`,
`view_salesordersalreadybilled`.`alreadyBilledTotal`,
`listSalesOrders`.`totalSOAmount` as 'Total',
`listSalesOrders`.`comment` as 'comment',
(SELECT
	CASE
	WHEN
		`listsalesorders`.`state` IS NULL
	THEN
		"---"
	ELSE
		(SELECT description FROM descsostate WHERE descsostate.idx=listsalesorders.state)
	END) as 'SO State',
(SELECT 
	CASE 
	WHEN 
		(`view_listInvoices`.`state` IS NULL OR `view_listInvoices`.`state` IN (0, 4, 5, 6, 7))
	THEN 
        "---"
	ELSE
		(SELECT description FROM descinvoicestate WHERE descinvoicestate.idx=view_listInvoices.state)
	END) as 'Invoice State',
(SELECT CASE
	WHEN
		`view_listInvoices`.`contNN` = 1
	THEN
		"Not Needed"
	WHEN
		`view_listInvoices`.`sentToContact` = 1
	THEN
		"Sent"
	WHEN 
		`view_listInvoices`.`state` IS NULL AND `view_listInvoices`.`status` = 7
	THEN
		"---"
	ELSE
		"No"
	END) as 'Contact',
(SELECT CASE
	WHEN
		`view_listInvoices`.`acctNN` = 1
	THEN
		"Not Needed"
	WHEN 
		`view_listInvoices`.`receivedConfirmation`=1
	THEN
		"Confirmed"
	WHEN
		`view_listInvoices`.`sentToAccounting` = 1
	THEN
		"Sent"	
	WHEN 
		`view_listInvoices`.`state` IS NULL AND `view_listInvoices`.`status` = 7
	THEN
		"---"
	ELSE
		"No"
	END) as 'Acct',
(SELECT CASE
	WHEN
		`listsalesorders`.`quoteSent`=1
	THEN
		"Sent"
	WHEN
		`listsalesorders`.`quoteNeeded`=1
	THEN
		"Needed"
	ELSE
		"---"
	END) as 'Quote',
(SELECT CASE
	WHEN
		`listsalesorders`.`poSent`=1
	THEN
		"Received"
	WHEN
		`listsalesorders`.`poRequested`
	THEN
		"Requested"
	WHEN
		`listsalesorders`.`poNeeded`=1
	THEN
		"Needed"
	ELSE
		"---"
	END) as 'PO',
(SELECT CASE
	WHEN 
		datediff(now(), view_listInvoices.dateDue) <= 30 AND `view_listInvoices`.`status`!=7
	THEN
		"Out for Payment"
	WHEN
		datediff(now(), view_listInvoices.dateDue) > 30 AND datediff(now(), view_listInvoices.dateDue)<=60 AND `view_listInvoices`.`status`!=7
	THEN
		"Past Due"
	WHEN
		(datediff(now(), `view_listInvoices`.`dateDue`) > 60 AND `view_listInvoices`.`status`!=7)
	THEN
		"Way Past Due"
	WHEN
		(`view_listInvoices`.`status`=7)
	THEN
		"Paid"
	ELSE
		"---"
	END) as 'Payment'

FROM `listSalesOrders`
LEFT JOIN `view_listinvoices` ON `listsalesorders`.`idx` = `view_listinvoices`.`parentSOId`
LEFT JOIN `listUsers` as `soprep` ON `soprep`.`idx` = `listsalesorders`.`individualId`
LEFT JOIN `listUsers` as `ilu` ON `ilu`.`idx` = `view_listinvoices`.`individualId`
LEFT JOIN `listProjects` ON `listProjects`.`idx` = `listSalesOrders`.`parentProjectId`
LEFT JOIN `listAssets` ON `listAssets`.`idx` = `listProjects`.`parentAssetId`
LEFT JOIN `listAreas` ON `listAreas`.`idx` = `listAssets`.`parentAreaId`
LEFT JOIN `listLocations` ON `listLocations`.`idx` = `listAreas`.`parentLocationId`
LEFT JOIN `listCustomers` ON `listCustomers`.`idx` = `listLocations`.`parentCustomerId`
LEFT JOIN `listUsers` as `cu` ON `cu`.`idx` = `listSalesOrders`.`custContactId`
LEFT JOIN `view_salesordersalreadybilled` ON `view_salesordersalreadybilled`.`idx` = `listSalesOrders`.`idx`
LEFT JOIN `view_salesorderscostallocated` ON `view_salesorderscostallocated`.`idx` = `listSalesOrders`.`idx`
WHERE 
(:salesOrderId=-1 OR `listSalesOrders`.`idx`= :salesOrderId) AND
(:customerId=-1 OR `listCustomers`.`idx`=:customerId) AND 
(:locationId=-1 OR `listLocations`.`idx`=:locationId) AND
(:areaId=-1 OR `listAreas`.`idx`=:areaId) AND
(:assetId=-1 OR `listAssets`.`idx`=:assetId) AND 
(:projectId=-1 OR `listProjects`.`idx`=:projectId) 
AND
CASE
	WHEN (DATE_FORMAT(:startDate, '%Y-%m-%d')='1985-08-09' AND DATE_FORMAT(:endDate, '%Y-%m-%d')='1985-08-09') OR (:startDate IS NULL AND :endDate IS NULL)
	THEN 1=1
	ELSE
	`listsalesorders`.`dateEntered` >= :startDate AND `listsalesorders`.`dateEntered` <= :endDate
END
AND 
(:ItemStatus = -1 OR `listsalesorders`.`status`= :ItemStatus)
AND
(:ShippingStatus  =-1 OR `listsalesorders`.`shippingstatus`=  :ShippingStatus)
ORDER BY `listCustomers`.`name`, `listLocations`.`name`, `listSalesOrders`.`number`

with the parameters -

image
image1192×290 18.6 KB

Is it really just a singular parameter that is causing the issue (the one at position 1 - ItemStatus or ShippingStatus?) or is there something with the structure of my NQ that is no longer acceptable in 8.1.26?

1 Like
2

Not obviously buggy to me.

1 Like
3

Is there a more detailed stack trace over on the Gateway?

4

I turned these to trace

image
image711×229 6.79 KB

Refreshed the table and I only see this in the gateway logs

image
image1198×245 19.5 KB

No error appears.

5

Oh, hmm. I think this could be a regression. Somebody recently tried to make it so named queries could have the -- here's a comment comment syntax in them, but the implementation was naive and if the query contained other instances of -- (like yours does) it broke things...

If you replace those with another arbitrary character just to test does the query work again?

3 Likes
6

Yeah, I remember this. Trying to make Ignition support something (SQL scripts) that generic JDBC doesn't support. Because people are using it with drivers that do support it, but that breaks the conversion of colon-delimited NQ parameters into JDBC question-mark parameters.

Short of a true SQL parser in Ignition (!!), one group or the other will be unhappy.

2 Likes
7

Yea that must have been the cause, I replaced the "---" with "###" and now it is working as expected.

The customer really wants --- for blanks in the data so I guess the next question is is there a way for me to show a couple of hyphens without breaking things in Ignition within the 8.1.26 version but I suppose that will be database specific. Probably more suited for a different topic though. Thanks!

8

Maybe - - - or some unicode alternative to -. Yuck.

1 Like
9

That did solve the original query I posted about, but after figuring out the solution, I moved on to the next query with the same error and but it did not have a -- anywhere -

SELECT 
sq.idx, 
sq.Flagged, 
sq.customerName, 
sq.locationName, 
sq.projectName, 
sq.number, 
sq.vendorName, 
sq.`Vendor Purchase Order`, 
sq.`Total Invoice Amount`, 
sq.`Total Amount Paid`, 
sq.sumOrdered, 
sq.totalOrdered, 
sq.sumConfirmed, 
sq.totalConfirmed, 
sq.sumReceived, 
sq.totalReceived,
sq.countBilled,
sq.countToEngr,
sq.countOkByEngr,
sq.countToAcct,
sq.countOkByAcct,
sq.countPaid,
sq.StatusNum, 
dvip.name as 'Payment Status', 
sq.createdDate
FROM 
(SELECT 
lvi.idx,
lvi.flag as 'Flagged',
lc.name as customerName, 
ll.name as locationName, 
lp.name as projectName, 
lvi.number,
lv.name as vendorName, 
lvp.vendorPONumber as 'Vendor Purchase Order',
SUM(lpi.unitcost)+SUM(lpi.taxAmount) as 'Total Invoice Amount',
lvip.amount as 'Total Amount Paid',
SUM(lpi.orderedButNotConfirmed) as sumOrdered,
COUNT(lpi.orderedButNotConfirmed) as totalOrdered,
SUM(lpi.confirmedByVendor) as sumConfirmed,
COUNT(lpi.confirmedByVendor) as totalConfirmed,
SUM(lpi.receivedByPSI) as sumReceived,
COUNT(lpi.receivedByPSI) as totalReceived,
SUM(ISNULL(lpi.billedToPSI)) as countBilled,
SUM(ISNULL(lpi.toEngr)) as countToEngr,
SUM(ISNULL(lpi.okByEngr)) as countOkByEngr,
SUM(ISNULL(lpi.toAcct)) as countToAcct,
SUM(ISNULL(lpi.okBYAcct)) as countOkByAcct,
SUM(ISNULL(lpi.paidByPSI)) as countPaid,
        (SELECT 
                (CASE
                        WHEN lvip.flag > 0
                		THEN 
                			4
                        WHEN
                            ((SUM(lpi.unitcost)+SUM(lpi.taxAmount) = lvip.amount
                                OR (SUM(lpi.unitcost)+SUM(lpi.taxAmount) = 0)))
                        THEN
                            1
                        WHEN (SUM(IFNULL(lvip.amount, 0)) = 0) THEN 2
                        WHEN (SUM(lpi.unitcost)+SUM(lpi.taxAmount) > IFNULL(lvip.amount, 0)) THEN 3
                        ELSE 2
                    END)
            ) AS 'StatusNum',
lvi.createdDate
FROM listvendorinvoices lvi
LEFT JOIN listvendorpurchaseorders lvp ON lvp.idx = lvi.parentVendorPOId
LEFT JOIN listvendors lv ON lv.idx = lvp.vendorId
LEFT JOIN listprojects lp ON lp.idx = lvp.projectId
LEFT JOIN listassets las ON las.idx = lp.parentAssetId
LEFT JOIN listareas la ON la.idx = las.parentAreaId
LEFT JOIN listlocations ll ON ll.idx = la.parentLocationId
LEFT JOIN listcustomers lc ON lc.idx = ll.parentCustomerId
LEFT JOIN listpartsinstances lpi ON lpi.billedToPSI = lvi.idx
LEFT JOIN (SELECT parentInvoiceId, SUM(amount) as amount, SUM(flag) as flag FROM listvendorinvoicepayments GROUP BY parentInvoiceId) as lvip ON lvi.idx = lvip.parentInvoiceId
WHERE 
(:vendorPoId  =-1 OR lvi.parentVendorPOId=:vendorPoId) AND
(:invoiceNumber = 'All' OR lvi.number= :invoiceNumber) AND 
(:flag = -1 OR lvi.flag = :flag) AND
(:customerId=-1 OR lc.idx=:customerId) AND 
(:locationId=-1 OR ll.idx=:locationId) AND
(:areaId=-1 OR la.idx=:areaId) AND
(:assetId=-1 OR las.idx=:assetId) AND 
(:projectId=-1 OR lp.idx=:projectId) 
AND
CASE
	WHEN (DATE_FORMAT(:startDate, '%Y-%m-%d')='1985-08-09' AND DATE_FORMAT(:endDate, '%Y-%m-%d')='1985-08-09') OR (:startDate IS NULL AND :endDate IS NULL)
	THEN 1=1
	ELSE
	lvi.createdDate >= :startDate AND lvi.createdDate <= :endDate
END
GROUP BY lvp.idx, lvi.idx
HAVING
CASE 
	WHEN :orderedButNotConfirmed=0
	THEN sumOrdered!=totalOrdered OR sumOrdered IS NULL
	WHEN :orderedButNotConfirmed=1
	THEN sumOrdered=totalOrdered
	ELSE
	1=1
END
AND
CASE 
	WHEN :confirmedByVendor=0
	THEN sumConfirmed!=totalConfirmed OR sumConfirmed IS NULL
	WHEN :confirmedByVendor=1
	THEN sumConfirmed=totalConfirmed
	ELSE
	1=1
END
AND
CASE 
	WHEN :receivedByPSI=0
	THEN sumReceived!=totalReceived OR sumReceived IS NULL
	WHEN :receivedByPSI=1
	THEN sumReceived=totalReceived
	ELSE
	1=1
END
AND
CASE
    WHEN :billedToPSI=0
    THEN countBilled > 0
    WHEN :billedToPSI=1
    THEN countBilled=0
    ELSE
    1=1
END
AND
CASE
    WHEN :toEngr=0
    THEN countToEngr > 0
    WHEN :toEngr=1
    THEN countToEngr=0
    ELSE
    1=1
END
AND
CASE
    WHEN :okByEngr=0
    THEN countOkByEngr > 0
    WHEN :okByEngr=1
    THEN countOkByEngr=0
    ELSE
    1=1
END
AND
CASE
    WHEN :toAcct=0
    THEN countToAcct > 0
    WHEN :toAcct=1
    THEN countToAcct=0
    ELSE
    1=1
END
AND
CASE
    WHEN :okByAcct=0
    THEN countOkByAcct > 0
    WHEN :okByAcct=1
    THEN countOkByAcct=0
    ELSE
    1=1
END
AND
CASE
    WHEN :paidByPSI=0
    THEN countPaid > 0
    WHEN :paidByPSI=1
    THEN countPaid=0
    ELSE
    1=1
END) as sq
LEFT JOIN descvendorinvoicepaymentstatus dvip ON dvip.idx = sq.StatusNum
WHERE 
(:StatusNum=-1 OR sq.StatusNum=:StatusNum) 
ORDER BY sq.idx

params -

image
image1196×551 37 KB

Same error in client - GatewayException: java.sql.SQLException: Could not set parameter at position 1 (values was -1)

Same thing in Gateway log - just showing execution, no error appearing

image
image1176×125 10.7 KB

I've already called into support and am awaiting a callback. But I guess there must be an additional issue.

10

Hmm, the comment thing was the only recent change I could find or was aware of.

I'll have to let the support call find it's way to the right developer eventually. I think he's out today.

1 Like
11

My fault - I was looking at the wrong named query on my previous post.

The issue was more "--" in the named queries. Adding a space in the middle was sufficient to fix the error. Thanks - I never would have figured this out.

4 Likes
12

Phew!

1 Like
13

Just because I know I will be asked - is this considered a bug that will be changed in some future version, or something I should be considerate of in my named queries going forward?

14

We're discussing this now.

I'm advocating for reverting the original "fix" and not introducing a new one unless it's done correctly with a SQL parser.

Either way, it seems like it's obviously a bug, and I'm hoping it's at least reverted by the next release.

The problem is somebody out there obviously has statements with comments that will be broken after the revert...

6 Likes
15

I know I'm late to the party, and it is hacky, but perhaps you could use something like:

CONCAT('-', '-', '-')

...as the field value instead of the static --- as a functionally-equivalent workaround. :person_shrugging:

4 Likes
16

This has just tripped us up upgrading from 8.1.25 to 8.1.39..... I have quite a few other gateways to update, would you be able to identify which versions would have this regression? I have the following list of versions I'd like to upgrade to 8.1.39.

8.1.17 / 21 / 25 / 27 / 30

I will search through all the NQ's and look for '--' and remove them before any upgrades. But would be great to know the above please.

17

It looks like the regression was introduced in 8.1.23 and fixed in 8.1.28.

1 Like
18

Thank you, Kevin.