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


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

Time registration system & SQL

Started byRob van Erk <erk.v@hotmail.com>
First post2013-07-04 21:38 -0700
Last post2013-07-16 06:40 -0700
Articles 13 — 3 participants

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


Contents

  Time registration system & SQL Rob van Erk <erk.v@hotmail.com> - 2013-07-04 21:38 -0700
    Re: Time registration system & SQL "Valery N." <mustangsoftcom@gmail.com> - 2013-07-05 00:13 -0700
    Re: Time registration system & SQL "Valery N." <mustangsoftcom@gmail.com> - 2013-07-05 00:15 -0700
      Re: Time registration system & SQL Rob van Erk <erk.v@hotmail.com> - 2013-07-05 13:32 -0700
        Re: Time registration system & SQL "Valery N." <mustangsoftcom@gmail.com> - 2013-07-05 22:48 -0700
          Re: Time registration system & SQL Rob van Erk <erk.v@hotmail.com> - 2013-07-05 23:08 -0700
            Re: Time registration system & SQL "Valery N." <mustangsoftcom@gmail.com> - 2013-07-05 23:47 -0700
    Re: Time registration system & SQL Rob van Erk <erk.v@hotmail.com> - 2013-07-06 10:35 -0700
    Re: Time registration system & SQL Rob van Erk <erk.v@hotmail.com> - 2013-07-07 05:28 -0700
      Re: Time registration system & SQL "Valery N." <mustangsoftcom@gmail.com> - 2013-07-07 09:27 -0700
    Re: Time registration system & SQL Rob van Erk <erk.v@hotmail.com> - 2013-07-07 11:49 -0700
      Re: Time registration system & SQL "Claus Wagner" <clausw@kabelbw.de> - 2013-07-08 11:44 +0200
      Re: Time registration system & SQL "Valery N." <mustangsoftcom@gmail.com> - 2013-07-16 06:40 -0700

#5456 — Time registration system & SQL

FromRob van Erk <erk.v@hotmail.com>
Date2013-07-04 21:38 -0700
SubjectTime registration system & SQL
Message-ID<7c62ce74-6282-4ecb-811d-ad6849c29cc4@googlegroups.com>
I have a request to set up a time registration system. Customers ask me to provide them with information on when a user starts up and shutdown his PC.
Attached my VO-code. Since I want to be somewhat carefull with using our cetral MS SQL 2005 Server I would be happy if someone could have a look at the code 
and advise me if for the SQL part (INSERT, UPDATE, error handling) looks "healthy". In this forum I have read a lot of usefull information on SQL and I get the impression that incorrectly developed SQL code could completely shutdown your SQL Server. That is what I definetely must avoid.

So, a user starts up his PC (login script of policies) with "TIMEREG STARTUP" and during a shutdown "TIMEREG SHUTDWN" will be run. A user can restart his PC 
multiple times a day and I created my code in such a way the system will only create one record per user per day. So, my primary key will be "today()+getuser().

The customer is situated in various countries in Europe. They are using our central systems. Hence my idea to use our Shared SQL server (processor license so no SQL CALS required).

Thanks for any feedback given.


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
method Start () class app
LOCAL pszCommandLine as psz
LOCAL sFilename   := "c:\timereg\timereg.log"       as STRING
LOCAL cBuf,cResult,sIP,sPCname           as STRING 
LOCAL sLogtime,sLogdate,sType,sUser,sKey as STRING  
LOCAL sSQL,sError,output,sDevice,sEmpty  as STRING
LOCAL oConn                              as AdoConnection
LOCAL oRecordset                         as ADORecordset
LOCAL liRecs	                          as LONG
LOCAL lFound := false                    as logic
                        
// Retrieve parameters from executable  
pszCommandLine := GetCommandLine()  
cBuf           := AsString(pszCommandLine)
sType          := AllTrim(Upper(SubStr(cBuf,9,7)))  
sDevice        := AllTrim(Upper(SubStr(cBuf,17,6)))

// Define variables 
SetDateFormat("dd/mm/YYYY")           

