SQL is a standard language for storing, manipulating and retrieving data in relational databases.
I have a simple SQL service that does a simple select from an SQL database. After the select, I do an update to set the ProcessedFlag to "Y" for yes, so my next pass doesn't select records already processed. The service works fine, except when it's done I get the error below, anybody know what is causing this error?
I worked through the Community for proposals to provide end users
in an easy way with data formatted as EXCEL sheet.
There is a great article Tips & Tricks - SQL to Excel
there's an important message embedded: "EXCEL can interpret HTML tables and display them as usual"
Where's the light weight export to EXCEL ?
Good old CSP is well equipped to produce HTML tables accepted from EXCEL as input.
With modern Browsers you don't even need <head>and <body> tags.
So the required code around your SQL result set is really slim.
And you are free to add any formatting you need either by HTML or in SQL.
In $system.SQL.Shell(), it would also report the number of global references for a given query.
Is it possible that I could also get this statistics from the %SQL.Statement interface when I launch a dynamic sql?
Thanks.
I use %SQL.Statement. The original column names are not contained in the metadata.
Sample query:
SELECT column1->name as name, column2 age FROM sample.table
How to get:
I am currently working on a issue with WRC on one of my Inbound SQL Adapters not returning all the records it should be. If I looked at the count of the records in Ensemble and compare it to that of a Microsoft SQL View, Ensemble seems to be off by a few records here and there. I am using a full dynamic select statement in my settings of the adapter.
In a previous exercise, I was able to show the power of Caché.
A medium-designed set of interdependent tables with some GB of data.
URLs cross reference over some million pages resulting in ~3 billion records
Competition was between
Criteria were Speed + Storage consumption
I composed a customized loader fed over a "raw" TCP connection
Mapping the "objects" into the final table by directly writing to Global Storage.
Is it possible to import a Cube from SQLServer (format .abf) in a Cache?
I have an .abf file and I want to open it in Cache. Is it possible?
Thanks
hi
I execute sql like this: select * from DHC_PatBillDetails where PBD_PBO_ParRef>='2046121'
error message:
but the sql: select * from DHC_PatBillDetails where PBD_PBO_ParRef='2046121' can be executed successfully
why?
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
resultset = statement.%Execute(param1, param2)
-- but it might be
resultset = statement.
Hi,
Is it possible to use the value of a column that is populated by its own subquery, in the WHERE clause of the outer-query ?
The following fails (it does not parse syntactically):
SELECT A, B, (SELECT S1 FROM Table2) "C" FROM Table1 WHERE C>10
I guess I could wrap it up as in inner query of it's own - this way (which works) :
Select * from (SELECT A, B, (SELECT S1 FROM Table2) "C" FROM Table1) where C>10
but I was wondering if there was some syntax in the original snippet that I could do instead.
Thanks - Steve
Hello! So, my knowledge on the Cache database is extremely limited, and I was hoping I could find some assistance here. I'm connecting to the DB via ODBC. The table(s) I'm interested in are named as such nameYYYYMMDD. So each day, a new table is created with logs. We'd like to grab these records each day, for the previous day's logs.
My question is, since the table name changes every day, how can I go about automating this? Can I craft a variable that is the table name plus some date functions and use that?
Hi everyone
Is there any way to change a class definition (especifically a query definition during the compilation time)?
The idea is:
I have an abstract class with a parameter where I will define the ROWSPEC of a query and some methods to populate e temporary table
The implementation class will override the parameter, specifying the ROWSPEC of this implementation, and the methods will populate the rows in the same format as the ROWSPEC.
I want to change the ROWSPEC of the inherit query according to the implementation of the overriden parameter.
I have a query text that I need to execute and return results as xml.
It may be an SQL query or an MDX query. Is there some easy way to determine which one is it?
Any ideas?
Hi!
I have a global-mapped class that I want to add a transient property through (or at least, not stored on the database). This property is the sum total of various charge classes that are linked to the class via child relationships.
I want this new transient property to be visible at least via SQL.
My current approach is to total it up in a ClassMethod called via SqlComputeCode. It seems to work, but feels clunky. I wonder if there is a way without passing the %%ID through? I tried it with $this but that only returns the object name in a ClassMethod.
Greetings,
I am working on the first of many triggers which will have identical code upon row insertion or update of a single column. According to the document I should be able to define a multiple-event trigger using Cache SQL/DDL.
Here is a link to the current CREATE TRIGGER documentation. Within the description section is the following paragraph:
A single-event trigger is triggered by a specified INSERT, DELETE, or UPDATE operation. A multiple-event trigger is defined to execute when any one of the specified events occurs on the specified table.
Quotes (1NF/2NF/3NF)ru:
Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else). The same value can be atomic or non-atomic depending on the purpose of this value. For example, “4286” can be
- atomic, if its denotes “a credit card’s PIN code” (if it’s broken down or reshuffled, it is of no use any longer)
- non-atomic, if it’s just a “sequence of numbers” (the value still makes sense if broken down into several parts or reshuffled)
This article explores the standard methods of increasing the performance of SQL queries involving the following types of fields: string, date, simple list (in the $LB format), "list of <...>" and "array of <...>".
I have a stored procedure like below:
Call USER.SP('select * from Sample.Person where SSN='aaaaa'','0','S')How could I escape the single quotes for aaaaa? I try double single quotes but in vain.
Thanks you.
"SELECT %SYSTEM.SQL_TableExists('table name') "could work as expected under SQL shell,
but for "CALL %SYSTEM.SQL_TableExists('table name')" does not work (not any error reported, it just show nothing).
Is there any reason why CALL could not be applied to a stored procedure?
Thanks.
Sometimes, it comes in very handy (especially for the EAV model) to use array properties in a class and be able to qickly search by their elements: both the key and the value.
Let’s take a look at a simple example:
I would like to start a discussion regarding Caché Objects and Caché SQL.
It is my understanding that the creators of Caché Objects see Caché SQL as the reporting arm of Objects and as such SQL is essential to Caché Objects.
I once met a Caché Objects programmer who was writing code to $Order through the Globals because that person thought that Caché SQL was too slow and inefficient. I attempted to convince the person otherwise.
So, what say you? Is SQL essential to Caché Objects?
Or
Is Caché SQL a nice to have but we can do without? Is it too slow? Is it too inefficient?
Hi There
New to the cache sql commands looking for how to create a parameter within a sql statement like with MSSQL is @ and Oracle is :
What is cache's?
I try to find a function, which generates hash values of columns. In MS SQL Server I can use
select hashbytes('sha2_256', my_column) ...
to create hash values of my_column. Is it possible to use such things in Caché?
Thank you
André
Hi all,
I have a non objectscript application connecting to a cache instance via ODBC and one column is a list of serial objects. The output from the query contains a lot of special characters and I'm hoping there's a better way to get this data back so I won't have to perform extensive parsing on the application side.
I've tried using the $ListToString() function, but that didn't help much, probably because the list contains complex objects rather than primitives.
Any help will be greatly appreciated!
Hello,
I have a question related to running an SQL query for range of data.
I am running a query like:
Select A,B from table_name where A>=12345 AND A<=12390
- Where A and B are my two of the properties under the mentioned table/class definition
- A is an integer property
Question: The result of the above query does not return me all the values between the mentioned range of integer though I do have all the values in that range. I see that some of the values are missing in result.
Any reason why this query won't work?
Any ideas?
Thanks,
Aditi
In addition to its general security, Caché offers SQL security with a granularity of a single row. This is called row-level security. With row-level security, each row holds a list of authorized viewers, which can be either users or roles. By default access is determined at object modification Some time ago I became interested in determining row-level security at runtime. Here's how to implement it.
Whenever I try to run a simple query on a production database table, I get a timeout 504 error. I'm using the SQL interface in Ensemble. One of the queries is a simple SELECT TOP 10. Even SELECT COUNT(*) gives a timeout.
I have tried to run the SQL Runtime Statistics, but this gives me a timeout as well.
Does anyone have an idea where I might look to find what is causing this?
Thanks in advance,
Best regards
Glenn van Bavel,
This is a translation of the following article. Thanks [@Evgeny Shvarov] for the help in translation.
Someone posted a question on DC asking whether it was possible to determine access rights for a particular table row always at runtime, and if it was, how could one do that?
Answer: it is possible and it’s not hard at all.
How to simply going to get the value of the system language from ^%z? Because I got a problem, U2 context isn't setup while connecting the SQL Connect.
I'm breaking my head trying to figure how to solve this issue.
When using SQL, If the column (property) is populated with at least one value and is a %List, then I can check if it's $listvalid and $listlength(column) > 0.
Now let's suppose there's no data inserted for that column on next row.
I can't simply use $listvalid because $listvalid("") = 1. Yeah, empty values is a $list for Caché.
So my question is, how do I discover if the regarding column is indeed a %List independently of being empty or not when retrieving it's value via SQL?
Is the InterSystems Cache has SQL or Non-SQL concept?