recordset and SAP HANA ERROR

[bruno_peyron]

Hi,

I have a trouble when i execute this query with sapbobscom.recordset (The query is ok on HANA Designer).

What is wrong ? Is Recordset support SEMICOLON ?

The error message is
1). [SAP AG][LIBODBCHDB DLL][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near ";": line 4 col

My Test Query

create Local temporary table #Table (
    TRANSPCODE nvarchar(15) ,
    TRANSPNAME nvarchar(100) 
);


INSERT INTO #Table (TRANSPCODE, TRANSPNAME)
SELECT 
    T0."CardCode", 
    T0."CardName"
FROM "SBO_TEST".OCRD T0 
where T0."GroupCode" = 101;


select * from #Table;
drop  table #Table;

Kinds Regards

[pedro_magueija · Accepted solution]

Hi Bruno,

Last I heard they didn't support multiple statements in one go. Meaning that you'll have to execute the Create, Insert, Select and Drop in separate calls.

Or create a HANA procedure and call that.

Execution of multiple queries in one go does not work with the ODBC Driver.
With other database, we can split multiple queries within only one by specifying ;
With Hana ODBC driver, this functionnality is not available.
With Hana JDBC driver, you may utilize addBatch() to execute multiple queries.
Example that does not work :
CREATE LOCAL TEMPORARY  table #prc as (select '1' AS Test from dummy);
SELECT * FROM #prc;
DROP TABLE #prc
Workaround : instead of sending 1 big query with multiple query inside, send 3 different query to execute.
Performance : If this was to designed to enhance performance, consider using Pre-compiled sql statement with parameters. As you don't need to compile a statement each time, you gain speed. If this is not enough, go with multiple connection to do the work (insert, update...)

Have a look also here:

<- towards the end of the post your situation is described.

Good luck.

---

Best regards,

Pedro Magueija

[bruno_peyron · Accepted solution]

Thank you for your response.

I try to create a procedure for this job

Best regards

PEYRON Bruno