Friday, April 3, 2009

Passing a list of integers to a stored procedure.

TSQL stored procedures don't support arrays as input parameters, so in order to pass a list we have to convert it to a supported type and then convert it back to a list. One solution is to serialize the List<int> into an xml string, pass it as nText and then use the SQL Server xml capabilities to transform it into a table.



List<int> results = new List<int>();

XmlSerializer xs = new XmlSerializer(typeof(List<int>));

StringWriter sw = new StringWriter(new StringBuilder());

xs.Serialize(sw, results);

PatientResultsTableAdapter ta = new PatientResultsTableAdapter();

table = ta.GetDataByPatientBins(sw.ToString());


CREATE PROCEDURE [dbo].[SelectList]
    @ids nText
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @hDoc int
 
    --Prepare input values as an XML documnet

    EXEC sp_xml_preparedocument @hDoc OUTPUT, @ids

    select *
    from results
    where result_id in (select id from openxml (@hDoc,
'/ArrayOfInt/int'
) with (id int 'text()'))
                        

    EXEC sp_xml_removedocument @hDoc
END

Wednesday, January 21, 2009

Save a diagram in SQL Server 2008



if ($args.length -lt 3)
{
Write-host "usage: save_diagram db_name diagram_name file_name"
}
else
{
function Join-String
{
begin { $sb = New-Object System.Text.StringBuilder }
process { $sb.Append($_) | Out-Null }
end { $sb.ToString() }
}

$dbname = $args[0]
$filename = $args[2]
New-item $filename -type file -force | out-null

$declarationsStr = "SET NOCOUNT ON
DECLARE @newid INT
DECLARE @DiagramSuffix varchar (50)"


$insertStr = "PRINT 'Suffix diagram name with date, to ensure uniqueness'
SET @DiagramSuffix = ' ' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16)

PRINT 'Create row for new diagram'
BEGIN TRY
PRINT 'Write diagram NAME into new row (and get [diagram_id])'
INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition]) VALUES ('{0}'+@DiagramSuffix, 1, {1}, 0x)
SET @newid = SCOPE_IDENTITY()
END TRY
BEGIN CATCH
PRINT 'XxXxX ' + Error_Message() + ' XxXxX'
PRINT 'XxXxX END Tool_ScriptDiagram2005 - fix the error before running again XxXxX'
RETURN
END CATCH"


$updateStr = "UPDATE sysdiagrams SET [definition] .Write ({0}, null, 0) WHERE diagram_id = @newid `n"

$connString = "server=george-b\Advia;Integrated Security=True;Initial Catalog={0}"
$query = "SELECT [name],[principal_id],[diagram_id],[version],[definition] FROM sysdiagrams"
$conn = new-object "System.Data.SqlClient.SqlConnection" ($connString -f $dbname)
$sqlcmd = new-object "System.Data.SqlClient.SqlCommand" ($query, $conn)
$conn.Open()
$reader = $sqlcmd.ExecuteReader()
$reader | &{
begin
{
$values = new-object "System.Object[]" $reader.FieldCount
$declarationsStr | Add-Content $filename
}
process
{
$_.GetValues($values) | out-null;
$name = $values[0]
$version = $values[2]
"Generating sql script for diagram [{0}]." -f $name | write-host
$insertStr -f $($name, $version) | Add-Content $filename
$i = 0
$s = "0x"
$values[4] | %{if ($i -eq 32) {$i = 0; $updateStr -f $s; $s = "0x"}
else {$i+=1; $s += "{0:x2}" -f $_ }} | Join-string | Add-Content $filename
if ($s -ne "0x") {$updateStr -f $s | Add-Content $filename}
}
}
$conn.Close()
}

Wednesday, January 7, 2009

Roundcubemail on Solaris 10 with SJWS 7

Solaris 10 06/06
Sun Java Web Server 7.0
php with php_cgi
move .htaccess php configuration into iniset.php
make cookie always non secure.