sEmpty         := "" 
sPCname        := AllTrim(NetName()) 
sIP            := GetIPAddress(sPCname)  
sUser          := AllTrim(GetUser())                                    
sLogdate       := AsString(Today())
sLogtime       := Time()            
sKey				:= sLogdate+Left(sUser,10)
sError         := sLogdate+" "+Time()+" Started from PC: "+NetName()+ " ,user : "+GetUser()  + " IP: " + sIP + " "
  
// Administrator logins will not be registered in system  
IF Instr("ADMINISTRATOR",sUser)
	return 0
ENDIF

// Check if parameters are included. If not cancel 
if sType <> "STARTUP" .and. sType <> "SHUTDWN"     
	cResult := sError + " but no valid parameter found" 
	LogWrite(cResult, sFilename) 
   return 0 
ENDIF                                    

// Ado connection 
output := Adoconnect(sUser)
if !Empty(output) 
	output := sError + output 
	LogWrite(output,sFilename)
	return 0                    
endif		         

// Is program started up during login or logoff?
IF sType = "SHUTDWN"  // PC is shutdown  
   sSQL  := "UPDATE DBTIMEREG SET " + ;
            "LOGOFFDATE       =  '" + sLogdate + "'," + ;
            "LOGOFFTIME       =  '" + sLogtime + "' " + ;
            "WHERE UNIQUEKEY  =  '" + sKey     + "'"                
ELSE                                            
	// Check if user already started his PC (today) at earlier time same day
	sSQL       := "SELECT * FROM DBTIMEREG where UNIQUEKEY = " + "'" + sKey + "'"
   oConn      := AdoGetConnection()
   oRecordset := oConn:Execute(sSQL,@liRecs,adCmdText)

   if oRecordset:RecordCount = 0  // User did not login earlier today on this PC?
      sSQL  := "INSERT INTO DBTIMEREG (Username,Logindate,Logintime,Logoffdate,Logofftime,device,pcname,uniquekey) VALUES ('" + ;
         	    sUser + "','" + sLogdate + "','" + sLogtime + "','" + sEmpty + "','" + sEmpty + "','" + sDevice  + "','" + sPCname + "','" + sKey + "')" 					
	else
		return // User perhaps restarted his PC on same day. No record inserted, record already exist
	endif
********************* Run SQL command *****************************
oConn       := AdoGetConnection()
oRecordset	:= oConn:Execute(sSQL,@liRecs,adCmdText)

return 0                                                                               *******************************************************************************
****  ADO
******************************************************************************
Function Adoconnect(sUser as string)
LOCAL oConn       as AdoConnection
LOCAL lError      as LOGIC
LOCAL sError :="" as STRING
LOCAL uError      as USUAL
//LOCAL cbErr as CODEBLOCK
//cbErr := ErrorBlock({|oErr|_Break(oErr)})

BEGIN SEQUENCE
      oConn := AdoConnection{}
      oConn:ConnectionTimeout := 5
      oConn:provider          := "SQLOLEDB"
      oConn:ConnectionString  := "Data Source=HOME_LAPTOP\SQLEXPRESS;" + ;
         	      				   "Initial Catalog=timereg;"       + ;
							       	   "User Id=" + sUser + ";Password=;"    + ;
								         "Trusted_Connection=yes"
      oConn:CursorLocation    := adUseClient
      RECOVER USING uError
        sError := uError:description
        lError := true
END                             
    
