Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.clipper.visual-objects > #5486 > unrolled thread
| Started by | Rob van Erk <erk.v@hotmail.com> |
|---|---|
| First post | 2013-07-10 13:16 -0700 |
| Last post | 2013-07-11 12:25 -0700 |
| Articles | 8 — 4 participants |
Back to article view | Back to comp.lang.clipper.visual-objects
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
| From | Rob van Erk <erk.v@hotmail.com> |
|---|---|
| Date | 2013-07-10 13:16 -0700 |
| Subject | Debug 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]
| From | Willie Moore <williem@wmconsulting.com> |
|---|---|
| Date | 2013-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]
| From | Johan Nel <johan.nel@xsinet.co.za> |
|---|---|
| Date | 2013-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]
| From | "Er!k \\/!$$Er" <nospam@nospam.url> |
|---|---|
| Date | 2013-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]
| From | Johan Nel <johan.nel@xsinet.co.za> |
|---|---|
| Date | 2013-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]
| From | Rob van Erk <erk.v@hotmail.com> |
|---|---|
| Date | 2013-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]
| From | "Er!k \\/!$$Er" <nospam@nospam.url> |
|---|---|
| Date | 2013-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]
| From | Rob van Erk <erk.v@hotmail.com> |
|---|---|
| Date | 2013-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