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.

1405

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.

1406

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.

1407

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


1888

1889

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

[barrymavin]

Well VFP does not have virtual tables so perhaps you have the uid and psd somewhere in your code such as the named connection.

[barrymavin]

As I mentioned in your ticket and for the benefit of others.

You need to put the uid and password in the connstr.

This is not the only way to do it but you will allow you to proceed past this stage by doing that.

A VT is the equivalent in VFP terms as combining:

- a connection
- a view
- a cursoradaptor

But looks like a normal table so can be used in desktop/web/mobile apps without any messy coding required.

They can be paramerized and can be Requeried
They handle fetch on demand of individual rows or pages or rows from any database type
They handle syntax translation
They handle conflict resolution
Etc...

[yvonne.milne]

Slightly old screenshot, but the entry fields still apply:

Modify Virtual Table with uid/pwd (https://www.lianja.com/doc/images/7/71/Vt2_modifyvt_uidpwd.png)

From:

Virtual Tables - Further Configuration and Troubleshooting (https://www.lianja.com/doc/index.php/Virtual_Tables_-_Further_Configuration_and_Troubleshooting#Virtual _table_marked_as_.28encrypted.29_or_.28Access_Deni ed.29_.2F_File_does_not_exist)


Regards,

Yvonne

[barrymavin]

Normally the simplest solution to get started is to put the credentials in the odbc DSN itself. Once you have everything working you can revisit this.

[CharlieDenver]

Thanks very much both
I assumed that when the virtual tables are created from the menu - As I had to enter the UID & Password - it would somehow propagate that detail to the vt
I understand now that each virtual table has to be manually set up with the UID & PWD after they have been created using that method
So I'm guessing I can issue a line of code that cteates a vt with the UID/PWD, I'll look at that
I get it now, thanks for clearing this up

[CharlieDenver]

I'll revisit the DSN, that seems to be the issue, Local MS-SQL works fine, I only get the issue for remote MS-SQL
I can see why now so I'll leave you in peace - thanks

[hmischel@diligentsystems.com]

Not sure if this is helpful, but I have created several procs that I use when connecting to MSSQL, which I do for a good portion of my applications.

Here is an example of one.

This is a proc called getremotefulltable.


param tablename, cursorname


gnConnHandle =sqlconnect("matchbackODBC","servicename","servicepwd")
lianja.writelog(str(gnConnHandle))
strw1 =' select * from '+tablename+' '
=SQLPREPARE(gnConnHandle,strw1,'cresult')
=SQLEXEC(gnConnHandle)
macro = 'select * from cresult into cursor '+cursorname+' readwrite'
&macro
use in cresult
=sqldisconnect(gnConnHandle)

I use it like this.

getremotefulltable('exceptions','c_exceptions')

So then in Lianja, I have a cursor called c_exceptions which exists after the connection string is closed.

I the same thing for filtered tables where I also pass in a where condition.

Just my 2 cents.

Herb