Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Working with MSSQL

  1. #1
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,235

    Working with MSSQL

    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.

    Name:  Screen Shot 2017-02-03 at 11.04.04 AM.jpg
Views: 765
Size:  91.5 KB

    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.

    Name:  Screen Shot 2017-02-03 at 11.14.02 AM.jpg
Views: 723
Size:  67.2 KB

    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.

    Name:  Screen Shot 2017-02-03 at 12.46.06 PM.jpg
Views: 729
Size:  77.2 KB

    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.
    Last edited by barrymavin; 2017-02-02 at 23:49.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  2. #2
    Senior Member
    Join Date
    Feb 2012
    Location
    Rome - Italy
    Posts
    1,877
    excellent example.
    Thanks Barry

    Fabio

  3. #3
    Member
    Join Date
    Apr 2019
    Location
    UK
    Posts
    44
    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

  4. #4
    Lianja MVP
    Join Date
    Dec 2012
    Location
    Croatia, Zagreb
    Posts
    1,121
    You should get list of tables with DIR

    Also
    Code:
    use yourtable
    browse
    Last edited by josipradnik; 2019-04-09 at 11:37.

  5. #5
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,235
    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.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  6. #6
    Member
    Join Date
    Apr 2019
    Location
    UK
    Posts
    44
    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


    Name:  lj5.jpg
Views: 134
Size:  51.3 KB

    Name:  lj6.png
Views: 126
Size:  18.5 KB

    Any help much appreciated

    Phillip
    Last edited by CharlieDenver; 2019-04-11 at 04:51. Reason: Added New Image

  7. #7
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,235
    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. .
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  8. #8
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,235
    You have not authenticated. Unless your username / password is in the DSN.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  9. #9
    Member
    Join Date
    Apr 2019
    Location
    UK
    Posts
    44
    Thanks I've submitted a ticket

  10. #10
    Member
    Join Date
    Apr 2019
    Location
    UK
    Posts
    44
    I believe it is, I've used the same DSN in VFP with no issues

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us