Sunday, October 30, 2011

Pentaho Data Integration: Tip to import double properly from Ms Access

For some reason, floating point numbers (Double) became corrupted when you import then from Microsft Access using PDI on countries where the decimal character is comma. The workaround to fix that is explicitly declare a dot on the metadata for each column where you get Type = "Number"

More information can be found on this forum thread:
http://forums.pentaho.com/showthread.php?84480-Bug-Error-when-importing-double-from-a-Microsoft-Access-Database

Sunday, August 28, 2011

jEdit macro: encode and decode selection in mime64

Save the code bellow as Base64.bsh on "...jedit/macros" folder
To execute, just select the text and go macros -> Base64
The script will try to figure out if the selection is already encoded and perform the apropriate action

		/**
		 * Encode/decode selected text into/from base64
		 *
		 * To get this script running, you need to drop the commons-codec-x.x.jar file from:
		 * http://commons.apache.org/codec/download_codec.cgi
		 * into your "...jedit/jars" folder
		 *
		 * @author Marcelo Gennari
		 * @version 1.0.0
		 *
		 */

		import org.apache.commons.codec.binary.Base64;

		void debugLog(String msg) {
			Log.log(Log.ERROR, BeanShell.class, "MacroDebug: " + msg);
		}

		/**
		 * Determine if the string is base64 encoded
		 *
		 * Detection method: It must have >3 different consonants in sequence and
		 * none of non-base64 chars
		 *
		 */
		boolean isBase64Encoded(String str) {
			String base64chars = "BCDFGHJKLMNOPQRSTVWXYZbcdfghjklmnopqrstvwxyz";
			String base64nonchars = " !#$%&()*,-.;@_¨"; // if has one of this is considered non-encoded
			int consecutiveChar = 0, maxConsecutiveChar = 3; // >3 different consonants in sequence is considered encoded
			char lastChar;
			boolean rtn = false;

			char[] charArray = str.toCharArray();
			for (i=0; i < charArray.length; i++) {
				String caractere = Character.toString(charArray[i]);
				if (base64chars.indexOf(caractere) >= 0) {
					if (charArray[i] != lastChar)
						consecutiveChar++;
					if (consecutiveChar > maxConsecutiveChar) {
						rtn = true;
					}
				} else {
					if (base64nonchars.indexOf(caractere) >= 0) {
						rtn = false;
						break;
					}
					consecutiveChar = 0; // reset counter
				}
				lastChar = charArray[i];
			}

			return rtn;
		}


		////////////////////////////////////////////////////////////////////////////////
		//                              "main"                                        //
		////////////////////////////////////////////////////////////////////////////////
		if(buffer.isReadOnly())
			Macros.error(view, "Buffer is read-only.");
		else{
			String selectedText = textArea.getSelectedText();
			if (selectedText != null) {
				if (isBase64Encoded(selectedText)){
					textArea.setSelectedText(new String(Base64.decodeBase64(selectedText)));
				} else {
					textArea.setSelectedText(Base64.encodeBase64String(selectedText.getBytes()));
				}
			}
		}
	

Saturday, August 6, 2011

How to attach java api documentation into eclipse

This tutorial is intended to integrate the java api doc into eclipse for those work in a non-internet environment or just want to have the api locally.

Download the documentation: http://www.oracle.com/technetwork/java/javase/downloads/index.html


Open Eclipse and go: Window > Preferences > Java > Installed JREs > Edit

Attach the .zip file:



Repeat for all these: resources.jar, rt.jar, jsse.jar, jce.jar and charsets.jar
Finish > OK


Just pass mouse over or open the object-options menu to see the api documentation integrated into your code.


Monday, August 2, 2010

jEdit text editor: Reasons why you should try it

Recently I give a try on the jEdit text editor with some concerns about performance on account of the fact is being a Java application. That feeling was gone away in the first minutes of use. It's a stable, lightweight and full-featured application. After some days working with it I realized it has became my editor of choice (followed by Notepad++). There is a lot of reasons for that and some of them is gonna be pointed out:

