Any SAPs out there?

[mudskipper]

In case anyone cares, we got round it by creating a stored procedure whose signature took all the parameters in the order that the odbc command is passing them, then changed the sql command to just call the sp (so no code change). Not perfect, but it works and I get paid

[mudskipper]

The idea of this is that we don't need to make any changes to the datasource databases, so no creating a stored procedure.

Having said that, they are happy for us to do so.

But I don't want to change the code to call a stored procedure if I can help it - I'm trying to keep it as generic as possible.

So we'd need to call the SP from the sql command, so would still have the issue of passing paramters to the sql so that it could pass them to the SP.

If I do need to change the code, I will, but we've got this solution working nicely for about a dozen customers already, so if I can avoid specific code for specific customers I'd prefer to.

I'm pretty sure it's just a syntax thing - just frustrating not to be able to sit and play with it without the pain of a webex with everyone watching!

I'll try NAT's download and see if I can get that working.

[MrMarkyMark]

Couldn't you use a similar method as the example here?

https://help.sap.com/viewer/de2486ee...1ac6541b8.html

[WTFH]

Have you tried DROPTABLE ?

[mudskipper]

I'm surprised that the answer to an SAP question could be condensed into a few lines and not the usual 500 pages of documentation that is normally required for their software.

It may not be answered yet

[WTFH]

I'm surprised that the answer to an SAP question could be condensed into a few lines and not the usual 500 pages of documentation that is normally required for their software.

[TheFaQQer]

I'm disappointed.

This thread seems to have a clearly defined problem, with enough information to seek accurate responses, with no need to pull teeth to get to the bottom of the problem. And the people who have replied seem to have some knowledge and how to solve the problem, without the OP resorting to laughing at them for not understanding and berating them for not having more than two years experience.

This place really has gone down hill.

[mudskipper]

HANA SQLScript reference is here, in case you missed it.

I don't know odbc, so I don't know what you mean by the ?, but your declares look correct. What is the exact syntax error? Are you able to post the exact script?

SAP have a VM of HANA Express you could download to directly create your own SQL Scripts. Might speed things up.

Ta muchly - that's probably what I need.

Haven't got access at the moment, but IIRC the message was something along the lines of Syntax error on declare

[NotAllThere]

HANA SQLScript reference is here, in case you missed it.

I don't know odbc, so I don't know what you mean by the ?, but your declares look correct. What is the exact syntax error? Are you able to post the exact script?

SAP have a VM of HANA Express you could download to directly create your own SQL Scripts. Might speed things up.

[mudskipper]

Cheers - I'll try the set thing.

[eek]

I believe from limited experience probably 10+ years ago and a very quick google search tells me you need something like...

SET 'period' = '?';
SET 'actuality' = '?';

SELECT MYAMOUNT from MYTABLE where MYPERIOD=(select SESSION_CONTEXT('period') from DUMMY) and MYACTUALITY=(select SESSION_CONTEXT('actuality') from DUMMY);


UNSET 'period';
UNSET 'actuality';

Good luck I expect you'll need it but its variation of the above...

[mudskipper]

I've got a .net application that runs a sql script to populate a staging table. It's written to be generic - ODBC connection to the feed system(s), and the sql is read from a text file that is configured for each customer (using OdbcCommand - so the SQL is just a string). A bunch of parameters are passed in to cater for all scenarios.

So far, the only systems we've interfaced with have been sql server. An ODBC connection doesn't support named parameters, so the (sql server) sql script looks something like

DECLARE @period varchar(4) = ?;
DECLARE @actuality varchar(2) = ?;

SELECT sum(myamount) as amount from mytable where myperiod=@period and myactuality = @actuality;

All works hunky dory

I need to be able to do the same with a HANA database, but can't work out the variable syntax

With the help of google, I've got -

declare period varchar(4) := ?;
declare actuality varchar(2) := ?;

SELECT sum(MYAMOUNT) as amount from MYTABLE where MYPERIOD=eriod and MYACTUALITY=:actuality;

But I get a syntax error on declare

To add to the difficulty, I only have access to configure the system via a webex screenshare, where every mouse click or keystroke comes with a 2 second timelag, so it's not easy just to edit and try stuff.#

How do I declare variables in HANA?

Any clues?