SQL

Syndicate content 29 

I have a query string that I am creating programmatically, based on some user inputs.  The user might search on 5 fields, or 8 fields, or no fields.

In my sql statment, some of these fields require parameters in the %Execute statement.

 

For example:

if user picks lastname, sql = "select * from person where lastname = ?"

if user also picks age, sql = "select * from person where lastname=? and age > ?"

 

I then have these lines of code to create my result set:

set statement = %SQL.Statement

statement.%Prepare

Last answer 18 July 2017 Last comment 20 July 2017
0   0 3
7754

views

0

rating

Date range queries going too slow for you?  SQL Performance got you down?  I have one weird trick that might just help you out! (SQL Developers hate this!)*

If you have a class that records timestamps when the data is added, then that data will be in sequence with your IDKEY values - that is, TimeStamp< TimeStampif and only if ID1 < IDfor all IDs and TimeStamp values in table - then you can use this knowledge to increase performance for queries against TimeStamp ranges.  Consider the following table:

Last comment 6 September 2019
+ 16   1 10
6578

views

+ 16

rating

Class Queries in InterSystems Caché is a useful tool that separates SQL queries from Caché Object Script code. Basically, it works like this: suppose that you want to use the same SQL query with different arguments in several different places.In this case you can avoid code duplication by declaring the query body as a class query and then calling this query by name. This approach is also convenient for custom queries, in which the task of obtaining the next row is defined by a developer. Sounds interesting? Then read on!

Last comment 9 December 2016
+ 14   0 9
4064

views

+ 14

rating

The Caché System Management Portal includes a robust web-based SQL query tool, but for some applications it’s more convenient to use a dedicated SQL client installed on a user’s PC.

SQuirreL SQL is a well known open source SQL client built in Java, which uses JDBC to connect to a DBMS. As such, we can configure SQuirreL to connect to Caché using the Caché JDBC driver.

Last comment 13 July 2016
+ 7   0 9
3428

views

+ 7

rating

Hello,

I'm working on integrating a Cache database into an existing .NET project of mine.  I'm trying to execute certain queries utilizing the Cache ODBC driver.  The first error I ran into was "[S1000] [Cache ODBC][State : S1000][Native Code 417] Access Denied".  The part that's throwing me for a loop though is that the application ran ~200 successful queries before I started getting that.  After some of those Access Denied errors, they turned into "[Cache ODBC][State : S1T00][Native Code 450] Request timed out due to user timeout".  This was all with the regular (2.5?) driver.

Last answer 21 November 2017 Last comment 22 November 2017
0   0 4
2586

views

0

rating

I have been using the query below and it was working fine but now it's giving a SQL error. There were no changes made that could cause this to stop working. There is no Field 'APPLICATIONID' in the table.

QueueSQL=select distinct (convert(char(5),SkillsetID)+'='+Skillset) from iagentbySkillsetStat where ApplicationID > 10000
QueueMappingSQL=SELECT DISTINCT (convert(char(5),SkillsetID)+'='+Skillset), SkillsetID FROM iagentbySkillsetStat iagentbySkillsetStat WHERE (iagentbySkillsetStat.ApplicationID>10000)

DB- Intersystems Cache

Last answer 16 September 2016 Last comment 16 September 2016
0   0 1
2495

views

0

rating

As we all know, Caché is a great database that accomplishes lots of tasks within itself. However, what do you do when you need to access an external database? One way is to use the Caché SQL Gateway via JDBC. In this article, my goal is to answer the following questions to help you familiarize yourself with the technology and debug some common problems.

Last comment 28 December 2018
+ 8   2 4
2046

views

+ 8

rating

Introduction

The field test of Caché 2016.2 has been available for quite some time and I would like to focus on one of the substantial features that is new in this version: the document data model. This model is a natural addition to the multiple ways we support for handling data including Objects, Tables and Multidimensional arrays. It makes the platform more flexible and suitable for even more use cases.

Last comment 15 June 2016
+ 13   0 11
1945

views

+ 13

rating

 Error:

    [SQLCODE: <-400>:<Fatal error occurred>]
    [Cache Error: <<SUBSCRIPT>%0AmEdun+4^%sqlcq.HSREGISTRY.cls966.1 ^||%sql.temp(1,"")>]
    [Location: <ServerLoop - Query Fetch>]
    [%msg: <Unexpected error occurred:  <SUBSCRIPT>%0AmEdun+4^%sqlcq.HSREGISTRY.cls966.1 ^||%sql.temp(1,"")>]

 

