is there any way to use sqlanydb with database host name?

[Former Member]

Hi,

I am new to sqlanydb. I found that sqlanydb.connect() takes dsn as part of its input. My question is if pre-defining DSN in my system is not easy to do (for example I am not a system administrator), is there any way that I can connect using host name and port number? In cx_Oracle there is a function called makedsn(host, port) that can generate a DSN ad-hoc. Can I do similar things with sqlanydb?

Any suggestion is appreciated.

[jack_schueler]

Here is an example similar to those in the documentation. You can use any connection parameter you like, including short or long forms. You don't have to use Breck's arcane LINKS connection parameter :-).

import sqlanydb
myuid = raw_input("Enter your user ID: ")
mypwd = raw_input("Enter your password: ")
# Create a connection object, then use it to create a cursor
con = sqlanydb.connect( userid=myuid, 
           pwd=mypwd, 
           servername='Demo', 
           host='demo-t3.example.com:2638' )
cursor = con.cursor()

# Execute a SQL string
sql = "SELECT * FROM Employees"
cursor.execute(sql)

# Get a cursor description which contains column names
desc = cursor.description
print len(desc)

# Fetch all results from the cursor into a sequence,
# display the values as column name=value pairs,
# and then close the connection
rowset = cursor.fetchall()
for row in rowset:
    for col in range(len(desc)):
        print "%s=%s" % (desc[col][0], row[col] )
    print
cursor.close()
con.close()

[Breck_Carter]

My use of the word "arcane" applies to SQL Anywhere's behavior when DOBROADCAST=NONE is omitted when working in a complex and rapidly changing network environment involving multiple SQL Anywhere servers.

DOBROADCAST=NONE forces SQL Anywhere to connect ONLY to the host and port specified, not some other target that is determined by the "arcane" rules.

> You don't have to use Breck's arcane LINKS connection parameter :-).

Does your connection string default to DOBROADCAST=NONE?

Perhaps not... the Help says "It is recommended that you only use the CommLinks (LINKS) connection parameter if you need to specify TCP/IP protocol options other than HOST or ServerPort (PORT)."

It is dangerous to turn the phrase "arcane [thing]" into "arcane [person]"... things tend to lie there and take it, persons not so much 🙂

[jack_schueler]

It's my understanding that the combination of server name, host address, and port should suffice to ensure you are connecting to the correct server. You can add DatabaseName (DBN) to ensure the correct database on that server. I used repeated your use of the word "arcane" because it is my belief that you shouldn't have to resort to use of LINKS anymore. However, I have a feeling that I may be enlightened shortly as to why it is still necessary.

[jack_schueler]

I think the use of "arcane" applies quite well to the LINKS parameter. There are few who understand all the complexity that this parameter offers. I didn't mean to imply that Breck was arcane, but I did like his use of the term arcane for the stuff that happens "under the hood".

[VolkerBarth]

AFAIK, one of the "pitfalls" of the HOST connection parameter is the fact that it enforces the use of TCP/IP whereas with the LINKS(HOST=...) communication parameter a same-machine connection (server and client on the same box) would use the "cheaper"/"faster" ShMem protocol, so that might be something to address when the same connection string should be used both on the local and different machines...

---

That being said, like Breck, I'd like to know whether the HOST connection parameter enforces the connection to exact that machine...

[Breck_Carter]

@Volker: JBSchueler may be correct that "the combination of server name, host address, and port should suffice to ensure you are connecting to the correct server", I don't have the resources to test it right now (a large, rapidly evolving wide-area and local-area network with many SQL Anywhere databases being added and removed). My experience has been, on many occasions, that DOBROADCAST=NONE solves strange symptoms. The debate boils down to "the new-school HOST connection parameter described here versus the old-school HOST protocol option described here". Both descriptions are very long, very complex and internally inconsistent (i.e, arcane), and almost identical as far as I can tell.

...oh, another thing, don't get me started about SERVER versus ENG connection parameters... they are semantically identical, and "SERVER" is like "MIRROR" in that nobody really knows what you're talking about when you use it in a sentence... "server" can mean "machine" so people think "host" or "computer name" but "engine" clearly means "running SQL Anywhere instance" as in dbsrv -n name... at least it does when you apply the Telephone Test 🙂

