Groovy Coolness - How to save files to database

I had a dilemna today. As part of a code upgrade I moved the storing of some template files out of the database and over to cache store, or directly from resource. Before this we had to manually load new templates into database for each release. Gets quite messy... Anyway....

I needed to get all the files back into the database, but needed to do it automatically... Because the table was altered to store the actual file name instead of the file, it makes it quite simple to do... And here comes groovy to the rescue.

With this small amount of code, I was able to read all records in database, get the file associated to that record and store the actual file contents back to the database. Sweet :)

def db=Sql.newInstance("jdbc:db2://192.168.20.9:50000/TESTDB","foo","bar","com.ibm.db2.jcc.DB2Driver")




db.eachRow("SELECT template_name FROM form_version") { row ->

println "Reading file " + row.template_name

def f= new File("d:/testing/resources/$row.template_name")

println "Found $f"

if (f.canRead()) {

def b = f.getText("UTF-8")

db.executeUpdate "UPDATE form_version SET dcs_form_layout=$b WHERE template_name=$row.template_name"

println "All done"

}

else {

println "Cannot read file $f. This could be real bad!!"

}

}

Groovy makes database and file manipulation a real breeze. If I had used java there would be a lot of lines of code. Makes my day so much quicker!

Java - The effects of not closing Input Streams

 We had some peculiar errors with a web application running under Sun Glassfish the other day. If you're running Glassfish on linux, chances are the user process has a limit of 1024 open files. This may sound like a lot, but if you were running a web server, eg Glassfish and multiple applications under it, you could soon run into 'interesting issues' that can appear in any part of the process.   In a real world example, our app started getting broken pipes for SMTP connections (and more nasty issues with Glassfish locking up). Looking into it further it appears a lovely programmer coded the system to read resource files from the file system, but never bothered to close the files. If you read around the Internet pretty much best practice says to always close any file streams that are opened for either reading or writing. 




So I thought I'd do a program to test it out. Here it is....


/**
 * Create a wad of files for testing open files limit on linux
 * args : I = input stream only (is the default)
 *  : O = output stream only
 *  : X = both. Generates output files then does input files from output files
 * @author jamesb
 *
 */
public class InputStreamTest
{

 private static int NUMFILES=5000;
 private static byte[] DATA = "THIS IS A TEST OF FILEINPUT OUTPUTSTREAMS AND THE EFFECTS OF NOT CLOSING THEM".getBytes();

 public static void main(String[] args) {
  
  String whatToDo="I";//I meaning input streams
  boolean closeFiles = false;
  
  if (args != null && args.length > 0)
   whatToDo = args[0].toUpperCase();
  if (args.length> 1) {
   if (args[1].equalsIgnoreCase("Y"))
    closeFiles = true;
  }
  
  InputStreamTest tester = new InputStreamTest();
  
  if (whatToDo.equals("CLEANUP")) {
   tester.cleanup();
   System.exit(0);
  }

  System.out.println("Waiting 10 seconds for you to find the process ID");
  synchronized (tester)
  {
   try {
    tester.wait(10000);
   } catch(InterruptedException e) {
    System.err.println("All over red rover");
   }
  }
  
  
  if (whatToDo.equals("O") || whatToDo.equals("B")) {
   tester.doOutputStreams(closeFiles);
   System.out.println("All files written. Waiting 2 seconds");
   synchronized (tester)
   {
    try {
     tester.wait(2000);
    } catch(InterruptedException e) {
     System.err.println("All over red rover");
    }
   }
  }

  if (whatToDo.equals("I") || whatToDo.equals("B")) {
   tester.doInputStreams(closeFiles);
  }
  
  System.out.println("NOW WAITING 10 MINUTES. GO AND CHECK OPENFILES. YOU HAVE PLENTY OF TIME");
  System.out.println("HINT ON LINUX /usr/sbin/lsof  etc etc etc");
  int TENMINUTES = 1000 * 10 * 60 * 10;
  synchronized (tester)
  {
   try {
    tester.wait(TENMINUTES);
   } catch(InterruptedException e) {
    System.err.println("All over red rover");
   }
  }
  
 }
 
 
 private File[] getFiles() {
  String tmpDir = System.getProperty("java.io.tmpdir");
  System.out.println("java.io.tmpdir=" + tmpDir);
  
  File dir = new File(tmpDir);
  File[] files = dir.listFiles(new FilenameFilter() {
   public boolean accept(File arg0, String arg1)
   {
    if (arg1.startsWith("InputStreamTest_"))
     return true;
    return false;
   }});
  return files;
 }
 
 
 public void cleanup() {
  File[] files = getFiles();
  for (File f : files) {
   System.out.println("Deleting file [" + f.getName() + "]");
   f.delete();
  }
  System.out.println(files.length + " files deleted");
 }
 
 
 public void doOutputStreams(boolean closeFiles) {
  System.out.println("Generating files");
  String tmpDir = System.getProperty("java.io.tmpdir");
  System.out.println("java.io.tmpdir=" + tmpDir);
  long timeStamp = System.currentTimeMillis();
  System.out.println("Timestamp = " + timeStamp);
  for (int i = 0; i <>
   try {
    String fileName = tmpDir + File.separator + "InputStreamTest_" + timeStamp + "_" + i + ".dat";
    System.out.println("Creating file [" + fileName +  "]");
    File file = new File(fileName);
    FileOutputStream os = new FileOutputStream(file);
    os.write(DATA);
    os.flush();
    if (closeFiles) {
     System.out.println("Closing file " + file.getName());
     os.close();
    }
    
   } catch (IOException e) {
    System.err.println("Unable to write file");
    e.printStackTrace(System.err);
    throw new RuntimeException(e);
   }
  }
  System.out.println(NUMFILES + " created");
 }
 