BEGIN SEQUENCE
      oConn:Open(nil,nil,nil,nil)            
      RECOVER USING uError
      IF oConn:State <> adStateOpen
        // Error occurred Lokale logfile updaten.....
        lError := true
        IF oConn:Errors:Count > 0
            // Ado Error
            sError := oConn:Errors:[Item,1]:description
        ELSEIF IsInstanceOfUsual(uError, #Error)
            sError := uError:description     
        ELSE
            sError := "Unknown error"
        END IF
      endif 
END SEQUENCE

return(sError)

********************************************************************************
****  WRITE LOGGING 
*******************************************************************************

Function LogWrite(newLogRow as string,filename as string) 
local fh as ptr 
local fp as dword 

if File(filename) 
        fh := FOpen(filename,FO_READWRITE)        // Open
else 
        fh := FCreate(filename)                   // Create 
endif 

if fh == F_ERROR 
        InfoBox("Error","Could not open " + filename + "!") 
        return 
endif 

fp := SetFilePointer(fh,0,null_ptr,FILE_END) 

FWriteLine(fh,newLogRow) 

CloseHandle(fh) 
return nil

[toc] | [next] | [standalone]


#5457

From"Valery N." <mustangsoftcom@gmail.com>
Date2013-07-05 00:13 -0700
Message-ID<30a9bc44-94f9-4153-be52-25b0f773e883@googlegroups.com>
In reply to#5456
In the program (it works with MySQL) I do it in a different way. For this purpose I use values by default for fields and stored procedures in SQL (they should be written). The user name, the IP address and time (DateTime) undertake from the SQL server. It allows to avoid a set of problems. Then a code in Ca-Vo actually сводётся to a SQL procedure call at start and at an exit from the program.

My council: use stored procedures.

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


#5458

From"Valery N." <mustangsoftcom@gmail.com>
Date2013-07-05 00:15 -0700
Message-ID<15ba3788-32fa-483a-815e-bf4ef6e3fe87@googlegroups.com>
In reply to#5456
In the program (it works with MySQL) I do it in a different way. For this purpose I use values by default for fields and stored procedures in SQL (they should be written). The user name, the IP address and time (DateTime) undertake from the SQL server. It allows to avoid a set of problems. Then the code in Ca-Vo will be reduced only to a SQL procedure call at start and at an exit from the program.

My council: use stored procedures.

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


#5461

FromRob van Erk <erk.v@hotmail.com>
Date2013-07-05 13:32 -0700
Message-ID<759debae-2e4a-42d2-a183-89400a5b79f5@googlegroups.com>
In reply to#5458
Hi Valery, 
Thanks. Will check if I can find some examples on Ado disk. Thought I was on the good way but apparently I need to look at stored procedure usage. Guess this means the code is running directly on SQL server. 
Rob

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


#5463

From"Valery N." <mustangsoftcom@gmail.com>
Date2013-07-05 22:48 -0700
Message-ID<c49253a0-5ee4-46e1-9bb6-e9da99d78a37@googlegroups.com>
In reply to#5461
Users can change the time and date on your computer, his name and address. And the way to make misinformation. But on the server - it can only make the administrator. Therefore, using the means of SQL, we will be safe. This will avoid conflicts with customers and users.

If at you it doesn't turn out, write, I will try to help.

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


#5464

FromRob van Erk <erk.v@hotmail.com>
Date2013-07-05 23:08 -0700
Message-ID<3dd2ba23-ef11-480c-a1b4-a8a7bf389e6a@googlegroups.com>
In reply to#5463
Hello Valery,
I don't exactly understand what to do. Will check other posts in this forum but if you could help that would be great. Perhaps you can send me some sample code. Already read yesterday more about SP but no idea for example how you retrieve information on the server side for username, etc. I agree that it is risky that date/time can be incorrect on the PC site but would expect that you need also to include username from pc as parameter to execute stored procedure. And does SP mean I run my VO code on Microsoft server, so ADO code(I am using Vo2ado) compiled with VO and run on our Microsoft SQL cluster? I think I will not get approval to run VO code on our shared Microsoft SQL cluster. I will also check for samples in this forum but any support would help.

Thanks and have a Nice weekend.
Rob

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


#5465

From"Valery N." <mustangsoftcom@gmail.com>
Date2013-07-05 23:47 -0700
Message-ID<945ff4c7-924f-4340-ae2b-1e33cec9a65d@googlegroups.com>
In reply to#5464
I will try to find time (not today) and to make an example.

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


#5466

FromRob van Erk <erk.v@hotmail.com>
Date2013-07-06 10:35 -0700
Message-ID<1fc7efb4-3921-4739-ac9e-60b3d166f718@googlegroups.com>
In reply to#5456
Hello Valery,

Thanks. A small example would help me a lot. In the meantime I purchased some SQL books that will explain me the working of stored procedures better. So far I developed different SQL based programs but never worked with stored procedures.
Today I read a lot on stored procedures and usage of this seems indeed very logic. It will put the SQL part on the SQL server and if something needs to be changed I would not need to distribute hundreds of new executables. I could simply change the stored procedure on the SQL server. All seems rather logic and tomorrow I will also look if I can find some examples for Vo2Ado. 

Very interesting stuff ... :-)

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


#5467