Here is an example of a query that gives the error:

Last comment 30 November 2016
0   0 4
0

answers

1875

views

0

rating

Hi All -

I was wondering if the below query could be enhanced to automatically look back 24 hours from the current datetime the query is run. As is now I, of course, have to update the date range in the WHERE clause manually.

The query is just getting all MessageBodyClassNames, counting them and then doing an AVG on TimeCreated and TimeProcessed. Nothing too complex.

 

SELECT MessageBodyClassName, count(ID) as Count_Of_Messages, avg(datediff(ss, TimeCreated, TimeProcessed)) as avg_processing_time_in_seconds

FROM %PARALLEL Ens.MessageHeader

Last answer 20 April 2017
0   0 2
0

comments

1692

views

0

rating

Order is a necessity for everyone, but not everyone understands it in the same way
(Fausto Cercignani)

Disclaimer: This article uses Russian language and Cyrillic alphabet as examples, but is relevant for anyone who uses Caché in a non-English locale.
Please note that this article refers mostly to NLS collations, which are different than SQL collations. SQL collations (such as SQLUPPER, SQLSTRING, EXACT which means no collation, TRUNCATE, etc.) are actual functions that are explicitly applied to some values, and whose results are sometimes explicitly stored in the global subscripts. When stored in subscripts, these values would naturally follow the NLS collation in effect (“SQL and NLS Collations”).

+ 9   0 0
1673

views

+ 9

rating

Caché Monitor is a database\sql tool primarily for InterSystems Caché but can also connect to MS SQL Server, MS Access and more databases. Within Caché Monitors Server Navigator you see all available Namespaces on your Caché Servers. No need to know the name of the Namespace, no need to configure many many JDBC Connections by hand. Just click on the namespace and see all objects like tables, views, classes and more...

Last comment 18 February 2016
+ 3   0 1
1637

views

+ 3

rating

The newer dynamic SQL classes (%SQL.Statement and %StatementResult) perform better than %ResultSet, but I did not adopt them for some time because I had learned how to use %ResultSet. Finally, I made a cheat sheet, which I find useful when writing new code or rewriting old code. I thought other people might find it useful.

First, here is a somewhat more verbose adaptation of my cheat sheet:

Last comment 15 August 2019
+ 7   2 13
1436

views

+ 7

rating

Have some free text fields in your application that you wish you could search efficiently?  Tried using some methods before but found out that they just cannot match the performance needs of your customers?  Do I have one weird trick that will solve all your problems?  Don’t you already know!?  All I do is bring great solutions to your performance pitfalls!

As usual, if you want the TL;DR (too long; didn’t read) version, skip to the end.  Just know you are hurting my feelings.

Last comment 10 May 2019
+ 20   1 3
1347

views

+ 20

rating

The object and relational data models of the Caché database support three types of indexes, which are standard, bitmap, and bitslice. In addition to these three native types, developers can declare their own custom types of indexes and use them in any classes since version 2013.1. For example, iFind text indexes use that mechanism.

Last comment 29 January 2016
+ 10   0 8
1311

views

+ 10

rating

Hi,

My doubt is about the SQL Query in Caché:

I don't want to take all results from the table and orgainze them manually, for example: I have a table with 50 records, but I only want to select 10 records, being from tenth until the twentieth and this without knowing their IDs.

So, how can I do this, without losing performance, there is any way?

 

Last answer 24 February 2017 Last comment 25 January 2018
+ 1   0 2
1238

views

+ 1

rating

The Art of Mapping Globals to Classes  (2 of 3)

 

If you are looking to breathe new life into an old MUMPS application follow these steps to map your globals to classes and expose all that beautiful data to Objects and SQL.

 

This example is going to cram in 4 or 5 different things beyond what was covered in Part 1 

Last comment 23 May 2017
+ 10   0 6
1232

views

+ 10

rating

Hello,

We are sometimes asked for examples of how to do 3rd party SQL database selects/updates/inserts/stored procedures from an Ensemble SQL Business Operation.

In the attached file there are 4 sample Ensemble Operations that demonstrate four different types of transactions with an external SQL DB:

Last comment 8 June 2016
+ 5   0 4
1107

views

+ 5

rating