Skip to main content

MS Access: fancy InStr function

I want to compare the installed packages between a couple of Linux servers. Obviously, using a database comes to mind. And my Office Pro includes a pretty good database: MS Access.

The problem

I have tables with a list of Linux packages installed on several servers, one table per server. I want a table with all the packages common to all servers, and for each package (ex: audit-libs-python-1.8-2.el5), the package root (audit-libs-python) and the version (1.8-2.el5).


Step 1: selecting the common packages



SELECT DISTINCT Srv1.Package
FROM Srv1, Srv2, Srv3, Srv4
WHERE Srv1.Package = Srv2.Package
and Srv2.Package = Srv3.Package
and Srv3.Package = Srv4.Package;

Step 2: extracting the package root and version

Package versions are not always written in a similar way. Some examples:
Package                Root        Version
anacron-2.3-45.el5     anacron     2.3-45.el5
aspell-en-6.0-3        aspell-en   6.0-3
bzip2-1.0.3-6.el5_5    bzip2       1.0.3-6.el5_5
db4-4.3.29-10.el5_5.2  db4         4.3.29-10.el5_5.2


However, the version starts with an hyphen (-) followed by a digit (0 to 9). Unfortunately, MS Access doesn't allow the use of Regex (regular expressions).
To overcome this, I replaced all digits by a special character (#) and then searched for the position of "-#"; the substring before this position is the package root, the one after is the version.


SELECT Package,
left(Package,instr(Package,"-")+
 instr(
  Replace(
   Replace(
    Replace(
     Replace(
      Replace(
       Replace(
        Replace(
         Replace(
          Replace(
           Replace(mid(Package,instr(Package,"-")),"9","#"),
          "8","#"),
         "7","#"),
        "6","#"),
       "5","#"),
      "4","#"),
     "3","#"),
    "2","#"),
   "1","#"),
  "0","#"),
 "-#")-2) as PackageRoot,

mid(Package,instr(Package,"-")+
 instr(
  Replace(
   Replace(
    Replace(
     Replace(
      Replace(
       Replace(
        Replace(
         Replace(
          Replace(
           Replace(mid(Package,instr(Package,"-")),"9","#"),
          "8","#"),
         "7","#"),
        "6","#"),
       "5","#"),
      "4","#"),
     "3","#"),
    "2","#"),
   "1","#"),
  "0","#"), "-#")) as PackageVersion

FROM [Common packages];


Sure that writing a VBA function might do the trick as well, but I wanted a SQL version.

Labels:

Comments

Post a Comment

Popular posts from this blog

Drive replacement for Fostex DMT8-vl

The IDE hard drive on my Fostex DMT8-vl multitrack recorder shows signs of its imminent death; when getting hot, I could not record anymore. Must be said this drive comes from an old Sun Station, and has been replaced because I/O failures were detected by Solaris. It worked at least 5 years in my recorder: not so bad. However, time is now to replace it. The DMT8-vl is not able to handle drives bigger than 8.4 GB. Well, it is able to (the current drive is 15 GB), but only 8.4 GB will be usable. My tought was to use a 8 GB CompactFlash; having no moving parts means no noise, which is quite temptating for a music recording device. I purchased a CompactFlash-IDE adapter on the internet (8$) and I had to build a male-male IDE cable adapter (4$). Unfortunately, this doesn't work. The drive is correctly discovered by the operating system, which proposes to format it ("format IDE?"). After answering "yes", the formating runs pretty fast (faster than on a real drive), ...
6 comments
Read more

My journey with ATtiny4313 (part 1)

Introduction The context For a personal project, I want to program an Atmel ATtiny4313 microcontroller. I use microcontrollers for several years, but not on a regular basis (maybe 3-4 times a year), mostly for testing and generally with a deceptive result. But failure isn't the best training? This article aims to share my errors and success, as well as being a personal log. The project The project is to replace the main board of a Soundpool MO4, a MIDI OUT extension for the Atari ST; details here . Overall, the project is pretty simple: reading the parallel port and copy verbatim the data to MIDI out. This is the reason behind my choice of this particular microcontroller, since it embeds an USART and has an 8 bits parallel port (20 pins DIP package). The MO4 having 4 MIDI Outs, I will use 4 ATTiny4313, one for each out; this is actually cheaper than finding a 4-channel USART. Each microcontroller must determine if the data present on the parallel port shoul...
Post a Comment
Read more

Samba: Clients get "system error 1223" (or 123) after a server reboot

Facts: a Linux+Samba server shares anonymously a folder. After a reboot, Win clients could not attach the share drive anymore. C:\>net use \\mylinux\folder Enter the user name for 'mylinux': System error 1223 has occurred. The operation was canceled by the user. C:\>net view \\mylinux\ System error 123 has occurred. The filename, directory name, or volume label syntax is incorrect. The process are present, and tcpdump doesn't provide much information. What's going on? After hours of headscratching, the light came: the firewall was on and no rules for the Samba protocol! Grrr!
2 comments
Read more