MapInfo Pro Developers User Group

I am using MapInfo V16, Mapbasic 16, MSSQL and I am very much new to Mapbasic like I started to work on it for a week.

I want to connect to MSSQL server and access X tables from Database A without letting the user pass any usernames and passwords to connect to the database. The user should not have any choice to select any other than X tables.

Dim nConn As Integer

Dim sMyDSN, sMyUsername, sMyPassword As String

sMyDSN = "MySQLServer"

sMyUsername = "PEM"

sMyPassword = "ABCDEF"

nConn = Server_Connect("ODBC", "DSN=" & sMyDSN & "; UID=" & sMyUsername & "; PWD=" & sMyPassword)

And then to select the tables

Register table "ROADS"

  Type ODBC

  Connection Handle nConn

  Toolkit "ODBC"

  Table "Select * From DB.ROADS"

  Into "C:\test\ROADS.tab"

Open Table "C:\test\ROADS.tab"

I could see this example is for MySQL but how I do I recreate the same for MSSQL? And in this example, For DSN do we have to give the DSN name along with the location?

Only DSN I know is a file that gets created when we create aODBC connection with MSSQL with the following details

[ODBC]

DRIVER=SQL Server

UID=sa

DATABASE=Test

WSID=KRISHNA

APP=MapInfo® Pro

SERVER= x.x.x.x

Description=ABCDEF

Now I would like to know how do we connect to the specific tables without letting the user see the credential window.

I tried like this

Keeping one DSN file by giving UID and PWD and calling the DSN with

Dim hdbc As Integer

hdbc = Server_Connect("ODBC", "SRVR=IP;UID=username;PWD=password")

But when I use this, MapInfo is still opening a window to select the DSN file as below

See Attachment

and then SQL login

See Attachment

Then we need to select the schema then Table and access type

See Attachment

I just wanted to remove the GUI part here and make the connection with Tables

Looking forward to seeing the Mapbasic code that can help me to make the connection with MSSQL database " Test" and connect to tables "xx,xy,xyz and abc " with DSN name " Grid.DSN"

Thanks in advance.

If you named data source (DSN) is stored in a file instead of in the registry, you need to point this file using the "FILEDSN" instead of the "DSN" parameter.

sMyDSNFile = ApplicationDirectory$() & "Grid.DSN"

nConn = Server_Connect("ODBC", "FILEDSN=" & sMyDSNFile & "; UID=" & sMyUsername & "; PWD=" & sMyPassword)

In the example above I'm assuming the DSN file is placed in the the same folder as the MapBasic Application.

Once you have the connection, you can use either Register Table to create Live DBMS tables or you can use the Server Link Table statement to create Linked DBMS Tables.

PS: In case you already are using the mbLibrary, have a look at the module called DBMSUtilityLib.mb. It contains a bunch of different functions for creating connections to and opening tables from a (spatial) database.

Hi Peter,

I think this can solve if the user has the DSN file in the specified path. Thanks for this Peter.

If I have to connect remote database ( MSSQL), Assuming I don't have DSN file. What would be the solution? I should be able to run a Mapbasic code and connect to the certain tables.

I don't know if I am thinking too many scenarios but connecting remote database without getting those GUI for selection of tables and DSN file location window will be necessary. The user just has to run a Mapbasic script and get this done.

Can this be doable @Peter Horsbøll Møller? ? 

Thanks :)

Hi Krishna,

The DSN/DSN file is basically a placeholder for information needed to connect to the database, such as server name, server type, driver type etc.

You can have all these parameters as part of your connection string, but personally I think it's easier to use a DSN file to keep these parameters. Also, Window gives you an user interface to actually setup and check your DSN.

You can't connect to a database without knowing the details that are in the DSN.

So that means, We can write the connection string and run that in MapBasic console to get the access directly without using the DSN. Isn't it?

Why I want to make a connection like that is to restrict the user not to choose all the table while they are connecting to the DB.

So I want to run such MapBasic code whenever the user opens his MapInfo application just like MapConfiguration in SSA with access control.

Actually, i am trying to make the connection without using the .MBX . But just Mapbasic methods ( like MapInfoApplication .Do , MapInfoApplication.RunMapBasicCommand ) from .NET application..

Yeah, that should work.

I haven't tried it myself but I have seen it done by some one else years back.

When you know the name of the table you want to connect to you can use this mapbasic command.

Register Table "Trees" TYPE ODBC Charset "WindowsLatin1" TABLE "Select * From ""MyDatabase"".""dbo"".""Trees""" CONNECTION "DSN=DNSNAME;Description=Test;UID=username;PWD=password;DATABASE=MyDatabase" toolkit "ODBC" Autokey Off CACHE ON Versioned Off Workspace "" ParentWorkspace "" Into "C:\MapInfo\Tables\Trees.tab"

You can build this from your variables like this (adding your parameters):

DIM sMyDNS as String

DIM sMyDatabase as String

DIM sMyUsername as String

DIM sMyPassword As String

DIM sMyTable as String

sMyDatabase = "GIS"

sMyDNS = "MySQLServer"

sMyUsername = "PEM"

sMyPassword = "ABCDEF"

sMyTable = "ROADS"

DIM cmd as String

cmd = "Register Table """ & sMyTable & """ TYPE ODBC Charset ""WindowsLatin1"" TABLE ""Select * From """"" & sMyDatabase & """"".""""dbo"""".""""" & sMyDatabase & """"""" CONNECTION "DSN=" & sMyDNS & ";Description=Test;UID=" & sMyUsername & ";PWD=" & sMyPassword & ";DATABASE=" & sMyDatabase & """ toolkit ""ODBC"" Autokey Off CACHE ON Versioned Off Workspace """" ParentWorkspace """" Into "C:\MapInfo\Tables\Trees.tab"""""

Run Command cmd

I just wrote this out of my head, there might be syntax errors in the above code.