Database

20-Jan-09 7:08

It would greatly help if you post the command you tried to use and the error message you got.

The need to optimize rises from a bad design.My articles[^]

Backup & Restore.

Nanda_MR19-Jan-09 20:15

Re: Backup & Restore. [modified]

19-Jan-09 20:15

Hello!

I m planning to use PostgreSQL but I have one doubt how can i take a backup.
I check some of website they refer dump command that also for LINUX How can i take backup in XP and how to restore after partition plz help me.

and i want to know where that backup file is stored.

Thanx's in advance.

Wendelius20-Jan-09 6:36

20-Jan-09 6:36

PostgreSQL uses a little bit different approach than most of DBMS's do. It creates a file containing SQL commands which can be again executed back to an empty database. This will lead to the situation where the database was when the dump was made.

Dump is taken using pg_dump or pg_dumpall utility and the result will be echoed which then again can be redirected to a file and to a place you choose.

For more info, look: SQL Dump[^]

Addition:
Of course every database can be backed up by taking copies from the files using operating system, but in order to do that, the database engine must be first shut down in order to get a consistent image.

The need to optimize rises from a bad design.My articles[^]

modified on Tuesday, January 20, 2009 12:56 PM

Nanda_MR20-Jan-09 19:38

20-Jan-09 19:38

Ya I tried it's working.
But I didn't find backup file.
Know I am format my hard disk so I'll copy that file in some removable devices after installation of XP I want run that backup file so How can i do that one ???

pass 5 hour back I am trying can't do it plzz help me.

Wendelius20-Jan-09 21:13

Re: Backup & Restore. [modified]

20-Jan-09 21:13

non-bugging Errors wrote:
But I didn't find backup file

What was the command you issued?

The need to optimize rises from a bad design.My articles[^]

Nanda_MR20-Jan-09 22:36

20-Jan-09 22:36

samp=# SELECT pg_dump -Fc -b -v -f "C:\std1.backup" samp;
ERROR: syntax error a or near ""C:\std1.backup""
LINE 1: SELECT pg_dump -Fc -b -v -f "C:\std1.backup" samp;
                                    ^