 public void doInputStreams(boolean closeFiles) {
  System.out.println("Reading files");
  
  File[] files = getFiles();
  
  if (files == null || files.length == 0)
   System.out.println("No files to read!!");
  
  byte tmpStore[] = new byte[DATA.length]; 
  for (File f : files)
  {
   try {
    System.out.println("Reading file " + f.getName());
    FileInputStream is = new FileInputStream(f);
    is.read(tmpStore);
    if (closeFiles) {
     System.out.println("Closing file " + f.getName());
     is.close();
    }
    
   } catch (IOException e) {
    System.err.println("Unable to read file");
    e.printStackTrace(System.err);
    throw new RuntimeException(e);
   }
  }
  System.out.println(NUMFILES + " have been read");
  
 }

}

Run it with these options 
> java testing.InputStreamTest B   : This will write 5000 files and then read them, it will not close the files
> java testing.InputStreamTest I   : This will read the 5000 files not closing after each read
> java testing.InputStreamTest O   : This will write 5000 files not closing after each file
> java testing.InputStreamTest B Y : This will read and write the files, closing after each
> java testing.InputStreamTest CLEANUP : Cleanup all the files

Where we don't do the closing of the stream I am able to get the reader side to break almost all the time with the error Too Many Open Files. It is a bit harder under linux and does depend on system speed and how often garbage collection occurs. But it can be broken!
How can one fix linux? Use the ulimit command. Under normal linux this will be set to 1024. Type ulimit -n to see what it is. I'm not sure yet how to increase the ulimit though. Still working on it

Windows 7 and Samba

Good to see Microsoft tweak windows 7 to not work with Samba out of the box.

Follow these steps to get around it

1. Go to Control Panel->System And Security->Administrative Tools
2. Select Local Security Policy
3. Select Local Policies->Security Options
4. Edit Network secruity:LAN Manager authentication level
   - Set to Send LM & NTLM responses
5. Edit Network security : Minium session security for NTLM SSP
   - Uncheck Require 128-bit encryption

Leaky buildings and warm houses

I'm renovating my own house (oops that should say 'our') and its interesting how much I've learnt along the way, from being able to lay a slab to putting purlins on the roof... Whilst doing all of this it did become very clear how easy it is to cut corners, which would invariably lead to water ingress or to a colder house.

What are the biggest mistakes I see with the whole leaky building thing :

1. We live in a rainy country. We need angled roofs and fat soffits!
2. Its an earthquake zone. Cladding needs to reflect that.
3. Never design a roof that pitches into the house, always make natural runoff fall away from the house.
4. Its a windy country. Forget just building wrap+cladding. Put in a solid moisture barrier like RAB or ply over wrap (its an earthquake zone remember) with a batten system followed by cladding.
5. Ventilate the roof. Keep those eaves clear of insulation and, in monopitch design, leave that ventilation gap and either do rough sized nogging between rafters or drill some ventilation holes out to soffits.
6. Ply sarking. Its a great solid barrier to the outside. Keeps cold/hot of tin roof at bay.
7. Lets get New Zealand out of the 'aluminium is king' way of thinking. We have finally moved into double glazing - wow we are now in the 70's at least. Wood really is still king, the debate is still raging over plastic windows - our ozone depletion may affect them adversely yet.... We love our windows and doors, and we should be able to 'have our cake and eat it' in that department if we can get to European standards...
8. Fill the walls with insulation! If you have 6x2 timber, fill the whole width with insulation, not just half of it.

Gosh I prattle.

OpenOffice Macro

I have a tendency to loose these types of things. So posting here instead of some hard drive that fails down track....

This macro goes into the parameters spreadsheet I have setup and iterates through all sheets and generates necessary records to go into new APM database system I'm building.

I use it for storing all database parameters for a system. This becomes very necessary when there are multiple test environments and you want to roll one out very quickly.
I basically would setup one sheet per parameter table and all parameters are contained within that sheet. The column labels relate to the database table column names, and a little flag tells us if the value is char or int type. With > 180 parameter tables, this makes life a breeze!

