Calling remote sybase query from Java to Unix machine

veryangryman

Hi all,

I run the following command local to a Unix machine


printf "Use db_1_1\ngo\nSelect count(*) from our_table where text = 'uno'\ngo\nexit"| /opt/sybase/sybase/OCS-15_7/bin/isql -Usa -Psybase11


This works ok for me. It returns a count as per the query.

However, my task is to call this command remotely. My code is like this


public String buildSQLCommand(String sybaseUserName, String sybasePassword, String problemText){
String unixCommandTowardsSQL='printf "'
String useDBCommand="Use fmadb_1_1\\ngo\\n"
String sqlQuery = "Select * from FMA_alarm_log where Problem_text = '" + problemText + "'\\ngo\\nexit"
String loginToSybaseCommand = '"| /opt/sybase/sybase/OCS-15_7/bin/isql -U' + sybaseUserName + " -P" + sybasePassword
System.out.println(problemText);
return unixCommandTowardsSQL + useDBCommand + sqlQuery + loginToSybaseCommand
}

public String fetchRelevantAlarmsFromSybase(){
String cmd = buildSQLCommand("sa", "sybase11", "uno")
sshRemoteCommandExecutor.execute(cmd)
System.out.println("Output: " + sshRemoteCommandExecutor.getStdOut())
return sshRemoteCommandExecutor.getStdOut()
}


Anyway, ive checked and it definitely uses the exact comand word for word. However it gives strange output on the unix machine

An error occurred when attempting to allocate localization-related structures.

Im not sure why this is; ive ruled out issues with what shell the sshRemoteCommandExecutor object uses and the length of the command. Can anyone advise what might be the problem or why the strange output.

Ive googled the error, but it mentions about sybase settings being the problem. Cant see that being the issue; the command works with a manual input. Thanks if can help

ChRoMe

veryangryman wrote: »

Hi all,

I run the following command local to a Unix machine


printf "Use db_1_1\ngo\nSelect count(*) from our_table where text = 'uno'\ngo\nexit"| /opt/sybase/sybase/OCS-15_7/bin/isql -Usa -Psybase11


This works ok for me. It returns a count as per the query.

However, my task is to call this command remotely. My code is like this


public String buildSQLCommand(String sybaseUserName, String sybasePassword, String problemText){
String unixCommandTowardsSQL='printf "'
String useDBCommand="Use fmadb_1_1\\ngo\\n"
String sqlQuery = "Select * from FMA_alarm_log where Problem_text = '" + problemText + "'\\ngo\\nexit"
String loginToSybaseCommand = '"| /opt/sybase/sybase/OCS-15_7/bin/isql -U' + sybaseUserName + " -P" + sybasePassword
System.out.println(problemText);
return unixCommandTowardsSQL + useDBCommand + sqlQuery + loginToSybaseCommand
}

public String fetchRelevantAlarmsFromSybase(){
String cmd = buildSQLCommand("sa", "sybase11", "uno")
sshRemoteCommandExecutor.execute(cmd)
System.out.println("Output: " + sshRemoteCommandExecutor.getStdOut())
return sshRemoteCommandExecutor.getStdOut()
}


Anyway, ive checked and it definitely uses the exact comand word for word. However it gives strange output on the unix machine

An error occurred when attempting to allocate localization-related structures.

Im not sure why this is; ive ruled out issues with what shell the sshRemoteCommandExecutor object uses and the length of the command. Can anyone advise what might be the problem or why the strange output.

Ive googled the error, but it mentions about sybase settings being the problem. Cant see that being the issue; the command works with a manual input. Thanks if can help

Why not use an actual JDBC link? That code is a mess.

veryangryman

ChRoMe wrote: »

Why not use an actual JDBC link? That code is a mess.

Wouldnt work with our systems, they have an option to harden the security settings so that only certain connection types/password combinations work (so im told)

As for the messy code, my apologies. Its just the way the command has to be built up. Long story short, it has to be run as if one was on the Unix server itself running the sybase command.

To go back to the question, is there any obvious reason why sybase doesnt like the format of the query?

ChRoMe

veryangryman wrote: »

Wouldnt work with our systems, they have an option to harden the security settings so that only certain connection types/password combinations work (so im told)

As for the messy code, my apologies. Its just the way the command has to be built up. Long story short, it has to be run as if one was on the Unix server itself running the sybase command.

To go back to the question, is there any obvious reason why sybase doesnt like the format of the query?

You are creating an ssh tunnel, you can just run JDBC through that. Taking that approach is far more secure then the swiss cheese setup you have now.

Also "it has to be run as if one was on the Unix server itself running the sybase command." doesn't make any sense.

Is there a senior dev there you can talk to? You don't appear to understand the environment you are operating in.

srsly78

Use the unix "expect" tool, too easy.

http://en.wikipedia.org/wiki/Expect

Procasinator

You should probably heed advice the others have given in this thread and the other related one.

That said, it seems to me your SSH session does not have the appropriate environment variables set that isql expects.

This guy seems to be doing something similar to you:
http://litletester.blogspot.ie/2012/04/running-sql-commands-on-sybase-from.html

You probably just need to set LC_ALL like he did. That, and as said here, make sure the SYSBASE variable is set.

The last post in this discussion may help:
http://database.ittoolbox.com/groups/technical-functional/sybase-l/sybase-installation-on-unix-platform-750706