EXSQL and EXQUERY commands

Discuss your general Proclaim related queries here.
galexander
Posts: 55
Joined: Fri Jul 25, 2014 8:22 am
Location: Manchester
Has thanked: 2 times
Been thanked: 4 times

EXSQL and EXQUERY commands

Post by galexander »

Just wondering if anyone has had a play with these commands for linking to and returning data from an external database?

I have set up the relevant db connection and the EXSQL command looks like it is connecting to an external SQL database, but I can't get the EXQUERY command to return any data, am getting errors or complaints that there is no table name in the query.

Anyone got this running successfully?

Thanks

bazzerfee
Posts: 13
Joined: Thu Jul 07, 2016 9:02 am
Has thanked: 2 times
Been thanked: 1 time

Re: EXSQL and EXQUERY commands

Post by bazzerfee »

Hi, I am trying to also access our External SQL Database from Proclaim. Did you ever get any information about these commands?

enthusiastp
Posts: 38
Joined: Tue Dec 05, 2017 12:33 pm
Has thanked: 1 time
Been thanked: 1 time

Re: EXSQL and EXQUERY commands

Post by enthusiastp »

I had to use EXEC-SP rather than EXQUERY in the end

Bit of blurb here from Steve may help

You need to create a stored proc in SS Management Studio, and make sure you explicitly define INPUT's and OUTPUT's.

viewtopic.php?t=513

galexander
Posts: 55
Joined: Fri Jul 25, 2014 8:22 am
Location: Manchester
Has thanked: 2 times
Been thanked: 4 times

Re: EXSQL and EXQUERY commands

Post by galexander »

Hi

Yes Steve's comments and code set me on the right path, get Eclipse to send you the mentioned manual if you haven't already got it.
Here are a few early pitfalls and bits of info I found out while getting things set up in the early days:

• No underscores in SQL table names (if table has underscores, create a view instead)
• No names greater than 19 characters
• No spaces in column names – alias them all to remove spaces
• Square Brackets around table names, No brackets round column names
• Can run against SQL views and procedures
• EXEC-SP useful if need to return unprompted values to use within Proclaim
• EXQUERY useful if want to return a list of data for the user to choose from