New post

#SQL

16

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

I've mapped multiple tables (UNION on mapped SQL classes) into a view, using CREATE VIEW.

Through ODBC, in Entity Framework, I am querying against that view and offering paging. The paging is implemented using IQueryable.Skip and IQueryable.Take.

Skip seems to have unexpected results, I believe due to incorrect SQL generated by the Entity Framework provider, though perhaps I've done something incorrectly. The generated SQL looks similar to this (with some bits replaced or altered for security reasons), the basic structure is unaltered.

0 11
0 669

Hi All

I'm looking for the a simple-quick-easy solution to monitor a SQL table thought Ensemble.

I have a process that update a DataBase ,a scheduled task that runs every night (Not Ensemble)

In the end it updates a table (replica_status) with a new recored with two fileds: Id, DateTime

I looked around the community but didn't find an answerd case.

I'm thinking on a Task that will run a sql outboud adapter BO that checks that table and send a alert if no new record was created yesterday

is this the right approach or is there's a better solution?

0 5
0 384

I'm running this code in our application, and in the terminal:

>set query="SELECT * FROM PMG_Data_Private.RemitInstance WHERE (VoucherNumber %INLIST ? SIZE ((10)))"

>set tRS = ##class(%ResultSet).%New("%DynamicQuery:SQL")

>set ok=tRS.Prepare(query)

>d $system.Status.DisplayError(ok)

ERROR #5540: SQLCODE: -76 Message: Cardinality mismatch between the SELECT-list and INTO-list
SQLTEXT: SELECT * FROM PMG_Data_Private.RemitInstance WHERE (VoucherNumber %INLIST ? SIZE ((10)))

0 5
0 1.5K

Dear all,

In our application made in Caché object script, when we deploy some classes modified we have the option of calling "Purge queries" feature of Caché.

We only were doing this call when a %Persistent class definition (table) was modified, as we assume that no other changes affect the cached queries and we don't want to purge them by default, as the first run of a query becomes slow.

We had a case where no table definition was changed but a purge queries was the solution.

0 3
0 245

Ok so I am way outside of my comfort zone, and had to build an application using CSP to give users the ability to access SQL configuration tables. These SQL Configuration tables will affect the data that is sent to the downstream system.

I saw in the examples where we were able to import GIF's/IMAGES into the CSP folders to use as a reference in our CSP pages. My question is how do you do that? If I try to import through studio, it tells me the file is invalid.

Just trying to make it a little more user friendly then blocks on a page.

0 1
0 809
Question
· Feb 28, 2020
How to get a Numbers View

In T-SQL, I can create a recursive CTE to create a subquery or a view that will hold a whole lot of numbers. Is there a way to do this in Cache SQL? I'd like the numbers from 0 to 10000 if possible.

Thanks!

0 3
0 282

Hi:

I have an sql outbound adapter. Sometimes we have trouble connecting to the database.

The timeout in develpment is set to 15 seconds in live it is 150 seconds as it is an always connected Buisness operation.

I thought adding E=S or/and X=S would suspend the message. Why does it not?

Is the only way around then adding something to the buisness operation itself/ creating a customised SQL buisness operation rather than EnsLib.SQL.OutboundAdapter?

Thanks

Mark

0 5
0 403

When using "IDENTITY" as my primary key, I can select the last inserted ID with

SELECT LAST_IDENTITY() FROM %TSQL_sys.snf;

Actually this is how Hibernate + Iris' Driver acquires the inserted ID when mapping with

@GeneratedValue(strategy = GenerationType.IDENTITY)

Now, considering that I am using the type "SERIAL" as my primary key instead, how can I get the last inserted ID?

Note that with "SERIAL" I can forcefully insert any value for this ID, from which Iris will continue generating values...

0 1
0 234

Background: We have our own SQL map that predates InterSystems'. A program writes an XML file for each table map class as $system.OBJ.Export would. $system.OBJ.LoadDir loads the XML files into .cls files.

The reason is a long story, but we need to update parameter EXTENTSIZE (only) in existing classes. This does not seem to happen. As a test I used $system.OBJ.Export to make an XML file and edited EXTENTSIZE in the two places it appears in the XML:

0 4
0 418

Hi members,

I have these data :

and need to exploit the first character of cot field (Library CDU classification) like this :

I get this result... it's approximatively what I want... but I need it in just one line !

With mySQL I get it with just GROUP BY rcddate

0 3
0 300

Hey Developers,

See how you can get high-performance relational access using SQL to manage data within your InterSystems products:

⏯ Using SQL with InterSystems IRIS

https://www.youtube.com/embed/7l-YT5y-nJo
[This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]

0 2
0 219

How can we insert into a table using a stored procedure?

Table structure :

CREATE TABLE SampleHospital (
Hospital VARCHAR(50),
Location VARCHAR(50),
UserName VARCHAR(50),
UserFullName VARCHAR(70),
LastLogin TIMESTAMP,
LoginCount int)

Procedure Name:

call Custom_MENS_Other.samplesp('2021-11-02','2021-11-04','H001')

0 2
0 206

I have a table, with autoincremented id

CREATE TABLE users (
    id SERIAL NOT NULL,
    name VARCHAR(30) NOT NULL,
    PRIMARY KEY (id)
)

I can add a new item there with an explicit id 

INSERT INTO users (id, name) VALUES (2, 'fred')

And while my id is autoincremented, I can omit it

INSERT INTO users (name) VALUES ('ed')

So, this time, I don't know the id, and I want to somehow get it.

I could do it with LAST_IDENTITY() function, but it just uses %RowID, and have no relation to the primary id

0 11
0 596

I need to run a SQL query and use the output to map PV1 7.1. The query is :

SELECT ID
FROM TestTable
WHERE ProviderName = 'TEST,PROVIDER' AND IDType= 'BPI'

When I run this query with the 'TEST PROVIDER' I do pull the ID in question but I can't figure out how to do it from the DTL given that there are various providers sent in PV 1 7 . Any assistance will be greatly appreciated.

0 8
0 262

Hi,

The system I am working on processes large numbers of records. Inserts, updates and so forth. There are multiple processes that can potentially work on the same table at the same time.

It is an almost impossible task to try and time these processes to not run at the same time, due to volumes, SLAs, etc.

Every now and then there are a few locking issues, Not many considering the volumes. It is just a bit of a nuisance as we need to reprocess these records later.

0 6
0 152