Friday, February 6, 2015

find non-printable characters in nvarchar string (T-SQL)

Yesterday I needed to find all characters in an nvarchar which are non-alpha-numeric... long story short... I found no better way than writing my own stored function and walk over the string char by char.

Good example is shown here: http://jamesveitch.com/t-sql-function-replace-non-printable-ascii-chars/

other solutions based on PatIndex: http://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server

... the only adaption needed to the function of the first link is in the if clause.

First you don't have to write hard coded numbers... there is a tsql-function called "ascii" which can be used instead (e.g.: ascii('A') => 65) AND is even used in the function to convert the current char to a number... a second function "unicode" does the same, but for unicode (n)chars.

Second: use begin and end statement and check explicitly for null

Third: adapt solution for language (e.g.: german öäüß,...) ...

kr Daniel
Posted by at No comments:
Labels:

Tuesday, February 3, 2015

sql server complex update

Today, I was asked how complex update statements can be executed; so I built the following sample:


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
declare @t1 TABLE (a int, b int)
declare @t2 TABLE (a int, b int)

insert into @t1 values (1,2)
insert into @t1 values (2,3)

insert into @t2 values (1,200)
insert into @t2 values (2,300)

update t2 set t2.b = t1.b
from @t1 as t1
inner join @t2 as t2
on t1.a = t2.a

select * from @t1
select * from @t2

in line 1,2 I create a temporary table variable and add some values in the following lines. In line 10 it starts become interesting. Here we update the value b from table @t2 aliased as t2 and overwrite the values with the joined values of table @t1 aliased as t1. This syntax with froms and joins is much more common in select statements, but can be used in update statements as well.

The much worse solution is to create a cursor over @t1 and overwrite @t2 line by line. This solution is officially bad.

kr, Daniel
Posted by at No comments:
Labels:

MongoDB

The last few days I had a look on MongoDB (using windows)...

MongoDB was developed by a company called 10gen and was open-sourced in 2009. The reason they started building a database was simply the lack of usable NoSql databases for their own service. 2013 they renamed their company to MongoDB Inc. to express their new focus. A key player here is Dwight Merriman.

MongoDB itself is a C++ open source NoSQL schemaless (but can be used with a schema) document store (IaaS) storing BSON (Binary JSON) documents with JavaScript as procedure language and good scaling behavior (CAP theorem: partial consistency).

Installation process is well documented and worked for me without any troubles. Best thing is that it can be skipped using mongoDB portable (e.g.: ZWAMP is a portable windows web-stack with mongo). The only thing to do is to create a config file or set --dbpath to a folder with the data and start mongod (daemon). Another nice feature is the ootb http-interface (can be enabled by argument) and the --rest switch which allows basic data querying.

