Monthly Archives: May 2015

ora_te: Road map

Here’s the target for next release. Handle loops and substitute values from cursors. So something like this can be handled.

'merge into {$dest_table} t1
using {$tmp_table} t2
on (t1.{$join_by} = t2.{$join_by} ) 
when matched then 
  update set {%1%t1.?1 = t2.?1%\r\n ,|n|?%}
delete where t2.status_code = ''D''
when not matched then insert( 
  {$dwh_pk}
  , {%2%t1.{{column_name}} = t2.{{column_name}} %,\r\n|w|{{|}}%} 
) values ( 
  {$sequence_name}.nextval 
  , {%2%t2.{$column_name}%,\r\n|w%} 
) 
where t2.status_code <> ''D''';

Quick and dirty editing of clipboard contents with Cygwin

Cygwin is capable of reading and writing windows’ clipboard so one can write quick and dirty formatting scripts with utility of choice. Here’s an example of using perl to format partially formatted SQL. Note reading from clipboard using /dev/clipboard and writing back to it.

$ perl -pe 's/^(\s*)(on|then|,)/\1  \2/ig' /dev/clipboard > /dev/clipboard

Edit
After testing this with real listings I found that getting strings from clipboard in Cygwin is broken sometimes.
First of all sometimes you can’t get string larger than 64K from /dev/clipboard.
May be there’s some flaw with reading text in appropriate encoding from clipboard. May be it’s broken because of my clipboard manager, namely CLCL… I don’t know
One can use getclip utility from cygutils-extra package instead but it has another flaw — it does not support Unicode strings.
The solution. As cygwin can pipe output of standard windows console programs to whatever utility you want. So here’s a short c++ program that gets clipboard contents in Unicode format and outputs them to standard console.

#include <exception>
#include <iostream>
#include <ostream>
#include <stdexcept>
#include <string>
#include <windows.h>
using namespace std;

class RaiiClipboard{
public:
  RaiiClipboard(){
	if (! OpenClipboard(NULL))
	  throw runtime_error("Can't open clipboard.");
	  // ... or define some custom exception class for clipboard errors.
  }

  ~RaiiClipboard(){
	CloseClipboard();
  }
  // Ban copy
private:
  RaiiClipboard(const RaiiClipboard&);
  RaiiClipboard& operator=(const RaiiClipboard&);
};

class RaiiTextGlobalLock
{
public:
  explicit RaiiTextGlobalLock(HANDLE hData): m_hData(hData){
	m_psz = static_cast<const wchar_t*>(GlobalLock(m_hData));
	if (! m_psz)
	  throw runtime_error("Can't acquire lock on clipboard text.");
  }

  ~RaiiTextGlobalLock(){
	GlobalUnlock(m_hData);
  }

  const wchar_t* Get() const {
	return m_psz;
  }

private:
  HANDLE m_hData;
  const wchar_t* m_psz;

  // Ban copy
  RaiiTextGlobalLock(const RaiiTextGlobalLock&);
  RaiiTextGlobalLock& operator=(const RaiiTextGlobalLock&);
};

void PutClipboardText()
{
  RaiiClipboard clipboard;

  HANDLE hData = GetClipboardData(CF_UNICODETEXT);
  if (hData == NULL)
	throw runtime_error("Can't get clipboard text.");

  RaiiTextGlobalLock textGlobalLock(hData);
  const wchar_t* pText = textGlobalLock.Get();

  int bufSize = WideCharToMultiByte(CP_UTF8, 0, pText, -1, NULL, 0, NULL, NULL);
  char *buf = new char[bufSize];

  WideCharToMultiByte(CP_UTF8, 0, pText, -1, buf, bufSize, NULL, NULL);

  cout << buf;
//  wprintf(L"%S", buf); crashes when buffer is big enough
  delete[] buf;
}

void SetClipboardText( std::string& aStr ){
	HGLOBAL hdl = NULL;
	void *ptr = NULL;
	const size_t len = aStr.length() + 1;
	try {
	  hdl = GlobalAlloc( GMEM_MOVEABLE, len );
	  ptr = GlobalLock(hdl);
	  memcpy( ptr, aStr.c_str(), len );
	  GlobalUnlock(hdl);
	  ptr = NULL;
	  RaiiClipboard clipboard;
	  EmptyClipboard();
	  SetClipboardData( CF_TEXT, hdl );
	}
  catch (...) {
	if(ptr)
	  GlobalUnlock(hdl);
	if(hdl)
	  GlobalFree(hdl);
	throw;
  }
}

#pragma argsused
int main(int argc, char* argv[]){
  static const int kExitOk = 0;
  static const int kExitError = 1;

  if ( 3 == argc ) {
	if ( std::string( argv[1] ) == "-s" ){
	  SetClipboardText( std::string( argv[2] ) );
	  return kExitOk;
	}
  }

  SetConsoleOutputCP(CP_UTF8);
  try{
	PutClipboardText();
	return kExitOk;
  }
  catch(const exception& e){
	cerr << "*** ERROR: " << e.what() << endl;
	return kExitError;
  }
}
//---------------------------------------------------------------------------

It was compiled with Borland C++ 2006, but with little effort can be compiled with any other windows c++ compiler.
Now one can use something like this in Cygwin

$ ./getclip.exe | perl -e 'while(<>){ my $s = $_; $s =~ s/</&lt;/g; $s =~ s/>/&gt;/g; $s =~ s/\r\n//g;  print $s;}' > /dev/clipboard

By some quirks it sometimes adds extra carriage returns that’s why an extra s/\r\n//g is put there. Also I’m not sure it will work with all Unicode chars.
Those RAII thing is from this brilliant answer on SO and character conversion is from this SO question
To properly work with unicode in Cygwing terminal I’ve set environment variable
LC_CTYPE to ru_RU.UTF-8 as this mail list suggests.
Happy Cygwinning!
Edit
Now utility supports -s switch to set clipboard to 2nd argument passed (not Unicode text).