Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.lang.clipper.visual-objects > #5486 > unrolled thread

Debug options Vo2Ado when using SQL Express?

Started byRob van Erk <erk.v@hotmail.com>
First post2013-07-10 13:16 -0700
Last post2013-07-11 12:25 -0700
Articles 8 — 4 participants

Back to article view | Back to comp.lang.clipper.visual-objects


Contents

  Debug options Vo2Ado when using SQL Express? Rob van Erk <erk.v@hotmail.com> - 2013-07-10 13:16 -0700
    Re: Debug options Vo2Ado when using SQL Express? Willie Moore <williem@wmconsulting.com> - 2013-07-11 11:30 +0000
    Re: Debug options Vo2Ado when using SQL Express? Johan Nel <johan.nel@xsinet.co.za> - 2013-07-11 05:01 -0700
    Re: Debug options Vo2Ado when using SQL Express? "Er!k \\/!$$Er" <nospam@nospam.url> - 2013-07-11 14:04 +0200
    Re: Debug options Vo2Ado when using SQL Express? Johan Nel <johan.nel@xsinet.co.za> - 2013-07-11 05:20 -0700
    Re: Debug options Vo2Ado when using SQL Express? Rob van Erk <erk.v@hotmail.com> - 2013-07-11 09:58 -0700
      Re: Debug options Vo2Ado when using SQL Express? "Er!k \\/!$$Er" <nospam@nospam.url> - 2013-07-11 20:09 +0200
    Re: Debug options Vo2Ado when using SQL Express? Rob van Erk <erk.v@hotmail.com> - 2013-07-11 12:25 -0700

#5486 — Debug options Vo2Ado when using SQL Express?

FromRob van Erk <erk.v@hotmail.com>
Date2013-07-10 13:16 -0700
SubjectDebug options Vo2Ado when using SQL Express?
Message-ID<5c53e6e7-c12c-4ac7-a8d9-4696c30a06a9@googlegroups.com>
I am struggling to find the root cause of my Vo2Ado code not doing what I expect it should do using a SQL Express DB. My main question is : what are the debug options I have in SQL Express (like the debug options VO is offering in VO2.8) to find the root cause of my issue? For example, the below is just executed but dos not seem to work at all.

oCmd := AdoCommand{}
oCmd:ActiveConnection := oConn
oCmd:CommandText := "SP_InsertRec" 
oCmd:CommandType := AdCmdStoredProc

oPars := oCmd:PARAMETERS_
oPar  := oCmd:CreateParameter("sDevice", adChar,adParamInput,nil,sDevice)
oPars:Append(oPar)
oPar  := oCmd:CreateParameter("sPCName", adChar,adParamInput,nil,sPCname)
oPars:Append(oPar)

oRecordset := oCmd:Execute(nil,nil,nil)

***************************************
*** Stored procedure in SQL Express ***
***************************************
PROCEDURE [dbo].[SP_InsertRec]
@sDevice CHAR(30),
@sPCName CHAR(30)

AS
BEGIN
DECLARE @sKey    CHAR(40)
DECLARE @sUser   CHAR(40)
DECLARE @sDate   CHAR(40)
DECLARE @nROWS   INT

SET @skey  = CURRENT_USER + GETDATE()
SET @sUser = CURRENT_USER
SET @sDate = GETDATE()
SET @nROWS = 0

BEGIN TRANSACTION
    SELECT @nROWS = count(username) FROM DBTIMEREG WHERE UNIQUEKEY = @sKEY
--    IF @nROWS is null     
//    Apparently the above statement is not allowed in MS Stored Procedure
       BEGIN
          INSERT INTO [DBTIMEREG] (username,logindate,device,pcname,uniquekey)
          VALUES(@sUser,@sDate,@sDevice,@sPcname,@sKey) 
       END
--    END
COMMIT TRANSACTION
RETURN;
END 

[toc] | [next] | [standalone]


#5488

FromWillie Moore <williem@wmconsulting.com>
Date2013-07-11 11:30 +0000
Message-ID<1983114842395234868.914946williem-wmconsulting.com@news.aioe.org>
In reply to#5486
Rob,

Have you tried executing your stores procedure from the SQLMagement studio?
When I approach a SQL issue, I always try to make sure that my stored procs
execute from the studio before I ever execute them from my program. 