I am using above code one more thing.
1. I used SELECT and without SELECT.
2. I used different Quoted ex=",',` and empty also.
3. and other method also tried but not working. Cry | :((

if i use this code o/p come like this but i didn't got backup file.

samp=# pg_dump samp -f user\db1.dump;
       List of tablespaces
    Name    |  Owner   | Location
------------+----------+----------
 pg_default | postgres |
 pg_global  | postgres |
(2 rows)

thanx's for reply
thanx's in advance.

modified on Wednesday, January 21, 2009 4:56 AM

Wendelius21-Jan-09 5:07

21-Jan-09 5:07

pg_dump is a utility, so you run it at command prompt. For example:

pg_dump -Fc -b -v -f "C:\std1.backup" samp

The need to optimize rises from a bad design.My articles[^]

Nanda_MR21-Jan-09 20:01

21-Jan-09 20:01

Thanx's it working with database log-in parameter.

Wendelius21-Jan-09 20:08

21-Jan-09 20:08

Good to hear, you're welcome Smile | :)

The need to optimize rises from a bad design.My articles[^]

RAISEERROR problem

George_George19-Jan-09 20:13

19-Jan-09 20:13

Hello everyone,

If in a store procedure, it is using SET XACT_ABORT ON (auto rollback transaction when there is error). Currently, I just rely on the output of the store procedure (when there is any errors inside) to handle success/error at client side (e.g. ADO.Net client), now I want to add RAISEERROr statement to throw the error outside to client. But I can not think of how to add RAISEERROR and fill in the related parameters for this function. Does anyone have any ideas?

Here is my pseudo code.

SET XACT_ABORT ON;

-- DML operations

commit;

thanks in advance,
George

J4amieC19-Jan-09 22:17

J4amieC

19-Jan-09 22:17

Keep it in the same discussion geoorge.

You have already been given a solution to this by Rob: http://www.codeproject.com/script/Forums/View.aspx?fid=1725&msg=2886661[^]

George_George20-Jan-09 2:51

20-Jan-09 2:51

Thanks J4amieC,

My question is different. Smile | :)

Whether RAISEERROR must be used in catch block? Any ideas or comments?

regards,
George

Hamid_RT22-Jan-09 23:18

Hamid_RT

22-Jan-09 23:18

Using RAISERROR[^].

Of one Essence is the human race
thus has Creation put the base
One Limb impacted is sufficient
For all Others to feel the Mace
(Saadi )

George_George4-Feb-09 22:44

Optimize an query select DB

4-Feb-09 22:44

Thanks Hamid!

I like this link you recommended!

regards,
George

TuanNGUYEN19-Jan-09 16:48

TuanNGUYEN

19-Jan-09 16:48

Hi all,
I have a table whick have got 2 fields [id - info], for example :
id info
1 A
2 B
3 C
...

Now, in my program, there is an array of id in program, ex : [1, 3, ...]. Now, I want to query the table to get the corresponding array of objects[id-info] like that : [[1, A], [3, C]...]. I knew some ways, but I think theys are still optimized, as following, 2 ways I knew :
- foreach array of id, get info correspoding the id, then add to the result array => each id, I have to query the DB 1 time => not good.
- generate a big select query with WHERE Condition is like (where id = 1 or id = 2 ...) => query DB 1 time, but the condition expression is complex to verify for DB.
- generate a big select query with Where condition is like (where id in[1, 2, 3]) => similair to 2rd way, not very good.
I think there are still better way, could you help me.
Currently, I am using C# with MSSQL, but I will change to MySQL in several months.
Thanks in advance.

Re: Optimize an query select DB

Wendelius19-Jan-09 18:19

Re: Optimize an query select DB

19-Jan-09 18:19

You could modify the in list to a XML query and use that, but since you're changing ty MySQL that most likely won't work in there. Using IN (value1, value2, ...) is supported by all DBMS although each DBMS may have restrictions in the amount of values. However, that's a working solution and the optimizer tries to find the best permutation for execution.

If the in list is large, this could be a modeling issue, but without anymore info about the problem, it's hard to say.

The need to optimize rises from a bad design.My articles[^]

TuanNGUYEN19-Jan-09 20:04

TuanNGUYEN

19-Jan-09 20:04

Yeah, I am agree, using IN expression is the best way in those three ways. But, in algorithm view, if we can convert the array of Id to a temporary table which have only field "Id", then we inner join two table on "Id". I think if the conversion is possible, that is the best solution, I have not much experience on SQL and programming, I still hope find a better way.

Re: Optimize an query select DB

Wendelius20-Jan-09 8:28

"begin catch" and "end catch"

20-Jan-09 8:28

That's true. If you would have the data in db (in some table format) that would be the best for the query itself. However, how would you get the data to the temporary table. In worst scenario you add it row by row. Little better if you can add them simultaneously, but still you're creating a table and populating it and both of those are time and resource consuming operations. So in overall even if the last query gets simplified, the overall throughput may be worse.

This is exactly why I mentioned the modeling of the db. If you could prevent those dynamic lists or make them significantly smaller, it would have a great impact to your query. But in order to do that, you would have to find common "feature" from the values, which you should then store in lookup tables. I can't say if this is possible at all or how it would be implemented in your case since it would require a whole lot of information about the issue.

One possibility you could use is to create a procedure which gets an array (or user defined table) of values and then uses a those values in join using the original query. I wrote a brief article on that: http://www.codeproject.com/KB/database/TableValuedParameters.aspx[^] but the downside is that it's SQL Server specific.

The need to optimize rises from a bad design.My articles[^]

George_George19-Jan-09 2:38

19-Jan-09 2:38

Hello everyone,

I am not sure my following code must be put between "begin catch" and "end catch"? Or they could be in any where in a store procedure -- e.g. outside "begin catch" and "end catch"?

IF @@ERROR <> 0
BEGIN
 IF @@TRANCOUNT > 0
  ROLLBACK
 DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
 SELECT @ErrMsg = ERROR_MESSAGE(),
      @ErrSeverity = ERROR_SEVERITY()
 RAISERROR(@ErrMsg, @ErrSeverity, 1)
END -- end of IF @@ERROR <> 0

thanks in advance,
George

J4amieC19-Jan-09 4:02

J4amieC

19-Jan-09 4:02

1.
Read the documentation on SQL Books Online about BEGIN CATCH ... END CATCH

edit: I know you're too bone-idle to search the internets, so here you go: http://technet.microsoft.com/en-us/library/ms175976.aspx[^]

2.

Try it out for yourself, observe the behaviour, feel free to ask a question about a specific aspect of the behaviour observed if you need to.

George_George19-Jan-09 18:41

19-Jan-09 18:41

Thanks J4amieC,

The link looks great! But does not answer all of my questions.

My scenario is different. Some store procedure I am maintaining is simple, just a wrap of a single insert/delete/update statement, and they are not in a transaction -- they also do not wrapped in try/catch.

In this situation, what is your suggested solution to return meaningful error information to client (e.g. an ADO.Net client program in my scenario)?

regards,
George

Rob Philpott19-Jan-09 6:29

Rob Philpott

19-Jan-09 6:29

Begin/End catch was added in Sql Server 2005 and is kind of handy to avoid having to check the status of @error at multiple points in your stored proc.

The code you've pasted looks like it would belong in a catch. A common pattern looks something like this:

begin transaction
begin try
  ...
  ... 
  ...
  commit transaction
end try
begin catch
  rollback transaction
  raiserror(ERROR_MESSAGE(), ERROR_SEVERITY())
end catch

You don't need to check @error any more - that is implied by reaching the catch block. Can't see any point in checking @@trancount either - either the commit or the rollback will get hit.

Regards,
Rob Philpott.

George_George19-Jan-09 18:40

19-Jan-09 18:40

Thanks Rob!

Your code sample looks great! But it covers how to handle and raise exception to client (e.g. an ADO.Net client program in my scenario) when there is transaction and try/catch.

But my scenario is different. Some store procedure I am maintaining is simple, just a wrap of a single insert/delete/update statement, and they are not in a transaction -- they also do not wrapped in try/catch.

In this situation, what is your suggested solution to return meaningful error information to client (e.g. an ADO.Net client program in my scenario)?

regards,
George

Rob Philpott19-Jan-09 23:13

Rob Philpott

19-Jan-09 23:13

Hey George,

Well, if I understand it right and that you have a simple stored proc which is just a single insert/update/delete etc. you won't be needing a transaction as the command will either succeed or fail, so no rollback will be needed.

In this case, from code, you can just call the stored proc directly. Should it fail (FK constraint for instance) this will be raised as an SQLException in the code. This comes for free, you don't need to do anything to get this.

If you've got a batch of commands which you want to work atomically (either all complete successfully or none do (rollback)) you'll want to wrap the sql in a transaction in the proc (as originally posted).

There is the ability to create transactions on the Connection object in code, but personally I wouldn't do this. Transactions should always be as small as possible, and you can't really control the scope as effectively.

Hope that makes some sort of sense.

Regards,
Rob Philpott.

George_George20-Jan-09 3:01