Android Question RDC returning SQL column name totally different from the SQL

[Anser]

Friends,

I am facing a very peculiar problem. Spent several hours already

From my app, via RDC I am executing the foloowing SQL statement on MySQL Server

SQL

SELECT Transaction_ID AS 'Estimate_No'
      , DATE_FORMAT(Transaction_Date, '%d-%m-%Y') AS 'Estimate_Date'
      , Fright_Amount AS 'Estimate_Amount'
      , Fin_Year
      , Transaction_Date     
     FROM transaction_master

As per the SQL, the Column Name of the 1st column should be "Estimate_No", but RDC is returning the Column name as "Transaction_ID" which is the original column name of the table.

I am getting the correct result when I checked the SQL result on HeidiSQL, Toad and one of my Windows application. Unfortunately, RDC is showing the column name differently, ie it is showing the original column name from the table ie "Transaction_ID" instead of the Column name "Estimate_No" as per the SQL.

The screen snapshots are attached here with.

Screen Snapshot of the SQL and result on HeidiSQL

Screen snapshot from B4A Log reqManager.PrintTable(EstimateRecSet)

Any hint where am I going wrong ?

The problem is only with this particular Column, the rest of the Column names are returned as expected.


Regards
Anser

 

[Erel]

The problem is in RDC. It returns the column name instead of the alias. You will need to do the replacement in your code.

 

[Anser]

Thanks Erel,

Yes, I replaced my code to use the original Column Name.

I was wondering why the problem only with this particular column, If you notice, the rest of the columns in the SQL are returned with the alias name properly.

Regards
Anser

 

[Anser]

Will there be any update in RDC resolving this serious bug ?.

The Following SQL statement is giving weird results from RDC

SELECT Tran.Fin_Year AS 'BillFnYear' \
     , Mast.Fin_Year  AS 'TranIdFnYear' \
     , IF ((Mast.`Status` = 'B' OR Mast.`Status` = 'P'),'Billed','Open') AS BillStatus \
FROM  tran_bills AS Tran  \
   LEFT JOIN jcb_transaction_master AS Mast \
     ON Mast.Call_ID = Tran.Call_ID \
WHERE Tran.Call_ID = ?

The Master Table and the Transaction Table both has a column with the same name (common name) ie "Fin_Year"
I need to identify both the columns separately, so I have to use the "AS" statement to differentiate the column names.

I expect the above query to return 3 columns as given below.

1. BillFnYear
2. TranIdFnYear
3. BillStatus

The result is really weird. The result that I am getting is given below.

1. Fin_Year
2. Nothing, this column itself is missing in the result
3. BillStatus (This one is working even though I used the "AS" clause )

I am nearing the completion of my Project and at this point of time changing the DataBase Access Technology is not all feasible.

Any hint on how to go about it ?

Thanks & Regards

Anser

 

[DonManfred]

Any hint on how to go about it ?

See answer #2

 

[Anser]

What about the missing column ?

Do you mean that I have to change the Table's Column Name ? Column names thru out the database to be unique ?

This table and its columns are accessed by other Desktop and Web applications running for more than 3 years. It is not at all a solution for me.

 

[Anser]

Solved this issue temporarily.

Created a stored procedure and then used the above given SQL statement inside it and then added the result set to a Temporary table and then retrieved the data from the Temporary Table

This solution works.

Regards

Anser

 

[incendio]

Usually I always use stored procedure with RDC.
It gives more control, such as who is the user that fired RDC, when it's happened, etc.

 

[Anser]

I too use Stored Procedures intensively in my Database.

Usually, I don't use Stored Procedures for simple SELECT Queries.

Anyway, I am happy that I found a workaround to resolve this issue.

Regards
Anser

 

[Anser]

For your information.

Till yesterday all I got was either the column name appearing different or column itself missing. This would cause an error on the app and we will come to know about this issue.

Today I have got a very scary result.

Column name returned as expected, but the value in the column totally wrong. As no error is raised here in this situation, we will never come to know unless we manually calculate and compare the result.

For eg.

SUM(Table1.Net_Amount) + IF(@FreightAmount IS NULL,0.00,@FreightAmount) AS 'Grand_Total'

In the above said SQL, I don't have an option to not use an ALIAS
The above SQL statement in all the development tool gives the right figure, but RDC returns a totally wrong figure.

These are very common SQL statement that we use.

Beware when you use Alias name for columns in RDC.

The workaround to resolve this issue is to use the Temporary table solution that I explained in post #7.
I am modifying all my queries/stored procedures in the database that use Column Alias to use Temporary Table. Not an easy task.

Waiting for an RDC version that resolves this issue.

Regards
Anser

 

[incendio]

On my database server Stored procedure has already returns data, no need for temporary table.

I don't know about mysql, but I think it could also returning data from stored procedure.

I use this strategy with RDC
1. Always call stored procedure for select/insert/update operation or
2. Every select/insert/update from RDC write to log table, & there is an application on server side to process this log

 

[Anser]

Dear incendio,

I don't have any other issues with RDC. It is great, efficient and fast.

The ONLY issue that I noticed with RDC so far are as follows.

1. RDC fails when we use ALIAS in Column names.
2. RDC Cannot handle OUT parameters in Stored Procedures.

Regarding the problem number 1 that I mentioned above, suppose if you have a table with the name Bill_Items with the following columns in it
ID
QTY
RATE

and if you try a very simple query as given below, RDC will fail to give proper results

SELECT ID, (QTY * Rate) AS 'Amount' FROM Bill_Items

SELECT RATE AS 'ItemRate' FROM Bill_Items

I haven't tried RDC with any other RDBMS, so no idea how it behaves in MSSQL OR ORACLE. I have used RDC only with MySQL and MariaDB.

Regards
Anser

 

[incendio]

Dear Anser,
1. Since it can't handle alias, i always use stored procedure.
2. Yes, RDC can handle out parameter, i use it many times. But this out parameter handling differently on each RDBMS. I read on this forum, user experience difficulty with out parameter on mysql, but there is a solution, search this forum.

 

[Anser]

Dear Incendio,

1.For me, The ALIAS calls are failing even from the stored procedures. The only solution working for me is catching the SQL queries results to a Temporary table and then make the stored procedure to return the result from the temporary table.

2. I stopped using OUT parameter, instead used the SELECT 0 AS 'Id', 'Everything OK' AS 'MSG' technique. Of course, I adapted/learned this trick from this forum itself.

Regards
Anser

 

[incendio]

Dear Anser,

Btw, why do need column name? You could use column index instead, though the codes are more difficult to read, but it will do the trick.

 

[Anser]

I am not sure whether referring column index would solve the issue that I have mentioned in my post #10 in this thread.

In case of a calculated column even the result/value is wrong.

Anyway I need to test calculated columns without an alias and referring the column with its index. As you rightly said, the code will not be neat and becomes a nightmare for future modifications.

Any idea whether this issue is due to the MySQL driver java files that comes along with the RDC, I mean some files that we download from the Oracle site ? Just a wild guess.

Thanks for your hints.

Regards
Anser

 

[Erel]

Try it with jRDC. It should return the correct column name.