Regards,
Willie
Vulcan VIP

[toc] | [prev] | [next] | [standalone]


#5489

FromJohan Nel <johan.nel@xsinet.co.za>
Date2013-07-11 05:01 -0700
Message-ID<86b89428-2acf-487c-b5db-6791798a3eeb@googlegroups.com>
In reply to#5486
Hi Rob,
> 
>     SELECT @nROWS = count(username) FROM DBTIMEREG WHERE UNIQUEKEY = @sKEY
> 
> --    IF @nROWS is null     
> 

Not sure if this might be the problem, since I come from a PostgreSQL environment, but in PG you will define the SELECT statement as:

 SELECT count(username) FROM DBTIMEREG WHERE UNIQUEKEY = @sKEY INTO @nRows

HTH,

Johan Nel
Vulcan VIP
Durban, South Africa.

[toc] | [prev] | [next] | [standalone]


#5490

From"Er!k \\/!$$Er" <nospam@nospam.url>
Date2013-07-11 14:04 +0200
Message-ID<krm70m$1247$1@adenine.netfront.net>
In reply to#5486
Rob,

Google for TSQL DEBUG STORED PROCEDURE
This is not a (VO2)ADO problem.
In fact, under what circumstances do you expect count(..) to return a null 
value?

If you execute this :  SELECT count(0) WHERE  0=1 you get 0 and not null

hth

Erik


"Rob van Erk"  schreef in bericht 
news:5c53e6e7-c12c-4ac7-a8d9-4696c30a06a9@googlegroups.com...

I am struggling to find the root cause of my Vo2Ado code not doing what I 
expect it should do using a SQL Express DB. My main question is : what are 
the debug options I have in SQL Express (like the debug options VO is 
offering in VO2.8) to find the root cause of my issue? For example, the 
below is just executed but dos not seem to work at all.

oCmd := AdoCommand{}
oCmd:ActiveConnection := oConn
oCmd:CommandText := "SP_InsertRec"
oCmd:CommandType := AdCmdStoredProc

oPars := oCmd:PARAMETERS_
oPar  := oCmd:CreateParameter("sDevice", adChar,adParamInput,nil,sDevice)
oPars:Append(oPar)
oPar  := oCmd:CreateParameter("sPCName", adChar,adParamInput,nil,sPCname)
oPars:Append(oPar)

oRecordset := oCmd:Execute(nil,nil,nil)

***************************************
*** Stored procedure in SQL Express ***
***************************************
PROCEDURE [dbo].[SP_InsertRec]
@sDevice CHAR(30),
@sPCName CHAR(30)

AS
BEGIN
DECLARE @sKey    CHAR(40)
DECLARE @sUser   CHAR(40)
DECLARE @sDate   CHAR(40)
DECLARE @nROWS   INT

SET @skey  = CURRENT_USER + GETDATE()
SET @sUser = CURRENT_USER
SET @sDate = GETDATE()
SET @nROWS = 0

BEGIN TRANSACTION
    SELECT @nROWS = count(username) FROM DBTIMEREG WHERE UNIQUEKEY = @sKEY
--    IF @nROWS is null
//    Apparently the above statement is not allowed in MS Stored Procedure
       BEGIN
          INSERT INTO [DBTIMEREG] 
(username,logindate,device,pcname,uniquekey)
          VALUES(@sUser,@sDate,@sDevice,@sPcname,@sKey)
       END
--    END
COMMIT TRANSACTION
RETURN;
END 


--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

[toc] | [prev] | [next] | [standalone]


#5491

FromJohan Nel <johan.nel@xsinet.co.za>
Date2013-07-11 05:20 -0700
Message-ID<664779b5-8cb1-4f07-8ad7-ea459dc1d2dc@googlegroups.com>
In reply to#5486
Hi Rob,

Sorry ignor my previous post.

If there are no records @nRows will contain 0.

So update your if statment to:

  IF @nRows = 0

There is no way that your select statement will return a null in the count(*).

HTH,

Johan Nel
Vulcan VIP
Durban, South Africa.

[toc] | [prev] | [next] | [standalone]


#5494

