Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.clipper.visual-objects > #5456 > unrolled thread
| Started by | Rob van Erk <erk.v@hotmail.com> |
|---|---|
| First post | 2013-07-04 21:38 -0700 |
| Last post | 2013-07-16 06:40 -0700 |
| Articles | 13 — 3 participants |
Back to article view | Back to comp.lang.clipper.visual-objects
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
| From | Rob van Erk <erk.v@hotmail.com> |
|---|---|
| Date | 2013-07-04 21:38 -0700 |
| Subject | Time 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]
| From | "Valery N." <mustangsoftcom@gmail.com> |
|---|---|
| Date | 2013-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]
| From | "Valery N." <mustangsoftcom@gmail.com> |
|---|---|
| Date | 2013-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]
| From | Rob van Erk <erk.v@hotmail.com> |
|---|---|
| Date | 2013-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]
| From | "Valery N." <mustangsoftcom@gmail.com> |
|---|---|
| Date | 2013-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]
| From | Rob van Erk <erk.v@hotmail.com> |
|---|---|
| Date | 2013-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]
| From | "Valery N." <mustangsoftcom@gmail.com> |
|---|---|
| Date | 2013-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]
| From | Rob van Erk <erk.v@hotmail.com> |
|---|---|
| Date | 2013-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]
| From | Rob van Erk <erk.v@hotmail.com> |
|---|---|
| Date | 2013-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]
| From | "Valery N." <mustangsoftcom@gmail.com> |
|---|---|
| Date | 2013-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]
| From | Rob van Erk <erk.v@hotmail.com> |
|---|---|
| Date | 2013-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]
| From | "Claus Wagner" <clausw@kabelbw.de> |
|---|---|
| Date | 2013-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]
| From | "Valery N." <mustangsoftcom@gmail.com> |
|---|---|
| Date | 2013-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