New post

#SQL

13

SQL is a standard language for storing, manipulating and retrieving data in relational databases.

In the previous article, we saw in detail about Connectors, that let user upload their file and get it converted into embeddings and store it to IRIS DB. In this article, we'll explore different retrieval options that IRIS AI Studio offers - Semantic Search, Chat, Recommender and Similarity.

Open Exchange app
4 0
1 263

Hi Community,

In this article, I will introduce my application iris-VectorLab along with step by step guide to performing vector operations.

IRIS-VectorLab is a web application that demonstrates the functionality of Vector Search with the help of embedded python. It leverages the functionality of the Python framework SentenceTransformers for state-of-the-art sentence embeddings.

Application Features

  • Text to Embeddings Translation.
  • VECTOR-typed Data Insertion.
  • View Vector Data
  • Perform Vector Search by using VECTOR_DOT_PRODUCT and VECTOR_COSINE functions.
  • Demonstrate the difference between normal and vector search
  • HuggingFace Text generation with the help of GPT2 LLM (Large Language Model) model and Hugging Face pipeline

Open Exchange app
2 0
0 397
Question
· Feb 1, 2023
NEED NUMBER ROWS IN SQL SELECT

Hi guys,

I'm trying find a way to create a row with the number of each row.

For example:

In the SQL above, i want a way to create a collum with the number of corresponding row, in this case:

1

2

3

In SQl SERVER i can use FOR, RANK or ROM_NUMBER to do this, but, i can't find a way to do the same in cache SQL.

Can someone help me?

Thank you.

0 4
0 441
Article
· Jan 22, 2024 2m read
Getting JSON from SQL

Did you know that you can get JSON data directly from your SQL tables?

Let me introduce you to 2 useful SQL functions that are used to retrieve JSON data from SQL queries - JSON_ARRAY and JSON_OBJECT.
You can use those functions in the SELECT statement with other types of select items, and they can be specified in other locations where an SQL function can be used, such as in a WHERE clause

The JSON_ARRAY function takes a comma-separated list of expressions and returns a JSON array containing those values.

18 11
4 873
Article
· Apr 26, 2024 3m read
Geo Vector Search #2

Technical surprises using VECTORs
>>> UPDATED

Building my tech. example provided me with a bunch of findings htt I want to share.
The first vectors I touched appeared with text analysis and more than 200 dimensions.
I have to confess that I feel well with Einstein's 4 dimensional world.
7 to 15 dimensions populating the String Theory are somewhat across the border.
But 200 and more is definitely far beyond my mathematical horizon.

Open Exchange app
3 4
0 268

Hello Community,

I'm executing the same query with same column name but in different case. An unique cached query generated while query executed first time. The query preparser only normalize the keywords and send to the SQL engine generates the Hash. Eventually use the cached query next use.

Now my question, The hash values are same for both of the queries. Then why it creates two cached queries.

