Querying SQL Server Tables From MapBasic

[Willy Nicolaisen]

Hello all
 
Using Open... from the File Menu in MI 10.0 I have created a .TAB file that contains the following statements:
 
!table
!version 500
!charset WindowsLatin1

 

Definition Table
Type ODBC
begin_metadata
"\IsReadOnly" = "FALSE"
"\DATALINK" = ""
"\DATALINK\Query" = "select * from ""dbo"".""TestUTM"""
"\DATALINK\ConnectionString" = "DSN=TestODBC;Description=TestODBC;UID=WILLYN64;Trusted_Connection=Yes;APP=EasyLoader;WSID=WILLYN64-PC;DATABASE=Test"
"\DATALINK\ToolKit" = "ODBC"
"\CACHE" = "ON"
"\MBRSEARCH" = "ON"
end_metadata

 

The resulting table contains all the rows Where År=2004 with the geographical objects from the server table.
   
Can I implement the above in a MapBasic 10.0 program?

 

The following statements from a MapBasic 10.0 program returns the same rows but without the geographical opbjects:

 

Dim hdbc, hstmt, tid, i As Integer
hdbc=Server_Connect("ODBC","DSN=TestODBC;Description=ALLELASTER på SQL Server;UID=WILLYN64;DATABASE=Test;Trusted_Connection=Yes;APP=EasyLoader")
hstmt=Server_Execute(hdbc,"Select * from ALLELASTER where År='2004'")
Server hstmt Fetch Into "ALLELASTER2004" File "e:\ALLELASTER2004.tab"
Server hdbc Disconnect

 

I expected, perhaps naively, that the server functions in MapBasic would have been updated to work like the statements in the above .tab file but, apparently, this is not so?

 

Regards
Willy

[Peter Horsbøll Møller]

Hi Willy,

 

The first time you post on MapInfo-L your e-mail has to be moderated by one of the moderators to prevent spam. That's why it took a while before your posting showed on the list.

 

I would recommend that you have a look at the Server Link Table statement.

This statement is "better" when you want to create a linked table.

 

You code would look like this:

 

Dim hdbc As Integer
hdbc=Server_Connect("ODBC","DSN=TestODBC;UID=WILLYN64;DATABASE=Test;Trusted_Connection=Yes")
Server hdbc Link Table 

   "Select * from ALLELASTER where År='2004'"

   Into ALLELASTER2004"
   Toolkit "ODBC"
   File "e:\ALLELASTER2004.tab"

 

I removed some of the parameters from the Connection String.

I would also recommend that you prefix the table name with the table owner/schema.

 

If you are looking for the syntax of a live table, you should look at the Register Table statement.

Within this you can use the Type ODBC to connect to a ODBC database.

 

Peter Horsbøll Møller
Pitney Bowes Business Insight - MapInfo

 

 

2009/8/17 Willy Nicolaisen <wiln...@hotmail.com>

[WillyN]

Hi Peter

Thank you very much.

I ended up with the following which worked like a charm:

Dim hdbc As Integer
hdbc=Server_Connect

("ODBC","DSN=TestODBC;UID=WILLYN64;DATABASE=Test;Trusted_Connection=Yes")
Server hdbc Link Table "Select * from ALLELASTER where År='2004'" Into

ALLELASTER2004 File "e:\ALLELASTER2004.tab"

I am retired now (I will be seventy come february) but as the saying
goes "Old love never dies" so I could not resist trying out the
evaluation version of MI 10.0.

This makes me wish that I was still active. I could have made some
very nice applications with this.

Regards,
Willy

On 17 Aug., 17:42, Peter Horsbøll Møller <mapinf...@horsboll-

moller.dk> wrote:
> Hi Willy,
>
> The first time you post on MapInfo-L your e-mail has to be moderated by one
> of the moderators to prevent spam. That's why it took a while before your
> posting showed on the list.
>
> I would recommend that you have a look at the Server Link Table statement.
> This statement is "better" when you want to create a linked table.
>
> You code would look like this:
>
>  Dim hdbc As Integer
> hdbc=Server_Connect("ODBC","DSN=TestODBC;UID=WILLYN64
> ;DATABASE=Test;Trusted_Connection=Yes")
> Server hdbc Link Table
>    "Select * from ALLELASTER where År='2004'"
>    Into ALLELASTER2004"
>    Toolkit "ODBC"
>    File "e:\ALLELASTER2004.tab"
>
> I removed some of the parameters from the Connection String.
> I would also recommend that you prefix the table name with the table
> owner/schema.
>
> If you are looking for the syntax of a live table, you should look at the
> Register Table statement.
> Within this you can use the Type ODBC to connect to a ODBC database.
>
> Peter Horsbøll Møller
> Pitney Bowes Business Insight - MapInfo
>

> 2009/8/17 Willy Nicolaisen <wilnic...@hotmail.com>

>
>
>
> >  Hello all
>
> > Using Open... from the File Menu in MI 10.0 I have created a .TAB file that
> > contains the following statements:
>
> > !table
> > !version 500
> > !charset WindowsLatin1
>
> > Definition Table
> > Type ODBC
> > begin_metadata
> > "\IsReadOnly" = "FALSE"
> > "\DATALINK" = ""
> > "\DATALINK\Query" = "select * from ""dbo"".""TestUTM"""
> > "\DATALINK\ConnectionString" =

> > "DSN=TestODBC;Description=TestODBC;UID=WILLYN64;Trusted_Connection=Yes;APP=­EasyLoader;WSID=WILLYN64-PC;DATABASE=Test"

> > "\DATALINK\ToolKit" = "ODBC"
> > "\CACHE" = "ON"
> > "\MBRSEARCH" = "ON"
> > end_metadata
>
> > The resulting table contains all the rows Where År=2004 with the
> > geographical objects from the server table.
>
> > Can I implement the above in a MapBasic 10.0 program?
>
> > The following statements from a MapBasic 10.0 program returns the same rows
> > but without the geographical opbjects:
>
> > Dim hdbc, hstmt, tid, i As Integer
> > hdbc=Server_Connect("ODBC","DSN=TestODBC;Description=ALLELASTER på SQL
> > Server;UID=WILLYN64;DATABASE=Test;Trusted_Connection=Yes;APP=EasyLoader")
> > hstmt=Server_Execute(hdbc,"Select * from ALLELASTER where År='2004'")
> > Server hstmt Fetch Into "ALLELASTER2004" File "e:\ALLELASTER2004.tab"
> > Server hdbc Disconnect
>
> > I expected, perhaps naively, that the server functions in MapBasic would
> > have been updated to work like the statements in the above .tab file but,
> > apparently, this is not so?
>
> > Regards

> > Willy- Skjul tekst i anførselstegn -
>
> - Vis tekst i anførselstegn -

[WillyN]

And I forgot to mention that it is blindingly fast. In less than a
second it returns more than 9600 records out of a total of more than
169000.

To me, the main reason to use a server database was to get around the
table size limitations of a MI table.

Willy

> > - Vis tekst i anførselstegn -- Skjul tekst i anførselstegn -