Push Images from hand held device

Question

Push Images from hand held device

Nisha-475382

Hall of Fame

Points: 3535
More actions
February 27, 2008 at 9:16 pm

#75858

I have a table for Work Order Images, into which the HandHeld developer does an RDA.Push of his WO_Images table.
New image records get inserted into SQL table; image records existing in both mobile and SQL table would get updated. (Also, mobile table obviously will not have all images from SQL table.)
Problem is, when a new image is captured on the mobile device, the image_surr_key (primary key) generated for the record would be unique for mobile table, but it may already exist in SQL table.
And, the same key may also be generated on a different mobile device at any time.
On site, when there are going to be multiple syncs, again, the key is bound to repeat in SQL when pushed.
I was wondering whether I could fire a trigger on the table after insert in order to make the keys unique.
Right now, the constraints on the Image_Surr_Key+Created_date fields.
What if I get rid of that and simply have a trigger?
Even if I do have a trigger, how should the update be performed?
This is what my SQL table looks like:
CREATE TABLE WORK_ORDER_IMAGES
(
Image_Surr_Key int NOT NULL,
WO_Hdr_Surr_Key numeric(18, 0) NULL,
Latitude decimal(18, 0) NULL,
Longitude decimal(18, 0) NULL,
Request_Surr_Key numeric(18, 0) NULL,
New_Ad_Surr_Key numeric(18, 0) NULL,
HH_ID numeric(18, 0) NULL
Captured_Image image NULL,
CreatedDatetime datetime NOT NULL,
)
This might be the data present in it at any time:
INSERT INTO tbl_WORK_ORDER_IMAGES
([Image_Surr_Key]
,[WO_Hdr_Surr_Key]
,[CreatedDatetime]
,[Request_Surr_Key]
,[New_Ad_Surr_Key]
,[HH_ID])
SELECT 11
,12
,'02/01/2008'
,146
,NULL
,NULL UNION ALL
SELECT 1
,700
,'02/01/2008'
,146
,NULL
,NULL
FROM ANOTHER DEVICE NOW:
INSERT INTO tbl_WORK_ORDER_IMAGES
([Image_Surr_Key]
,[WO_Hdr_Surr_Key]
,[CreatedDatetime]
,[Request_Surr_Key]
,[New_Ad_Surr_Key]
,[HH_ID])
SELECT 11 --REPEATING
,701
,'02/03/2008'
,146
,NULL
,NULL
I don't know how better to explain this guys, any suggestion would be appreciated.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

can youc hange your primary key? instead of using some integer value, use the combination of HandheldId + Image_Surr_Key; that way Image_Surr_Key can repeat, but the combination of handheld makes it unique instead of just the one value..

that

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 2

That would be like Device ID + Image_Surr_Key?

That's not going to work either.

Lets take an example:

Today

Device1 pushed 5 images with Surr_Keys 1,2,3,4,5

Device2 pushed 3 images with Surr_Keys 1,2,3.

Fine, no problem, SQL table has 5+3 records and Device ID + Image_Surr_Key would be unique.

However,

Tomorrow Device1 pulls data from SQL, none of the 8 images are required on his device as they probably are images of completed work orders now. Device1 completes a work order and takes 2 images.

Device1 pushes 2 images with Surr_Keys 1,2 (AGAIN)

What happens in SQL? Device1 with Surr_Keys 1,2 repeat!!!!

Could someone tell me if this trigger looks fine....?

CREATE TRIGGER Update_WORK_ORDER_IMAGES

ON tbl_WORK_ORDER_IMAGES

AFTER INSERT

AS

BEGIN

SET NOCOUNT ON;

IF EXISTS(SELECT * FROM INSERTED)

BEGIN

CREATE TABLE #tbl_Row_Num

(

Row_Num INT IDENTITY (1,1),

Image_Surr_Key INT,

WO_Hdr_Surr_Key INT,

Request_Surr_Key INT,

New_Ad_Surr_Key INT,

HH_ID INT,

CreatedDatetime DATETIME

)

IF @@ERROR<>0 GOTO HANDLER

INSERT INTO #tbl_Row_Num

SELECTImage_Surr_Key,

WO_Hdr_Surr_Key,

Request_Surr_Key,

New_Ad_Surr_Key,

HH_ID,

CreatedDatetime

FROM tbl_WORK_ORDER_IMAGES ORDER BY CreatedDatetime ASC

IF @@ERROR<>0 GOTO HANDLER

--TEST

SELECT * FROM #tbl_Row_Num

UPDATE tbl_WORK_ORDER_IMAGES

SET Image_Surr_Key=T.Row_Num

FROM #tbl_Row_Num T

WHERE tbl_WORK_ORDER_IMAGES.Image_Surr_Key=T.Image_Surr_Key

AND ISNULL(tbl_WORK_ORDER_IMAGES.WO_Hdr_Surr_Key,0)=ISNULL(T.WO_Hdr_Surr_Key,0)

AND ISNULL(tbl_WORK_ORDER_IMAGES.Request_Surr_Key,0)=ISNULL(T.Request_Surr_Key,0)

AND ISNULL(tbl_WORK_ORDER_IMAGES.New_Ad_Surr_Key,0)=ISNULL(T.New_Ad_Surr_Key,0)

AND ISNULL(tbl_WORK_ORDER_IMAGES.HH_ID,0)=ISNULL(T.HH_ID,0)

AND tbl_WORK_ORDER_IMAGES.CreatedDatetime=T.CreatedDatetime

AND tbl_WORK_ORDER_IMAGES.Image_Surr_Key<>T.Row_Num

--TEST

DECLARE @Cnt INT

SET @Cnt=@@ROWCOUNT

PRINT @Cnt

IF @@ERROR<>0 GOTO HANDLER

END

RETURN

HANDLER:

RAISERROR('Error inserting into tbl_WORK_ORDER_IMAGES',16,1)

END