FromRob van Erk <erk.v@hotmail.com>
Date2013-07-07 05:28 -0700
Message-ID<29fa9917-8f83-425f-a2f0-29ec1f2d4c69@googlegroups.com>
In reply to#5456
Hello Valery (and others),

I have been busy study SQL and now set up following code. Initially I thought I could simply create Stored Function in SQL Server 2005 but this does not allow me to UPDATE and INSERT Records. So, I know created function with EXEC command to Stored Procedures to Insert and Update records. All code seems accepted by SQL. I have not yet tested out the function in VO but all looks promising :-)Does this look better approach compared with my initial code? It is for me a learning process so any recommendation would he highly appreciated. I am now using system time of SQL server and Getuser() in SQL Function instead of forwarding this as parameter to SQL function. I don't think I can retrieve the users' PC name directly in SQL environment so included that as parameter for the function.

Thanks for any comments you can give.
Rob


****** Create function that can be called from VO-Code ******
USE [timereg]
GO
CREATE FUNCTION dbo.Mutation_TimeRec 
( @sType   CHAR(7),
  @sDevice CHAR(30),
  @sPcname CHAR(30))
RETURNS BIT
AS

BEGIN
DECLARE
    @return BIT, 
	@sKEY  CHAR(40),
	@nROWS INT,
	@sDATE CHAR(40),
	@sUSER CHAR(40),
	@lResult BIT 

SET @sDATE= GETDATE()
SET @sKEY= CURRENT_USER + GETDATE()
SET @nROWS= 0
SET @sUSER= CURRENT_USER

IF @sTYPE = 'SHUTDWN'
    BEGIN
      EXEC [SP_AddRec] @sKey,@sDate
    END
ELSE IF @sTYPE = 'STARTUP'
    BEGIN
    SELECT @nROWS = count(username) FROM DBTIMEREG WHERE UNIQUEKEY = @sKEY
    IF @nROWS= 0
       EXEC [SP_InsertRec] @sUser,@sDate,@sDevice,@sPCName,@sKey
       SET @return = 1
    END
RETURN @return
END

***** CREATE Stored Procedure to update existing record *****
USE [timereg]
GO

CREATE PROCEDURE SP_UpdateRec
@sKey CHAR(40),
@sDATE CHAR(40)

AS
BEGIN
BEGIN TRANSACTION
    UPDATE [DBTIMEREG] SET   logoffdate = @sDATE 
                       WHERE Uniquekey  = @sKEY
COMMIT TRANSACTION
RETURN;
END 
GO

USE [timereg]
GO

***** CREATE Stored Procedure to update Insert record *****

CREATE PROCEDURE SP_InsertRec
@sUser CHAR(40),
@sDATE CHAR(40),
@sDevice CHAR(30),
@sPCName CHAR(30),
@sKey    CHAR(40)

AS
BEGIN
BEGIN TRANSACTION
      INSERT INTO [DBTIMEREG] (username,logindate,device,pcname,uniquekey)
      VALUES(@sUSER,@sdate,@sdevice,@spcname,@sKey)
COMMIT TRANSACTION
RETURN;
END 
GO


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


#5468

From"Valery N." <mustangsoftcom@gmail.com>
Date2013-07-07 09:27 -0700
Message-ID<eab6b635-8f71-4345-90ba-5f4cd2d112ab@googlegroups.com>
In reply to#5467
You - well done! You have beautiful code.
But in order to verify the correctness of your code, I need the structure of your database. The only thing I think it can be simplified (the simpler - the more reliable). I am sorry that I have not studied your problem. Tomorrow I will try to show his example.

It seems to me that the use of SQL allows easier, and most importantly - improve the accuracy and reliability.

I read your problem and suggest you think about the solution of such problems:
1.	If the user has turned on the computer, but could not connect to the server - the recording won't be.
2.	If at the user connection becomes torn, the mark about completion of work won't be. As you will solve this problem.
3.	Work of one user from different computers (a different name of the computer, different IP).
4.	Work of the user at night (I began today, and I finished tomorrow).

There are more many questions …

It seems to me that your task can be changed and simply to conduct LOG of users. Then any data (about work of your user) will be solved performance of simple inquiry.
If your task becomes for monitoring of work of users (who now works), it is it can be done even simpler.

