How to setup a dynamic SQL ADO connection.

0 Likes
over 5 years ago

Problem:

How to setup a dynamic SQL ADO connection.

Resolution:

The code below demonstrates how to populate a String variable with an ADO Connection String.

Use this string variable in the SQL CONNECT statement and the remainder of your SQL statements.

- Start of sample code:

- Create a variable ws-connection-str:
01 ws-connection-str pic x(5000).

- Then in the procedure division:

**** Create an ADO Connection String to establish the ADO.Net Connection.
string 'Data Source=localhost;'
'Initial Catalog=MFTest;'
'Integrated Security=False;'
'Persist Security Info=False;'
'User ID=Thisismyuserid;' *> Specified User ID
'Password=thisismypassword;' *> Specified Password
'Enlist=True;'
'Pooling=True;'
'Min Pool Size=0;'
'Max Pool Size=100;'
'Asynchronous Processing=False;'
'Connection Reset=True;'
'MultipleActiveResultSets=False;'
'Replication=False;'
'Connect Timeout=15;'
'Encrypt=False;'
'TrustServerCertificate=False;'
'Load Balance Timeout=0;'
'Packet Size=8000;'
'Type System Version=Latest;'
'Application Name=".Net SqlClient Data Provider";'
'User Instance=False;'
'Context Connection=False;'
'Transaction Binding="Implicit Unbind";'
'MultipleActiveResultSets=True;'
'factory=System.Data.SqlClient;' *> Factory used to point to the
*> Provider Class we will use.
*> Additional settings documented in Microsoft Help
delimited by size
into ws-connection-str
end-string


Next is the SQL Connect statement code:

EXEC SQL
CONNECT using :ws-connection-str
END-EXEC
IF SQLCODE = 0
DISPLAY "Connected via a Connection String"
DISPLAY "The Connection string could be in code"
EXEC SQL
DISCONNECT CURRENT
END-EXEC
IF SQLCODE NOT = 0
DISPLAY "DISCONNECT ERROR"
STOP RUN
END-IF
END-IF

Comment List
Anonymous
Related Discussions
Recommended