|
Hi All
Perhaps someone can advise on a better solution to my problem ?
I have a table full of call (telephone) records, each record has the duration of the call made
the duration is in seconds
i.e
Call table
Call id, duration, telephonenumber
1 89 0122222222222
2 135 0192929292929
3 31 0121122211111
4 5....etc etc
what i need to do i go through each record and convert the duration to a time format
so
for call id 1, the result would be 01:29,
for 2, result would be 02:15
for 3, result would be 00:31
for 4, result would be 00:05
at the moment, below is what i am doing within a cursor for each row, and although it works fine
it is a really slow way of doing things, and when i have to go through a few million records you can imagine this takes quite a while
Can anyone suggest a quicker alternative ?
Hope that all makes sense ?
Simon
set @CounterVariable = 1
set @hours = 0
set @mins = 0
set @time_counter = 0
WHILE (@CounterVariable <= @dur)
BEGIN --- count the hours
set @CounterVariable = @CounterVariable + 1
set @time_counter = @time_counter +1
IF @time_counter = 60
BEGIN
set @hours = @hours + 1
set @time_counter = 0
END
END
--- mins is remainder (seconds)
set @mins = @time_counter
IF @hours < 10 and @time_counter < 10 -- build the time string
BEGIN
set @dur_time = '0'+ CAST(@hours as varchar(2)) + ':' + '0' + CAST(@mins as varchar(2))
END
ELSE IF @hours >= 10 and @time_counter < 10
BEGIN
set @dur_time = CAST(@hours as varchar(2)) + ':' + '0' + CAST(@mins as varchar(2))
END
ELSE IF @hours < 10 and @time_counter>= 10
BEGIN
set @dur_time = '0'+ CAST(@hours as varchar(2)) + ':' + CAST(@mins as varchar(2))
END
ELSE IF @hours >=10 and @time_counter>= 10
BEGIN
set @dur_time = CAST(@hours as varchar(2)) + ':' + CAST(@mins as varchar(2))
END
|
|
|
|
|
Hi,
Here some sql to calculate Hours, Minutes, seconds using only built in sql functions and in one sql select statement:
select floor(duration / 3600) as h, floor( (duration - floor (duration /3600) )/60) as m, duration - floor(duration / 3600) * 3600 - floor( (duration - floor (duration /3600) )/60) * 60 as sec
then u can concatenate all these fields
HTH.
Hayder marzouk
|
|
|
|
|
I may be too simplistic here, but I tend to agree with Hayder marzouk
(presuming I'm understand what you're trying to accomplish here).
I used a slightly different formula than he did: his didn't work quite right when I tried it in Access .. probably my fault. In MSAccess, this works:
CREATE PROCEDURE pcSeconds2FormattedTime( seconds INTEGER ) AS
SELECT (@seconds\3600) & ':' & ((@seconds - (@seconds \3600)*3600 )\60) & ':' & @seconds - (3600*(@seconds\3600) + 60*((@seconds - (@seconds \3600)*3600 )\60))
FROM dual;
(presuming you have something called "dual").
If you don't have the '\' operator (integer division), then use the FLOOR function as suggested. Also, in a real db system, use the real concatenation operator '||', not the '&' ampersand.
Depending on whether the SQL compiler will do "common subexpression elimination", this may or may not run any faster than your method.
|
|
|
|
|
thats great, thanks guys
Simon
|
|
|
|
|
Hi,
After i have back up and restore the database to another server on another computer, the mirror option under tasks after right clicking on the database does not appear .
So, the problem is with my software or others problems?
Thanks in advance.
|
|
|
|
|
|
Dear all,
We have a solution which makes use of the mirroring feature of SQL Server 2005 for high availability and automatic failover. But, because of the high cost of SQL Server this is not always an option for all our clients.
My question is, what is the best approach or architecture to implement a custom replication / mirroring feature between two SQL Server Express databases. I have read about a trigger-log approach on the master database with some service synchronizing to the slave? But how to go about when the master and slave switches roles? What about replicating structural changes also? Automatic failover is also totally something else, but not that important for now?
Any ideas, resources, etc. would be appreciated.
Kind regard!
|
|
|
|
|
I am using MS Access DB With MFC as programming platform. I have question about WHERE clause in SELECT query
"SELECT subcat_id,subcat_name FROM temp_subcat WHERE cat_id = 'get_cat' "
get_cat is a long variable, but when i enclose it in ' ' qoutes SQL or ODBC treats it as string and does not uses the value inside it which I wanted to.
i removed qoutes and tried still it doesnt work it throws exception like “Too few parameters, Expected 1” I think it might be ignoring the unquoted get_cat
Quesion is--->
Is there any method of enclosing variables in the SQL queries while using MS Access as database?
Like any syntax like ---> ' ". variable ." '
In other words:: I want to use variable in my SQL query and there must be some syntax provided by SQL or MS Access to identify such variable which is been used in the query and use the value inside that variable and not the variable itself as a string.
Plz help me out with this problem
San
San
|
|
|
|
|
i am new to sql and such stuff....
did you tried concatination?? or something like
"SELECT field FROM table WHERE field='".$var['var']."'"
hope this works!!
|
|
|
|
|
madeelch1986 wrote: did you tried concatination??
Not wise. You're setting yourself up to be attacked through a SQL Injection attack by concatinating values into a SQL String.
|
|
|
|
|
May be you are true but this is not what i needed actually
My requirement is, i am trapping some long value at runtime and i storing it into some long variable and i want to fetch the data from MS Access DB which corrosponds to this value only . So i want to use this variable in my SQL in WHERE clause for searching.
If you can help then please tell me how to do it.
Thanks
san
|
|
|
|
|
Yes thanks for you help
but i am sorry to say that it was not useful
I used to write this when i was working on PHP
i think i need to check some different way to write and use variables in my SQL query
Thanks
San
sazsas
|
|
|
|
|
santoshsan wrote: get_cat is a long variable, but when i enclose it in ' ' qoutes SQL or ODBC treats it as string and does not uses the value inside it which I wanted to.
That is because the database has no idea of the variables you have in your C++ applicaiton. They are separate applications, running in separate processes. The only way they communicate is through SQL. You have to tell the SQL the value you want. It cannot peer into another process and pull the value out.
It has been over 7 years since I did MFC and C++ so your best bet is to find out how to generate parameterised queries in MFC and put the value in as a parameter to the SQL query.
|
|
|
|
|
To Colin Angus Mackay
The para you wrote that is very true. One process cant predict about the variables which are generated in some other process unless there is some intermediate one like SQL to do it.
I used to write SQL queries which uses variables created in PHP. When I was working on PHP and MYSQL. It was like -->
“ SELECT field FROM table WHERE field = ‘”.$variable.”’ “
I tried to quote like this but MFC does not take the above statement as one whole string but it counts it upto-->
“ SELECT field FROM table WHERE field = ‘ ”
There must be some way that MS Access has provided to specify that the value which is been used in SELECT query is a variable. E.g. it has #date# to include date value.
I am still searching for it, if you find something helpful please help me out with this problem.
san
|
|
|
|
|
santoshsan wrote: "SELECT subcat_id,subcat_name FROM temp_subcat WHERE cat_id = 'get_cat' "
get_cat is a long variable, but when i enclose it in ' ' qoutes SQL or ODBC treats it as string and does not uses the value inside it which I wanted to.
You probably need to do something like this:
"SELECT subcat_id,subcat_name FROM temp_subcat WHERE cat_id = " + get_cat
Do keep in mind the SQL injection attacks warning from Colin, though.
Hope this helps.
Chandra Ram
|
|
|
|
|
I tried to create database independant using new feature of visual .net(factories)the problem happen when passing parameters for orcale and sql server the shape of this passed parms differs that will genrate error when passing parameter or i will have to write code for diffrent diffrent database cases & that will be on the contrary of the reason of factories and exisance.
thanks alot
Hassan amaar.
Hassan Amaar
|
|
|
|
|
I think you're maybe missing the point of the factory pattern - in what way do the passed parameters change?
|
|
|
|
|
Thanks alot for yuor replay,
it differs in the the way the prameter are passed for instance when passing aprameter to orcale prvider it will be like this :p + name
for Sqlprovider it will be like this :@ + name
so in this case according to what I understood I will have to create aclass for each data provider.
I will do appreciate if you told if there is better solution,and please give me an example.
Hassan amaar
Hassan Amaar
|
|
|
|
|
Well yes, this is really the idea behind the factory pattern, something like:
DalFactory fact = new DalFactory();
fact.GetDal();
then in the factory class:
GetDal()
{
IDal aDataLayerClass;
switch (source)
{
case "SqlServer":
aDataLayerClass = new SqlServerDal();
break;
case "Oracle":
aDataLayerClass = new OracleDal();
break;
etc...
}
return aDataLayerClass;
}
This means that whatever is calling your data layer, it doesn't matter to them what the datasource is in the background, as they are just dealing with a class like IDal, and you can write provider specific code for each datasource.
|
|
|
|
|
Hi,
In fact they are different :
SQLServer provider : @ + name
Oracle : p + name
OleDB : ? (without a name)
ODBC (i think) : ? (without a name)
Me too i don't know why MS decided to change parameter names with providers. It's as is it and we must deal with that.
There are many other differences :
- Data Types
- Quotes : []in sql and access , "" in Oracle,....
- And Queries are different so use only standard SQL (SQL-92 or SQL-99) to be sure that u don't need to rewrite your application when changing provider.
I think u must create a class for each provider and declare diffrences as variables that u change in each class. I am sure that's the better way.
HTH.
Hayder Marzouk
|
|
|
|
|
Thanks you for your replay
I think what you said is the only way to deal with this case
Hassan Amaar
Hassan Amaar
|
|
|
|
|
Hi Guys,
I had my database for my website in the remote webhost server.But the whole database got deleted suddenly.I could not track from where it happened.Now I am having a new webhost. I would like to give maximum protection, so that the database doesn't get deleted.
Can anybody help me with the necessary steps that has to be taken so that my database is secure.
Thanks
Jith
|
|
|
|
|
Check you application for possibility of injecting sql:
e.g. do you concatenate your sql strings together like
"select * from a where something = " + textbox1.text;
|
|
|
|
|
Hi all!
i have writing an stored procedure in sql server 2005 which are given below ...
Create procedure [dbo].[DGV_UpdateUser]
(
@uId nvarchar(100),
@uName nvarchar(300),
@uEmail nvarchar(300),
@uState nvarchar(300),
@Picture Image
)
AS
BEGIN TRAN
DECLARE @SQL_Query varchar(8000)
IF(EXISTS(SELECT * FROM Users WHERE Uid = @uId))
BEGIN
SET @SQL_Query = 'UPDATE Users SET Name = ''' + @uName + ''', Email = ''' + @uEmail + ''''
IF(@uState IS NOT NULL)
SET @SQL_Query = @SQL_Query + ', State = ' + @uState
IF(@Picture IS NOT NULL)
SET @SQL_Query = @SQL_Query + ', Picture = ' + @Picture
SET @SQL_Query = @SQL_Query + ' WHERE Uid = ''' + @uId + ''''
EXEC(@SQL_Query)
--print @SQL_Query
END
IF(@@error<>0)
ROLLBACK
ELSE
COMMIT
When i press F5 to create this procedure, i have faced following error:-
Msg 402, Level 16, State 1, Procedure DGV_UpdateUser, Line 21
The data types varchar and image are incompatible in the add operator.
----------------------------------------------------------------------
i have try to cast @Picture into Varchar but i have not successed to remove error. Can any one help me?
Thanks & Regards,
SAMir Nigam,
Software Developer,
STPL, Lucknow, India.
|
|
|
|
|
You have DECLAREd @SQL_Query as varchar. All your other variables are defined as nvarchar. Choose one and stick with it.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