Global clipText as String

Sub TestIt()
 'insertControlRow
 'BuildAPMSQL
 'generateInsertsForCurrentSheet
 'generateInsertForCurrentRow
 GenerateTableDeleteSQL
 Dim s as string
 Dim t as string
 s="Hello'"
 t=getCharField(s)
 msgbox t
End Sub

Function getCharField(fld$)
 Dim tmp$
 If IsNull(fld$) Or len(fld$)=0 Then
  tmp$=""
 Else
  tmp$=UCase(fld$)
 End If
   If tmp$ = "" Or tmp$ = "NULL" Or tmp$ = "(NULL)" Then
       getCharField = "NULL"
   Else
       getCharField = "'" & makeDBSafe(fld$, "'", "''") & "'"
   End If
End Function

Function getIntField(field)
   Dim tmp As String
   tmp = UCase(field)
   If tmp = "" Or tmp = "NULL" Or tmp = "(NULL)" Then
       getIntField = "NULL"
   Else
       getIntField = field
   End If
End Function

Function makeDBSafe(str$)
 dim a
 Dim c$
 Dim tmp$
 For a=1 to len(str$)
  c$=Mid(str$,a,1)
  If c$="'" Then
   tmp$=tmp$ & "''"
  Else
   tmp$=tmp$ & c$
  End If
 next
 makeDBSafe = tmp$
End Function

Function testMe()
 testMe="Hello"
End Function


Function testCell(cell)
 testCell="Hello" + cell
End Function

Function ConvertBillCodeToCustomerType(fld$)
 dim bcc$
 bcc$=mid(fld$,3,1)
 If bcc$="C" Then
  ConvertBillCodeToCustomerType="NOR"
 ElseIf bcc$="S" Then
  ConvertBillCodeToCustomerType="STF"
 Else
  ConvertBillCodeToCustomerType="NULL"
 End If
End Function

Function ConvertBillCodeToSubproduct(fld$)
 dim ct$
 ct$=left(fld$,1)
 if ct$="S" Then
  ct$="C"
 End If
 ConvertBillCodeToSubproduct="V" + ct$
End Function

function GetSheetName()
  GetSheetName = getCurrentSheet().getName()
end function

function getCurrentSheet()
 getCurrentSheet = ThisComponent.getCurrentController.getActiveSheet
end function

function getCurrentRow()
 oCell = ThisComponent.getCurrentController().getSelection().getCellByPosition(0,0)
 rowInt = oCell.getCellAddress.Row
 getCurrentRow = getCurrentSheet().getRows.getByIndex(rowInt)
end function

Function makeCurrentPath()
 currentPath = ThisComponent.url
 i = len(currentPath)
 For n = Len( currentPath ) To 1 Step -1
  If Mid( currentPath, n, 1 ) = "/" Then Exit For
 Next n
 makeCurrentPath = left(currentPath, n)
End Function

function BuildXMLTableDefinitions()
 'iterate over all sheets
 Dim oSheet As Object
 Dim eSheets As Object
 Dim oRow as Object
 Dim oColumn as Object
 Dim textFile as Object
 
 StatusText "Building XML tables now"

 eSheets = ThisComponent.getSheets.createEnumeration
 f1 = FreeFile()
 currentPath=makeCurrentPath() & "tablebuilder.xml"
 Open currentPath for output as #f1
 print #f1, ""

'Skip the _CTL sheet
While eSheets.hasMoreElements
oSheet = eSheets.nextElement()
'Only non control sheets please :)
if left(oSheet.getName,1) <> "_" Then
 StatusText "Generating Table " & oSheet.getName
 ' here you can work your sheet
 print #f1, "  "
 print #f1, "    "
 for a = 0 to 50
  oCell= oSheet.getCellByPosition(a, 1)
  txt = oCell.String
  if len(txt) > 0 Then
   print #f1, "      " & txt & ""
  Else
   Exit For
  End If
 next
 print #f1, "    "
 for b = 1 to 500
  if len(oSheet.getCellByPosition(0,b).String)=0 Then Exit For
  print #f1, "    "
  for c = 0 to a
   oCell= oSheet.getCellByPosition(c, b)
   txt = oCell.String
   if len(txt) > 0 Then
    print #f1, "      " & xmlSafe(txt) & ""
   Else
    Exit For
   End If
  next
  print #f1, "    "
 next


 print #f1, "  "
End If
Wend
StatusText "Completed..."
print #f1, ""
 close #f1
 msgbox "File is created and is at " & currentPath
end function