[Breck_Carter]

I agree that DSNs are Very Bad Things, on many levels, and should be avoided. ( If I could, I would eliminate the DSN tab from Foxhound... but that's not gonna happen 🙂

Here are some copy-and-paste entry from the "Show Examples" button on the Foxhound Menu "String" tab that lets you specify a target database:

ENG=ddd; DBN=ddd; UID=dba; PWD=sql; DRIVER=SQL Anywhere Native; 
ENG=ddd; DBN=ddd; UID=dba; PWD=sql; DRIVER=SQL Anywhere 12; 
ENG=ddd; DBN=ddd; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; 
ENG=ddd; DBN=ddd; UID=dba; PWD=sql; DRIVER=SQL Anywhere 17; 

The following entry is very useful for network servers; the DoBroadcast=NONE forces the connection to made ONLY to the specified IP and port (otherwise, if the specific target is missing, a connection to some other server may be made according to arcane and bizarre rules):

LINKS=TCPIP(HOST=192.168.1.101; PORT=2638; DoBroadcast=NONE);

Here's an example of a full-tilt AutoStart connection string, for the Foxhound sample database that shows the adhoc reporting schema:

ENG=adhoc_schema4; DBN=adhoc_schema4; UID=DBA; PWD=sql; DRIVER=SQL Anywhere 16; START=C:\\Program Files\\SQL Anywhere 16\\Bin32\\dbeng16.exe; DBF=C:\\ProgramData\\RisingRoad\\Foxhound4\\adhoc_schema4.db; AUTOSTART=YES; 

[VolkerBarth]

When making a connection to a SQL Anywhere database, you do not need to use a DSN connection parameter at all, it's just one of the many connection parameters you can use.

There are several samples with connection strings using the HOST (or SERVER) and DBN connection parameters to specify the machine name (HOST) or database server name (SERVER) and the database name (DBN), and those can be used with sqlanydb.connect(), too.

[Former Member]

Hi, Thank you all for your prompt response, it is very helpful. Now I partially solved my problem. Here is the piece of code I used here:

kwargs={'EngineName': 'ASIQ_DENT',
'CommLinks': 'tcpip(host=ASIQ_DENT,14110)',
'password': 'scott',
'uid': 'tiger'}
sqlanydb.connect(**kwargs)

My Python is v-3.4.5, and sqlanydb is v-1.0.8

This works on my linux server even if I did not specify the driver. However on my windows machine where standard Anaconda environment + sqlandb.py is installed, error message shows:

---

File "C:\\AppData\\Local\\Continuum\\Anaconda3\\lib\\site-packages\\sqlanydb.py", line 456, in load_library raise InterfaceError("Could not load dbcapi. Tried: " + ','.join(names))

TypeError: sequence item 0: expected str instance, NoneType found

---

I summarize my questions here:

1. Why it is not working in the windows environment? Is is because I did not install proper driver? Should the driver come with sqlanydb, or should I install it independently?

2. In the linux environment where the connection works, what is the purpose of using 'CommLinks': 'tcpip(host=ASIQ_DENT,14110)'? Why when I use 'host': 'ASIQ_DENT,14110' directly, it would give the error message like: OperationalError: (b"Parse error: Invalid or missing keyword near 'host'", -95)?

Thank you in advance!

[jack_schueler]

"Could not load dbcapi" is a reference to dbcapi.dll. Make sure that this DLL is somewhere in your path. The DLL is part of the SQL Anywhere software. You might find it in SDK\\PHP\\bin, Bin64, and/or Bin32.

[jack_schueler]

Instead of tcpip(host=ASIQ_DENT,14110) you should have tcpip(host=ASIQ_DENT:14110).

You could also have tcpip(HOST=ASIQ_DENT;PORT=14110).

See http://dcx.sap.com/index.html#sqla170/en/html/8142f3eb6ce21014930da2a0c3f8964a.html and http://dcx.sap.com/index.html#sqla170/en/html/814ec71c6ce21014bb5ee01d96730415.html for examples.