Android Question RDC - SQL Query throwing error

[Pravin Shah]

I am a newbie to this forum and trying some of the libs for one of my client application. I am using Remote Database Connector (RDC) to connect to MSSQL database. I am facing following problems

- I want fill listview with records by executing following query. As soon as the user logs in, I am calling this function on Activity_Create function

Activity.LoadLayout("AllWorkOrders")
    If FirstTime Then
        FillWorkOrders
    End If

Sub FillWorkOrders()
    Dim cmd As DBCommand
    Dim Result As String
    cmd.Initialize
    cmd.Name = "select_allworkorders"
    'cmd.Parameters = Array As Object(Main.CustID)
    Main.reqManager.ExecuteQuery(cmd, 0, "allworkorders")
End Sub

I am executing following query which is written in config.properties file

SELECT FinalProduct, SUM(PendingBadri) as status, SUM(QTY) as QTY ,SUM(SQM) as SQM , M.JCNumber,M.JCNo,M.JCDate
        FROM JC_Main M INNER JOIN JC_Sizes S ON M.JCNumber=S.JCNumber AND M.JCYear=S.JCYear
        INNER JOIN JC_Specs SP ON SP.JCNumber=M.JCNumber AND SP.JCYear=M.JCYear
        WHERE M.IsCancel=0 AND CustomerID= 1 GROUP BY FinalProduct, CustomerID, IsCancel,M.JCNumber,M.JCNo,M.JCDate order by M.JCDate desc

On execution of the query, I am getting following error

** Activity (allworkorders) Create, isFirst = true **
** Activity (allworkorders) Resume **
java.sql.SQLException: Invalid column name 'FinalProduct'.
** Activity (allworkorders) Pause, UserClosed = false **
** Activity (main) Create, isFirst = false **
sending message to waiting queue (CallSubDelayed - JobDone)

I have run the same query MS SQL server management studio but it is executing properly and gives correct results. But thru RDC, it is not working. Please help me to resolve this issue, I am not able to find what is going wrong.

Also after throwing above error, it is calling Activity Main instead of stopping in the allworkorders activity.

Appreciate your help!

 

[Reviewnow]

Your using derived table names, so in theory you should also use derivedTable.Columname when selecting columns

example
select M.FinalProduct from JC_Main M
group by M.FinalProduct

 

[Pravin Shah]

I am sorry I tried that but it is not working, it throws error. Is there any other solution?

I really need help. Thanks in advance

 

[Erel]

RDC doesn't do anything special with the query. It passes it to the database server using the JDBC driver.

Run some tests with simpler queries to understand what causes it to fail.

 

[Pravin Shah]

Thanks Erel, yes I am trying the same now. I will split the query and see if I can get the same results with trial and error

Taking this opportunity to THANK YOU for providing such wonderful tool.. this is really amazing.. there are endless ideas which one can develop in much faster way using this tool..

 

[Pravin Shah]

It was a stupid mistake.. I have written query in SQL manager to test and then copied as it is in config.properties file. The query was formatted by pressing enter key for readability. The notepad considers that as broken and did not consider next line as part of query. After removal, the query is working fine.

I am writing this solution in case any one face similar issues and also taking care of above while adding queries.