Six Stars

Database SQL Result Set to Context Variable

Hi,

I'd like to output the result from a SQL query into something like a context variable so it can be used later in the job.

Does anyone know how to do this? I'm thinking the context variable type object but I'm not sure how to set that up.

Ideas?

Thanks!

Croix

  • Data Integration
1 REPLY
Eleven Stars

Re: Database SQL Result Set to Context Variable

This problem intrigued me, so I thought I would give it a go. If you look at the code that is generated by Talend you will see that for every "row" there is a class created with a name that is made up of the name of row followed by "Struct". This is a public class which means you can use it. Using this information meant that solving this was actually pretty simple. The job structure looks like below. I didn't use a database input, instead I used a tRowGenerator. This doesn't matter since it will behave in the same way.

job.jpg

The tRowGenerator is made up of 3 columns (newColumn, newColumn1 and newColumn2) all Strings with dummy data. This is connected to a tJavaFlex where the main body of the code is. Before we look at this, you need to see the row1Struct class that is created by Talend. This is below....

public static class row1Struct implements
			routines.system.IPersistableRow<row1Struct> {
		final static byte[] commonByteArrayLock_LOCAL_Test12 = new byte[0];
		static byte[] commonByteArray_LOCAL_Test12 = new byte[0];

		public String newColumn;

		public String getNewColumn() {
			return this.newColumn;
		}

		public String newColumn1;

		public String getNewColumn1() {
			return this.newColumn1;
		}

		public String newColumn2;

		public String getNewColumn2() {
			return this.newColumn2;
		}

		private String readString(ObjectInputStream dis) throws IOException {
			String strReturn = null;
			int length = 0;
			length = dis.readInt();
			if (length == -1) {
				strReturn = null;
			} else {
				if (length > commonByteArray_LOCAL_Test12.length) {
					if (length < 1024
							&& commonByteArray_LOCAL_Test12.length == 0) {
						commonByteArray_LOCAL_Test12 = new byte[1024];
					} else {
						commonByteArray_LOCAL_Test12 = new byte[2 * length];
					}
				}
				dis.readFully(commonByteArray_LOCAL_Test12, 0, length);
				strReturn = new String(commonByteArray_LOCAL_Test12, 0, length,
						utf8Charset);
			}
			return strReturn;
		}

		private void writeString(String str, ObjectOutputStream dos)
				throws IOException {
			if (str == null) {
				dos.writeInt(-1);
			} else {
				byte[] byteArray = str.getBytes(utf8Charset);
				dos.writeInt(byteArray.length);
				dos.write(byteArray);
			}
		}

		public void readData(ObjectInputStream dis) {

			synchronized (commonByteArrayLock_LOCAL_Test12) {

				try {

					int length = 0;

					this.newColumn = readString(dis);

					this.newColumn1 = readString(dis);

					this.newColumn2 = readString(dis);

				} catch (IOException e) {
					throw new RuntimeException(e);

				}

			}

		}

		public void writeData(ObjectOutputStream dos) {
			try {

				// String

				writeString(this.newColumn, dos);

				// String

				writeString(this.newColumn1, dos);

				// String

				writeString(this.newColumn2, dos);

			} catch (IOException e) {
				throw new RuntimeException(e);
			}

		}

		public String toString() {

			StringBuilder sb = new StringBuilder();
			sb.append(super.toString());
			sb.append("[");
			sb.append("newColumn=" + newColumn);
			sb.append(",newColumn1=" + newColumn1);
			sb.append(",newColumn2=" + newColumn2);
			sb.append("]");

			return sb.toString();
		}

		public String toLogString() {
			StringBuilder sb = new StringBuilder();

			if (newColumn == null) {
				sb.append("<null>");
			} else {
				sb.append(newColumn);
			}

			sb.append("|");

			if (newColumn1 == null) {
				sb.append("<null>");
			} else {
				sb.append(newColumn1);
			}

			sb.append("|");

			if (newColumn2 == null) {
				sb.append("<null>");
			} else {
				sb.append(newColumn2);
			}

			sb.append("|");

			return sb.toString();
		}

		/**
		 * Compare keys
		 */
		public int compareTo(row1Struct other) {

			int returnValue = -1;

			return returnValue;
		}

		private int checkNullsAndCompare(Object object1, Object object2) {
			int returnValue = 0;
			if (object1 instanceof Comparable && object2 instanceof Comparable) {
				returnValue = ((Comparable) object1).compareTo(object2);
			} else if (object1 != null && object2 != null) {
				returnValue = compareStrings(object1.toString(),
						object2.toString());
			} else if (object1 == null && object2 != null) {
				returnValue = 1;
			} else if (object1 != null && object2 == null) {
				returnValue = -1;
			} else {
				returnValue = 0;
			}

			return returnValue;
		}

		private int compareStrings(String string1, String string2) {
			return string1.compareTo(string2);
		}

	}

You don't need to worry about this, you will simply use this class in order to get hold of the data and cast an Object context to it's correct type. The code in the tJavaFlex is as below....

Spoiler
context.row = row1;

This is simply setting the context.row Object context variable to the row1 object (an instance of row1Struct).

I then connect the tJavaFlex to a tJava via an iterate link. This is done to let us iterate through the rows and to use the context.row variable to hold the state and use it in the tJava. The code in the tJava is below....

 

row1Struct row = (row1Struct)context.row;

System.out.println(row.newColumn+"|"+row.newColumn1+"|"+row.newColumn2);

The first thing we do here is create an object called "row" of class "row1Struct" and then set it to the value held by the context.row variable. Notice its type is cast to "row1Struct".

After that, we are done. We can get the column values as we would in any tJava component by using the row.columnname format. This is shown in the System.out call I make below.

When running the code prints out something like below....

Dwight|Buchanan|Salem
Thomas|Harrison|Jefferson City
James|Kennedy|Columbus
Bill|Van Buren|Jefferson City
Millard|Ford|Harrisburg
Calvin|McKinley|Pierre
Rutherford|Lincoln|Springfield
Theodore|Ford|Baton Rouge
James|Adams|Annapolis
William|Monroe|Topeka
Herbert|Roosevelt|Frankfort
Thomas|Truman|Phoenix
John|Eisenhower|Saint Paul
Calvin|Fillmore|Juneau
Andrew|Cleveland|Bismarck
Herbert|McKinley|Des Moines
Millard|Roosevelt|Hartford


I hope this helps :-)

Rilhia Solutions