In SQL it is possible to receive a registration user name (in SQL) and the IP address (or a name of the connected computer). If you have to know other parameters, they should be transferred.

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


#5469

FromRob van Erk <erk.v@hotmail.com>
Date2013-07-07 11:49 -0700
Message-ID<41164388-aa0c-4dc6-af3e-06351d9e92e5@googlegroups.com>
In reply to#5456
Hi Valery,

Yes, I was also considering some of the points you mentioned. Difficult to find the right solution but that is the challenge I have. And, I though I was making progression but... it looks like I can not run a Stored Function from Vo2Ado, only Stored Procedures. So,.. I have to change my code again :-(

My idea is to create/append a log file on the user PC in case the connection to SQL fails. Also if the user will just press the power button of PC no record of logoff time will be registered in SQL. 

It is more complicated than I thought but very interesting to learn this area of SQL :-)

Rob

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


#5470

From"Claus Wagner" <clausw@kabelbw.de>
Date2013-07-08 11:44 +0200
Message-ID<kre1l2$lv4$1@news.albasani.net>
In reply to#5469
Hi Rob,

<<it looks like I can not run a Stored Function from Vo2Ado, only Stored Procedures.

I don't know Vo2Ado, but generally you can retrieve the return value of a Stored Function with a Select-Statement.

It's the same as you retrieve one value of a column of a specific row from a table, e.g.:
"SELECT Column1 FROM MyTable WHERE PkColumn = 1"

With Stored Function:
"SELECT MyFunction (1)"

----------

<<A user can restart his PC multiple times a day and I created my code in such a way the system will only create one record per user 
per day. So, my primary key will be "today()+getuser().

One golden rule in SQL is, never create a Primary Key with columns, which has any meaning for the application. (Hope, I described it 
understandable in English what I mean).
So I learned to use for Primary Key any automatical generated value, e.g. AUTOINCREMENT. Then for the columns for today(), getuser() 
you can create a UNIQUE INDEX.
Imagine, one day your boss (or your customer) gets the idea, it would be nice to store more records per user per day. If you have 
these columns in your Primary Key, you will have a greater problem... And maybe you have Foreign Keys from other tables... Then you 
have to redesign lots of your database structure.

Claus


"Rob van Erk" <erk.v@hotmail.com> schrieb im Newsbeitrag news:41164388-aa0c-4dc6-af3e-06351d9e92e5@googlegroups.com...
Hi Valery,

Yes, I was also considering some of the points you mentioned. Difficult to find the right solution but that is the challenge I have. 
And, I though I was making progression but... it looks like I can not run a Stored Function from Vo2Ado, only Stored Procedures. 
So,.. I have to change my code again :-(

My idea is to create/append a log file on the user PC in case the connection to SQL fails. Also if the user will just press the 
power button of PC no record of logoff time will be registered in SQL.

It is more complicated than I thought but very interesting to learn this area of SQL :-)

Rob

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


#5532

From"Valery N." <mustangsoftcom@gmail.com>
Date2013-07-16 06:40 -0700
Message-ID<8e97c491-55a7-41fb-98e7-d5bb4df51ff0@googlegroups.com>
In reply to#5469
воскресенье, 7 июля 2013 г., 21:49:57 UTC+3 пользователь Rob van Erk написал:
> Hi Valery, Yes, I was also considering some of the points you mentioned. Difficult to find the right solution but that is the challenge I have. And, I though I was making progression but... it looks like I can not run a Stored Function from Vo2Ado, only Stored Procedures. So,.. I have to change my code again :-( My idea is to create/append a log file on the user PC in case the connection to SQL fails. Also if the user will just press the power button of PC no record of logoff time will be registered in SQL. It is more complicated than I thought but very interesting to learn this area of SQL :-) Rob

Good day!

Last week I was very busy and could not get back in time to the conversation. But, today, I read everything that was written in a week. I see that you have much work.
Indeed, SQL can not know the name of the computer and the user in Windows. But he knows the registration data for an existing connection. Perhaps it is useful to you:

SELECT sp.login_time, sp.loginame, dmec.client_net_address 
FROM sys.sysprocesses sp 
  JOIN sys.dm_exec_connections dmec ON sp.spid = dmec.session_id
WHERE sp.spid = @@SPID;

[toc] | [prev] | [standalone]


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


csiph-web