Set Port Number for ODBC Connection

[former_SQLA_member1694895]

Hi

To connect to our database over a WIFI or VPN connection I need to specify a Port Number. This worked fine until I rebooted the server last night and the Port on which the database was listening has changed.

Is there a way I can specify a Port Number that should always be used?

We are running SQL Anywhere 9.0, no option to upgrade.

[VolkerBarth · Accepted solution]

You can specify the port number the server should use with the PORT protocol option, such as

dbsrv9 -x tcpip(port=2638) -n MyServer...

or even specify a range of ports of which the server should use all those that are available:

dbsrv9 -x tcpip(port=2638,49152-49155) -n MyServer...

Likewise, the client can explicitly use a specified port (or a port range, of which then the first available is used) in the PORT protocol option of the LINKS connection parameters:

-c "...;LINKS=tcpip(host=<ServerIP>;port=2638,49152-49155);..."

---

For details, see that FAQ .

[former_SQLA_member1694895 · Accepted solution]

Thanks, Is there a way of seeing the current port range that has been specified?

[VolkerBarth · Accepted solution]

Hm, that could be visible in the server's and/or client's communication logs, i.e. dbsrv9 -z resp. the LOG= client connection parameter.

You can also query the server's command line resp. used TCP/IP addresses (including ports):

SELECT property('CommandLine'), property('TcpipAddresses');

but the latter will not tell whether they have been explicitly specified or not (unless they differ from the default ports, i.e. 2638 and then 49152 and up...).

---

Sorry, property('TcpipAddresses') will not work for you, it has been introduced with v11.

[Breck_Carter · Accepted solution]

> seeing the current port range

SELECT PROPERTY ( 'CommandLine' );

'-c 100M -o dbeng9_log_ddd9.txt -os 10M -x tcpip(port=12345,12350-12359) -zl -zp -zt ddd9.db -hn0,7568:192 '

[former_SQLA_member1694895 · Accepted solution]

Can I ask how I run this?

It might seem like a simple question, but I have very limited knowledge in this area.

[VolkerBarth · Accepted solution]

Just start Interactive SQL (either on the box with the database server or on your client machine) and execute the SQL query there...

[Breck_Carter · Accepted solution]

In addition to what Volker said:

The SELECT statement I posted is shorthand for the following query:

SELECT PROPERTY ( 'CommandLine' ) FROM dummy;

If you are familiar with Oracle, for example, dummy is like dual.

You can run the query from any application that connects to SQL Anywhere via ODBC. It returns a singleton result set; i.e., a single column in a single row. The column data type is varchar(32000).

For example, in PowerBuilder you can code this:

SELECT PROPERTY ( 'CommandLine' )
  INTO :ls_commandline
  FROM dummy
 USING SQLCA;

In this particular case, it is up to your application to parse the return value; e.g., to find and understand the port values 12345,12350-12359 that are embedded in the command line.

The PROPERTY function is a builtin SQL Anywhere function that returns the current value of a specific server-level property.

The CommandLine property is the string of options that followed the "dbsrv9" program name on the Windows command line, plus some extra "magic" stuff that SQL Anywhere adds (-hn0,7568:192) which you can ignore.

[jack_schueler]

You could try running the dblocate utility (dblocate ip-address).

Example: dblocate 10.7.161.42

However, I must confess that version 9 of this tool crashed on me (version 9 is very ancient software).

[former_SQLA_member1694895]

Thanks for the response. I will give this a try.

Reference version 9, it is a third party piece of software so I have no option to update. I know the supplier has just released an alternative system that runs on a new cloud based platform so maybe this is why we are stuck on v9.