Query1: select * from MyLearn.Test where Name['Kev1'

Query2: select * from MyLearn.Test where NamE['Kev1'

0 0
0 110

The InterSystems IRIS has a series of facilitators to capture, persist, interoperate, and generate analytical information from data in XML format. This article will demonstrate how to do the following:

  1. Capture XML (via a file in our example);
  2. Process the data captured in interoperability;
  3. Persist XML in persistent entities/tables;
  4. Create analytical views for the captured XML data.

Capture XML data

The InterSystems IRIS has many built-in adapters to capture data, including the next ones:

Open Exchange app
4 0
1 305

Hi Developers!

Here're the technology bonuses for the InterSystems Vector Search, GenAI, and ML contest 2024 that will give you extra points in the voting:

  • Vector Search usage - 5
  • IntegratedML usage - 3
  • Embedded Python - 3
  • LLM AI or LangChain usage: Chat GPT, Bard, and others - 3
  • Questionnaire - 2
  • Docker container usage - 2
  • ZPM Package deployment - 2
  • Online Demo - 2
  • Implement InterSystems Community Idea - 4
  • Find a bug in Vector Search, or Integrated ML, or Embedded Python - 2
  • First Article on Developer Community - 2
  • Second Article On DC - 1
  • First Time Contribution - 3
  • Video on YouTube - 3
  • Suggest a new idea - 1

See the details below.<--break->

3 0
0 310

Hi everyone.

I have a function that may end up being called from a number of transformations at the same time, and within the function there's some Embedded SQL to first check if a local table has an entry, and then adds the entry if it doesn't exist.

To prevent a race condition where the function is called by two transformations and they both end up attempting to insert the same value, I'm looking to use the table hint "WITH TABLOCK" on the insert, but this seems to be failing the syntax checks within vscode.

Are table hints supported with embedded sql?

0 4
0 129
Article
· Apr 8, 2024 1m read
using Procedure Parameters with ODBC/JDBC

Hi,

I was struggling with a procedure that was meant to receive a string and use it as a filter, I've found that since I want the procedure to do some data transformation and return a dataset, I needed to use objectScript language.

I've created the procedure using the SQL GUI in the portal, and everything works fine when calling the procedure from the SQL GUI but not through a JDBC connection here is the call "call spPatientOS('2024-04-07T12:35:32Z')"

3 2
0 249

Hey everyone,

I'm currently running into a very weird issue to where I am trying to connect with a 64 bit version of SQL Server Management Studio (SSMS) to a HealthShare instance. I have created a System DSN using the Drivers (image below) that were downloaded with the Client version of the install and I'm able to successfully connect using my credentials.

0 2
0 418

It is probably somewhere in the doc. So hard to search. Hope InterSystems going to AI on their community/support data.

How to see a meaningful value in management portal - sql explorer. For instance
Class MySetting Extends %Persistent{
Property Name As %String;

Class MyObj Extends %Persistent{
Property Setting As MySetting;

0 1
0 117

I am currently adding a field to our Existing messaging from Epic, however there might be a possibility I need to back load data into the Ancillary system. While I have the previous messages that can be sent, they do not have this additional field that I am adding to the message.

I can do a lookup against Epic Clarity SQL Database; however, I don't want to throw a wrench into the workflow if the system cannot connect to the Epic Clarity SQL Database.

1 3
0 208

For example, I have two timestamp values ('2024-04-01 10:00:00', '2024-04-01 11:30:30'). I would like to find the difference between these two timestamps, and I need the result in hours:minutes:seconds (hh:mm:ss) format.

Expected Output: 01:30:30


Note: I need an SQL query command. I should not use ClassMethod, Function, or Stored Procedure.


Could anyone please provide me with an SQL query for my question?

0 4
0 250

I know that you can use Do $SYSTEM.SQL.Schema.ImportDDL() to insert sql files into IRIS however I was wondering if there is a way that I can upload .sqlite files into iris? I have about 20 .sqlite files that I need to get into my database. I tried using the ImportDDL method but it said "SKIPPING non-SQL SOURCE:"

0 6
0 150

What is Unstructured Data?
Unstructured data refers to information lacking a predefined data model or organization. In contrast to structured data found in databases with clear structures (e.g., tables and fields), unstructured data lacks a fixed schema. This type of data includes text, images, videos, audio files, social media posts, emails, and more.

8 3
0 387

Using VECTOR_COSINE() in SQL query to perform a text similarity search on existing embeddings in a %VECTOR column.

Code is below.

Commented out sql query returns this error: SQLCODE: -29 Field 'NEW_EMBEDDING_STR' not found in the applicable tables^ SELECT TOP ? maxID , activity , outcome FROMMain .AITest ORDER BY VECTOR_COSINE ( new_embedding_str ,

Sql query as written returns ERROR #5002: ObjectScript error: <PYTHON EXCEPTION> *<class 'OSError'>: isc_stdout_write: PyArg_ParseTuple failed!

0 10
0 232

What I find really useful about IRIS when teaching my subject of Postrelational databases is the fact that it is a multi model database. Which means that I can actually go into architecture and structure and all that only once but then show the usage of different models (like object, document, hierarchy) using the same language and approach. And it is not a huge leap to go from an object oriented programming language (like C#, Java etc) to an object oriented database.

However, along with advantages (which are many) come some drawbacks when we switch from object oriented model to relational. When I say that you can get access to the same data using different models I need to also explain how it is possible to work with lists and arrays from object model in relational table. With arrays it is very simple - by default they are represented as separate tables and that's the end of it. With lists - it's harder because by default it's a string. But one still wants to do something about it without damaging the structure and making this list unreadable in the object model.

So in this article I will showcase a couple of predicates and a function that are useful when working with lists, and not just as fields.

9 1
0 218 
When I use Escape logic when inserting or updating Oracle Table I'm getting Max-Length exceeded error. With the original value the length is good but after I add Escape Logic, it causes value to be greater than max-length. The original value was "I visited O'Brien before heading out of town." and after added Escape logic it was "I visited O''Brien before heading out of town." Max-Length is 45.

INSERT INTO MyText
    (text)
VALUES
    ('I visited O''Brien before heading out of town.')
                 /\
             right here

Any ideas around this?

0 5
0 242