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.

asked 11 May, 01:15

LeiFeng's gravatar image

LeiFeng
105
accept rate: 0%


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.

permanent link

answered 11 May, 03:20

Volker%20Barth's gravatar image

Volker Barth
30.6k306456662
accept rate: 32%

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; 
permanent link

answered 11 May, 07:40

Breck%20Carter's gravatar image

Breck Carter
25.8k428592852
accept rate: 20%

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()
permanent link

answered 11 May, 10:53

JBSchueler's gravatar image

JBSchueler
2.0k2837
accept rate: 15%

edited 11 May, 10:57

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 :)

(11 May, 11:05) Breck Carter
Replies hidden

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.

(11 May, 12:26) JBSchueler
Replies hidden

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".

(11 May, 13:55) JBSchueler

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...

(13 May, 04:01) Volker Barth
1

@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 :)

(13 May, 15:24) Breck Carter

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!

permanent link

answered 11 May, 13:23

LeiFeng's gravatar image

LeiFeng
105
accept rate: 0%

edited 11 May, 13:26

"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.

(11 May, 13:36) JBSchueler

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.

(11 May, 13:43) JBSchueler
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×34
×16

question asked: 11 May, 01:15

question was seen: 190 times

last updated: 13 May, 15:48