Tuesday, June 24, 2008

nHibernate - a thing i like

I have a table: Act, and an other table Service Name
between them, there's an other table, that has actId and ServicenameId too ...

I needed somehow for the act object to see all the servicenames. somehow ... in a list maybe? or an ilist?

Here's how u do it:




< bag name="ServiceNameList" lazy="false" table="BOMMSS_ServiceNameAct">
< key column="TARFSS_ActId"/>
< many-to-many class="Atlas.Tariff.DataAccess.ServiceName,Atlas.Tariff.DataAccess" column="ServiceNameId"/>
< /bag>




in the mapping file (xml), and add something like this:




public IList ServiceNameList
{
get { return m_servicenamelist; }
set { m_isChanged |= (m_servicenamelist != value); m_servicenamelist = value; }
}




in the class def.

Tuesday, June 10, 2008

SQL and Underscore

Recently one of our QA told me that searching for a record with _ in it, won't work.
Actually it works, but the thing is that the '_' ( underscore ) is a reserved SQL character, and you have to know how to use it.

Originally the '_' ( underscore ) was designed to:
Let's say you want to find the word 'synopsys' in your table. But you don't if it was 'synopsys' or 'sinopsys' :)
In this case you should do this:




select Title from YourTable where title like 's_nopsys'




And this will find them both. (if any)

Back to Our case.
What our code looked like was something like this:




select Title from YourTable where title like '%_%'




Of course it returned everything from the table ... so QA was kinda right ... filter was not working.
But if QA would've known to search for '[_]' ... then it would've worked ...


It is doable, of course, not to have to add the '[' and the ']' in the search box ... but ... does it worth it?
If you can explain your 'client', how to use the '_' ( underscore ), and how to use the % sign ... in the search query ...
well maybe you don't have to change any of your code, because your 'client' might be happy to use these extra features.

If you can't talk your 'client' into this, you can try something like SQL's escape:




select Title from YourTable where title like '%\_%' escape '\'




or, add some extra character, because next time they'll search for '\' :)
so maybe do something like this:




select Title from YourTable where title like '%' + char(13) + '_%' escape char(13)




If you have some thoughts on this ... tell me about it ...

Monday, June 9, 2008

Youtube "bug" ?

Ever since the new youtube-player is in, i am experiencing this problem
frecvently. My colleagues can see a video , I get a (youtube) link from them, and
I can't see anything.

I re-installed flash player too.

If the videos are available in poor and also better quality one of them
it loads forever and never starts, the other one has this error.

Browser: ie6
allvideos: no
conntype: lan
errormsg: We're sorry, this video is no longer available
os: xp_pro

I wrote youtube. They told me that "Google Web Accelerator" might be the problem. That is right ... I uninstalled ... and it works ...

I thought I'd write down, maybe it'll help some1

;)

cheers

Friday, June 6, 2008

SQL Generate Insert script for pretty much any table ...

I am pretty proud of this code ... I made it a while ago, but still use it pretty often.
What does it do?
generates insert script for each row in a table ...
Let's say you create a table, you insert some data, and you'd like to generate a script to put the table and the data in an other database ... well ... you first script your table ... and then you run: sp_GenerateInsert 'YourTableNameHere'

(this is a stored procedure, and the result is only printing a string on the screen - you can run it whenever you want - won't harm your database structure OR data)

and that is it !





if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GenerateInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GenerateInsert]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO


CREATE procedure sp_GenerateInsert
@TableName varchar(50)
as
Begin

-- Utility stored Procedure
-- Returns the list with inserts (re-create the table with all the data)

print '-- Swoosh 2004'
print ''
print 'SET IDENTITY_INSERT ' + @TableName + ' ON'
print 'GO'
print ''

declare @strSQL varchar(8000)
declare @ColumnList varchar(8000)
declare @ColumnListInOne varchar(8000)
declare @ColumnName varchar(8000)
declare @ColumnType int
declare @ID as integer

select @ID = id from sysobjects where type = 'U' and name = @TableName
set @ColumnList = ''
set @ColumnListInOne = ''

declare ccursor cursor for
select Name, Xtype
from syscolumns
where id = @ID

open ccursor
fetch next from ccursor into @ColumnName, @ColumnType
while @@FETCH_STATUS = 0
begin

Select @ColumnList = @ColumnList + @ColumnName + ', '

if (@ColumnType = (select xtype from systypes where name = 'int')) or (@ColumnType = (select xtype from systypes where name = 'float'))
begin
Select @ColumnListInOne = @ColumnListInOne
Select @ColumnListInOne = @ColumnListInOne + ' ltrim(rtrim(replace(isnull(convert(varchar(3000), ' + @ColumnName + '),''{|SWOOSH_NULL_REPLACER|}''),'''''''','''''''''''')))'
end
else
begin
Select @ColumnListInOne = @ColumnListInOne + '''''''''' + ' + '
Select @ColumnListInOne = @ColumnListInOne + ' ltrim(rtrim(replace(isnull(convert(varchar(3000), ' + @ColumnName + '),''''),'''''''','''''''''''')))'
end

if (@ColumnType = (select xtype from systypes where name = 'int')) or (@ColumnType = (select xtype from systypes where name = 'float'))
begin
Select @ColumnListInOne = @ColumnListInOne
end
else
begin
Select @ColumnListInOne = @ColumnListInOne + ' + ' + ''''''''''
end

Select @ColumnListInOne = @ColumnListInOne + ' + '', '' + '

fetch next from ccursor into @ColumnName, @ColumnType
end --while @@FETCH_STATUS = 0
close ccursor

deallocate ccursor

set @ColumnList = Left(@ColumnList,len(@ColumnList)-1)
set @ColumnListInOne = Left(@ColumnListInOne,len(@ColumnListInOne)-9)

set @strSQL = ''
set @strSQL = @strSQL + ' declare @AllValues varchar(8000)'
set @strSQL = @strSQL + ' set @AllValues = '''''
set @strSQL = @strSQL + ' declare ccursor cursor for '
set @strSQL = @strSQL + ' select ' + @ColumnListInOne + ' from ' + @TableName
set @strSQL = @strSQL + ' open ccursor '
set @strSQL = @strSQL + ' fetch next from ccursor into @AllValues '
set @strSQL = @strSQL + ' while @@FETCH_STATUS = 0 '
set @strSQL = @strSQL + ' begin '
set @strSQL = @strSQL + ' print ''insert into ' + @TableName + ' (' + @ColumnList + ') values ('' + replace(@AllValues, ''{|SWOOSH_NULL_REPLACER|}'', ''NULL'') + '')'' '
set @strSQL = @strSQL + ' fetch next from ccursor into @AllValues '
set @strSQL = @strSQL + ' end '
set @strSQL = @strSQL + ' close ccursor '
set @strSQL = @strSQL + ' deallocate ccursor '
exec (@strSQL )

print ''
print 'SET IDENTITY_INSERT ' + @TableName + ' OFF'
print 'GO'

End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO





Have fun with it ...
and if you come up with some cool "extension" / "upgrade" to it ... tell me about it ;)
p.s. there is an other version, that doesn't include the first column (generates the insert script without the Identity column ... which is also usable ...)

Thursday, June 5, 2008

Google Analytics

Google Analytics - This is a screenshot:
(site traffic for one of my sites)



Question ...
Can anyone pls tell me what is "other" ?

nHibernate vs. Active Record

I have used nHibernate in 3 projects

read more on it here and here

I didn't like it because of the XML stuff.

next time i'll use Active Record (read more about that here), to see how that behaves, maybe i'll post something new afterwards ;)