Tools:

  • php MongoDB admin
  • MMS: Mongo Management Service
  • Mongo Shell (ootb)
  • RoboMongo
  • MongoVUE
  • Query Translator ( http://www.querymongo.com/ )
    e.g.: translates 'select * from x where _id <= "3"' to '    db['x'].find({"_id": { "$lte" : "3" }});'
The following code (php) shows my first tries with mongoDB (I used dBug http://dbug.ospinto.com/ http://sourceforge.net/projects/php-dbug/ for visualization)



  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
<?php 
 header('Pragma: no-cache');
 include_once('dbug\dbug.php'); // its open source and a single file to copy http://dbug.ospinto.com/
 if (!class_exists('Mongo')) die("Mongo support required. Install mongo pecl extension with 'pecl install mongo; echo \"extension=mongo.so\" >> php.ini'");

 // returns a connection object to work with
 function Connect()
 {
  try
  {
     return new Mongo('mongodb://localhost:27017', array('connect' => true));
  }
  catch (MongoConnectionException $ex)
  {
     error_log($ex->getMessage());
     die("Failed to connect to MongoDB");
  }
 }

 function ShowDBs()
 {
  $dbs = Connect()->listDBs();
  new dBug($dbs);
  echo "<hr />";
 }

 function CreateDB($name)
 {
  $mongo = Connect();
  $mongo->selectDB($name)->createCollection('__tmp_collection_');
  $mongo->selectDB($name)->dropCollection('__tmp_collection_');
   }
   
 function DropDB($name)
 {
  Connect()
   ->selectDB($name)
   ->drop ();
 }

 function CreateCollection($db, $col)
 {
  Connect()
   ->selectDB($db)
   ->createCollection($col);
 }

 function AddDocument($db, $col, $doc)
 {
  Connect()
   ->selectDB($db)
   ->selectCollection($col)
   ->save($doc);
 }

 function ShowDocuments($db, $col)
 {
  $cur = Connect()
   ->selectDB($db)
   ->selectCollection($col)
   ->find();
  
  foreach($cur as $data) 
  {
   echo "\n<div style='float:left;margin-right:10px'>\n";
   new dBug($data);
   echo "\n</div>\n";
  }
 }

 function CreateSPToAddNumbers($db,$col)
 {
  Connect()
   ->selectDB($db)
   ->selectCollection('system.js')
   ->save(array(
    '_id'   =>                        'addNumbers',
    'value' => new MongoCode('function addNumbers(x, y) { return x + y; }')
   ));
   
 }

 function CallSPToAddNumbers($db, $col)
 {
  echo "<div style='clear:left'/><div style='padding-top:40px'>Calculation: 30 + 12 = ";
  var_dump(Connect()->selectDB($db)->execute(
    'function(x, y) { return addNumbers(x,y); }', 
    array(30, 12)));
  echo "</div>";
 }

?>

<html>
<head><title>mongo test</title></head>
<body>
<?php

 //ShowDBs();
 DropDB('CC');

 //ShowDBs();
 CreateDB('CC');
 // die();
 
 //ShowDBs();
 CreateCollection('CC','x');
 // die();
 
 AddDocument('CC','x', array("_id" => "1", "name" => "John",     "details" => array("born" => "1986", "status" => "tired")));
 AddDocument('CC','x', array("_id" => "2", "name" => "Lenny",    "details" => array("born" => "1981", "status" => "tired")));
 AddDocument('CC','x', array("_id" => "3", "name" => "Frank",    "details" => array(                  "status" => "tired")));
 AddDocument('CC','x', array("_id" => "4", "name" => "Ryan",     "details" => array("born" => "1988", "status" => "tired")));
 AddDocument('CC','x', array("_id" => "5", "name" => "Will",     "details" => array("born" => "1990", "status" => "tired")));

 ShowDocuments('CC','x');

 CreateSPToAddNumbers('CC','x');
 CallSPToAddNumbers  ('CC','x');
?>
</body>
</html>

... the code drops and then creates a db 'CC' (cloud computing) with a collection x with 5 documents then shows the document and the result of an example function.

kr, Daniel
Posted by at No comments:
Labels:

Monday, February 2, 2015

Prototyping (Part 1)

I am currently doing some research on "prototyping". My interests concentrate on desktop applications and how requirements can be validated. In some cases a lack of understanding costs days, weeks, months or years of time. The earlier errors are found or not made the less costs arise to get back on track. So I think some additional costs and time spending is nothing compared to a completely misunderstood requirement (the more important the more expensive).

In this post I concentrate on software found in an - as good as infinite - list on http://c2.com/cgi/wiki?GuiPrototypingTools ...

What I am looking for is a software which:

  • shows screens
  • offers walk-through-prototyping 
  • is optimized for web pages and windows desktop applications
  • can handle code extensions to enrich the prototype
  • uses .NET or JavaScript for enrichment
  • optional: can use photography (e.g.: paper prototypes) as base of work
before creating a list of possible matching tools here a tool for android which inspired me: POP https://popapp.in/ ... it is really amazing. I would recommend to try it if you build mobile apps! ... but I don't, so I keep on looking for an alternative. (See also: http://alternativeto.net/software/pop--prototyping/ )

My top 10 tools from the link list above are (with focus to the mentioned requirements / price-schemas: commercial, single-user license):
unmentioned till here: Big 3rd party vendors like telerik or infragistics built their own prototyping software. It makes sense to use their products when you use their libraries, because the prototypes are nearer to the final result (less imagination needed by the customer).

The prototyping market is currently focusing on app development and web development (clear trend). Enterprise applications (what I think often means desktop applications) are only supported by good established, bigger products. My requirements are not met by any product (found nothing after hours of research and even after looking tons of other listing pages, which are mostly subsets of the mentioned list). 

Some smart guys on the net advised to use paper only, others talk about their experience with pure drawing programs, but in my opinion such prototyping is not close enough to the final product to validate customer needs. Other opinions are to use lightswitch or other RAD tools, but I think they aren't the right way to validate the requirements either. I think this is also a bad idea because you turn from "no code" to "code it all", but it brings us to an important question:

Who should build a prototype and why? 

For validating business requirements I advice a requirements engineer, project manager or management guy. Validating technical questions like: "we want an interface, can we technically get it up and running" will be done by a technical engineer and is completely different to the other situation. I think I will dive deeper into this question in a following post.

Anyway, I think that this time there is a chance to build a product which has unique features on the market and I am looking forward to do something in this area...

kr, Daniel
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)