- Multi Platform: Works on Windows, Mac, Linux and any other java compatible OS.
- Portable: Run direct on external hdd. All settings are kept in .XML and .properties files in a sub folder of your choice.
- The most powerful Search and Replace available in a text editor: Full Regex specification with Bean Shell scripting capabilities for back references. Example: Let's say that you need apply a increment function on every number found you your text (replace 1 by 2, 10 by 11 and so on). Just search for (\d+) and replace by Integer.parseInt(_1) + 1 Java expression. This example was only a small scratch on the surface of the possibilities that it brings to you.
- Themable: For people who prefer working with the eye-comfort-black-background on a reverse color scheme
- Expandable: Plugins and more plugins (hundreds of them)
- Macros: Record and program/debug your repetitive tasks with the powerful beanshell scripting
- Open Standard: Everything (including the macro language) can be done with Java!
- BeanShell: The default macro language for Java. Why you should use it: It is a real Java command interpreter. So, if you know java, you already know bean shell, if you don't know Java, learn bean shell and gain Java basics acknowledge "for free". It is the official Java Scripting language, see JSR 274.
- Keyboard shortcuts: Every single command (including macros) can easily be attached/unattached to any key combinations in a simple and clever interface.
- Customizing: Nearly everything. The most flexible and expandable text editor I've found so far.
- Word Letter: Personalize which chars will be considered a regular letter. Eg: 2010-08-01 can be entire selected by a double-click if you add "-" to "Extra Word Characters" option
- Rectangular selection (Ctrl + Mouse Drag). In this mode, multiple lines can be written simultaneously as you type.
- Line Wrapping: 3 modes, respecting the indentation of previous line.
- Mixed language support: Eg: If you work with an .html file with has js and css embedded, each region will be properly colorized.
- Language customization: If one of the 150 modes (c, c++, Java, html, XML, etc) don't fits you. Don't worry, just create your own definition in a XML-based mode file (and share with the community). The syntax for XML mode file is very well documented.
- Screen split: Limitless. Work with 2, 4, 10...N regions with different files on the same window.
- Word occurrence: See all occurrences of the highlighted word in a side gutter as you get on eclipse or netbeans IDEs (faster than both). It works even with ordinary txt files. (highlight plugin). One of the main use for it is quickly find variables among several texts with just one mouse click.
- Database: Just select your SQL statement, press a button and get the resultset from mysql, mssql, oracle, postgree and any other jdbc compatible rdbms. Export to csv, txt, etc. Browse and navigate on your database schema on the file browser pane. (SQL plugin)
- FTP: Edit directly your ftp-hosted files. Browse remote directories.
- HTML/XHTML/XML: jEdit caches the dtd validation file and use it to provide auto-complete and wizard.
- css: Get a graphical interface to construct and validade the style files.
- Spell Check: Choose among 3 engines: Aspell, Hunspell (OpenOffice and Mozilla) or Voxspell

But nothing is perfect and it has some small drawbacks. It get a bit slow on dealing with big files and you need a long command line to start it

Besides the quantity of functionalities, the quality of them is noticeable. Always when you dig deeper, you get a good surprise.

My command line to start jEdit is:

D:\Portables\Java\jdk\bin\javaw.exe -Xmx768m -jar "D:\Portables\jEdit\jedit.jar" -norestore -reuseview -background -settings="D:\Portables\jEdit\settings"

Where D:\Portables (as you may already figured out), can be a External HDD or a synchronized-with local folder.

Screenshot:

Tuesday, February 16, 2010

Autohotkey - Sending ALL chars correctly by Send or SendPlay command

