Writing data to Adabase using the SQL Gateway

, , , , , ,
1

Hello. We originally purchased the SQL Gateway predominately to pull data from our Adabas environment into a datawarehouse, however we would now like to consider using it to write data back to Adabas in potentially large volumes, accessed via web applications. Can anyone tell me if the SQL Gateway product is a viable solution for this task? It seems to work well for read transactions but I’m uncertain about it’s ability to handle large quantities of write transactions. Any advice would be appreciated. Thank you.

2

Yes. The SQL Gateway can support this.
It is being done by several large companies.

3

Any chance you can provide a reference at one of these companies so that I could speak with someone? I am trying to work through our local SAG rep as well. Thank you.

4

I in development do not have access the customer data.
Could you please contact your SAG representative?

Thanks
Rick

5

Hi Richard!
Can you give some sample SQL script to write to Adabas. I was told to use an opequery SQL. tHANX!

joey

6

Hi Joey,

If you could provide a little more detail on what you are trying to accomplish, we can provide a sample or some guidance.

Thanks

lm

7

javascript:emoticon(‘:D’);
Wow, thanx for a quick response.

Writing to an adabas file from Windows SQL server. Already set up as linked server and able to read adabas files via Visual Studio.

Thanx again!

joey

8

Hi Joey,

In order for this to work you need a few things:

  1. Ensure you are using CONNX 12 or above.
  2. Make sure your linked server has the RPC & RPC Out option enabled.

Here are some examples of how to issue select and update statements with a link server configured as described above:

These examples are based on a CDD built against the adabas sample employees table - the catalog name of the CDD is called adalocal. The name of the linked server is called adawin.

– Select using 4 part name ( can be slow depending on the criteria used)
– limited to SQL server syntax
select * from adawin.adalocal.connx75.employees where personnel_id = ‘50003800’

– Example of using CONNX specific grammar via OpenQuery
– SQL gets passed directly to the CONNX SQL Engine
select * from openquery(adawin, ‘select * from employees where personnel_id = ‘‘123’’ {statistics}’)

– Regular select
select * from openquery(adawin, ‘select * from employees where personnel_id = ‘‘50003800’’’)

– Update statement sending SQL directly through to CONNX SQL Engine
exec (‘update employees set middle_name = ‘‘ABC’’ where personnel_id = ‘‘50003800’’’) at adawin

9

A million thanx again Larry!

Sorry I forgot to mention, initially we’ll just do STORE/INSERT into adabas, so I can just replace the UPDATE with the INSERT command, right?

Thank you very much.

joey

btw, where you at here in the States?

10

Yes, you can just replace update with insert.

I forgot to show you something else which makes exec very handy - it is already designed to handle parameter markers.

– Insert statement sending SQL directly through to CONNX SQL Engine and using parameter markers
exec (‘insert into employees (personnel_id, first_name, name) values (?,?,?)’, ‘123456’, ‘JOEY’, ‘VENTURA’) at adawin

– Exec with select and parameter markers
exec (‘select * from employees where personnel_id = ?’, ‘123456’) at adawin

– openquery with select
select * from openquery(adawin, ‘select * from employees where personnel_id = ‘‘123456’’’)

Thanks

lm

11

We are at the Bellevue, WA office of Software AG.

Thanks

lm

12

Hi Larry,

I was able to do a select query. Also, I changed the RPC/RPC OUT both setting to true. I tried to do an update and I got this error:

=======
OLE DB provider “CONNXOLEDB” for linked server “MAINFRAME_MHT_DB14” returned message " Update Access Denied on Column.
update “hwdcs1_cahwnet_gov”.“dbo”.“PATIENT_CASE” set << Syntax Error >> transaction_date = 20170113

where transaction_date = 20170113 and ISN_PATIENT_CASE = 44644
".
Msg 7215, Level 17, State 1, Line 5
Could not execute statement on remote server ‘MAINFRAME_MHT_DB14’.

Is there a setting in the CDD file?

Thanx!
joey

13

Hi Joey,

When you create a data dictionary, the default access for all tables is read only.

You have several options to resolve the issue.

  1. Change the default access for the entire CDD to read/write (Via the data dictionary menu option)
  2. Explicitly grant insert/update/delete access to the CONNX user in question.
  3. Create a group, and grant insert/update/delete access to the CONNX group, then add the CONNX user to the group.

You can accomplish 2 & 3 either via the data dictionary GUI, or via standard GRANT sql statement as documented in the CONNX SQL grammar.

14

Thanx again!

Can the read/write access be set by file instead of the whole CDD? If not, should I create a separate CDD for files that I need insert/update?

Thanx!
joey

15

Hi Joey,

Yes I should have been more specific in my description of option #2 and #3.

With these two options you could grant access to that file only. Please look at the security tab in the CDD for the given table.

You could also use SQL Grant syntax - something like:

Grant insert on MyTableName to UserOrGroup;

Thanks

lm

16

Sorry if I have too many questions.

Can I leave the “Default Access = Read”, and use the Grant insert on MyTableName to UserOrGroup SQL command, will this work?

Thanx!
joey

17

Hi Joey,

Yes it will work.

Thanks

lm