Using JSON functions with Always Encrypted data in SSMS - Database Discussion Boards

Re: update temp table with results from sub query (actually it is a function)

joost.versteegen7-Feb-19 4:45

7-Feb-19 4:45

thanks. i tried it, but i get the error 'Cannot call methods on table'.
too bad. my original looks so inefficient, but it will have to do i guess.

Re: update temp table with results from sub query (actually it is a function)

Richard Deeming7-Feb-19 8:34

Richard Deeming

7-Feb-19 8:34

How about:

SQL

UPDATE T
SET formula_name = r.name, forumula_version = r.verion
FROM @TEMP As T
CROSS APPLY dbo.fn_GetFormulaForJob(T.wo_id, T.oper_id, T.seq_no) As r;

Using APPLY | FROM (Transact-SQL) - SQL Server | Microsoft Docs[^]
SQL Server CROSS APPLY and OUTER APPLY[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: update temp table with results from sub query (actually it is a function)

joost.versteegen7-Feb-19 11:20

joost.versteegen

7-Feb-19 11:20

wow, ok, i will try it first thing in the morning. thanks.

Re: update temp table with results from sub query (actually it is a function)

joost.versteegen7-Feb-19 20:33

joost.versteegen

7-Feb-19 20:33

this works! Thanks.

modified 8-Feb-19 2:41am.

Tables left and right

Member 141362635-Feb-19 5:21

Member 14136263

5-Feb-19 5:21

Hi!
Yesterday i used your app sqlite compare to compare 2 databases.
My problem is that there is two tables with the same data, but the program says that one is on the left side and the other one is on the right side. Any idea ?

Re: Tables left and right

Richard MacCutchan5-Feb-19 6:10

Richard MacCutchan

5-Feb-19 6:10

Member 14136263 wrote:
i used your app sqlite compare

Assuming you are talking about some CodeProject article, please post your question in the forum below the article. Only then is the author likely to see your message.

Re: Tables left and right

ZurdoDev7-Feb-19 4:41

ZurdoDev

7-Feb-19 4:41

Member 14136263 wrote:
Any idea ?

No.

1. This site does not have a sqlite compare product. There might be an article that one of the 14 million members wrote.
2. One on the left and one on the right. Yes, because that's how you compare side to side.

So, this is the wrong place but even still your question does not make any sense.

Everyone is born right handed. Only the strongest overcome it.

Fight for left-handed rights and hand equality.

Using JSON functions with Always Encrypted data in SSMS

Hypermommy4-Feb-19 6:06

Hypermommy

4-Feb-19 6:06

I have an Azure SQL database that uses the Always Encrypted functionality. In this database I have a column called entryObject. It is an nvarchar field containing JSON data. It is encrypted using a Randomized encryption type.

I am currently writing SQL that should us the JSON_MODIFY function to update this data as well as unencrypted data in another table. I would like to have all of this functionality wrapped in a transaction and in a stored procedure. This way, my C# code can call this one stored proc and, if successful, I'll know that all data was updated successfully. However, any time I try to use the JSON_MODIFY function or even JSON_QUERY with my encrypted entryObject column's data, I get an error stating

Argument data type nvarchar(max) encrypted with (encryption_type = 'RANDOMIZED', ...  is invalid for argument 1 of json_query function.

How can I use JSON_MODIFY to change one of the values in this encrypted JSON?

Thanks in advance for any assistance you can give.

Denise

Re: Using JSON functions with Always Encrypted data in SSMS

Richard Deeming4-Feb-19 9:20

Richard Deeming

4-Feb-19 9:20

Always Encrypted (Database Engine) - SQL Server | Microsoft Docs[^]:
Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server).
...
Decryption occurs via the client. This means that some actions that occur only server-side will not work when using Always Encrypted.

SQL Server doesn't know how to decrypt your data. It can't read the value stored in your column, so it can't issue a JSON query against it, let alone modify the value.

You'll need to load the data into your client application, make the changes there, and then update the database value.

You can do that within a transaction, using TransactionScope or BeginTransaction. But you can't do it from a stored procedure.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Using JSON functions with Always Encrypted data in SSMS

Hypermommy6-Feb-19 2:38

Hypermommy

6-Feb-19 2:38

Hi there,

Thanks for the reply. I was hoping to keep it all in SQL as that's where I was more comfortable with the ability to rollback a transaction in case of failure. I will definitely look into transaction processing in C# and thank you for pointing me in the right direction.

Denise

Denise

database

Member 141284154-Feb-19 6:03

Member 14128415

4-Feb-19 6:03

how to store and retrieve amharic word from sql server

Re: database

Jörgen Andersson4-Feb-19 8:41

Jörgen Andersson

4-Feb-19 8:41

Amharic is supported by Unicode, so if your column type is nvarchar and you use unicode all the way (string in .Net is Unicode) there should be no problem.

Wrong is evil and must be defeated. - Jeff Ello

Having difficulty to replicate entries in SQL query result

paul4everyone28-Jan-19 2:37

paul4everyone

28-Jan-19 2:37

Good day all!
Please assist if you can in resolving this my SQL query difficulty.
I have 2 query results as follows;

Table A
CID | NAME | CREDENTIAL | BAL | GID1 | GID2 | UNIQUE_ID
01 | AAA | debtor | 20 | 03 | 02 | 01-03,02
02 | BBB | debtor | 15 | 01 | 05 | 02-01,05
03 | CCC | debtor | 10 | 02 | 04 | 03-02,04

TABLE B
CID | NAME | BAL
01 | AAA | 20
02 | BBB | 15
03 | CCC | 10
04 | DDD | 5
05 | EEE | 2
06 | FFF | 4

Now I need help in getting TABLE C that should look like this;

CID | NAME | CREDENTIAL | BAL | UNIQUE_ID
01 | AAA | debtor | 20 | 01-03,02
03 | CCC | guarantor | 10 | 01-03,02
02 | BBB | guarantor | 15 | 01-03,02

02 | BBB | debtor | 15 | 02-01,05
01 | AAA | guarantor | 20 | 02-01,05
05 | EEE | guarantor | 2 | 02-01,05

03 | CCC | debtor | 10 | 03-02,04
02 | BBB | guarantor | 15 | 03-02,04
04 | DDD | guarantor | 5 | 03-02,04

WHAT I INTEND TO ACHIEVE
This is like a loan collection summary.
CID = General Primary Key for every customer
NAME = Customer names
CREDENTIAL = To help me diffentiate between the loan collector and the guarantors, so that I can group in my front end VB application.
BAL = everyone account balance
GID = guarantor for the loan collector, GID is a subset of CID
UNIQUE_ID = A string to combine the CID and the GID for a loan transaction so that I can easily isolate everything about a single loan transaction.

Table A is an abridged form of each loan collection transaction.
Table B carries the account balance for every customer.
Now, I expect Table C to carry all loan record in Table A and use the entries in GID1 and GID2 columns to fetch their corresponding details from Table B while retaining the Unique_ID entries from Table A so that sorting by Unique_ID will bring all loans together.

Please assist me with a way to achieve this, I have been thinking for days, yet no breakthrough.
Thanks in advance

modified 28-Jan-19 21:07pm.

Re: Having difficulty to replicate entries in SQL query result

Richard MacCutchan28-Jan-19 2:56

Richard MacCutchan

28-Jan-19 2:56

Take a look here: sql join - Google Search[^].

Re: Having difficulty to replicate entries in SQL query result

paul4everyone28-Jan-19 4:20

paul4everyone

28-Jan-19 4:20

Thanks Richard for your response. Indeed, I have taken a look at the link suggestion. The 2 result sets I have above was achieved using 'Joins'. But achieving 'Table C' is the challenge as I currently can't figure a way out of it.

Re: Having difficulty to replicate entries in SQL query result

ZurdoDev28-Jan-19 4:48

ZurdoDev

28-Jan-19 4:48

It would help is you would explain how to get Table C. It is not obvious to me just by looking at the data.

Everyone is born right handed. Only the strongest overcome it.

Fight for left-handed rights and hand equality.

Re: Having difficulty to replicate entries in SQL query result

paul4everyone28-Jan-19 6:54

paul4everyone

28-Jan-19 6:54

Thanks
I have edited my original post to carry a better explanation on what I want.

Re: Having difficulty to replicate entries in SQL query result

Richard MacCutchan28-Jan-19 6:01

Richard MacCutchan

28-Jan-19 6:01

Given that we have no information about your source database tables it is not something that we can do for you.

Re: Having difficulty to replicate entries in SQL query result

paul4everyone28-Jan-19 6:51

paul4everyone

28-Jan-19 6:51

Thanks,
I have edited my original post to add an explanation about the entire process I intend to achieve.

Table A and Table B is a result of SQL query combining multiple database tables. So I intend to retrieve Table C from the result of Table A and Table B

Re: Having difficulty to replicate entries in SQL query result

Mycroft Holmes28-Jan-19 11:02

Mycroft Holmes

28-Jan-19 11:02

What is not obvious is whether Table A has entries for "Gaurantor" and if there are a max of 2 records for each CID in Table A

Assuming Table B has a single entry for each CID
Select *
from TableB
inner join TableB B and TableA A on A.CID = B.CID

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

Re: Having difficulty to replicate entries in SQL query result

paul4everyone28-Jan-19 15:02

paul4everyone

28-Jan-19 15:02

CID in Table A is always single entry as a customer can't have two running loans as same time and in Table B CID is still single entry as is the account balance of all customers. But Table A.CID is always a subset of Table B.CID.
"guarantor" entries under CREDENTIAL column in Table C is just a string input ( like select 'guarantor' as CREDENTIAL) or (isnull(CREDENTIAL, 'guarantor')) to make me identify that entry as details of a particular guarantor while UNIQUE_ID entries in Table C helps groups individual loan together as both the loan collector and guarantors will have same entries.
CID in Table C can have multiple same values but all entries must be drawn from the primary column Table B.CID

Re: Having difficulty to replicate entries in SQL query result

Mycroft Holmes29-Jan-19 10:58

Mycroft Holmes

29-Jan-19 10:58

Ok that is very straight forward, pick your primary table A or B as they have a single value for the CID. Use inner joins from A to B and A to C and select the fields you need to display.

If there are missing records in your secondary tables then use left outer joins.

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

Re: Having difficulty to replicate entries in SQL query result

paul4everyone31-Jan-19 10:50

paul4everyone

31-Jan-19 10:50

I tried all I could, but still failed to see anyway to directly generate Table C from Table A and B.
I have to generate a new Table D (which looks like Table C - Table A) from my based database tables. Using Table A Union Table D now gives me Table C.

Thanks a lot for your support.

Re: Having difficulty to replicate entries in SQL query result

Mycroft Holmes31-Jan-19 11:15

Mycroft Holmes

31-Jan-19 11:15

My bad, I thought table C existed with CID and Credential.

Where do you expect to get the credential for Guarantor from it is not in table A or B in your examples.

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

Re: Having difficulty to replicate entries in SQL query result

paul4everyone2-Feb-19 19:03

paul4everyone

2-Feb-19 19:03

"guarantor" entries under CREDENTIAL column in Table C is just a string input ( like select 'guarantor' as CREDENTIAL) or (isnull(CREDENTIAL, 'guarantor')) to make me identify that entry as details of a particular guarantor.

Since I have gotten a work around for this particular challenge, how do I mark my question as 'Closed' or 'Answered'?

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.