FromRob van Erk <erk.v@hotmail.com>
Date2013-07-11 09:58 -0700
Message-ID<c0a58b21-cd0a-4253-9aef-eb080bc0d216@googlegroups.com>
In reply to#5486
Thanks for the input.

Willie,
What I did is run an "Execute" in SQL Express. This returns a "command completed successfully". So, content of SP seems ok. Not sure if I can run / test the Stored Procedure using paramaters in MS Management Studio.

Johan,
I already tried what you suppose but I got an error "incorrect syntax near 'END'. I removed the last 'END' from below code and now the code is accepted. 

BEGIN TRANSACTION
    SELECT @nROWS = count(username) FROM DBTIMEREG WHERE UNIQUEKEY = @sKEY
    IF @nROWS = 0
       BEGIN
          INSERT INTO [DBTIMEREG] (username,logindate,device,pcname,uniquekey)
          VALUES(@sUser,@sDate,@sDevice,@sPcname,@sKey) 
       END
===> removed this line   END
COMMIT TRANSACTION

Erik,
Thanks. "0" is returned if the getuser()+curdate() does not exist. If user exist (e.g. he restarted his PC on same day) than only logoff date will be saved in same record. So, I only want to save login/logoff data of a user in one row/record. Hence my idea to use the record count to check if record already exist on SQL Server.

[toc] | [prev] | [next] | [standalone]


#5495

From"Er!k \\/!$$Er" <nospam@nospam.url>
Date2013-07-11 20:09 +0200
Message-ID<krmscs$1fo9$1@adenine.netfront.net>
In reply to#5494
Rob,

What both Johan and I try to explain, is that Count() will never return 
null.
So you code should be
IF @nROWS=0
BEGIN
....
END

Erik


"Rob van Erk"  schreef in bericht 
news:c0a58b21-cd0a-4253-9aef-eb080bc0d216@googlegroups.com...

Thanks for the input.

Willie,
What I did is run an "Execute" in SQL Express. This returns a "command 
completed successfully". So, content of SP seems ok. Not sure if I can run / 
test the Stored Procedure using paramaters in MS Management Studio.

Johan,
I already tried what you suppose but I got an error "incorrect syntax near 
'END'. I removed the last 'END' from below code and now the code is 
accepted.

BEGIN TRANSACTION
    SELECT @nROWS = count(username) FROM DBTIMEREG WHERE UNIQUEKEY = @sKEY
    IF @nROWS = 0
       BEGIN
          INSERT INTO [DBTIMEREG] 
(username,logindate,device,pcname,uniquekey)
          VALUES(@sUser,@sDate,@sDevice,@sPcname,@sKey)
       END
===> removed this line   END
COMMIT TRANSACTION

Erik,
Thanks. "0" is returned if the getuser()+curdate() does not exist. If user 
exist (e.g. he restarted his PC on same day) than only logoff date will be 
saved in same record. So, I only want to save login/logoff data of a user in 
one row/record. Hence my idea to use the record count to check if record 
already exist on SQL Server. 


--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

[toc] | [prev] | [next] | [standalone]


#5496

FromRob van Erk <erk.v@hotmail.com>
Date2013-07-11 12:25 -0700
Message-ID<00f4d989-20eb-4ecb-be8e-9e43659f0233@googlegroups.com>
In reply to#5486
Erik,

I will check further but if I run the below code now in Management Studio is just seems to work fine using. If I delete all records and run the SP in Managment Studio by using following code (by the way... :-) thanks for the tip to google for TSQL Debug) the record is added. If I run it again than no additional record is added (which is correct). 

EXEC    [dbo].[sp_insertrec]
        @sdevice = 'DELLOPTIPLEX',
        @spcname = 'PCROB'

The problem in my SP code was the following line:

SET @skey  = CURRENT_USER + GETDATE() 
 
it should have been:

SET @skey  = CURRENT_USER + CONVERT(VARCHAR(24),GETDATE(),111)

Thanks for the EXEC command I could trace the issue in the SP. 

First step (get a proper working SP) looks ok but I still can not get the SP run from wihin VO. Will have to investigate this further. I will have to google further.... :-). But very interesting stuff to learn.
 

[toc] | [prev] | [standalone]


Back to top | Article view | comp.lang.clipper.visual-objects


csiph-web