function xmlSafe(txt) As String
 If txt="(null)" Then
  xmlSafe = "NULL"
  Exit Function
 End If

 newTxt = ""
 for a=1 to len(txt)
  char1 = mid(txt, a, 1)
  select case char1
  case "<"    newTxt = newTxt & "<"   case "&"    newTxt = newTxt & "&"   case else    newTxt = newTxt  & char1   End Select  Next  xmlSafe = newTxt end function   'Generates all table deletes 'Does it in reverse order to hopefully ensure all data gets deleted Public function GenerateTableDeleteSQL()  Dim oSheet As Object  Dim eSheets As Object  eSheets = ThisComponent.getSheets.createEnumeration  lastNumber = 10  Dim textFile as Object  f1 = FreeFile()  currentPath=makeCurrentPath() & "fulldeletes.sql"  Open currentPath for output as #f1  print #f1, "-- Table Deletion for MAPS WOW Parameter Tables"  print #f1, "-- Written by James Bushell"  print #f1, "-- Automatically generated (insert date here!)"  While eSheets.hasMoreElements   oSheet = eSheets.nextElement()   If left(oSheet.getName,1) <> "_" Then
   orderNumber = oSheet.getCellByPosition(2,0).getString
   if orderNumber="" Then orderNumber = lastNumber
   lastNumber = val(orderNumber) + 1
   print #f1, "DELETE FROM " & oSheet.getName & ";"
  End If
 Wend
 close #f1
 StatusText "File now available at " & currentPath
 Msgbox "File now available at " & currentPath
End function


'Generates ALL SQL for parametermanager
function BuildAPMSQL()
 StatusText "Generating Table/Column definitions for APM"
 'iterate over all sheets
 Dim oSheet As Object
 Dim eSheets As Object
 Dim oRow as Object
 Dim oColumn as Object
 eSheets = ThisComponent.getSheets.createEnumeration
 Dim textFile as Object
 f1 = FreeFile()
 currentPath=makeCurrentPath() & "apmsql.sql"
 Open currentPath for output as #f1
 print #f1, "-- Application Parameter Manager (APM)"
 print #f1, "-- Loader File for MAPS WOW"
 print #f1, "-- Written by James Bushell"
 print #f1, "-- Automatically generated (insert date here!)"
 
 print #f1,""
 print #f1,""
 print #f1,"TRUNCATE TABLE table_data;"
 print #f1,"TRUNCATE TABLE table_columns;"
 print #f1,"TRUNCATE TABLE table_list;"
 print #f1,""
 
 x = 0
 SystemID = 1 ' This is WOWMAPS as from system_names
 'Skip the _CTL sheet
 While eSheets.hasMoreElements
  oSheet = eSheets.nextElement()
  'Only non control sheets please :)
  columnNameList=""  
  if left(oSheet.getName,1) <> "_" Then
   statusText "Generating table " & oSheet.getName
   x = x + 10
   tmp = "INSERT INTO table_list (table_id,system_id, table_name, table_order) VALUES(" & x & "," & SystemID & "," & getCharField(oSheet.getName) & ","
   cellVal = oSheet.getCellByPosition(2, 0).String
   if (cellVal > "") Then
    tmp = tmp & cellVal
   Else
    tmp = tmp & x 'This is the table order. TODO MAKE IT BETTER!!
   End If
   tmp = tmp & ");"
   print #f1, tmp
   
   hdr="INSERT INTO table_columns (table_id,field_type_id,column_name,column_order) VALUES("
   ftr = ");"
   for a = 0 to 50
    oCell= oSheet.getCellByPosition(a, 2)
    txt = oCell.String
    if len(txt) > 0 Then
     if a > 0 Then columnNameList = columnNameList & ","
     columnNameList = columnNameList & txt
     tmp=hdr
     tmp = tmp & x & "," 'table_id generated
     'Now get the control char for type of field
     'C=char=1 in apm db
     'I=Integer=2 in apm db
     oCell = oSheet.getCellByPosition(a, 1)
     fieldType = oCell.String
     if (fieldType="I") Then
      tmp = tmp & "2"
     Else
      tmp = tmp & "1"
     End If
     tmp = tmp & "," & getCharField(txt) & ","
     tmp = tmp & (a+1)
     tmp = tmp & ftr
     print #f1, tmp
    Else
     Exit For
    End If
   next
   
   'Do the data here
   
  End If
 Wend
 close #f1
 StatusText "Completed..."
 msgbox "File is created and is at " & currentPath
end function


