Tuesday, November 25, 2008

MSSQL to MySQL

To day i was struggling to catch a error inside a stored procedure using MySQL. Faced lot of difficulties and still not found good result. There is no equal function in MySql to RAISERROR in MSSQL.

As a solution for this what we can we do is, we can call a function as this way
CALL RaiseError();

There for because of the none existence of this function this will be raise an error.
So it is very logical..:-)

So procedure will be work very fine.....Great...

Example:

DROP PROCEDURE IF EXISTS `dbname`.`procedureName` $$
CREATE DEFINER=`root`@`%` PROCEDURE `edbname`.`procedureName`(
pId bigint,
pName varchar(50)

BEGIN

IF EXISTS
(SELECT
1
FROM
tableName
WHERE
((Id = pId)
)
THEN

UPDATE
tableName
SET
Name = pName,

WHERE
(Id = pId);

ELSE
CALL RaiseError();
/*There is no RaiseError metho in MySql.
Because of that reason here call nonexistent method which is 'RaiseError()'.There for this will produce an error*/

END IF;

END $$

DELIMITER ;

Friday, November 21, 2008

Orgnized well


When buying things like clothes, set a rule for yourself that you can only buy something new, to replace something you already have. For example, from a practical point of view, let’s say you need to have 15 business shirts. Set a ‘budget’ of having 15 shirts, and then when you want to buy one, first throw away (or give to charity) the worst of the other. Can’t make a choice which one to throw out? Guess what: you won’t need a new shirt.

[http://www.iwillteachyoutoberich.com/blog/tip-9-only-buy-new-things-when-replacing-something-old]

Codesmith problem

In my mechine i have installed Codesmith and when trying to connect to Mysql DB it is not allowed to access it. Basically it doesnt enable the connection establishing window.


Even if you mannually typethe connection string and click on 'Test' button it will say Mysql.data.dll not exist.

This is because of compatibility issue...

To sortout this.....:-)

So i had t installed earlier version. It also did not work. So copy 'MySql.Data.dll'
to bin folder of codesmith (C:\Program Files\CodeSmith\v5.0\bin). Mannaully type the connction string and test. Then it will works fine................................

Convert MSSQL DB to MySql

Today started work on converting the MSSQL database to MySql .All the sql script and sp's has to convert. Used Codesmith to generate to SP. It succeeded and have to create remaining scripts....

Convert VB to C# or C# to VB

This will help you to convert.Therefore you can try many examples without considering language(C#,VB) .
http://converter.telerik.com/

XRay

You can view the CSS setting of the web controls.It really show the XRay of selected control.
http://westciv.com/xray/index.html

SQL Designer

Today i found a very interesting which is web based Entity Relationship Designer.Enjoy this...http://ondras.zarovi.cz/sql/demo/

Thursday, November 20, 2008

Add column to mid of table

In sql Server you cant add a column to mid of a table .If you add a new column to existing table then it will append end of the table. But in case of adding a column to to mid of the table you can do it like as follows;
  • Create a new table with the required columns,
  • Copy the data from the old table into the new table
  • Delete the old table
  • Then rename the new table.
Sample Code
---Old table
Create Table A
(col1 varchar(50) null,
col2 varchar(50) null)

--Add new column col3
Begin Transaction

Create Table B
(
col1 varchar(50) null,
col3 varchar(50) null, -- NEW COLUMN IN MIDDLE (NULLABLE)
col2 varchar(50) null
)
go
if exists(select * from A)
exec('insert into B (col1, col2)
select col1, col2 from A with (holdlock tablockx)')
go
drop table A
go
execute sp_rename 'B', 'A', 'object'
go

commit


Wednesday, November 19, 2008

NHibernate

This is new topic for me and I started to work on that few days ago. It is interesting to play and I learn lot during the days. :-) .Learning curve of the NHibernate is bit long and few resources are available. But using trial and error could be able to solve lot of problems.


What is ?


NHibernate is ORM solution and is intended for transparent binding .NET classes to database tables.Commonly we have mismatch beween the relational database and our objects. The aim of the NHibernate is reducing time efforts to switch application to another database provider .Basically it provides low overhead .


“NHibernate increases your performance as a developer”.







Monday, November 17, 2008

Back to Blogging

Last few months i could not able to continue my blog..:-( ..Again i hope to continue it......:-)