B4J Question WebApi - Error

[DarkoT]

Hi,
need little help. I created small desktop App based on @aeric solution WebApi which allows to create dynamically web api based on sql query-ies (some of desktop applications still not using web api for exchange data and is very difficult to connect directly to database). If database admin in "house" know the data structure and is familiar with a sql language, there can simple write query and system will create web api with this query. System cover two type of DB - MS SqL server and MySql. In any case - I have one stupid problem and cannot find it. If I run app in debug mode (and stop app with breakpoints) the system works perfectly, but when I run without braking points, the system send error - I cannot find it...

Error:
(NullPointerException) java.lang.NullPointerException

Sub SelectFromSql(Query As String, DbType As String) As HttpResponseMessage
    #region Documentation
    ' #Desc1 = Get a category by id
    ' #Desc2 = List all categories
    ' #Elems = 2
    #End region
    Dim List1 As List
    List1.Initialize
    
    Try     
        Dim rs As ResultSet

        ' database type
        If DbType.ToUpperCase = "MSSQL" Then rs = MsSql.ExecQuery2(Query, Null)
        If DbType.ToUpperCase = "MYSQL" Then rs = MySql.ExecQuery2(Query, Null)
        
        ' for all records in returned set
        Do While rs.NextRow
            Dim Map2 As Map
            Map2.Initialize
            For i = 0 To rs.ColumnCount - 1
                Map2.Put(rs.GetColumnName(i), rs.GetString(rs.GetColumnName(i)))
            Next
            ' generiramo list
            List1.Add(Map2)
        Loop
        rs.Close
        
    
        If List1.Size > 0 Then
            Rm.ResponseCode = 200
            Rm.ResponseData = List1
        Else
            Rm.ResponseCode = 404
            Rm.ResponseError = "ERP data for select not found..."
        End If
            
    Catch
        LogError(LastException)
        Rm.ResponseCode = 422
        Rm.ResponseError = "Error Execute Query"
    
    End Try
    
    Return Rm         
End Sub

Public Sub ReturnHttpResponse (mess As HttpResponseMessage, resp As ServletResponse)
    If mess.ResponseCode >= 200 And mess.ResponseCode < 300 Then ' SUCCESS
        If mess.ResponseString = "" Or mess.ResponseString = Null Then mess.ResponseString = "ok"
        If mess.ResponseMessage = "" Then mess.ResponseMessage = "Success"
        mess.ResponseError = Null
    Else ' ERROR
        If mess.ResponseCode = 0 Then mess.ResponseCode = 400
        If mess.ResponseString = "" Or mess.ResponseString = Null Then mess.ResponseString = "error"
        'If mess.ResponseMessage = "" Then mess.ResponseMessage = "Bad Request"
        If mess.ResponseError = "" Then mess.ResponseError = "Bad Request"
        mess.ResponseMessage = Null
    End If
    If mess.ContentType = "" Then mess.ContentType = CONTENT_TYPE_JSON
    If mess.ResponseData.IsInitialized = False Then mess.ResponseData.Initialize   
    ' Override Status Code
    If mess.ResponseCode < 200 Or  mess.ResponseCode > 299 Then
        resp.Status = 200
    Else
        resp.Status = mess.ResponseCode
    End If
    resp.ContentType = mess.ContentType
    Dim Map1 As Map = CreateMap("s": mess.ResponseString, "a": mess.ResponseCode, "r": mess.ResponseData, "m": mess.ResponseMessage, "e": mess.ResponseError)
    resp.Write(Map2Json(Map1))
End Sub

I assume there is somewhere inside of Sub ReturnHttpResponse error... But I can not find it - because the system work's correct when I run debug mode...
If have somebody can take a look I can share full project...

Thank you...
Darko

 

[ilan]

this doesnot look correct to me:

MsSql.ExecQuery2(Query, Null)

if you use ExecQuery2 you need to pass the values

it should look like this:

dim id as int = 1
MySql.ExecQuery2("SELECT * FROM table1 WHERE id = ?", Array as Int(id))

 

[DarkoT]

this doesnot look correct to me:

MsSql.ExecQuery2(Query, Null)

if you use ExecQuery2 you need to pass the values

it should look like this:

dim id as int = 1
MySql.ExecQuery2("SELECT * FROM table1 WHERE id = ?", Array as Int(id))

Hi ilan,
no, this is not the problem... I changed mysql.ExecQuery2 to mySqlExecQuery (difference is that second function not accept any parameters)... And I still have the same error...

 

[ilan]

post your query

 

[ilan]

also remove the try catch block and post the FULL error log

 

[DarkoT]

post your query

also remove the try catch block and post the FULL error log

The query is really dummy - just simple select from ms sql table: select * from d_s_01 order by ds01naziv desc;

and in log (after I removed try catch) is still same message:

But - when I run this code with breakpoint and run over all rutines with F8 - it's running perfectly without any errors... Strange..

 

[DarkoT]

The query is really dummy - just simple select from ms sql table: select * from d_s_01 order by ds01naziv desc;

and in log (after I removed try catch) is still same message:

View attachment 134302

But - when I run this code with breakpoint and run over all rutines with F8 - it's running perfectly without any errors... Strange..

Mybe is the problem that system returns results "to slow" - how should I wait that I really received all records in recordset from server? It's posible to use Wait for routine inside of function which returns HttpResponseMessage? Maybe is here the problem...

 

[ilan]

it is hard to tell like this. if you can create a simple project where the error can be reproduced than upload it and i will have a look

 

[DarkoT]

it is hard to tell like this. if you can create a simple project where the error can be reproduced than upload it and i will have a look

No problem, I can give you full project but you will need to change some data for Ms Sql connection and for MySql Connection. Can I send you whole project to private msg?

 

[ilan]

Mybe is the problem that system returns results "to slow" - how should I wait that I really received all records in recordset from server? It's posible to use Wait for routine inside of function which returns HttpResponseMessage? Maybe is here the problem...

you have ExecQueryAsync but again it is hard to understand what you are doing. i need to see the whole code

 

[ilan]

No problem, I can give you full project but you will need to change some data for Ms Sql connection and for MySql Connection. Can I send you whole project to private msg?

it would be a very good practice to always use a sqlite version when you build such a Project. like this it is easier for debuging.
i am working right now on a big Rest Api Server and i use sqlite and mysql inside and just use the b4x conditional symbols to switch between them
if you can create sqlite version it would be possible for me to check

 

[DarkoT]

it would be a very good practice to always use a sqlite version when you build such a Project. like this it is easier for debuging.
i am working right now on a big Rest Api Server and i use sqlite and mysql inside and just use the b4x conditional symbols to switch between them
if you can create sqlite version it would be possible for me to check

Okey, I will... I will send you ASAP... Thank you...

 

[aeric]

I suspect the problem is when initializing the database connection object. Maybe due to wrong password.

 

[DarkoT]

I suspect the problem is when initializing the database connection object. Maybe due to wrong password.

No, no, is not the problem with password; keep in mind that the system works perfect in debug mode when I stop running with breakpoint and left run forward after few seconds...

 

[aeric]

When you commented the Try-Catch, where is the line having the error?

 

[DarkoT]

When you commented the Try-Catch, where is the line having the error?

aeric, wait - I will give you a code (whole project), just I need to convert this to sqllite, that you can test...

 

[aeric]

aeric, wait - I will give you a code (whole project), just I need to convert this to sqllite, that you can test...

alright. I may also install a mssql server for test. Just it will take up 10GB of my hdd.

Spoiler: Download

 

[aeric]

Try add this inside SelectFromSql sub.

Rm.Initialize

 

[DarkoT]

Try add this inside SelectFromSql sub.

Rm.Initialize

That it's!!!

Thank you for help... This is solution and works perfectly... Thank you for all...

 

[aeric]

Thanks for using my template.