EricT

Search through all tables for a string

Occasionally it becomes important to find all rows that have a particular string – for example your shop address that is changing.

https://www.mssqltips.com/sqlservertip/6148/sql-server-loop-through-table-rows-without-cursor/

Here I found a stored procedure, that given a table name will output all string columns+rows that contain a string. In case it disappears, here is the complete code:

USE master
GO

CREATE PROCEDURE dbo.sp_FindStringInTable @stringToFind VARCHAR(max), @schema sysname, @table sysname 
AS

SET NOCOUNT ON

BEGIN TRY
   DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE ' 
	   
   SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + @stringToFind + ''' OR '
   FROM INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_SCHEMA = @schema
   AND TABLE_NAME = @table 
   AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')

   SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
   EXEC (@sqlCommand)
   PRINT @sqlCommand
END TRY

BEGIN CATCH 
   PRINT 'There was an error. Check to make sure object exists.'
   PRINT error_message()
END CATCH 
GO

EXEC sys.sp_MS_marksystemobject sp_FindStringInTable
GO

Note that this is stored in the master table (first line), and declared as a system object (last line).

Next we want to automatically call this routine for every table in our database. For this I found a sample here: https://stackoverflow.com/questions/1656804/sql-call-stored-procedure-for-each-row-without-using-a-cursor

use YOURDATABASE
GO
declare @tableName nvarchar(80) = ''
while (1 = 1) begin
   select top 1 @tableName = Table_NAME
   from information_schema.TABLES
   where table_name > @tableName
   order by TABLE_NAME

   if @@rowcount = 0 break;

   print @tableName
   exec sp_findstringintable '%555-1212%', 'dbo', @tablename
end
GO

Executing this snippet will search for the telephone number 555-1212 in all text/string columns in all tables.

posted @ Thursday, January 20, 2022 11:13 AM | Feedback (542)

Some DOS batch tips

Create a date string for a filename (thanks go to StackOverflow)

The below is one long line:

for /F “usebackq tokens=1,2 delims=” %%i in (`wmic os get LocalDateTime /VALUE 2^>NULL`) do if ‘.%%i.’==’.LocalDateTime.’ set ldt=%%j

You can shorten to yyyymmdd like so:

set ldt=%ldt:~0,8%

To zip an entire folder structure into a file (with the date in the name)

The following is one line long. It should not wrap

c:\tools\7za\7za a \\target\folder\file-%ldt%.zip c:\toBackup\folder\*

Invoking VSS (disk shadow) and then working with files from the shadow

Disk shadowing involves setting up the shadow, and then executing commands against the shadow. Unfortunately sometimes things go awry while executing the commands, and then the shadow hangs around. So I always start the script with a shadow removal. Here’s what my batchfile looks like:

diskshadow –s cleanup.cmds
diskshadow –s diskshadow.cmds

The cleanup.cmds contains the following:

UNEXPOSE p:

That’s all. It removes the temporary drive p: which was set up as the volume shadow drive.
Next we look at diskshadow.cmds:

SET CONTEXT PERSISTENT NOWRITERS
SET METADATA example.cab
SET VERBOSE ON
BEGIN BACKUP
ADD VOLUME C: ALIAS systemVolumeShadow
CREATE
EXPOSE %systemVolumeShadow% p:
EXEC c:\scripts\backupfiles.cmd
UNEXPOSE p:
END BACKUP
RESET

In a nutshell, using the file example.cab to hold some metadata, it starts a volume shadow operation on C: and exposes the shadow as p: It then runs whatever commands you want which are stored in backupfiles.cmd, typically copy operations from p: to a backup.

posted @ Thursday, April 7, 2016 12:11 PM | Feedback (680)

Setting up an existing VS2013 to do netmf

The following steps are taken from a support page at ghielectronics. In case the page disappears, here’s the gist of it, as done on my new Windows 10 laptop (windows 10 is new, not the laptop…)

download netmf 4.3 (QFE2) from here: http://netmf.codeplex.com/downloads/get/1423115. Install MicroFrameworkSDK.msi and netmfvs2013.vsix.
Download and install Microsoft’s .Net Gadgeteer Core: http://gadgeteer.codeplex.com/downloads/get/918081
download and install the latest release ghi netmf sdk 2015 R1: https://www.ghielectronics.com/support/netmf/sdk/38/ghi-electronics-netmf-sdk-2015-r1

That’s it.

posted @ Sunday, January 10, 2016 11:01 AM | Feedback (619)

Showing a byte in binary / in octal - C#

To display an 8-bit byte in binary, code the following:

 byte xyz = 27;
 string xyz_binary = Convert.ToString(xyz, 2).PadLeft(8, '0');

For octal (with always leading 0), do:

 byte xyz = 27;
 string xyz_octal = Convert.ToString(xyz, 8);
 if (xyz_octal.Length > 1)
    xyz_octal = "0" + xyz_octal;

posted @ Wednesday, January 16, 2013 3:07 PM | Feedback (660)

CPPUnit in VS2010–with a sample

I modified the CPPUnit 1.12.1 environment so it works in Visual Studio 2010. I also included a small project that uses CPPUnit as part of the build of the small project. Until I find a permanent home for the files, email me at erict [ at ] powersoft [ dot ] ca , and I will send to you.

The sample below doesn’t begin to explore the power that is available in CPPUnit. The examples provided in CPPUnit itself need to be studied. But this is a start…

Building CPPUnit

To recompile all the CPPUnit stuff, open examples.sln, then click on Build | Batch Build…, do select all, then unselect all the Template configurations (I don’t know what the Template configurations do). You will get some errors in the build, but these are example builds included which are supposed to fail.

A sample project of my own using CPPUnit

The project I needed was to test a class, which is defined in a header file. I named the project TestSomeClassesInHeaders.

Below I will describe all the steps I had to do to create the small project.

Create new project in VS2010: Visual C++ | Win32; Console application; add Project Name ‘TestSomeClassesInHeaders’; click Finish to complete the application wizard
Go into project properties;
1. C/C++; General and modify the ‘Additional Include Directories’ field by adding the path to the folder cppunit-1.12.1\include
2. Linker; General and modify the ‘Additional Library Directories’ field by adding the path to the folder cppunit-1.12.1\lib
3. Linker; Input and modify the ‘Additional Dependencies’ field by adding the name cppunitd.lib for your debug configuration, and cppunit.lib for the release configuration.
4. Build Events | Post-Build Event, add the line $(TargetDir)$(TargetFileName) so that the tests execute as part of the build.

Modify the file TestSomeClassesInHeaders.cpp by adding the following headers:

   1:  #include <cppunit/CompilerOutputter.h>

   2:  #include <cppunit/extensions/TestFactoryRegistry.h>

   3:  #include <cppunit/ui/text/TestRunner.h>

Replace the contents of _tmain as follows:

   1:  // get the top-level suite of tests (registry is not the Win32 registry)

   2:  CppUnit::Test * suite = CppUnit::TestFactoryRegistry::getRegistry().makeTest();

   3:  // add the test to the list of tests to run

   4:  CppUnit::TextUi::TestRunner runner;

   5:  runner.addTest(suite);

   6:  // change the default outputter to a compiler error format outputter

   7:  runner.setOutputter(new CppUnit::CompilerOutputter(&runner.result(), std::cerr));

   8:  // run the tests

   9:  bool wasSuccessful = runner.run();

  10:  // return error code 1 if one or more tests failed

  11:  return wasSuccessful ? 0 : 1;

If you now build, you will have a working framework, without any tests. But part of the build is the execution of the runner.
Next we add a dummy class which needs to be tested:
Add a C++ class using the class wizard (Add Class). Call it NewClass, and make it inline. In the generated header file add a public method, as follows:
```
   1:  int Feature(int x)
```
```
   2:  {
```
```
   3:      return x;
```
```
   4:  }
```
Next we add a test object. Add a C++ class using the class wizard (Add Class). Call it NewClassTesting. Don’t make it inline.
Just above the constructor in the CPP File, add the following to identify it as a test suite:
```
   1:  CPPUNIT_TEST_SUITE_REGISTRATION(NewClassTesting);
```

The header file of the testing class needs to be modified quite a bit. Here is the complete file, with comments:

   1:  #pragma once

   2:  #include <cppUnit/TestCase.h>

   3:  #include <cppUnit/extensions/HelperMacros.h>

4:

   5:  #include "NewClass.h"   // the class we want to test

6:

   7:  class NewClassTesting :

   8:          public CppUnit::TestFixture

   9:  {

  10:      CPPUNIT_TEST_SUITE(NewClassTesting);

  11:      CPPUNIT_TEST(test1);

  12:      // ... name each method that should be executed

  13:      CPPUNIT_TEST_SUITE_END();

14:

  15:  public:

  16:      NewClassTesting(void);

  17:      ~NewClassTesting(void);

18:

  19:      void test1()

  20:      {

  21:          NewClass nc;

  22:          int x = nc.Feature(5);

  23:          CPPUNIT_ASSERT(x == 5);

  24:      }

  25:  };

Again build. You now have a framework and unit tested your NewClass!
Just for the fun of it, change line 23 of the testing class to CPPUNIT_ASSERT(x != 5); Upon building you will see build errors with a description ‘error : Assertion’. Double clicking here will show you which assert failed. You can single step through the project to find out details.

Enjoy.

Here's cppunit-1.12.1-VS2010.zip

posted @ Tuesday, February 21, 2012 1:19 PM | Feedback (982)

Having a link to the created build in CruiseControl.Net

NOTE: This has been updated so I don’t have to hard-code the outside URL, along with updates that work with V1.8.4 of CC.Net

The basic idea is to have the name of the generated build traverse from the build script into the web dashboard. Since my build names include the SVN revision number and the build number, the name can't be hard-coded in the web dashboard. For the benefit of completeness, I will also show how I arrive at the SVN revision number (again, there are probably easier ways of doing this, but I haven't found any).

Step 1: To get the revision number, do an SVN INFO command and parse out the revision number. Done as follows:

  <target name="GetLatest">
    <exec program="svn.exe"
          commandline="info https://server/svn/project/trunk --username readonly --password readonly"
          output="./test.txt"
          />
    <foreach item="Line" in="./test.txt" property="lineitem">
      <if test="${string::get-length(lineitem) > 0}">
        <regex pattern="^(?'cmd'.*):\ (?'cmdval'.*)$" input="${lineitem}"/>
        <if test="${cmd=='Revision'}">
          <property name="revision" value="${cmdval}"/>
        </if>
      </if>
    </foreach>

    <echo message="Current revision is ${revision}"/>

    <loadfile file="MasterVersion.txt" property="versions"/>
    <property name="versionstring" value="${versions}.${revision}.0"/>
    <SetFileVersions basedir="." resetversion="false" version="${versionstring}"/>
  </target>

The last three lines bear explanation: I have a small text file that contains the first three numbers of my version string, i.e. MajorVersion.MinorVersion.Branch The revision number becomes the fourth number in line 2, above. Line 3 is a NAnt task DLL I wrote which hunts for all known source files where the version number should exist and replaces this field in the found sources. These files include: AssemblyInfo in VB.Net, C# and C++/CLI, resource files for unmanaged C++ and VB6 project files. By modifying the source files before building I essentially stamp the current version string into all my builds. Also doing it this way shows when a developer build is executed, because the version strings will not be stamped, and my default version numbers in all these files is a very noticeable 999.999.999.999.

Step 2: At the end of the NAnt script, package the files into a logical filename. After the packaging output the package name into a small XML File:

  <target name="Package">
    <zip zipfile="${CCNetArtifactDirectory}/projectname-${versionstring}.zip">
...
    </zip>

    <echo file="./packagename.xml">
      <![CDATA[<output>projectname-${versionstring}.zip</output>
]]></echo>
  </target>

In ccnet.config (the script that runs the NAnt build script described above), there is a section in every project called publishers. Merge the packagename in:

    <publishers>
      <merge>
        <files>
          <file>.\packagename.xml</file>
        </files>
      </merge>
      <xmllogger/>
      ...
    </publishers>

In the folder c:\program files\CruiseControl.NET\webdashboard\xsl is a file called header.xsl. Near the start of this file a variable is being defined (the variable name being defined is ‘modification.list’). Add the following two variable definitions:

      <xsl:variable name="filename" select="/cruisecontrol/build/output"/>
      <xsl:variable name="webpath" select="/cruisecontrol/@project"/>

A bit further down is an xsl:if statement that checks if the build was successful. If it is, add the ‘download here’ portion in. The following fragment shows the entire xsl:if statement:

            <xsl:if test="not (/cruisecontrol/build/@error) and not (/cruisecontrol/exception)">
                <tr><td class="header-title" colspan="2">BUILD SUCCESSFUL</td></tr>

              <tr>
                <td class="header-title" colspan="2">Download result here: <a href="/{$webpath}/{$filename}"><xsl:value-of select="$filename"/></a></td>
              </tr>

            </xsl:if>

In the folder c:\program files\CruiseControl.NET\server\xsl is also a file called header.xsl. It is used to build the emails being sent out. We will do more-or-less the same thing here: Add the variables (note a third variable, and add a link. In addition, we need to add a link if the email is sent off-site and we have to expose an externally-visible link to the HTML webdashboard.

First the variables:

 <xsl:variable name="filename" select="/cruisecontrol/build/output"/> 
 <xsl:variable name="webpath" select="/cruisecontrol/@project"/> 
 <xsl:variable name="weburl" select="/cruisecontrol/integrationProperties/CCNetProjectUrl"/>

A little further down is that same xsl:if statement again. Here I’ve defined it as follows:

    <xsl:if test="not (/cruisecontrol/build/@error) and not (/cruisecontrol/exception)">
        <tr><td class="header-title" colspan="2">BUILD SUCCESSFUL</td></tr>
        <tr>
            <td class="header-label">Build output:</td>
            <td class="header-data"><a href="{$weburl}/{$webpath}/{$filename}"><xsl:value-of select="$filename"/></a></td>
        </tr>
    </xsl:if>

Lastly, in IIS add a virtual directory named the same as the project, and point it at the project’s Artifacts folder (typically c:\program files\CruiseControl.NET\server\project\Artifacts.

posted @ Tuesday, December 20, 2011 9:28 AM | Feedback (613)

Serial Port log4net Appender

As we needed a way to send our logs from a Windows CE device in near real time to an off-machine logging window, we decided that logging to a serial port would be the simplest option (our device has 8 serial ports!). The software already uses log4net for logging, and the best way was to use an appender that outputs to a serial port.

Since I couldn’t find an appender that writes to a COM port, I had to write my own. It was very easy, since log4net provides most of the functionality in an object called an AppenderSkeleton, from which my COMAppender is derived.

Right now the code is very simplistic – data is pumped out the serial port without regard to errors or flow control. Testing has been minimal (only on Windows CE, but it should also work on Windows 32/64). Only two settings can be specified in the appender configuration:

PortNumber (an integer number; in Windows CE a number less than 10 will create the string COMx, a number greater than 9 will generate the device name: $device\COMx).
BaudRate (an integer number, I’ve tested only with 115200).

To add this appender into the configuration file, specify the following:

<appender name="SerialAppender"
type="PowerSoft.log4net.Appender.COMAppender, COMAppender, version=1.0.0.0, culture=neutral, PublicKeyToken=null">
<PortNumber value="10"/>
<BaudRate value="115200"/>
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%date [%thread] %-5level %logger: %message%newline" />
</layout>
</appender>

Obviously the specifics of PortNumber, BaudRate and conversionPattern is up to you.

Source code is now on codeplex at http://comappender.codeplex.com/

posted @ Tuesday, January 25, 2011 11:47 AM | Feedback (611)

Having a link to the created build in CruiseControl.Net

I have not been able to find a simple way to include a link to the created build show up in the web dashboard, nor the email. So I've rolled my own modifications. I'm sure there are better ways of doing it (such as a plug-in), but didn't have time to research the architecture. THIS IS A HACK! The following description has been implemented in CC.Net V1.4.2.14. After I describe this I will double check that it also works on V1.5. The basic idea is to have the name of the generated build traverse from the build script into the web dashboard. Since my build names include the SVN revision number and the build number, the name can't be hard-coded in the web dashboard. For the benefit of completeness, I will also show how I arrive at the SVN revision number (again, there are probably easier ways of doing this, but I haven't found any).

Step 1: To get the revision number, do an SVN INFO command and parse out the revision number. Done as follows:

  <target name="GetLatest">
    <exec program="svn.exe"
          commandline="info https://server/svn/project/trunk --username readonly --password readonly"
          output="./test.txt"
          />
    <foreach item="Line" in="./test.txt" property="lineitem">
      <if test="${string::get-length(lineitem) > 0}">
        <regex pattern="^(?'cmd'.*):\ (?'cmdval'.*)$" input="${lineitem}"/>
        <if test="${cmd=='Revision'}">
          <property name="revision" value="${cmdval}"/>
        </if>
      </if>
    </foreach>

    <echo message="Current revision is ${revision}"/>

    <loadfile file="MasterVersion.txt" property="versions"/>
    <property name="versionstring" value="${versions}.${revision}.0"/>
    <SetFileVersions basedir="." resetversion="false" version="${versionstring}"/>
  </target>

Step 2: At the end of the NAnt script, package the files into a logical filename. After the packaging output the package name into a small XML File:

  <target name="Package">
    <zip zipfile="${CCNetArtifactDirectory}/projectname-${versionstring}.zip">
...
    </zip>

    <echo file="./packagename.xml">
      <![CDATA[<output>projectname-${versionstring}.zip</output>
]]></echo>
  </target>

In ccnet.config (the script that runs the NAnt build script described above), there is a section in every project called publishers. Merge the packagename in:

    <publishers>
      <merge>
        <files>
          <file>.\packagename.xml</file>
        </files>
      </merge>
      <xmllogger/>
      ...
    </publishers>

      <xsl:variable name="filename" select="/cruisecontrol/build/output"/>
      <xsl:variable name="webpath" select="/cruisecontrol/@project"/>

A bit further down is an xsl:if statement that checks if the build was successful. If it is, add the ‘download here’ portion in. The following fragment shows the entire xsl:if statement:

            <xsl:if test="not (/cruisecontrol/build/@error) and not (/cruisecontrol/exception)">
                <tr><td class="header-title" colspan="2">BUILD SUCCESSFUL</td></tr>

              <tr>
                <td class="header-title" colspan="2">Download result here: <a href="/{$webpath}/{$filename}"><xsl:value-of select="$filename"/></a></td>
              </tr>

            </xsl:if>

In the folder c:\program files\CruiseControl.NET\server\xsl is also a file called header.xsl. It is used to build the emails being sent out. We will do more-or-less the same thing here: Add the variables, and add a link. In addition, we need to add a link if the email is sent off-site and we have to expose an externally-visible link to the HTML webdashboard.

The following is done immediately after the definition of the table

<tr>
  <td class="header-title" colspan="2">
    <nobr>
      <a href="http://www.company.com/ccnet/">Real link to web-page</a>
    </nobr>
  </td>
</tr>

A little forther down is that same xsl:if statement again. Here I’ve defined it as follows:

            <xsl:if test="not (/cruisecontrol/build/@error) and not (/cruisecontrol/exception)">
                <tr><td class="header-title" colspan="2">BUILD SUCCESSFUL</td></tr>
                <tr>
                  <td class="header-label">Build output:</td>
                  <td class="header-data"><a href="http://www.company.com/{$webpath}/{$filename}"><xsl:value-of select="$filename"/></a></td>
                </tr>
            </xsl:if>

Lastly, in IIS add a virtual directory named the same as the project, and point it at the project’s Artifacts folder (typically c:\program files\CruiseControl.NET\server\project\Artifacts.

posted @ Wednesday, October 27, 2010 11:00 AM | Feedback (673)

log4net in DLLs

Sometimes the deliverable is a class library and specific logging is included. The configuration file for logging in this case was supposed to be part of the DLL, and stay in the DLL folder, not the executable folder. To get this to work, I did the following:

In the main constructor of the DLL:

   System.Reflection.Assembly ea = System.Reflection.Assembly.GetExecutingAssembly();
   string file = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(ea.Location), "specific.dll.log4net");
   log4net.GlobalContext.Properties["SpecificPath"] = System.IO.Path.GetDirectoryName(ea.Location);
   log4net.Config.XmlConfigurator.ConfigureAndWatch(new System.IO.FileInfo(file));
   log.InfoFormat("Initializing SPECIFIC");

And this is what the specific.dll.log4net file looks like:

<?xml version="1.0" encoding="utf-8" ?>
<log4net>
  <appender name="FileAppender" type="log4net.Appender.FileAppender">
    <file type="log4net.Util.PatternString" value="%property{SpecificPath}\log-file.txt" />
    <appendToFile value="true" />
    <layout type="log4net.Layout.PatternLayout">
      <conversionPattern value="%date [%thread] %-5level %logger [%property{NDC}] - %message%newline" />
    </layout>
  </appender>
  <root>
    <level value="DEBUG" />
    <appender-ref ref="FileAppender" />
  </root>
</log4net>