Working with MSSQL

[barrymavin]

It is simple to work with third party databases in Lianja.

Apart from Virtual Tables you can perform queries very quickly with minimum coding.

Take a look at this screenshot which demonstrates how to perform SQL queries quickly against MSSQL.



Notice how you can specify the CONNSTR (connection string) which will connect to MSSQL and perform a SELECT * FROM the specified table name selecting the rows that match the WHERE condition.

We can also specify the SQL SELECT statement we want to execute. See below.



Now that we have the MSSQL data in a local cursor we can slice it up and output JSON to send back to a web/mobile client.



Clearly we can query MySQL data and PostgreSQL data also and fetch the data into a local cursor.

At this point we can then perform a join of MSSQL and MySQL and PostgreSQL if that is required.

Enjoy.

[phabio]

excellent example.
Thanks Barry

Fabio

[CharlieDenver]

Hello

I have created a DSN in windows called TestADM and have a successful connection to an MS SQL database on a remote server
If I open the console and type my DSN in the Connstring textbox of the input form and click the green tick I get -> ODBC connection to TestADM successful
if I type DIR I was expecting to see a list of tables

Please can you assist with why I don't see that

Many Thanks

[josipradnik]

You should get list of tables with DIR

Also

use yourtable
browse

[barrymavin]

Connecting to the server using a username and password will only grant you rights for that user. If no tables are listed using DIR then you have no access. You can troubleshoot by issuing a SQL SELECT and seeing the error message and if that does not throw any light on the problem SET DEBUG ON in the lianja/Vfp console tab then issue DIR in the console tab, exit Lianja then attach the debug_client file from the Lianja\debug directory to a ticket and we will have a look at it and advise.

[CharlieDenver]

Hi Barry

I followed the trouble shooting through and this is where I am at

My ODBC connection is successfull, I have cteated vt and they appear as access denied
I connected with the ODBC (input) tab as instructed
I issued a select * from table
it returns the data OK

But my vt tables are still showing as access denied in the data section






Any help much appreciated

Phillip

[barrymavin]

Your connection is succeeding.
DIR in the ODBC tab is working.
SET DEBUG ON in the Lianja/VFP tab.
go to data workspace and double click your database
exit lianja
look in c:\lianja\debug directory and let me see the debug_client files. If confidential submit as a ticket. .

[barrymavin]

You have not authenticated. Unless your username / password is in the DSN.

[CharlieDenver]

Thanks I've submitted a ticket

[CharlieDenver]

I believe it is, I've used the same DSN in VFP with no issues