cancel
Showing results for 
Search instead for 
Did you mean: 

ODBC Connections using dynamic parameters

2,211

I built a 32-Bit Windows application, which connects to 3 different database servers (3 different companies under one roof).

The clients and servers are on different subnets ( servers on 192.168.60.xxx and clients on 192.168.58.xxx ).

The only way I've been able to consistently connect is by creating 3 different ODBC Datasources, with the 'main' database always connecting, as it runs on the standard port 2638, however, in order to connect to the other two, I've had to burn in the port # in the DSN. Unfortunately, every time we reboot our server, the ports change...

So, I'm wondering if I can create a SINGLE ODBC data source, and to dynamically provide the host, server name, port, and database name at the time of connection...

Accepted Solutions (0)

Answers (2)

Answers (2)

VolkerBarth
Contributor

...application, which connects to 3 different database servers

Does one instance of the application connects to all three databases in parallel, or can choose to which single one to connect at a time, and in the latter case, can it "shift" to another database lateron within the same run?

Unfortunately, every time we reboot our server, the ports change...

Just to better understand: Do those three database servers run on the same machine or on different machines? (Database servers on different machines should not change their ports, and for multiple database servers on the same machine you can certainly provide "stable" port numbers by using the PORT protocol option with dbsrvX -x...

FWIW, you do not need ODBC DSNs at all, SQL Anywhere also allows for DSN-less connections. E.g. you could use a connection string like the following:

"Driver=SQL Anywhere 17;Server=demo17;UID=dba;PWD=sql;DBN=demo"
jack_schueler
Product and Topic Expert
Product and Topic Expert

Just to be clear, the connection string would include the HOST=computer-ip:port connection parameter. For example, "DSN=basedsn;HOST=YKFN123456.global.mycorp:2639;Server=YKFN123456_OData;...". If you do use a DSN, then placing it at the start like this sets defaults for whatever parameters you specify in the data source, which can then be overridden using explicit parameters that follow.

Breck_Carter
Participant
0 Kudos

To confuse matters, there is a HOST connection parameter (shown above) and HOST protocol option that is a sub-parameter of the LINKS connection parameter. The latter (LINKS) is often used intead of the HOST connection parameter to prevent horrible mistakes in large, complex networks involving many subnets and SQL Anywhere servers. It is used because you can specify the DOBROAD=NONE protocol option to stop SQL Anywhere from disregarding the specified IP address and port number if they don't work. Without DOBROAD=NONE, under certain circumstances, the connection can be made to an entirely different server. With DOBROAD=NONE, according to the Help, "Specifying DoBroadcast=NONE causes no UDP broadcasts to be used, and the database server address cache (located in the sasrv.ini file) is ignored. A TCP/IP connection is made directly with the HOST/PORT specified, and the database server name is verified. If you specify DoBroadcast=NONE, the HOST (IP) protocol option is required."

IMO, the DOBROAD=NONE behavior is what I ALWAYS want. I.e., if I specify the wrong IP and/or port, I WANT THE CONNECTION TO FAIL, not connect to some random server in a stinking cache or one that responds to a broadcast.

IMO, the introduction of the HOST connection parameter was a BIG MISTAKE.

VolkerBarth
Contributor
0 Kudos

I may be wrong BUT AFAIK the HOST protocol option is an important "hint" whereas the HOST connection parameter is mandatory... Are you sure the latter may be ignored? The one known drawback for me is that the HOST connection parameter enforces TCP/IP even for local connection that would otherwise use ShMem.

Breck_Carter
Participant
0 Kudos

When the Help Portal stops throwing "500 Internal Server Error", check out this topic: Troubleshooting: How the Host connection parameter locates database servers.

...so many words, so little clarity.

I'm not "sure" about anything, except that DOBROAD=NONE on the client side has solved many problems over the years.

VolkerBarth
Contributor
0 Kudos

According to that particular FAQ, the undocumented IgnoreCache=Yes connection parameter might do the same trick when using the HOST connection parameter.

However, I still wonder why it should be difficult to find an IP address in a different subnet... (I do understand it's "difficult" when you do not specify an IP address/host name but let the network search for a machine with the fitting database server name...)

You probably cannot get this into a single ODBC data source, but you could go completely without an odbc data source if you use SQLDriverConnect with a connection string instead of using SQLConnect. Obviously you can dynamically create the connection string yourself.

You'll have to do a bit research on the details of the connect string. For Anywhere specific options look at DocCommentXchange. In addition you'll have to add the driver which should be used, for example as "Driver=SQL Anywhere 17" (this is the driver name displayed in the ODBC administrator).

VolkerBarth
Contributor

I don't think there's particular research necessary, I guess this is similar to the part of my answer...:)

FWIW, you do not need ODBC DSNs at all

0 Kudos

Indeed most of the answer can be researched from the previous ansers and comments (though I miss the "Driver=" parameter which AFAIK is critical and not in Anywhere docs).

But maybe the main goal of my answer was to be short. 🙂

VolkerBarth
Contributor
0 Kudos

though I miss the "Driver=" parameter

Well, that's why I think my answer has already contained that sample 🙂