New post

#SQL

16

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

Question
· Oct 30, 2018
MSSQL Data Warehouse

Hello,

I have been trying to pull data through a linked server in SSMS from an InterSystems Cache Database for a while, this is to enable us to join the data to other source systems in our Data Warehouse.

I have set up an ODBC connection and a linked server to the database and can execute queries through OPENQUERY in Management studio, but the data is huge (> 100million rows). So when I execute a SQL query with a WHERE clause the query just spins.

0 2
0 797
Question
· Oct 30, 2018
ID vs %ID in tables

What is the difference between %ID and ID in a database table? Both seem to reference the same column labelled ID.

For context, I am trying to create a viewer class for an existing persistent class.

Let us call the persistent class A, with SqlTableName = OldA.

The viewer class will be B with SqlTableName = A and ViewQuery = {select %ID, <other fields> from <some other class with the same fields as A>}

0 1
0 1.1K
Question
· Oct 17, 2018
SQLGateWay performance.

I have a ZEN page with nine tablepanes. Each tablepane queries a table in the same SQLServer db. I have a single SQLGateWay(odbc) to this SQLServer db. I need to get better performance when I query all nine table at the same time. Would my performance improve if I had nine SQLGateWays(nine odbc configurations/connections), one for each query? I would appreciate any and all suggestions for getting the very best performance when using SQLGateWays. Thank you.

0 1
0 261

I have created a view to stage some data in a different format and then want to reference that view in a SQL query from a table that filters the data from the view using a property of the table.

Example:

select
MsgId,
FileName,
(select
ReportName
from
custom_view
where
MsgId = ReportId
) as ReportName
from
main_table

Is this even possible? When I try this, I get an error table not found for the view?

David

0 1
0 268
Question
· Oct 11, 2018
Double Quotes

Hi,

I am trying to have the output of a field in sql have double quotes around them. The script is in Studio and I am accessing It through a task.

I have tried """"tect"""" and others...Please help

Aloha

Lou

0 4
0 1K

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
Question
· Oct 3, 2018
CreateProperty with curl

Hello,

I have a question about creating properties with curl.

I already did create properties in Java with the following command.

<DO db.%CreateProperty("TotalSteps","%Integer","$.TotalSteps")>

It created the property TotalSteps with the type %Integer and the data path $.TotalSteps (since the header of my data source is also TotalSteps).

Now I would like to create the same property in curl with the following command

0 2
0 436

Hi everyone,

I need submit a object have a json string as property of class, but I get an issue, object can't save to SQL table if I take json data to json property. I try making a test class with only json property and get the same issue, no data row in SQL table after run ClassMethod . Some one know what's problem, please help me. Here my test class:

1 2
0 736
Question
· Oct 2, 2018
%NOLOCK in insert query

I would like to know , if its safe to use %NOLOCK in insert query while doing some parallel processing in Ensemble. We will have pool size of more than 1 for a business process. The business process does an entry in internal meta data table , which will be used for some internal reporting. The same row might get updated by some other process later point of time. i could not produce any error or any issues . Looking forward to hear some opinion on this.

1 1
0 1.1K

Hi all,

We have a big problem because we have the following message when I try to open the messages in a production.

ERROR <Ens>ErrException: <DATABASE>zfindStatement+29^%SQL.DynamicStatement.1 ^%sqlcq("MYNAMESPACE","Query",8,"Gns8AZO5dJclytqv13l9gUuLUyo=",""),c:\intersystems\healthshare\mgr\cache\ -- logged as '-' number - @''

SOURCE ELEMENT: %ZEN.Component.tablePane (resultsTable)

1 4
0 465

Is there a way to lock at the record level? I know that you can lock at the table level:

&sql(UNLOCK mytest IN EXCLUSIVE MODE)

but am unable to find a way to lock just at the record/row level.

Would this also affect a SELECT statement done on the record?

0 1
0 368

We have a new requirement being push down by our Data Security to no longer use Local SQL Accounts to access our Databases. So they asked me to create a Service Account that is on the Domain for our connections to each database.

I tried just changing my JDBC connection to using this Service Account and Password but I am not having any luck trying to connect to the database.

" Connection failed.
Login failed for user 'osumc\CPD.Intr.Service'. ClientConnectionId:ade97239-c1c8-4ed1-8230-d274edb2e731 "

1 4
0 3.7K

Hello there,

I have some problem with inserting date to table using dynamic sql.

I have Country class. This table has relationship with class Continent as parent and child. In addition I have another statistic class where property Country has type of Country class. I get ID such as "1||1" next I execute dynamic sql INSERT INTO Stats(Country) VALUES("_CountryId_"). Then I select Stats table and see that value of Country column is "11") As a result a can't get Country object.

Please advice solution of this problem

Thank you

0 2
0 342
Question
· Jul 20, 2018
Oracle Transaction

Hi,

I have a question related with EnsLib.SQL.OutboundAdapter adapter.

Is there a way to open an oracle transaction and do either commit or rollback depending on the query result?

Is posible to keep open an oracle transaction to execute a lot of queries on different methods on the same business operation and do a commit when the business process finished?

Thanks and Best Regards

0 4
0 328

I have a class that has a property calledTags (like DescriptiveWords, but tags), where multiple tags are possible. I am trying to decide on list of Objects vs. array of Objects.

Based on this post: https://community.intersystems.com/post/querying-list-property-sql, sounds like using an array of Objects is the better way to go. Indeed, I already noticed that it's not possible to have duplicates when using an array of Objects.

0 4
0 813

I am trying to fetch the data from cache database. But i got the error like "CSP application closed the connection before sending a responce".

Below is the query.

SELECT
CallbackComment
FROM SQ.CBPhoneResult_View Where PhoneDateODBC = '2018-04-09'

I have investigated and found that "CallbackComment" contains the special character single quotes " ' " for one result and due to this i got this error.

In this field data is enter by customer. so we cannot restrict them like Do Not use single quotes.

0 30
0 1.5K

I have a serial object:

Class EmbedObj Extends %SerialObject

which is stored as a property of another object

Class ContainerObj Extends %Persistent

Property InnerObj As EmbedObj;

Property Foobar As %String;

Question:

From within the context of an instance of EmbedObj, how can I navigate to the containing instance of "ContainterObj" and find that value of its Foobar property?

0 8
0 628
Question
· Jun 14, 2018
Single Row SQL.Snapshot

I have several stored procedures that when I execute them they will only return a single snapshot. In my BP I have been setting this to a Snapshot variable then looping using a WHILE through the snapshot variable just to get that single value.

Since it is only a single row, is there an easier way where I don't have to do a WHILE loop to pull the values out of that row? Can I call First Row or something like that to get me just the row into the Snapshot variable?

0 1
0 415

Hi Everyone,

I am trying to built a SSIS package thru BIDS Visual Studio 2013. My Datasource is a InterSystems Cache Database, I wanted to Import Tables records from the Datasource to MS SQL Server 2014.

As a Sanity check. I only created one Package to import one Table to MSSQL Server to try out. The connection to the InterSystems Cache Database was successful. The DSN for the InterSystems Cache Database is created in the System for 32 and 64 Bit.

0 6
0 1.9K