Myujth

I am trying to troubleshoot a C program that connects to a local SQL database and then runs a series of commands. Should be SQL Coding 101... but I'm missing something fundamental. I'm working in a Linux container, which supports both the SQL server and is where my C program runs.

Some basics: Here's the Linux version:

root@1234567890:/home/me# uname -mrs
Linux 4.4.0-64-generic x86_64
root@1234567890:/home/me#

I have my SQL server running, installed from the instructions here. The service is up, and I can manually query it with SQLCMD:

root@1234567890:/home/me#
root@1234567890:/home/me# ps -ef | grep sql
root 1 0 0 15:45 ? 00:00:00 /opt/mssql/bin/sqlservr
root 8 1 0 15:45 ? 00:01:25 /opt/mssql/bin/sqlservr
root 13988 13639 0 18:38 pts/2 00:00:00 grep --color=auto sql
root@1234567890:/home/me#
root@1234567890:/home/me#
root@1234567890:/home/me# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'Password01'
1> SELECT @@servername;
2> go

--------------------------------------------------------------------------------------------------------------------------------
abc123456789

(1 rows affected)
1>

Not sure why there are two instances of "sqlservr", but overall, the above is promising. Back when I set up the SQL server, I let it pick the default TCP port, 1433, and it looks like the service is listening there:

root@1234567890:/home/me# netstat -l
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 127.0.0.11:40962 *:* LISTEN
tcp 0 0 *:1433 *:* LISTEN
udp 0 0 127.0.0.11:60893 *:*
Active UNIX domain sockets (only servers)
Proto RefCnt Flags Type State I-Node Path
root@1234567890:/home/me#

Some Networking 101 troubleshooting here:

root@1234567890:/home/me# telnet 127.0.0.1 1433
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
^C

Connection closed by foreign host.
root@1234567890:/home/me#

Hmm. If my SQL server was listening on this port, would this be the response I would expect to see here? Not sure.

Now to my C code. I have the "mysqlclient" library in on my machine and it seems to be running just fine. Here's the code:

/* sqlToy.c */
#include <stdio.h>
#include "/usr/include/mysql/mysql.h"

/* Adapted from:
 https://www.youth4work.com/Talent/C-Language/Forum/118317-how-do-link-database-with-in-a-c-program
*/

int main() 
 MYSQL mysql;
 printf("...attempting initization... ");
 if(mysql_init(&mysql)==NULL) 
 printf("nInitialization errorn");
 return 0;
 else 
 printf("Initilization successful!n");
 

 printf("...attempting connection... ");
 if(mysql_real_connect(&mysql, "localhost", "sa", "Password01", NULL, 1433, NULL, 0) == NULL)
 printf("Connection errorn");
 return 0;
 
 mysql_close(&mysql);
 return 1;

Here's program compilation and the output:

root@1234567890:/home/me# gcc -Wall sqlToy.c -lmysqlclient
root@1234567890:/home/me# ./a.out
...attempting initization... Initilization successful!
...attempting connection... Connection error
root@1234567890:/home/me# 

The "Connection error" message pops up IMMEDIATELY, with no waiting.

I also note that when I do a "tail -f /var/opt/mssql/log/errorlog", I never see an error message when my program runs. Which makes me wonder if the C program's request is reaching the SQL server at all.

I suspect there are one of two possibilities going on here:

(A) The mysql_real_connect() command in the C code is wrong, I'm using the wrong options, the wrong flags, etc. And a failed login is not logged in /var/opt/mssql/log/errorlog.

(B) Although it looks like everything is good to go, there's some network problem. ie, the SQL server really isn't listening on 1433, something like that.

Any ideas? Thanks...

^{Transferring comment into an answer.}

I would expect to use the MySQL client libraries (functions such as mysql_init() in your code) to connect to a MySQL database server. Similarly, I'd expect to use the SQL Server client libraries (probably ODBC plus the SQL Server driver) to connect to SQL Server.

Expecting the MySQL client library to connect to an SQL Server database is at least 'not obviously correct'. Having looked briefly at the MySQL C API documentation, I think that it is 'obviously not correct'. So, if you want to connect to SQL Server, you should probably use ODBC (and the SQL Server driver).

There's an ODBC driver for MySQL too; if you do need to connect to both SQL Server and MySQL in the same program, it might be sensible to use ODBC for both.

Generalizing, most DBMS provide several methods of connecting to the database server. For Java code, there is likely a JDBC driver; for .NET code, there might be a .NET driver, or it might use ODBC under the covers. For most other languages, the usual technique is to choose between an ODBC driver for the DBMS or the DBMS's native connectivity, which goes by many different names. You need to match the client code libraries with the database server you want to connect to. Expecting an Oracle driver to connect to DB2 is not plausible; expecting a MySQL driver to connect to SQL Server is not plausible. There may be techniques that allow such inter-connectivity, but they require great care. The ODBC infrastructure has a common, driver independent layer (driver manager) and a unique-per-DBMS, driver dependent layer. You can sometimes write code using ODBC and arrange to connect to different DBMS by having different drivers loaded concurrently.