rem THis is hardly ever run
private function insertControlRow()
'RUn once code
 'iterate over all sheets
 Dim oSheet As Object
 Dim eSheets As Object
 Dim oRow as Object
 Dim oColumn as Object
 Dim oCell as Object 
 eSheets = ThisComponent.getSheets.createEnumeration
 'Skip the _CTL sheet
 x=10
 While eSheets.hasMoreElements
  oSheet = eSheets.nextElement()
  'Only non control sheets please :)
  columnNameList=""  
  if left(oSheet.getName,1) <> "_" Then
   'Automatically generate the control lines
   if left(oSheet.getCellByPosition(0,0).String,14) <> "LEAVE THIS ROW" Then
    oSheet.getRows().insertByIndex(0,2)
   End If
   oCell = oSheet.getCellByPosition(0,0)
   oCell.String = "LEAVE THIS ROW AND THE NEXT ONE ALONE!!!"
   oCell = oSheet.getCellByPosition(1,0)
   oCell.String = "ORDER"
   oCell.CellBackColor = 10040064
   oCell = oSheet.getCellByPosition(2,0)
   oCell.String = x
   oCell.CellBackColor = 10040064
   x = x + 10
   
   'Now set all the fields to CHAR
   for a = 0 to 50
    oCell= oSheet.getCellByPosition(a, 2)
    txt = oCell.String
    if len(txt) > 0 Then
     if oSheet.getCellByPosition(a,1).String="" Then
      oSheet.getCellByPosition(a,1).String="C"
     end if
    Else
     Exit For
    End If
   next
  end if
 Wend
end function


Rem Builds all of the data into SQL file
Rem ORDER in each sheet (on row 1 col c) is important as is row 2 for C (char) and I (int)
function buildAllDataSQL()
 StatusText "Generating data INSERT sql"
 'iterate over all sheets
 Dim oSheet As Object
 Dim eSheets As Object
 Dim oRow as Object
 Dim oColumn as Object
 eSheets = ThisComponent.getSheets.createEnumeration
 Dim fileSheets(255)
 Dim textFile as Object
 f1 = FreeFile()
 f2 = FreeFile()
 currentPath=makeCurrentPath()
 Open currentPath & "mainsql.sql" for output as #f1
 print #f1, "-- Application Parameter Manager (APM)"
 print #f1, "-- DATA Generation File for MAPS WOW"
 print #f1, "-- Written by James Bushell"
 print #f1, "-- Automatically generated (insert date here!)"
 
 x = 0
 SystemID = 1 ' This is WOWMAPS as from system_names
 'Skip the _CTL sheet
 While eSheets.hasMoreElements
  oSheet = eSheets.nextElement()
  'Only non control sheets please :)
  columnNameList=""
  bRow = true
  iRow = 3
  lastNumber = 10
  if left(oSheet.getName,1) <> "_" Then
   StatusText "Working through table [" & oSheet.getName & "]"
   orderNumber = oSheet.getCellByPosition(2,0).getString
   if orderNumber="" Then orderNumber = lastNumber
   lastNumber = val(orderNumber) + 1
   fileName = currentPath & format(orderNumber,"00000") & "_" & oSheet.getName & ".sql"
   Open fileName for output as #2
   fileSheets(x) = fileName

   'Build all Column Names
   columnNameList = "INSERT INTO " & oSheet.getName & "("
   for a = 0 to 50
    'Get column name
    txt = oSheet.getCellByPosition(a, 2).String
    if len(txt) > 0 Then
     if a > 0 Then
      columnNameList = columnNameList & ","
     End If
     columnNameList = columnNameList & txt
    Else
     Exit For
    End If
   Next
   columnNameList = columnNameList & ") VALUES("

   while bRow = true
    oRow = oSheet.getRows().getByIndex(iRow)
    if (oRow.getCellByPosition(0,0).String = "") Then
     bRow = false
    Else
     StatusText "Table [" + oSheet.getName + "] Parsing Row " & iRow
     rowData = ""
     for a = 0 to 50
      oCell= oRow.getCellByPosition(a, 0)
      txt = oCell.String
      if len(txt) > 0 Then
       if a > 0 Then
        rowData = rowData & ","
       End If
       'Now get the control char for type of field
       'C=char=1 in apm db
       'I=Integer=2 in apm db
       oCell = oSheet.getCellByPosition(a, 1)
       fieldType = oCell.String
       if (fieldType="I") Then
        rowData = rowData & GetIntField(txt)
       Else
        rowData = rowData & GetCharField(txt)
       End If
      Else
       Exit For
      End If
     next
     print #2, columnNameList & rowData & ");"
    End If
    iRow = iRow + 1
   Wend
   close #2
   'Do the data here
   x = x + 1
  End If
 Wend
 
 StatusText "Sorting generated files"
 ShellSort(fileSheets)
 for a = lbound(fileSheets) to ubound(filesheets)
  if (fileSheets(a) > "") Then
   print #f1 ""
   Open fileSheets(a) For Input As #2
   StatusText "Merging " & fileSheets(a)
   Do While NOT EOF(2)
    Line Input #2, entry
    print #f1, entry
   Loop
   Close #2
  End If
 next
 close #f1
 StatusText "Completed..."
 msgbox "File is created and is at " & currentPath & "mainsql.sql"
end function



