{"id":5955,"date":"2017-12-13T19:33:46","date_gmt":"2017-12-13T19:33:46","guid":{"rendered":"https:\/\/alt2.minisoft.com\/support\/?p=5955"},"modified":"2017-12-14T17:33:36","modified_gmt":"2017-12-14T17:33:36","slug":"troubleshooting-sql-database-error-the-statement-did-not-return-a-result-set","status":"publish","type":"post","link":"https:\/\/c002.minisoft.com\/support\/troubleshooting-sql-database-error-the-statement-did-not-return-a-result-set\/","title":{"rendered":"TroubleShooting: SQL Error &#8211; The Statement did not return a result set"},"content":{"rendered":"<p><strong>Problem:\u00a0<\/strong>When using a stored procedure to get data from MySQL data source in eFORMz, eFORMz returns the error, &#8220;Statement did not return a result set&#8221;<\/p>\n<p><a href=\"https:\/\/alt2.minisoft.com\/support\/wp-content\/uploads\/2017\/12\/Statementdidnotreturnaresultset.jpg\"><img decoding=\"async\" loading=\"lazy\" class=\"size-full wp-image-5958 aligncenter\" src=\"https:\/\/alt2.minisoft.com\/support\/wp-content\/uploads\/2017\/12\/Statementdidnotreturnaresultset.jpg\" alt=\"\" width=\"452\" height=\"370\" srcset=\"https:\/\/c002.minisoft.com\/support\/wp-content\/uploads\/2017\/12\/Statementdidnotreturnaresultset.jpg 452w, https:\/\/c002.minisoft.com\/support\/wp-content\/uploads\/2017\/12\/Statementdidnotreturnaresultset-300x246.jpg 300w, https:\/\/c002.minisoft.com\/support\/wp-content\/uploads\/2017\/12\/Statementdidnotreturnaresultset-150x123.jpg 150w, https:\/\/c002.minisoft.com\/support\/wp-content\/uploads\/2017\/12\/Statementdidnotreturnaresultset-250x205.jpg 250w, https:\/\/c002.minisoft.com\/support\/wp-content\/uploads\/2017\/12\/Statementdidnotreturnaresultset-220x180.jpg 220w, https:\/\/c002.minisoft.com\/support\/wp-content\/uploads\/2017\/12\/Statementdidnotreturnaresultset-366x300.jpg 366w\" sizes=\"(max-width: 452px) 100vw, 452px\" \/><\/a><\/p>\n<p>But you see a result set returned from MySQL using the stored procedure call.<\/p>\n<p><a href=\"https:\/\/alt2.minisoft.com\/support\/wp-content\/uploads\/2017\/12\/resultset.jpg\"><img decoding=\"async\" loading=\"lazy\" class=\"size-full wp-image-5961 aligncenter\" src=\"https:\/\/alt2.minisoft.com\/support\/wp-content\/uploads\/2017\/12\/resultset.jpg\" alt=\"\" width=\"172\" height=\"220\" srcset=\"https:\/\/c002.minisoft.com\/support\/wp-content\/uploads\/2017\/12\/resultset.jpg 172w, https:\/\/c002.minisoft.com\/support\/wp-content\/uploads\/2017\/12\/resultset-117x150.jpg 117w, https:\/\/c002.minisoft.com\/support\/wp-content\/uploads\/2017\/12\/resultset-141x180.jpg 141w\" sizes=\"(max-width: 172px) 100vw, 172px\" \/><\/a><\/p>\n<p><strong>Cause:\u00a0<\/strong>MySQL returns the count of the number of rows affected by a SQL statement or stored procedure as part of the result set. The JDBC driver used in MySQL to get to the data source can become confused as to what it should put into the ResultSet, therefore throwing the error.<\/p>\n<p><strong>Resolution: <\/strong>The stored procedure must include\u00a0<strong>SET NOCOUNT ON<\/strong>\u00a0at the beginning of the procedure declaration to suppress the &#8220;n rows affected&#8221; counts to avoid the confusion. For example:<\/p>\n<div class=\"geshifilter\">\n<div class=\"sql geshifilter-sql\">\n<pre>CREATE PROCEDURE p2\r\nAS\r\n\u00a0\r\nSET NOCOUNT ON\r\n\u00a0\r\nDECLARE @t1 TABLE(\r\n    REF VARCHAR(20)\r\n)\r\n...........<\/pre>\n<p>&nbsp;<\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Problem:\u00a0When using a stored procedure to get data from MySQL data source in eFORMz, eFORMz returns the error, &#8220;Statement did not return a result set&#8221; But you see a result set returned from MySQL using the stored procedure call. Cause:\u00a0MySQL returns the count of the number of rows affected by a SQL statement or stored [&hellip;]<\/p>\n","protected":false},"author":15,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[91,15],"tags":[84,352,354,338,356,353,355,283],"_links":{"self":[{"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/posts\/5955"}],"collection":[{"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/comments?post=5955"}],"version-history":[{"count":5,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/posts\/5955\/revisions"}],"predecessor-version":[{"id":5964,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/posts\/5955\/revisions\/5964"}],"wp:attachment":[{"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/media?parent=5955"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/categories?post=5955"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/tags?post=5955"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}