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

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

Issue with Soundpool MO4

I have a Atari STe with a Soundpool MO4 MIDI extension. It used to work very well, but unfortunatelly doesn't anymore: Cubase still detects it, and I can output MIDI to it but nothing is coming out from any MIDI Out. It took me a while to tackle it (lack of time, lack of tool, other items to play with), but I gave a glance last week-end. The parallel port on the Atari uses only the following signals: Pin 1 : Strobe (Atari -> MO4) Pin 2 : Data 0 (Atari -> MO4) Pin 3 : Data 1 (Atari -> MO4) Pin 4 : Data 2 (Atari -> MO4) Pin 5 : Data 3 (Atari -> MO4) Pin 6 : Data 4 (Atari -> MO4) Pin 7 : Data 5 (Atari -> MO4) Pin 8 : Data 6 (Atari -> MO4) Pin 9 : Data 7 (Atari -> MO4) Pin 11: Busy (MO4 -> Atari) The MO4 also decodes few other pins, but since the Atari doesn't, my guess is the MO4 was also targeted for PC. Inside the box, the MO4 is architectured around a CPLD (IspLSI1016 from Lattice) which contains the logi...
7 comments
Read more