Send and SendPlay has issues with sending some "special chars" properly. So I've done a simple function that "Safetize" your string to be send correctly. Hope be useful!
To beginners: Just put code below in a text file with a .ahk extension. Open it with autohotkey and press Ctrl+0

  ;##############################################################################
  ;Some chars are not sent properly by "Send" or "SendPlay" command.
  ;Use this function to get around this issue
  ;Version 1.0.2 Marcelo Gennari
  ;##############################################################################
  SafeSend(string) {
   local varStringOut := ""
   Loop, parse, string
   {
    If (InStr("!#+^{}/", A_LoopField, True) > 0) {
     If (A_LoopField = "/")
      varStringOut := varStringOut . "{ASC 47}" ; "/" has as special action
     else
      varStringOut := varStringOut . "{" . A_LoopField . "}"
    } else {
     varStringOut := varStringOut . A_LoopField
    }
   }
   return, %varStringOut%
  }

  ;Test SafeSend (Ctrl+0)
  ^0::
   varStringToBeSend := "!""#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVW"
   varStringToBeSend .= "XYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~€‚ƒ„…†‡ˆ‰Š‹ŒŽ"
   varStringToBeSend .= "‘’“”•–—˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇ"
   varStringToBeSend .= "ÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ"
   run, notepad
   WinWaitActive
   varSafeStringToBeSend := SafeSend(varStringToBeSend)
   SendPlay, {Space 3}With SafeSend: %varSafeStringToBeSend%
   SendPlay, {Enter 2}
   SendPlay, Without SafeSend: %varStringToBeSend%
  Return
 

Sunday, February 14, 2010

Count the number of occurrences of one string inside another

I believe this is the smallest and fastest function you will find over there
It behaves like an inStr extension, of where it's based on.

  '###############################################################################
  'Count the number of occurrences of one string inside another
  'Optional parameters:
  ' optCompare: 0=vbBinaryCompare (default) or 1=vbTextCompare
  ' start: Start position where search will begin, defaults to 1.
  'Version 1.0.0 100214 Marcelo Gennari
  '###############################################################################
  Public Function inStrCount(haystack As String, needle As String, _
    Optional optCompare As Integer = 0, Optional start As Long = 1) As Long
   Dim lastFound As Long
   Dim lenNeedle As Integer
   Dim rtn As Long
   If haystack = "" Then Exit Function
   lenNeedle = Len(needle)
   lastFound = InStr(start, haystack, needle, optCompare)
   Do While lastFound > 0
    rtn = rtn + 1
    lastFound = InStr(lastFound + lenNeedle, haystack, needle, optCompare)
   Loop
   inStrCount = rtn
  End Function


  Public Sub inStrCountTest()
   Debug.Print "Result x Expected = " & inStrCount("aca", "a") & " x 2"
   Debug.Print "Result x Expected = " & inStrCount("aaa", "a") & " x 3"
   Debug.Print "Result x Expected = " & inStrCount("aaccaaa", "aa") & " x 2"
   Debug.Print "Result x Expected = " & inStrCount("aaccaaaa", "aa") & " x 3"
   Debug.Print "Result x Expected = " & inStrCount("aaa", "aaaa") & " x 0"
  End Sub
 

Wednesday, September 2, 2009

JBoss5 security based on MySQL

Create database called jboss_db_security wity the follow structure:

