#ODBC

Introduction

This article is intended to be a simple tutorial on how to create ODBC connections and working with them, since I found starting with them a little bit confused, but I had amazing people to take my hand and walk me through it, and I think everyone deserves that kind of help too.
I'm going to divide each little part in sections, so feel free to jump to the one you feel the need to, although I recommend reading everything.

Using SQL Gateway with Python, Vector Search, and Interoperability in InterSystems Iris

Part 3 – REST and Interoperability

Now that we have finished the configuration of the SQL Gateway and we have been able to access the data from the external database via python, and we have set up our vectorized base, we can perform some queries. For this in this part of the article we will use an application developed with CSP, HTML and Javascript that will access an integration in Iris, which then performs the search for data similarity, sends it to LLM and finally returns the generated SQL. The CSP page calls an API in Iris that receives the data to be used in the query, calling the integration. For more information about REST in the Iris see the documentation available at https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...

How to create an ODBC connection on your native Windows laptop to IRIS running on a Windows VM on the same computer, test the connection, and pull data from IRIS into Excel.

Introduction

There is a Link Procedure Wizard option within the Management Portal (System > SQL >Wizards > Link Procedure) which I had reliability issues with so I decided to use this solution instead.

Hi Community

I have created a simple package that allows the use of Cache with the Laravel Framework.

From my initial testing everything seems to be operating smoothly but I would like to appeal to the PHP users in the community to help me improve this package.

For those of you out there who have time and would be interested in this, please visit the repo at https://github.com/jeandormehl/laracache

Thanks in advance

Using SQL Gateway with Python, Vector Search, and Interoperability in InterSystems Iris

Part 1 - SQL Gateway

Hello

In this article we will look at the use of SQL Gateway in Iris. SQL Gateway allows Iris to have access to tables from other (external) database via ODBC or JDBC. We can access Tables or Views from various databases, such as Oracle, PostgreSQL, SQL Server, MySQL and others.

Using SQL Gateway with Python, Vector Search, and Interoperability in InterSystems Iris

Part 2 – Python and Vector Search

Since we have access to the data from our external table, we can use everything that Iris has to offer with this data. Let's, for example, read the data from our external table and generate a polynomial regression with it.

For more information on using python with Iris, see the documentation available at https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=AFL_epython

Let's now consume the data from the external database to calculate a polynomial regression. To do this, we will use a python code to run a SQL that will read our MySQL table and turn it into a pandas dataframe:

The ideal number of table permissions to assign for your users is zero. Permissions should be granted upon sign-in based on the application used for access. For web applications, we have a simple way of doing this by appointing application roles, matching roles, and required resources in the System Management Portal.

ODBC and JDBC connections present a different problem, however, especially when third-party applications are involved. As providers of an ERP system, our customers often wish to be able to employ various software packages to integrate with or report on their data. Many of these programs are capable of running any kind of query. Yet, letting them do that can be devastating to a customer’s data.

Hi all,

I'm trying to use LOAD DATA to insert 11k (11,377) rows of data. LOAD BULK DATA is not available for the version of IRIS I am using.

After calling LOAD DATA it says only 5,500 rows has been inserted. The LOAD DATA docs says any error rows are skipped and a count of skipped rows can be found in %SQL_Diag.Result however there are no results here. There are no errors in the xDBC error log either.

Why have over half the rows been skipped?

One of our apps uses a class query to support a ZEN Report and works just fine in that report, producing the expected results every time. We’ve since migrated to InterSystems Reports and noticed that, for a report using the same class query, 100s of extra rows with the same column values appear at its bottom.

In my previous articles I demonstrated the steps to connect, retrieve and save data to Caché from Appeon PowerBuilder by using ODBC.
In this article I will demonstrated how to Delete data from Caché with Appeon PowerBuilder (https://www.appeon.com/products/powerbuilder) by using ODBC

I have a MySQL server with "posts" table.

I also have a Caché server with "downloadedposts" table.

They are connected from Caché to MySQL via SQL Gateway

I want to keep Caché table synced with MySQL one (MySQL "posts" table is a master copy), so periodically Caché queries MySQL server and downloads data. So far so good, and if a record appears or changes in MySQL table, Caché downloads the changes.

The problem I'm encountering is that sometimes rows would be deleted from MySQL "posts" table.

How do I synchronize deletions?

Get the quick and easy steps for setting up ODBC/JDBC connections to InterSystems IRIS® data platform and InterSystems IRIS for Health™:

• Setting Up JDBC Connections
• Setting Up ODBC Connections

Now in an easy-to-use, checklist format to help you work through the process on your own system.

Enjoy!

Hello Community,

Thank you all for your continued feedback and support of our ad hoc reporting platform, VDM. There's been some questions around setting up a non-ODBC connection for InterSystems platforms. We published a new YouTube video showing the steps necessary to connect to InterSystems Caché and InterSystems IRIS with BridgeWorks VDM.