0

Creating a DB connection and executing a query

SELECT DISTINCT USERNAME AS TEST, SEC_OBJ_CODE AS TEST1 ,SEC_OBJ_MEMBER_VAL, DML_MODE FROM SEC_SECURE_BY_OBJ_MEMBER_USER_TMP;

Now from the java class, the objective is to create a temporary table with the same column and the below code helps to achieve it but in the output, it prints with column names "TEST" and "TEST1" but the expectation is to get - "USERNAME" and "SEC_OBJ_CODE" as mentioned in the query.

Java class:

PreparedStatement ps = connection.prepareStatement("SELECT DISTINCT USERNAME AS TEST, SEC_OBJ_CODE AS TEST1 ,SEC_OBJ_MEMBER_VAL, DML_MODE FROM SEC_SECURE_BY_OBJ_MEMBER_USER_TMP");
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            String tableName = null;
            StringBuilder sb = new StringBuilder( 1024 );
            if ( columnCount > 0 ) {
                sb.append( "Create table " ).append("ML_TEMP").append( rsmd.getTableName( 1 ) ).append( " ( " );
            }
            // for columns
            for ( int i = 1; i <= columnCount; i ++ ) {
                if ( i > 1 ) sb.append( ", " );
                String columnName = rsmd.getColumnLabel( i );
                String columnType = rsmd.getColumnTypeName( i );

                sb.append( columnName ).append( " " ).append( columnType );

                int precision = rsmd.getPrecision( i );
                if ( precision != 0 ) {
                    sb.append( "( " ).append( precision ).append( " )" );
                }
            }
            sb.append( " )" );
            System.out.println( sb.toString() );

Output : Create table ML_TEMP ( TEST VARCHAR2( 512 ), TEST1 VARCHAR2( 255 ), SEC_OBJ_MEMBER_VAL VARCHAR2( 4000 ), DML_MODE CHAR( 1 ) );

Expectation : Create table ML_TEMP ( USERNAME VARCHAR2( 512 ), SEC_OBJ_CODE VARCHAR2( 255 ), SEC_OBJ_MEMBER_VAL VARCHAR2( 4000 ), DML_MODE CHAR( 1 ) );

Any input is appreciated. Thanks

MT0
  • 143,790
  • 11
  • 59
  • 117
Harish Dalmia
  • 76
  • 11
  • 1
    Use `getColumnName` instead of `getColumnLabel`. You may find the explanation [here](https://stackoverflow.com/questions/4271152/getcolumnlabel-vs-getcolumnname). There may be differences between driver implementations, so check if it gives the correct output – Jhilton Nov 04 '22 at 05:39
  • Thanks, I tried that. It produces the same result. – Harish Dalmia Nov 04 '22 at 05:44
  • 1
    Then do you really need the alias ? setting the alias to what you need may be more straightforward. Otherwise there might be limitations if a columns is a calculated value between 2 or more columns – Jhilton Nov 04 '22 at 05:49
  • Tried it just now and `String columnName = rsmd.getColumnName( i );` did the work in my local environment – Jhilton Nov 04 '22 at 06:01
  • I have tried again but still it gives the ALIAS name only. Any other suggestions to look forward. – Harish Dalmia Nov 04 '22 at 10:29
  • Think, in oracle- it is not feasible directly. Ideally - getColumnName() should return the column name but in oracle driver getColumnName() & getColumnLabel() behaves similar way. I will keep open this question for sometime and wait for inputs. If not received, will close. – Harish Dalmia Nov 04 '22 at 11:01
  • Curiosity question: why do you do that on the client and not on the server using DBMS_SQL Api? You could have a PROCEDURE receiving the query text and the name of the temp table to create and doing the job. – p3consulting Nov 06 '22 at 08:35

0 Answers0