-- Generated by MySQLWorkbench SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL'; CREATE SCHEMA IF NOT EXISTS `jboss_db_security` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; USE `jboss_db_security`; -- ----------------------------------------------------- -- Table `jboss_db_security`.`usuario` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `jboss_db_security`.`usuario` ( `id` INT NOT NULL AUTO_INCREMENT , `user` VARCHAR(45) NOT NULL , `pass` VARCHAR(45) NOT NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `jboss_db_security`.`role` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `jboss_db_security`.`role` ( `id` INT NOT NULL AUTO_INCREMENT , `role` VARCHAR(45) NOT NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `jboss_db_security`.`usuario_role` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `jboss_db_security`.`usuario_role` ( `usuario_id` INT NOT NULL , `role_id` INT NOT NULL , PRIMARY KEY (`usuario_id`, `role_id`) , INDEX `fk_usuario_role_usuario` (`usuario_id` ASC) , INDEX `fk_usuario_role_role` (`role_id` ASC) , CONSTRAINT `fk_usuario_role_usuario` FOREIGN KEY (`usuario_id` ) REFERENCES `jboss_db_security`.`usuario` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_usuario_role_role` FOREIGN KEY (`role_id` ) REFERENCES `jboss_db_security`.`role` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION); USE `jboss_db_security`; -- ----------------------------------------------------- -- Data for table `jboss_db_security`.`usuario` -- ----------------------------------------------------- SET AUTOCOMMIT=0; INSERT INTO `usuario` (`id`, `user`, `pass`) VALUES (1, 'user1', 'pass1'); INSERT INTO `usuario` (`id`, `user`, `pass`) VALUES (2, 'user2', 'pass2'); COMMIT; -- ----------------------------------------------------- -- Data for table `jboss_db_security`.`role` -- ----------------------------------------------------- SET AUTOCOMMIT=0; INSERT INTO `role` (`id`, `role`) VALUES (1, 'administrador'); INSERT INTO `role` (`id`, `role`) VALUES (2, 'superusuario'); INSERT INTO `role` (`id`, `role`) VALUES (3, 'convidado'); COMMIT; -- ----------------------------------------------------- -- Data for table `jboss_db_security`.`usuario_role` -- ----------------------------------------------------- SET AUTOCOMMIT=0; INSERT INTO `usuario_role` (`usuario_id`, `role_id`) VALUES (1, 1); INSERT INTO `usuario_role` (`usuario_id`, `role_id`) VALUES (1, 2); INSERT INTO `usuario_role` (`usuario_id`, `role_id`) VALUES (2, 3); COMMIT; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;



Drop mysql-connector-java-5.1.6-bin.jar on folder: ${JBOSS_HOME}\server\default\lib\
Where ${JBOSS_HOME} is your JBoss root folder


Create file ${JBOSS_HOME}\server\default\deploy\mysql-ds.xml
With the following contents

<?xml version="1.0" encoding="UTF-8"?> <datasources> <local-tx-datasource> <jndi-name>MySqlDS</jndi-name> <connection-url>jdbc:mysql://127.0.0.1:3306/jboss_db_security</connection-url> <driver-class>com.mysql.jdbc.Driver</driver-class> <user-name>root</user-name> <password>fiap</password> <valid-connection-checker-class-name> org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker </valid-connection-checker-class-name> <metadata> <type-mapping>mySQL</type-mapping> </metadata> </local-tx-datasource> </datasources>



Testing the JDBC connection
Create a new web project Eg: test_jdbc and create the file index.jsp
With the following contents:

<%@page contentType="text/html" import="java.util.*,javax.naming.*,javax.sql.DataSource,java.sql.*"%> <% DataSource ds = null; Connection con = null; PreparedStatement pr = null; InitialContext ic; try { ic = new InitialContext(); ds = (DataSource) ic.lookup("java:/MySqlDS"); con = ds.getConnection(); pr = con.prepareStatement("SELECT USER, PASS FROM USUARIO"); ResultSet rs = pr.executeQuery(); while (rs.next()) { out.println("<br> " + rs.getString("USER") + " | " + rs.getString("PASS")); } rs.close(); pr.close(); } catch (Exception e) { out.println("Exception thrown " + e); } finally { if (con != null) { con.close(); } } %>

Deploy it on JBoss and open on your browser: http://localhost:8080/test_jdbc

You should get:
user1 | user1
user2 | pass2



Edit the file: ${JBOSS_HOME}\server\default\conf\login-config.xml and add the following node:

<application-policy name="test-policy"> <authentication> <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag="required"> <module-option name="dsJndiName">java:/MySqlDS</module-option> <module-option name="principalsQuery"> SELECT pass FROM usuario WHERE user = ? </module-option> <module-option name="rolesQuery"> SELECT r.role, 'Roles' FROM usuario_role ur INNER JOIN role r ON ur.role_id = r.id INNER JOIN usuario u ON ur.usuario_id = u.id WHERE u.user = ? </module-option> </login-module> </authentication> </application-policy>



References:

http://www.jboss.org/community/wiki/DatabaseServerLoginModule
JBoss5_Installation_And_Getting_Started_Guide.pdf