Recordset empty issue-CodePudding

I have issue with filling up recordset.

I stored this query into txt file:

DROP TABLE IF EXISTS "temp_invoice_id_amount_1";
CREATE TEMPORARY TABLE "temp_invoice_id_amount_1" AS
SELECT
    ROW_NUMBER() OVER() "RN",
    c."ID" "CaseID",
    p."ID" "PackageID",
    trim(concat(d."LastName", ' ', d."FirstName")) "FullName",
    c."ContragentCaseID",
    i."ID" "InvoiceID",
    i."AccountNum",
    i."FromDate" "InvoiceFromDate",
    i."ToDate" "InvoiceToDate",
    i."InvoiceCurrency",
    (coalesce(sum(td."Amount" * er."Value"), 0) * (-1)):: NUMERIC(12,2) "NumActualInvoiceAmount",
    trim(replace(to_char((coalesce(sum(td."Amount" * er."Value"), 0) * (-1)):: NUMERIC(12,2), '99999990D00'),'.',',')) "ActualInvoiceAmount"
FROM "Package" p
JOIN "Case" c ON p."ID" = c."PackageID"
JOIN "Debtor" d ON d."ID" = c."DebtorID"
JOIN "Invoice" i ON c."ID" = i."CaseID" AND i."IsDeleted" = 0
LEFT JOIN "Transaction" t ON i."ID" = t."InvoiceID" AND t."IsDeleted" = 0 AND t."IsPayment" = 0
LEFT JOIN "TransactionDetails" td ON t."ID" = td."TransactionID"
LEFT JOIN "ExchangeRate" er ON
    --t."PaymentDate" BETWEEN er."FromDate" AND er."ToDate" AND
    er."SourceCurrency" = t."Currency" AND
    er."DestinationCurrency" = i."InvoiceCurrency"
WHERE p."ID" in ($package)
AND i."AccountNum" not like 'OP%'
AND p."Inserted"   interval '1 minute' > i."Inserted"
group by c."ID", trim(concat(d."LastName", ' ', d."FirstName")), c."ContragentCaseID", i."FromDate", i."ToDate",
         i."InvoiceCurrency", i."ID", p."ID";

CREATE UNIQUE INDEX "idx_temp_invoice_id_amount_1" ON temp_invoice_id_amount_1("InvoiceID");

DROP TABLE IF EXISTS "temp_case_id_amount_1";
CREATE TEMPORARY TABLE "temp_case_id_amount_1" AS
SELECT t1."CaseID",
       trim(replace(to_char((coalesce(sum(t1."NumActualInvoiceAmount" * er_eur."Value"), 0)):: NUMERIC(12,2), '99999990D00'),'.',',')) "ActualCaseAmount_EUR",
       trim(replace(to_char((coalesce(sum(t1."NumActualInvoiceAmount"), 0)):: NUMERIC(12,2), '99999990D00'),'.',',')) "ActualCaseAmount_HRK"
FROM temp_invoice_id_amount_1 t1
LEFT JOIN "ExchangeRate" er_eur ON
    current_date BETWEEN er_eur."FromDate" AND er_eur."ToDate" AND
    er_eur."SourceCurrency" = t1."InvoiceCurrency" AND
    er_eur."DestinationCurrency" = 'EUR'
LEFT JOIN "ExchangeRate" er_hrk ON
    current_date BETWEEN er_hrk."FromDate" AND er_hrk."ToDate" AND
    er_hrk."SourceCurrency" = t1."InvoiceCurrency" AND
    er_hrk."DestinationCurrency" = 'HRK'
GROUP BY t1."CaseID";

CREATE UNIQUE INDEX "idx_temp_case_id_amount_1" ON temp_case_id_amount_1("CaseID");

SELECT ti1."RN",
       ti1."CaseID",
       --ti1."PackageID",
       ti1."FullName",
       'ZIPPY',
       'CITTY',
       'STREETY',
       ti1."ContragentCaseID",
       --ti1."InvoiceID",
      -- ti1."AccountNum",
       ti1."InvoiceFromDate",
       ti1."InvoiceToDate",
       ti1."InvoiceCurrency",
       ti1."ActualInvoiceAmount",
       '0',
       --tc1."ActualCaseAmount_EUR",
       tc1."ActualCaseAmount_HRK",
       '0'
FROM temp_invoice_id_amount_1 ti1
JOIN "temp_case_id_amount_1" tc1 ON ti1."CaseID" = tc1."CaseID";

Then i used this part to read from txt file:

Dim strSQL2 As String
fileSpec = "\\192.168.0.7\...\Reports\confirmation.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTS = objFSO.OpenTextFile(fileSpec, ForReading)
strSQL2 = objTS.ReadAll
strSQL2 = Replace(strSQL2, "$package", package)
objTS.Close

And this part for executing the query:

cmd.CommandType = ADODB.CommandTypeEnum.adCmdText
cmd.ActiveConnection = Conn
cmd.CommandText = strSQL

cmd2.CommandType = ADODB.CommandTypeEnum.adCmdText
cmd2.ActiveConnection = Conn
cmd2.CommandText = strSQL2

Set rs = New ADODB.Recordset
Set rs = cmd.Execute

Set rs2 = New ADODB.Recordset
Set rs2 = cmd2.Execute

But for some reason recordset rs2 is totally empty while rs1 is normally filled with data. Does anyone knows what could be reason why recordset rs2 is empty?

CodePudding user response：

I don't know for sure, but I suspect the issue is you are executing everything at once. It's likely doing everything but not returning the results the way you expect.

I think what you may want to do is execute each code block in sequence rather than all at once.

Something like this:

Dim rs2 as ADODB.Recordset

For Each codeBlock In Split(strSQL2, ";")
  cmd2.CommandText = codeBlock
  rs2 = cmd2.Execute
Next codeBlock

You can also put a breakpoint on each "execute" to check the results at each step.

You'll probably also need to omit the final semicolon so it doesn't try to execute blank (or otherwise handle that the command text has to contain some non-whitespace).

Out of curiosity, why the temp tables? Why not simply run the query? Have you considered functions/views?

CodePudding user response：

I didn't find exact solution but workaround solution.

I splitted my query into 2 separate txt files.

First file contains part where i am dropping and creating temp tables,

and second file contains only this:

SELECT ti1."RN",
   ti1."CaseID",
   --ti1."PackageID",
   ti1."FullName",
   'ZIPPY',
   'CITTY',
   'STREETY',
   ti1."ContragentCaseID",
   --ti1."InvoiceID",
  -- ti1."AccountNum",
   ti1."InvoiceFromDate",
   ti1."InvoiceToDate",
   ti1."InvoiceCurrency",
   ti1."ActualInvoiceAmount",
   '0',
   --tc1."ActualCaseAmount_EUR",
   tc1."ActualCaseAmount_HRK",
   '0'
FROM temp_invoice_id_amount_1 ti1
JOIN "temp_case_id_amount_1" tc1 ON ti1."CaseID" = tc1."CaseID";

Then i executed these 2 files separately.