SQA versus SQL

[alexcweiner]

I have my system configured to work with SQAPL, via odbc, to access a remote MySQL database on Linux. What additional configuration needs to be done to get the SQL namespace working with MiServer? The following contains my setups, and what isn't working.


My /etc/odbc.ini looks like this:

Code: Select all

[ysql]
Description=yhnmjuik MySQL instance
Driver=MySQL
user=yhnm
password=juik
Server=yhnmjuik.com
Port=3306
Socket=/var/lib/mysql/mysql.sock
Database=YH
Option=3
ReadOnly=No


My /etc/odbcinst.ini looks like this:

Code: Select all

[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib64/libmyodbc5w.so
Setup=/usr/lib64/libodbcmyS.so
FileUsage=1
UsageCount=5


This works, and correctly retrieves the data:

Code: Select all

SQA.Init ''
r← SQA.Connect 'YK' 'ysql'
zz← SQA.Do 'YK' 'Select * From tableau'


My Datasources.xml looks like this:

Code: Select all

<Datasources>
<Datasource>
<Name>YHNMJUIK server</Name>
<DSN>ysql</DSN>
</Datasource>
</Datasources>


I try to use SQL after Miserver has loaded and I get this error or connection (attempt):

Code: Select all

 s←SQL.ConnectTo'ysql'                 
601    Unable to connect to "ysql" due to [unixODBC][MySQL][ODBC 5.3(w) Driver]Access denied for user 'yhnm'@'MYLOCALIP' (using password: NO)


Where "MYLOCALIP" is my local IP address. I don't understand why it is trying to access my local IP. I also do not understand why it states there is no password, when there is a password in same config that it pulls username from.

[Brian|Dyalog]

To All -

Alex had sent me an email regarding this issue as well and I addressed it in my reply email to him, but neglected to post a follow-up in this forum... so,

The reason for the failure was you're trying to use the dsn and not the Name you've assigned the connection in Datasources.xml. What was confusing me is that he should have received a message that said 601 (Datasource "ysql" not found). There is code in MiServer that explicitly looks up the name and issues that message. So, the example as posted still wouldn't work, but it would have generated a completely different message.

As it turns out, for security purposes, Alex had edited the content of what he posted in the forum. Alex and I had addressed this in our email conversation.

A bit of background... The SQL namespace that's provided with MiServer is something of an experiment that I implemented 6 years ago to provide a high-level interface to SQA. It was modeled after techniques I'd used with ColdFusion. SQAPL connections can have all sorts of options, using a dsn, going dsn-less, etc, and I wanted to provide a simple means of hiding that and pushing those details into a configuration file.