Private Sub ShellSort(myList())
Dim k1 As Long, k2 As Long, listSize As Long
Dim x1 As Long, isSorted As Boolean
Dim swapping
listSize = UBound(myList()) +1 -LBound(myList())
k1 = Fix(listSize /2)
do while k1 > 0
 k2 = UBound(myList()) - k1
 isSorted = true
 for x1 = LBound(myList()) to k2
   if StrComp(myList(x1), myList(x1 +k1), 0) = 1  then
     swapping = myList(x1)
     myList(x1) = myList(x1 +k1)
     myList(x1 +k1) = swapping
     isSorted = false
   end if
 next
 if isSorted then
   k1 = Fix(k1 /2)
 end if
loop
End Sub


Private Function _makeInsertSQLForSheet(oSheet)
 'Only non control sheets please :)
 columnNameList=""
 bRow = true
 iRow = 3
 numCols = 0
 if left(oSheet.getName,1) <> "_" Then
  orderNumber = oSheet.getCellByPosition(2,0).getString
  if orderNumber="" Then orderNumber = 1
  fileName = makeCurrentPath() & format(orderNumber,"00000") & "_" & oSheet.getName & ".sql"
  Open fileName for output as #2

  'Build all Column Names
  columnNameList = "INSERT INTO " & oSheet.getName & "("
  for a = 0 to 50
   'Get column name
   txt = oSheet.getCellByPosition(a, 2).String
   if len(txt) > 0 Then
    if a > 0 Then
     columnNameList = columnNameList & ","
    End If
    columnNameList = columnNameList & txt
   Else
    Exit For
   End If
  Next
  numCols = (a-1)
  columnNameList = columnNameList & ") VALUES("

  while bRow = true
   StatusText "Parsing Row " & iRow
   oRow = oSheet.getRows().getByIndex(iRow)
   if (oRow.getCellByPosition(0,0).String = "") Then
    bRow = false
   Else
    rowData = ""
    for a = 0 to numCols
     oCell= oRow.getCellByPosition(a, 0)
     txt = oCell.String
     if len(txt) > 0 Then
      if a > 0 Then
       rowData = rowData & ","
      End If
      'Now get the control char for type of field
      'C=char=1 in apm db
      'I=Integer=2 in apm db
      oCell = oSheet.getCellByPosition(a, 1)
      fieldType = oCell.String
      if (fieldType="I") Then
       rowData = rowData & GetIntField(txt)
      Else
       rowData = rowData & GetCharField(txt)
      End If
     Else
      Exit For
     End If
    next
    print #2, columnNameList & rowData & ");"
   End If
   iRow = iRow + 1
  Wend
  close #2
  status = "File now available at " & fileName
  StatusText status
  msgbox status
  
 End If
End Function


Private Function _makeInsertSQLForRow(oSheet, oRow)
 'Only non control sheets please :)
 columnNameList=""
 bRow = true
 iRow = 3
 numCols = 0
 if left(oSheet.getName,1) <> "_" Then
  if (oRow.getCellByPosition(0,0).String = "") Then
   msgbox "Cannot build this row as theres no data on it!!"
  Else
   'Build all Column Names
   columnNameList = "INSERT INTO " & oSheet.getName & "("
   for a = 0 to 50
    'Get column name
    txt = oSheet.getCellByPosition(a, 2).String
    if len(txt) > 0 Then
     if a > 0 Then
      columnNameList = columnNameList & ","
     End If
     columnNameList = columnNameList & txt
    Else
     Exit For
    End If
   Next
   numCols = (a-1)
   columnNameList = columnNameList & ") VALUES("
  
   rowData = ""
   for a = 0 to numCols
    oCell= oRow.getCellByPosition(a, 0)
    txt = oCell.String
    if len(txt) > 0 Then
     if a > 0 Then
      rowData = rowData & ","
     End If
     'Now get the control char for type of field
     'C=char=1 in apm db
     'I=Integer=2 in apm db
     oCell = oSheet.getCellByPosition(a, 1)
     fieldType = oCell.String
     if (fieldType="I") Then
      rowData = rowData & GetIntField(txt)
     Else
      rowData = rowData & GetCharField(txt)
     End If
    Else
     Exit For
    End If
   next
   _makeInsertSQLForRow = columnNameList & rowData & ");"
   StatusText "Row built and available on clipboard"
  End If
 Else
  msgbox "Cannot build row against control sheet!"
 End If
End Function

private Function ProgressBar
 ProgressBar = ThisComponent.CurrentController.StatusIndicator
End Function


REM display text in status bar
Sub StatusText(sInformation as String)
 Dim iLen As Integer
 Dim iRest as Integer

 iLen = Len(sInformation)
 iRest = 270-iLen
 ProgressBar.start(sInformation+SPACE(iRest),0)
End Sub

public Function generateInsertsForCurrentSheet()
 StatusText "Please wait - generating INSERT file"
 _makeInsertSQLForSheet getCurrentSheet()
