Bug? jSQL CreateCallStatement freezes program

[MrKim]

I have a function in SQL Server that has a SMALLINT parameter.
The SQL Looks Like this:
TKLogout 134273, 1, 0, '', -1
This is the line of code that freezes:

Dim S As Object = sql1.CreateCallStatement(SQLStr, Null)  'FAILED (ACTUALLY IT HUNG FOREVER) with a negative number

This is the first line of the SQL Stored Procedure SQL:

CREATE PROCEDURE [dbo].[TKLogout] @TKID AS INT, @QtyComplete AS INT, @Tk_DefectiveQty AS INT, @DefectNote AS NVARCHAR(MAX), @OpComplete as SMALLINT

My Android program Hangs completely and forever on the CreateCallStatement line. If I change the -1 to a 1 or a 0 it works fine. Which actually works for me, the -1 resulted from a boolean whcih should have been converted to a 1 anyway, but I wanted to report the issue.

It did NOT happen on the ExecCall line. An error message would be OK but it completely freezes the program.

If you think this is a jdbc driver issue let me know and I will report it to the proper authorities.

Thanks

 

[Erel]

Note that the library name is JdbcSQL. Not jSQL.

Have you checked the unfiltered logs for any errors?

 

[MrKim]

Note that the library name is JdbcSQL. Not jSQL.

Have you checked the unfiltered logs for any errors?

I am still using JSQL, I notice you just released JdbcSQL, I will switch and see if it has the same issue.

 

[MrKim]

Note that the library name is JdbcSQL. Not jSQL.

Have you checked the unfiltered logs for any errors?

After about a minute Android says program not responding do you want to close it and the following is written to the unfiltered log.

Thread[2,tid=26712,WaitingInMainSignalCatcherLoop,Thread*=0xaec20000,peer=0x12c8f0a0,"Signal Catcher"]: reacting to signal 3
Wrote stack traces to '/data/anr/traces.txt

NOTE: This is with JSQL. I will now switch to JdbcSQL.

 

[OliverA]

This may not have anything with to do jSQL/JdbcSQL, but the way information is processed with our around these technologies. Look at this stackoverlflow answer to a "WaitingInMainSignalCatcherLoop" for some clues.

 

[MrKim]

After switching to JdbcSQL I ran it twice once with a break on the offending line, once without. Both times I waited over 5 minutes. The program never responded, no error message was generated in the unfiltered log, and Android never said 'not responding' with an offer to close the program.
When I had the break on the line and hit F8 to proceed the message on the screen "Program paused on line XXX" wasn't even removed.

 

[OliverA]

sql1.CreateCallStatement(SQLStr, Null)

Why are you passing Null for the Args? Your args are

134273, 1, 0, '', -1

See: https://www.b4x.com/b4j/help/jsql.html#sql_createcallstatement
See: https://programmaticponderings.com/...erver-stored-procedures-with-java-using-jdbc/

Looks like it should be something like this

Dim SQLStr as String = "{call TKLogout(?,?,?,?,?)}"
dim callStmt as Object = sql1.CreateCallStatement(SQLStr, Array as Object(134273, 1, 0, '', -1))
Dim rs as ResultSet
rs = sql1.ExecCall(callStmt)

Note 1: This is untested code and may have typos/logic/semantic flaws.
Note 2: No experience in calling stored procedures with JDBC. I'm just going by what's on the web. This may get you in the right direction hopefully.
Note 3: TKLogout may be dbo.TKLogout or something similar (may)

 

[MrKim]

Why are you passing Null for the Args? Your args are


See: https://www.b4x.com/b4j/help/jsql.html#sql_createcallstatement
See: https://programmaticponderings.com/...erver-stored-procedures-with-java-using-jdbc/

Looks like it should be something like this

Dim SQLStr as String = "{call TKLogout(?,?,?,?,?)}"
dim callStmt as Object = sql1.CreateCallStatement(SQLStr, Array as Object(134273, 1, 0, '', -1))
Dim rs as ResultSet
rs = sql1.ExecCall(callStmt)

