hello community,
We dont have any username or password for our ms sql company database, we use windows native authentication which uses Active Directory Domain to authenticate the user. How can i connect our microsoft SQL database ? I am unable to connect as i have nothing to write in database username and password. Please have a look on the picture below and let me know please how can i connect my sql database with thingworxs.....
Thanks for your suggestions. I would like to give you in depth implementation information of my current store procedure. I send the request with a complete new entry with a serial number / timeStamp and it get saved in the SQL table. Then i go through steps of it from front end so for example i have machine and i need to take picture on every step and send it to SQL database and my store procedure sees the if the entry exists with the serial number and timestamp it just stores subentries in the jsonData- NVARCHAR(MAX) column field in the SQL.
So, we store main entry and then in one of its field jsonData we save its sub entries that are 7 or 10 in JSON...
Now do you have any other idea to store the latest 100 entries ? other wise i have to copy this procedure exactly same and just add the condition to delete the other entries except the latest 100. is it a good approach what do you think? it means i have two similar procedures for two sql tables and then in one of them i have a condition to maintain only latest 100 entries..
or do you have any other recommendation..
In case you want to read my existing store procedure which is as below
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
-- SEGMENT 1: We start the store procedure for storing QualityData by initializing the column names we have in our table.
--
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
CREATE PROCEDURE InsertOrUpdateStatus
@QualityCheckTimestamp Datetime2,
@SerialNumber VARCHAR(255),
@Status BIT,
@Affix VARCHAR(255),
@LineLocation VARCHAR(255),
@LineName VARCHAR(255),
@UserIdentification VARCHAR(255),
@JsonData NVARCHAR(MAX),
@StepNumber INT,
@ProductCategory VARCHAR(255),
@Comment VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON; -- I have used this because i dont want any updates from the SQL server to boost the PERFORMANCE for our system so that we dont get the message that how many rows are affected.
--SET @QualityCheckTimestamp = CONVERT(datetime2, SWITCHOFFSET(@QualityCheckTimestamp, '+02:00'));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
-- SEGMENT 2: We have declared these four variables here which we would use in store procedure so with IF NOT EXISTS i see that if entry with this serialNumber,lineName and QualityCheckTimestamp
--exists? If exists the we will NOT add it again and if the entry with this key does not exists then we will add the entry into our database.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
DECLARE @CurrentJson NVARCHAR(MAX),
@ExistingStatus BIT,
@Index INT,
@AnyStatusFalse BIT = 0
IF NOT EXISTS (
SELECT 1 FROM ML1QualityDataTable
WHERE QualityCheckTimestamp = @QualityCheckTimestamp
AND SerialNumber = @SerialNumber
AND LineName = @LineName
)
BEGIN
INSERT INTO ML1QualityDataTable
(SerialNumber, Status, Affix, LineLocation, LineName, UserIdentification, QualityCheckTimestamp, JsonData, ProductCategory)
VALUES
(@SerialNumber, @Status, @Affix, @LineLocation, @LineName, @UserIdentification, @QualityCheckTimestamp, JSON_QUERY('[' + @JsonData + ']'), @ProductCategory)
END
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
-- SEGMENT 2: FINISHED.
--
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------>
-- SEGMENT 3: Now we will see if the entry with that details exists then we will only edit its QualityStepData which is in database called jsonData. So I will just update the json object in that
-- jsonData column . I will update the status of the jsonData Object to true or false according to my wish. This requirement is implemented on the mashup. when i dont send any jsonData from backend
-- then it means that i am sending only the stepNumber to update its status. So this segment i can change the status of any object with the stepNumber i am sending from the backend.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------>
ELSE
BEGIN
SELECT
@CurrentJson = jsonData,
@ExistingStatus = status
FROM ML1QualityDataTable
WHERE
QualityCheckTimestamp = @QualityCheckTimestamp
AND SerialNumber = @SerialNumber
AND LineName = @LineName
IF @StepNumber IS NOT NULL
BEGIN
IF @JsonData IS NULL OR LTRIM(RTRIM(@JsonData)) = '' -- We have checked here that jsonData is not null and with LTRIM AND RTRIM i have removed the empty spaces so that we have jsonData.
BEGIN -- I have here the jsonData null that means i will just update the status of the jsonData object which i have feature on the mashup.
SELECT @CurrentJson = jsonData
FROM ML1QualityDataTable
WHERE QualityCheckTimestamp = @QualityCheckTimestamp
AND SerialNumber = @SerialNumber
AND LineName = @LineName
IF ISJSON(@CurrentJson) = 1 -- ISJSON means if the input string is a right json format then it returns 1 otherwise if not then it returns 0.
BEGIN
SELECT @Index = [key] -- We will grab the key of json Object which has the stepNumber which i have sent from thingworx.
FROM OPENJSON(@CurrentJson) -- OPENJSON converts the currentJson which is a jsonData into the rows format so that we can easily iterate on it
WHERE JSON_VALUE(value, '$.stepNumber') = CAST(@StepNumber AS NVARCHAR(255)) -- JSON_VALUE means we get value of the stepNumber from jsonData and iterate on it untill we get the equal stepNumber which i have sent from backend. CAST is used to set the type of stepNumber.
IF @Index IS NOT NULL -- So if i get the index which is a key of the object i wanted so that i can manipulate the status of that object
BEGIN
SET @CurrentJson = JSON_MODIFY( -- JSON MODIFY updates the json and returns the updated results
@CurrentJson, --json data string we have
'$[' + CAST(@Index AS NVARCHAR(255)) + '].status', --path of the object where we need to update the status
@Status -- the value which needs to be updated now
)
SET @CurrentJson = JSON_MODIFY(
@CurrentJson, --json data string we have
'$[' + CAST(@Index AS NVARCHAR(255)) + '].comment', --path of the object where we need to update the status
@Comment -- the value which needs to be updated now
)
UPDATE ML1QualityDataTable
SET jsonData = @CurrentJson -- here we are setting the new jsonData Object which we have manipulated in that main entry.
WHERE QualityCheckTimestamp = @QualityCheckTimestamp
AND SerialNumber = @SerialNumber
AND LineName = @LineName
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
-- SEGMENT 3: ENDS
--
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
--SEGMENT 4: This part of the code is also for the mashup purposes which checks when we change the status of subentry from mashup and if we turn all the false entries into true then it will change
-- the main entry to true also and vice versa.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
SET @AnyStatusFalse = CASE
WHEN EXISTS (
SELECT 1 FROM OPENJSON(@CurrentJson)
WHERE CAST(JSON_VALUE(value, '$.status') AS BIT) = 0
)
THEN 1
ELSE 0
END
UPDATE ML1QualityDataTable
SET Status = IIF(@AnyStatusFalse = 1, 0, 1)
WHERE QualityCheckTimestamp = @QualityCheckTimestamp
AND SerialNumber = @SerialNumber
AND LineName = @LineName
END
ELSE
BEGIN
PRINT 'No JSON object with the provided step number found.'
END
END
ELSE
BEGIN
PRINT 'Current JSON data is not valid JSON.'
END
END
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
-- SEGMENT 4: FINISHED
--
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
-- SEGMENT 5: This part of the code is only to insert the new objects in the jsonData if i send the entry with the same primarykeys with additional jsonData object then i will just add that
-- json Object into it.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
ELSE
BEGIN
SELECT @Index = [key]
FROM OPENJSON(@CurrentJson)
WHERE JSON_VALUE(value, '$.stepNumber') = CAST(@StepNumber AS NVARCHAR(255))
IF @Index IS NOT NULL
BEGIN
SET @CurrentJson = JSON_MODIFY(@CurrentJson, '$[' + CAST(@Index AS NVARCHAR(255)) + ']', JSON_QUERY(@JsonData))
END
ELSE
BEGIN
SET @CurrentJson = JSON_MODIFY(@CurrentJson, 'append $', JSON_QUERY(@JsonData))
END
UPDATE ML1QualityDataTable
SET jsonData = @CurrentJson
WHERE
QualityCheckTimestamp = @QualityCheckTimestamp
AND SerialNumber = @SerialNumber
AND LineName = @LineName
END
END
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------>
-- SEGMENT 5: FINISHED
--
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------>
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------>
-- SEGMENT 6: This will just update the general status of the main entry that means if any single of the status of the sub entry will be false then main entry would also be false otherwise
--it will stay true.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------>
-- Check if there is a need to update the main status based on the JSON content
IF @JsonData IS NOT NULL AND LTRIM(RTRIM(@JsonData)) <> ''
BEGIN
DECLARE @ShouldUpdateStatus BIT;
SELECT @ShouldUpdateStatus = CASE
WHEN EXISTS (
SELECT 1 FROM OPENJSON(JSON_QUERY('[' + @JsonData + ']'))
WHERE CAST(JSON_VALUE(value, '$.status') AS BIT) = 0
) THEN 1
ELSE 0
END;
IF @ShouldUpdateStatus = 1
BEGIN
UPDATE ML1QualityDataTable
SET status = 0
WHERE
QualityCheckTimestamp = @QualityCheckTimestamp
AND SerialNumber = @SerialNumber
AND LineName = @LineName
END
-- No ELSE part to automatically update status to true
END
END
END
working in less then 1 second same as on SQL management studio
not working
working but too slow like after 30 seconds
Thank you so much!! :it works as expected i just add ' ' apostrophe around it. but would be good information for future about symbols around [[ ]].
SELECT TOP 100 *
FROM dbo.ML1QualityDataTable
WHERE lineName = <<lineName>>
AND qualityCheckTimestamp >= DATEADD(DAY, -1, GETDATE())
ORDER BY qualityCheckTimestamp DESC;
i gave lineName like this as below:
