|
|
Both are a real database file. .sdf file are called embedded database (the database server is just a class library that you access) so your application will become the database "server".
|
|
|
|
|
First: i'm sorry for my bad english
I've a problem,
in my webaplication, i use sqlite to manage some data for links
in a simple favourite links page. To get all links i use:
$connectionhandle = sqlite_open('mystart2', 0666, $sqliteerror);
$result = sqlite_query($connectionhandle, $query, SQLITE_ASSOC);
$result = sqlite_fetch_array($result);
print_r($result);
ok, the functions for connecting and doing the query are capsuled
in classes but i think that doesn't matter
This query returns just the first row from this table,
so just one row, but i'm sure that there exists at least 3 rows.
That means that the query should return 3 rows, not just one.
What's the problem?
modified on Friday, January 16, 2009 4:43 PM
|
|
|
|
|
Hi,
it may be wise to show us some code, the SQL line seems fine.
modified on Friday, January 16, 2009 8:57 AM
|
|
|
|
|
Hi all,
I have table with data in my database as follows
table name :sample (col 'ps' is primary key)
village ps val
1 1 56
1 2 67
1 3 65
1 4 70
2 5 74
2 6 64
2 7 32
3 8 46
3 9 56
4 10 64
My requirement is i have to get the Count of 'village' whose val > 60 and val < 60
for that i tried in the following way..(for finding avg 'val' of villages)
select sum(val)/count(ps) assessment from sample group by village
output:
assessment
66
56
59
54
But i need the count of above assessment values > 60 and < 60
i.e count of assessment > 60 , < 60
For that i tried in following way(i know i wrote wrong...but it gives you the idea what i am trying for)
select count(*) sample where (select sum(val)/count(ps) assessment from sample group by village) > 60
Please suggest me how to do that...
thanks in advance.
|
|
|
|
|
you need to use HAVING...
something like:
select sum(val)/count(ps) assessment from sample group by village HAVING sum(val)/count(ps) > 60
let me know if it helps
Intelligence is almost useless for those who have nothing else!
Email: caiokf@gmail.com
|
|
|
|
|
thank you for your suggestion.
I got it.
Its similar approach as you suggested.
select count(*) from sample group by village
having avg(value) > 60
select count(*) from sample group by village
having avg(value) < 60
|
|
|
|
|
Good Morning ALL
I want to Restore a database, i have the Following SP
Create PROCEDURE [dbo].[sp_RestoreDatabase]
@dbname char(32), -- the database name to restore as
@filename char(32),
@Results int OUTPUT
AS
set nocount on
declare @path char(256)
set @path = 'C:\DATABASES\' -- the location of the backuped up database file (on the SQL Server)
execute('sp_ClearDatabaseConnections ' + @dbname) --kill the existing connections to the current Database
-- Restore the database
RESTORE DATABASE @dbname
FROM DISK ='C:\DATABASES\'+ @filename
WITH MOVE 'TNGoedit_Data' TO 'C:\DATABASES\'+ @dbname + '.mdf' ,
MOVE 'TNGoedit_Log' TO 'C:\DATABASES\'+ @dbname + '_log.ldf',
REPLACE,RECOVERY;
-- Was the command successful or was there a problem
if ( (select @@Error) = 0 ) begin
SET @Results = 1
end
else begin
SET @Results = 0
end
When i Compile this SP i get the Following Error
Msg 102, Level 15, State 1, Procedure sp_RestoreDatabase, Line 27<br />
Incorrect syntax near '+'.
Where is my Problem
Thanks
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za
|
|
|
|
|
Do you have an extra comma at the end of this line:
MOVE 'TNGoedit_Log' TO 'C:\DATABASES\'+ @dbname + '_log.ldf',
|
|
|
|
|
No , the Problem points on the line that uses the "+" Sign
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za
|
|
|
|
|
There was a plus on that line too, but if you double click the error, which of those lines is highlighted?
|
|
|
|
|
It goes to this line
FROM DISK = @path + @filename
Thanks
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za
|
|
|
|
|
What happens if you first modify the variable and then use only it in the restore, like:
...
SET @filename = 'C:\DATABASES\'+ @filename
-- Restore the database
RESTORE DATABASE @dbname
FROM DISK = @filename
...
|
|
|
|
|
Now it moved and Targeted the Following lines
WITH MOVE 'TNGoedit_Data' TO @path + @dbname + '.mdf' ,
MOVE 'TNGoedit_Log' TO @path + @dbname + '_log.ldf',
Thanks
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za
|
|
|
|
|
Seems that the problem was found so try to do the same for all variables, like:
...
SET @movetarget1 = @path + @dbname + '.mdf'
...
WITH MOVE 'TNGoedit_Data' TO @movetarget1...
|
|
|
|
|
Thanks i have Change the SP to look like this and it compiled fine ,
Create PROCEDURE [dbo].[sp_RestoreDatabase] <br />
<br />
@dbname char(32), -- the database name to restore as<br />
@filename char(32),<br />
@Results int OUTPUT<br />
<br />
AS<br />
<br />
set nocount on<br />
<br />
declare @path char(64)<br />
declare @dbmdf char(64)<br />
declare @dbldf char(64)<br />
set @path = 'C:\DATABASES\' -- the location of the backuped up database file (on the SQL Server)<br />
<br />
execute('sp_ClearDatabaseConnections ' + @dbname) --kill the existing connections to the current Database<br />
<br />
SET @filename = 'C:\DATABASES\'+ @filename<br />
set @dbmdf = 'C:\DATABASES\' + @dbname + '.mdf'<br />
set @dbldf = 'C:\DATABASES\' + @dbname +'_log.ldf'<br />
-- Restore the database<br />
RESTORE DATABASE @dbname <br />
FROM DISK = @filename<br />
WITH MOVE 'TNGoedit_Data' TO @dbmdf ,<br />
MOVE 'TNGoedit_Log' TO @dbldf,<br />
REPLACE,RECOVERY;<br />
-- Was the command successful or was there a problem<br />
if ( (select @@Error) = 0 ) begin<br />
SET @Results = 1<br />
end<br />
else begin<br />
SET @Results = 0<br />
end
and i tested it like this
DECLARE @Results INT
EXEC [dbo].[sp_RestoreDatabase] 'oBookingUFS','oBookingUFS',@Results out
and i got the Following Error
<br />
Msg 3234, Level 16, State 2, Procedure sp_RestoreDatabase, Line 27<br />
Logical file 'TNGoedit_Data' is not part of database 'oBookingUFS '. Use RESTORE FILELISTONLY to list the logical file names.<br />
Msg 3013, Level 16, State 1, Procedure sp_RestoreDatabase, Line 27<br />
RESTORE DATABASE is terminating abnormally.
Thanks
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za
|
|
|
|
|
Are you sure that this is the logical file name 'TNGoedit_Data' or should it be for example 'TNGoedit'
You can verify the logical names using RESTORE FILELISTONLY[^]:
RESTORE FILELISTONLY
FROM DISK = 'C:\DATABASES\oBookingUFS';
|
|
|
|
|
Hello everyone,
Two basic questions about transactions,
1. For single SQL insert/update/delete statement, are there any needs or benefits to wrap into a transaction?
2. I wrote T-SQL like this,
BEGIN TRY
Begin Transaction
...
commit
END TRY
BEGIN CATCH
rollback
...
END CATCH
my question is, is it a must to call commit explicitly just before end try statement? Any means to commit transaction automatically after end try statement?
regards,
George
|
|
|
|
|
George_George wrote: For single SQL insert/update/delete statement, are there any needs or benefits to wrap into a transaction
It's always inside a transaction. Either implicit or explicit. If you don't want to be able to rollback the operation then it wouldn't matter, if you have logical error handling etc, then explicit transaction would be needed.
George_George wrote: is it a must to call commit explicitly just before end try statement? Any means to commit transaction automatically after end try statement
Basically commit point canbe anywhere, even at client side. However if you explicitely start a transaction, you have to explicitely end it. If you don't execute commit, database will roll back the transaction at the end of the session (unless other transaction handling is occurring after this transaction).
|
|
|
|
|
Thanks Mika,
1.
"If you don't want to be able to rollback the operation then it wouldn't matter, if you have logical error handling etc, then explicit transaction would be needed." -- I am confused. Could you show me a sample about the benefit please? My detailed confusion is even if I do not start transaction explicitly, I could still catch the error and handles it.
2.
"Basically commit point canbe anywhere, even at client side." -- what do you mean client? No matther what kinds of API you are using or even using Management Studio directly, I think we are all "client" of database engine.
regards,
George
|
|
|
|
|
George_George wrote: if I do not start transaction explicitly, I could still catch the error and handles it
Yes you can if the error is 'technical' and SQL Server sees it as an error. But what happens if the error is logical. Consider following imaginary scenario.
- insert into a table
- check that some business rule apply
- if business rule is violated, undo the insert
The previous is very simple and it wouldn't be a problem to just change the order of the operations. However, this is not always the case.
Since there's typically no harm in using explicit transactions, personally I've decided to use them always.
George_George wrote: what do you mean client? No matther what kinds of API you are using or even using Management Studio directly, I think we are all "client" of database engine.
I meant a scenario where your try catch is inside a stored procedure and a client application (whatever it is) calls the procedure. Ending point can be at the calling side so the procedure is actually just a part of a bigger transaction scope.
|
|
|
|
|
Thanks Mika!
"I meant a scenario where your try catch is inside a stored procedure and a client application (whatever it is) calls the procedure. Ending point can be at the calling side so the procedure is actually just a part of a bigger transaction scope." -- so in this scenario, client code (e.g. ADO.Net) starts the transaction, but what is the practice of writing store procedure code in this way? Do we also need try-catch and begin transaction/rollback/commit code in store procedure?
BTW: I have this confusion because I as a store procedure developer -- not sure whther client will start another transaction or not. So, how could I write code in store procedure which could handle both client starts transaction or store procedure self-start transaction?
regards,
George
|
|
|
|
|
How the transaction scope is defined is a matter of opinion. I think most people use the logic that stored procedure starts and ends the transaction. However, there are downsides especially if rollback is executed because in that case the whole transaction is rolled back, even if it's started outside the stored procedure.
So this means that you actually cannot decide alone what transaction scope will be used. The application may be affected by your decisions. You could use @@TRANCOUNT to investigate if transaction is started outside the procedure and in the logic of your stored procedure you would execute rollback based on the initial transaction count.
There's a very good article you should read: http://www.codeproject.com/KB/database/sqlservertransactions.aspx[^]
|
|
|
|
|
George_George wrote: 1. For single SQL insert/update/delete statement, are there any needs or benefits to wrap into a transaction?
No, there are no benefits in this case.
George_George wrote: Any means to commit transaction automatically
This tells you all about it MSDN[^]
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thanks Ashfield!
Two more comments/questions after learning,
1.
I want to confirm with you that if we call begin transaction, do you mean the only way to commit transaction is to explicit call commit?
2.
If I do not use begin transaction explicitly using begin transaction statement (the default behavior is to auto-commit for each successful statement), no other configurations to disable auto-commit for successful each statement -- means we could have chance to commit later in a batch manually explicitly?
regards,
George
|
|
|
|