Note 1: This is untested code and may have typos/logic/semantic flaws.
Note 2: No experience in calling stored procedures with JDBC. I'm just going by what's on the web. This may get you in the right direction hopefully.
Note 3: TKLogout may be dbo.TKLogout or something similar (may)

Thanks for your help. The SQL is in my first Post. I should have put it in the code section. Everything works with a positive value the negative completely hangs. I have it working, I don't need negative numbers here, just reporting the bug. Things should not hang completely.

 

[OliverA]

It still may, just may, not be a bug.

1) Looking at your stored procedure definition, the last parameter is defined as a SMALLINT. According to Microsoft documentation (source), SMALLINT is a 2 byte data type.
2) You pass in its place a -1 (or 1) and the GetType method in B4J returns java.lang.Integer. This is just a wrapper for Java's primitive integer type (source). According to Oracle's documentation (source) that is a 32bit (4 byte) value.
3) Looking at the source of jSQL (here), CreateCallStatement uses the setObject method of Java's PreparedStatement Interface (here). The method setObject is a generic version of the more detailed set* methods of PreparedStatement. So setObject sees an java.lang.Integer object, which then translates to a equivalent setInt method. And that may be the problem, since technically, this needs to be a call to setShort, since Java's primitive type short is a 16bit (2 byte) value, the same as SMALLINT.

In other words, I think the wrong type of parameter is being passed on to your stored procedure, but it's not really a bug in B4J/Java. So why don't you create a short variable in B4J, assign that variable the value you want to pass and see what happens.

' Just some code fragments
Dim OpComplete as Short
OpComplete = -1
sql1.CreateCallStatement(SQLStr, Array as Object(134273, 1, 0, '', OpComplete))

 

[MrKim]

It still may, just may, not be a bug.

1) Looking at your stored procedure definition, the last parameter is defined as a SMALLINT. According to Microsoft documentation (source), SMALLINT is a 2 byte data type.
2) You pass in its place a -1 (or 1) and the GetType method in B4J returns java.lang.Integer. This is just a wrapper for Java's primitive integer type (source). According to Oracle's documentation (source) that is a 32bit (4 byte) value.
3) Looking at the source of jSQL (here), CreateCallStatement uses the setObject method of Java's PreparedStatement Interface (here). The method setObject is a generic version of the more detailed set* methods of PreparedStatement. So setObject sees an java.lang.Integer object, which then translates to a equivalent setInt method. And that may be the problem, since technically, this needs to be a call to setShort, since Java's primitive type short is a 16bit (2 byte) value, the same as SMALLINT.

In other words, I think the wrong type of parameter is being passed on to your stored procedure, but it's not really a bug in B4J/Java. So why don't you create a short variable in B4J, assign that variable the value you want to pass and see what happens.

' Just some code fragments
Dim OpComplete as Short
OpComplete = -1
sql1.CreateCallStatement(SQLStr, Array as Object(134273, 1, 0, '', OpComplete))

Oliver, thanks again for your help. IF you look at my first post you will see that the problem, for me is resolved , I was passing -1 by mistake. All I am really passing is a 0 or a 1 so my situation is fine.

I am just trying to resolve the larger problem of the system completely freezing when the wrong value is passed. It seems to me an error should be thrown and control returned to the program. I would like to report the issue to whatever entity should know about this. Honestly, I know very little about Java, nor do I have time to learn (wish I did). If this is by design, fine, if not, then I would like to report it to the keepers of the code so it can be fixed.

 

[OliverA]

I understand, it's just there is no way of knowing if this is something that needs fixing on the B4A/B4J end. The error "WaitingInMainSignalCatcherLoop" just means something is blocking the app from processing messages and therefore it is killed. "CreateCallStatement" is a blocking statement, and therefore could cause this issue. But we don't really know why and probably have at least 3 scenarios:

