RE: [las_users] LAS V7 : Unable to make intermediate netCDF file.

Roland,

This one is as bad as it gets, eh? The data is being written using Python’s struct.pack. I’m able to read using it Python’s MySQLdb and struct modules; however, Java’s DataInputStream returns garbage. Included are the two test scripts/Java code.

Python code:

import MySQLdb

import struct

conn = MySQLdb.connect("localhost", "user", "passwd", "argo2")

cursor = conn.cursor(MySQLdb.cursors.DictCursor)

sql = "SELECT profile_id,t,x,y,pres,pres_qc,psal,psal_qc,temp,temp_qc FROM profiles WHERE (temp<99999.0 OR temp>99999.0) AND ((x>=-180.0) AND (x <=179.0)) AND ((y>=-90) AND (y<=89)) AND ((pres>=0) AND (pres<=5000)) AND ((t>=349069.0) AND (t<=349069.0));"

cursor.execute(sql)

result_set = cursor.fetchall()

for row in result_set:

col = row["pres"]

print col

i=0

j=4

length = col.buffer_info()[1] * col.itemsize

while (length):

print struct.unpack('=f', col[i:j].tostring())

i +=4

j +=4

length -=4

print "Number of rows returned: %d" % cursor.rowcount

cursor.close()

conn.close()

Java code:

import java.io.*;

public class argo2_test {

public static void main (String args[]) throws Exception

{

java.sql.Connection conn;

java.sql.ResultSetMetaData meta;

java.sql.Statement stmt;

java.sql.ResultSet result;

int i;

System.out.println("JDBC sample application starts...");

System.out.println("Application tries to register the driver.");

// this is the recommended way for registering Drivers

java.sql.Driver d =

(java.sql.Driver)Class.forName("com.mysql.jdbc.Driver").newInstance();

System.out.println("Driver succesfully registered.");

// the user is asked for a connect string

System.out.println(

"Now sample application needs a connectstring in format:\n"

);

System.out.println(

"jdbc:mysql://<host>:<port>/<database>?<user name>=value&<password>=value\n"

);

System.out.print("\nPlease enter the connect string >");

//BufferedReader reader =

// new BufferedReader(new InputStreamReader(System.in));

//String sCon = reader.readLine();

String sCon = "jdbc:mysql://localhost:3306/argo2?user=user&password=passwd";

// next, the connection is attempted

System.out.println("Attempting to connect :" + sCon);

conn = java.sql.DriverManager.getConnection(sCon);

System.out.println("Driver succesfully connected.");

String sQuery = "SELECT profile_id,t,x,y,pres,pres_qc,psal,psal_qc,temp,temp_qc FROM profiles WHERE (temp<99999.0 OR temp>99999.0) AND ((x>=-180.0) AND (x <=179.0)) AND ((y>=-90) AND (y<=89)) AND ((pres>=0) AND (pres<=5000)) AND ((t>=349069.0) AND (t<=349069.0));";

stmt= conn.createStatement();

result = stmt.executeQuery(sQuery);

System.out.println("Query executed and result set obtained.");

// we get a metadataobject containing information about the

// obtained result set

System.out.println("Obtaining metadata information.");

meta = result.getMetaData();

int cols = meta.getColumnCount();

System.out.println("Metadata information for columns is as follows:");

// we dump the column information about the result set

for (i=1; i <= cols; i++)

{

System.out.println("Column i:"+i+" "+meta.getColumnName(i)+ "," +

meta.getColumnType(i) + "," + meta.getColumnTypeName(i) + "," +

meta.getColumnDisplaySize(i));

}

// and finally, we dump the result set

System.out.println("Starting to dump result set.");

int cnt = 1;

while(result.next())

{

System.out.print("\nRow "+cnt+" : ");

for (i=1; i <= cols; i++) {

switch (i) {

case 5:

try

{

java.sql.Blob blob = result.getBlob(i);

long blen = blob.length();

DataInputStream distream = new DataInputStream(result.getBinaryStream(i));

System.out.println("Blob length: "+blen);

while (blen>0) {

float presVal = distream.readFloat();

System.out.println("pres value: "+presVal);

blen -= 4;

}

} catch (Exception ex) { ex.printStackTrace();}

break;

}

System.out.print(result.getString(i)+"\t");

}

cnt++;

}

stmt.close();

conn.close();

// and not it is all over

System.out.println("\nResult set dumped. Sample application finishes.");

}

Efren A. Serra (Contractor)
DeVine Consulting, Inc.
Naval Research Laboratory
Marine Meteorology Division
7 Grace Hopper Ave., STOP 2
Monterey, CA 93943
Code 7542
Office: 831-656-4650

From: Roland Schweitzer [mailto:Roland.Schweitzer@xxxxxxxx]
Sent: Tuesday, November 03, 2009 11:45 AM
To: Serra, Mr. Efren, Contractor, Code 7542
Cc: oar.pmel.las_users@xxxxxxxx
Subject: Re: [las_users] LAS V7 : Unable to make intermediate netCDF file.

Efren,

Unfortunately, I cannot read these data. However, looking at the schema it appears that the data you want to write to the netCDF file is stored in a mySQL "blob". The database backend service does not know how to deal with blobs. It expects the data variables to be one of the mySQL numeric data types. Is it possible to represent these data as a numeric data type?

Roland

Serra, Mr. Efren, Contractor, Code 7542 wrote:

Roland,

That’s progress, I thought. Here’s describe profiles_bak (t is supposed to be a multivalued key):

mysql> describe profiles_bak;

+----------------------+-----------------+------+-----+---------+-------+

+----------------------+-----------------+------+-----+---------+-------+

| t | double unsigned | YES | | NULL | |

| x | float(6,3) | | | 0.000 | |

| y | float(5,3) | | | 0.000 | |

| zlo | smallint(6) | | | 0 | |

| zhi | smallint(6) | | | 11000 | |

| dac | char(2) | YES | | NULL | |

+----------------------+-----------------+------+-----+---------+-------+

23 rows in set (0.00 sec)

mysql>

mysql> SELECT pres,y,x,t,temp FROM profiles_bak WHERE (temp<99999.0 OR temp>99999.0) AND ((x>=-180.0) AND (x <=179.0)) AND ((y>=-90) AND (y<=89)) AND ((pres>=0) AND

(pres<=5000)) AND ((t>=349069.0) AND (t<=349069.0));

| 33�@33AfffA33�A33�ff�BffB | -29.946 | 35.265 | 349069 | ��A��A��A\��A�n�Aj�A��A�E�Ad;�A?5�A'1�A-�A��A��A��A��AZ�A�A�ƤA�A+�A�C�Aw��A��A��Ash�A�A�A��A�7��A��AZ�A�ĔAb�A��A��A��A��A�l�AZ�AV�AT��t�A�ȅAJ

[...]