Error executing query

Questions and answers regarding general SQL and backend databases

Error executing query

Postby developers10 » Thu Aug 31, 2017 3:26 pm

Hi,

We changed from the net.sourceforge.jtds.jdbc.Driver to the com.microsoft.sqlserver.jdbc.SQLServerDriver
Since then, we get an arror when executing the databaseManager.getDataSetByQuery
Error :
Code: Select all
The statement did not return a result set.


When executing the entire same query directly in Sql Server Management studio, one record is returned.

When googling this, I get several posts stating we should append
Code: Select all
allowMultiQueries=true

to the connectionstring. Which I tried but no solution.
Could it be that this property is not passed by Servoy?
Or what is the solution to this problem?
FYI:
Code: Select all
SelectMethod=cursor
is already included also in the connectionstring

The query is complex but Sql nonetheless and worked with the previous driver :
Code: Select all
DECLARE @intDocHandle INT;
DECLARE @xmlString VARCHAR(max);

SET @xmlString = '<root><key ID=''0FD2AC09-9856-41B8-B5B6-0573775E5CE5''/>   </root>';

EXEC sp_xml_preparedocument @intDocHandle OUTPUT
   ,@xmlString;

IF OBJECT_ID('tempdb..#temp_801873526') IS NOT NULL
   DROP TABLE #temp_801873526;

CREATE TABLE #temp_801873526 (ID UNIQUEIDENTIFIER);

INSERT INTO #temp_801873526
SELECT *
FROM OPENXML(@intDocHandle, '/root/key') WITH (ID UNIQUEIDENTIFIER);

DECLARE @query AS NVARCHAR(max);
DECLARE @column_names AS NVARCHAR(max);

SELECT @column_names = ISNULL(@column_names + ',', '') + QUOTENAME(pde.tablename + '_' + pde.fieldname)
FROM price_rule
INNER JOIN price_dependency pde ON pde.price_rule_id = price_rule.price_rule_id
INNER JOIN #temp_801873526 ON pde.price_rule_id = #temp_801873526.ID
ORDER BY pde.sequence_nr;

IF @column_names IS NULL
   OR LEN(@column_names) = 0
   SET @query = N'select pri.valid_from,pri.valid_till,pri.result_value,pri.free_quantity, pri.price_item_id          from price_item pri where 1 = 0; ';
ELSE
   SET @query = N'select ' + @column_names +
      ',pvt.price_rule_type,pvt.return_result_type,pvt.price_rule_code,pvt.price_item_id,pvt.price_rule_id,pvt.uplift_helper, pvt.result_value,pvt.result_value_incl,pvt.currency_id,pvt.valid_from,valid_till,          pvt.free_quantity, pvt.flag_default, pvt.flag_fixed_price, pvt.flag_free,pvt.formula_id,pvt.valid_when_formula_id, pvt.max_amount,pvt.result_value_extra_qty,pvt.result_value_per,          pvt.result_value_per_unit_id,pvt.target_margin,pvt.current_margin ,pvt.formula_code, pvt.valid_when_formula_code,pvt.currency_code, pvt.result_value_per_unit_code           FROM          (          select  pru.price_rule_type,pru.return_result_type,pru.price_rule_code,pri.price_item_id,pri.price_rule_id,          cast(0 as numeric(18,6)) as uplift_helper,pri.valid_from,pri.valid_till,          pri.result_value,pri.result_value_incl,pri.free_quantity,pri.currency_id,          pri.flag_default, pri.flag_fixed_price, pri.flag_free,         pri.formula_id,pri.valid_when_formula_id,          pri.max_amount,pri.result_value_extra_qty,pri.result_value_per,          pri.result_value_per_unit_id, concat(pde.tablename,''_'',pde.fieldname) as tablefield,          target_margin,cast(0 as numeric(18,6)) as current_margin ,cast(space(1) as nvarchar(50)) as formula_code,cast(space(1) as nvarchar(50)) as valid_when_formula_code,          isnull(currency.currency_code,space(10)) as currency_code, isnull(au.articleunit_code,'''') as result_value_per_unit_code,          case when pva.price_value_id is null and pde.fieldtype=''UUID'' then cast(''00000000-0000-0000-0000-000000000000'' as nvarchar(64))          when pva.price_value_id is null then cast('''' as nvarchar(64))          when pde.fieldtype=''BOOL'' then cast(pva.value_boolean as nvarchar(64))          when pde.fieldtype=''DATE'' then convert(nvarchar(64),pva.value_datetime,103)          when pde.fieldtype=''INTEGER'' then cast(pva.value_integer as nvarchar(64))          when pde.fieldtype=''NUMBER'' then cast(pva.value_number as nvarchar(64))          when pde.fieldtype=''UUID'' then cast(pva.value_uuid as nvarchar(64))          else cast(pva.value_text as nvarchar(64))          end as column_value          from price_dependency pde          inner join #temp_801873526 on pde.price_rule_id = #temp_801873526.ID          inner join price_rule pru on pru.price_rule_id = #temp_801873526.ID          inner join price_item pri on pri.price_rule_id = #temp_801873526.ID          left join currency on pri.currency_id = currency.currency_id          left join articleunits au on pri.result_value_per_unit_id = au.articleunit_id inner join price_value pva on pde.price_dependency_id = pva.price_dependency_id  and pva.price_item_id = pri.price_item_id ) as data          pivot          (          max(column_value)          for tablefield in ('
      + @column_names + ')          ) as pvt ;';

EXEC sp_executesql @query;


IF OBJECT_ID('tempdb..#temp_801873526') IS NOT NULL
   DROP TABLE #temp_801873526;
developers10
 
Posts: 31
Joined: Thu Feb 23, 2017 5:33 pm

Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 5 guests