1) Something is wrong with CreateCallStatement
2) The stored procedure you are calling is expecting a SMALLINT, but you are passing an INT
3) Something inside the stored procedure itself could be acting up when passed a negative value for the last item

So we have at least 2 out of 3 cases that have nothing to do with a bug in JdbsSQL/jSQL nor the underlying JDBC.

To eliminate #2, all that needs to be done is to actually pass a -1 that has been declared as a SHORT. This has nothing to do with Java and can be easily done in B4A without resorting to Java.

To eliminate #3 you would have to execute the stored procedure via another mechanism (SQL Studio?) and see what it does with a -1 as the last parameter. At minimum I would probably do this and give myself the piece of mind the the stored procedure works properly and does not have a bug in it. Would that not be worth your time?

 

[MrKim]

I understand, it's just there is no way of knowing if this is something that needs fixing on the B4A/B4J end. The error "WaitingInMainSignalCatcherLoop" just means something is blocking the app from processing messages and therefore it is killed. "CreateCallStatement" is a blocking statement, and therefore could cause this issue. But we don't really know why and probably have at least 3 scenarios:

1) Something is wrong with CreateCallStatement
2) The stored procedure you are calling is expecting a SMALLINT, but you are passing an INT
3) Something inside the stored procedure itself could be acting up when passed a negative value for the last item

So we have at least 2 out of 3 cases that have nothing to do with a bug in JdbsSQL/jSQL nor the underlying JDBC.

To eliminate #2, all that needs to be done is to actually pass a -1 that has been declared as a SHORT. This has nothing to do with Java and can be easily done in B4A without resorting to Java.

To eliminate #3 you would have to execute the stored procedure via another mechanism (SQL Studio?) and see what it does with a -1 as the last parameter. At minimum I would probably do this and give myself the piece of mind the the stored procedure works properly and does not have a bug in it. Would that not be worth your time?

So here is my question:
I can tell you that -1 is, in fact invalid. The it is going into is actually a Bit (SQL doesn't have Boolean) so -1 would throw an error. But my SQL function does, in fact have error checking and would return an error if run. But run doesn't happen until sql1.ExecCall() so why would CreateCallStatement fail because of an invalid value?

 

[MrKim]

So here is my question:
I can tell you that -1 is, in fact invalid. The it is going into is actually a Bit (SQL doesn't have Boolean) so -1 would throw an error. But my SQL function does, in fact have error checking and would return an error if run. But run doesn't happen until sql1.ExecCall() so why would CreateCallStatement fail because of an invalid value?

On reflection, you triggered something for me that I need to experiment with. I usually do not use the parameters because it is just easier for me to format the entire string. The result is I am passing a string. Perhaps, CreateCallStatement using parameters does some additional formatting based on the data types of the parameter.

 

[OliverA]

On reflection, you triggered something for me that I need to experiment with. I usually do not use the parameters because it is just easier for me to format the entire string. The result is I am passing a string. Perhaps, CreateCallStatement using parameters does some additional formatting based on the data types of the parameter.

Oh, I thought you were doing that, using parameters (after I posted about them). BTW, the single quotes in my CreateCallStatement(s) examples above need to be double quotes (single ones do not work). I'll keep checking what I can (since now I know I'm looking at submitting a single string will all parameters) find.

TKLogout 134273, 1, 0, '', -1

Is this (the quoted above) the actual content, as is, that you pass on as the SQL statement?

 

[MrKim]

Oh, I thought you were doing that, using parameters (after I posted about them). BTW, the single quotes in my CreateCallStatement(s) examples above need to be double quotes (single ones do not work). I'll keep checking what I can (since now I know I'm looking at submitting a single string will all parameters) find.

Is this (the quoted above) the actual content, as is, that you pass on as the SQL statement?

Yes, and it works fine with 0 and 1 but not -1.

 

[OliverA]

What version of SQL server are you using? What JDBC driver and version?

 

[MrKim]

What version of SQL server are you using? What JDBC driver and version?

#AdditionalJar: jtds-1.3.1.jar
sql server 2012 with all service packs