End Function


public Function generateInsertForCurrentRow()

 startRow = ThisComponent.getCurrentController().getSelection().getRangeAddress().startRow
 endRow = ThisComponent.getCurrentController().getSelection().getRangeAddress().endRow

 Dim fullText, txt
 
 for a = startRow to endRow
  StatusText "Please wait - Building row " & a
  row = getCurrentSheet().getRows.getByIndex(a)
  txt = _makeInsertSQLForRow(getCurrentSheet(), row)
  fullText = fullText & txt & chr(10)
 next
 x = (endRow - startRow) + 1
 msgbox x & " row(s) generated and now available on clipboard"
 TextToClipboard fullText
End Function


Sub TextToClipboard(cText)
 clipText = cText
 oClip = createUnoService ("com.sun.star.datatransfer.clipboard.SystemClipboard")
 oTRX = createUnoListener("TR_", "com.sun.star.datatransfer.XTransferable")
 oClipContents = oClip.setContents(oTRX, null)
End Sub

private Function TR_getTransferData( aFlavor As com.sun.star.datatransfer.DataFlavor ) As Any
 If (aFlavor.MimeType = "text/plain;charset=utf-16") Then
   TR_getTransferData = clipText
 End If
End Function

private Function TR_getTransferDataFlavors() As Any
 Dim aF As New com.sun.star.datatransfer.DataFlavor
 aF.MimeType =    "text/plain;charset=utf-16"
 aF.HumanPresentableName = "Unicode-Text"
 TR_getTransferDataFlavors = Array(aF)
End Function

private Function TR_isDataFlavorSupported( aFlavor As com.sun.star.datatransfer.DataFlavor ) As Boolean
 TR_isDataFlavorSupported = (aFlavor.MimeType = "text/plain;charset=utf-16")
End Function

House Project - First Digging

We hire a digger and bobcat and start the excavations! Digger is a real breeze to operate. Too many years with flight sims and joysticks.

First stage is to remove the soil pushing against the dodgey conservatory. This was one of the reasons the conservatory was moving down the hill as it was being pushed by the soil every time it rained.

Nothing hard there. Just paint a line and dig it up.

Then, its on to tackle the side retaining that is ready to fall over. As its next to a neighbours driveway we could only remove enough soil to halt the wall falling over. Then the funs over and its out with the bobcat to clean up. Thats a fun little implement!










This left us with a huge pile of soil to try and cover over the back section. There was just so much of it! Note for anybody thinking of doing anything like this. Soil 'fluffs up' quite considerably. Expect a large mountain afterwards!

The house project

Its been going for 3 years now and will it ever end. Originally brought the house as a 'do it uper' and had a nice small budget to do that. The project sort of grew and grew and complication after complication....


This is the house as it was when we first brought it back in 2004. 80's hardiplank house. 2 bedroom with a dodgey conservatory tacked onto the side which was falling down.

With a not so nicely placed skyline garage as they had closed in the downstairs part and the garage door was falling down. The house is down a driveway and the turning circle at the bottom meant it was very hard to go into garage forwards and reverse out again.

The Conservatory

This is the conservatory they had tacked on. You can't see it, but the left side is sinking down and was pulling the house down! The garage door no longer worked as not enough head room had been allowed for it. It didnt help that the conservatory had been built right next to dirt bank with no retaining or water protection!

The Side Retaining Wall

A retaining wall at the side of the house. They had dug out a bank to make room for a door to the laundry (no internal access downstairs) and propped up the wall with a few half rounds and sleepers. Would have been fine had they installed drainage and used some full rounds closer together! Still, because its next to a driveway the council would have complained... What didn't help was the water pipe going down neighbours driveway had been leaking for years.

The downstairs laundry

The worst part of the house! This is located inside next to that retaining wall and had been dug out under the house to make enough room for a laundry. It was totally stuffed! Every time it rained the laundry would flood, and behind the wall of the laundry was a disaster as can be seen. The bank had collapsed over the years due to no drainage around the top half of the house.







So we brought the thing at a cheap price and started the renovations.....

Mythtv

I had some spare time over the weekend and cobbled together quite a few older computer parts to make Mythtv box.
Used knoppmyth as its easier to install. Whilst I could go through the manual method of installation, I'm a lazy bugger who prefers the one click approach. Shame on me!

Got it up and running easily except for tv tuner card. What a fiasco that was. To see if things would work I went down to Dick Smith Electronics and brought a cheap tv tuner card (no mpeg encoding - cheap being the word here!) and got it home and hey wow, everything except the card in the box... Wait another day, go back and exchange it and try again. Amazingly (I have no faith in dse products but thats all that is open on weekends around here!) the card was picked up first go and available to mythtv.

Then disaster.... Her indoors decides to play with it for a bit and now it says it cannot connect to backend database! But its running and on same server! Doh doh doh. Oh well.

When does brand new end

You buy an item from the shop and tell everyone 'hey look at my brand new [item name here]'. At what point does is this brand new item no longer deemed brand new. One day, one week, one year?
I have a brand new pair of rollerblades. I've never used them and they are getting near 2 years old. Brand new until used once maybe. Or twice. Or is it a personal preference...

Climate change

When the scientists finally come out and say "Oops we forgot to mention that global warming is exponential and is not linear" we'll all be roasting in 50 degree (c) temperatures, thats if massive weather storms don't get us first. 500km/h winds. Probably not a problem in the not too distant future.
Perhaps there really is a future for nuclear energy. It can even run our cars (electric). Now to convince the US that they would not be in control of waste reprocessing and that most countries already do this securely.

The biggest killer to our environment right now is down to just one person. And that person is...... George dubya Bush. Cheers George for being the biggest tit in history. Perhaps you can go down as the man who killed the planet

The Olympics

Its on again and all the non believers thought it would be a shambles. Good on ya Greece, all running to clockwork.
I reckon they should have a drugfree and a drug allowed olympics. It would sure be interesting to compare results! Imaging the guys lining up for 100m sprints! And womans weighlifting. eek. I doubt these people would have much of a life after competing though. They'd either have heart failure or their liver/kidneys would collapse!
But its all about television and viewer appeal isn't it! I'm sure more merchandise would be sold, and more people would watch.......

Civil war in America

I was recently asked about John Titor. I knew nothing of him until I googled him. Interesting theories. I don't really follow the notions of black holes and time travel. My belief is that black holes are a bit like a blender, at a very large scale with blades so fine they work at an atomic level. And if a time traveller did come back, he would know dates/times of many things. Heck he could tell us what a share price would be on any day!

Not knowing much about civil wars and how they start, I wonder how such a war would/could start in a democratic economy. I thought mass unemployment would be a key motivator for such to happen, which causes breakdown of the economy. There is no mention of that.

The talk of having a police state etc that breaks down the fabric of American society would surely take years to occur, and wouldn't there be checks and balances to stop such. With the patriot act, MPAA tactics, Guantanamo Bay and barbed wire free speech zones it does make me wonder if there is a breakdown already occuring. People cannot even invent anymore for fear of patent violation!

Its probably a fair bet that outright anarchy from within America would have to be next. But from which sector of society? The techies have been so busy creating things and using their minds, if they get bored, what woudl they get up to? Whilst the perceived and real threat is from offshore, what is being done at a social level to stop the internal breakdown?

Am I correct in the assumption that in American elections, there are but 2 opposing forces, Republicans and Democrates? Is there no allowance for forming an independant political party and running for office? Having but 2 allowed parties does not seem overall very democratic, in colution with each other its fairly well a fasict state.
Perhaps a good idea for the future to stop any possible turmoil would be to have open election processes where any person can form a party to challenge for seats which make up a government. It would certainly slow down progress for some things, but may help in others. It does seem the peoples voice has become smaller in America.

Starforce Copy Protection

They're at it again.... A new copy protection to piss off the users even more. When are they going to give up. And now it seems all manner of device drivers are installed just to play the game. I say no thanks, so its a sale lost! They make it almost impossibly hard just to play a game now!

Whats the best way to fight back? Buy the game, open the packet, then return it to the store and say it won't install on your machine! There are so many nasties to WWII installed, I wouldn't be surprised if it didn't install anyway!

Browser wars

So much bad press about Internet Explorer.
I used to be a die hard Windows freak. All development on Windows machines, a good little MSDN subsriber forking out $5K a year...... Then things changed.....
Fast forward. No more Visual Basic, no more MSDN. Its all JAVA/J2EE and with MONO now out, hopefully C#
And what about the browser? I used to like coding specifically for IE as it was nice and easy. But then the w3c standards caught up and other browsers got significantly better while IE humbled along.
I don't use IE anymore. I'm now on Mozilla Firefox, its a dang great browser and with the full Google toolbar now available, who needs anything else! Its free. Its opensource. Its regularly developed.
What does Firefox it have over IE?
1) Tabbed browsing
2) w3c standards based
3) Open source. No hidden gotchas. We still don't know some of the hidden Windows API's!
4) No virus problems yet.
5) If a patch is required, I bet it will come out quicker than IE. IE is too bolted into the operating system to allow rapid patch fixing.
6) Themes (aka skins). Who wants to have the same looking app as everyone else
7) Extensions. A thriving community of them.
8) Javascript console that tells me where the errors are located!
9) Works on Linux/MAC/Windows.

IE IS LEGACY

If you want better safer browsing, download Mozilla Firefox now! If Thunderbirad had an integrated calendar it would kill Outlook.

Blogging

Just what is this blogging all about. Only entering one will allow me to find out!