pacman82/odbc2parquet

Invalid SQL Data Type Reported on SQLFetch (Oracle 19c on Windows 10 64Bit)

chrisfw opened this issue · 29 comments

chrisfw commented

Hi,

I am attempting to use odbc2parquet to export a large Oracle 19c history table (millions of records) with the definition below and odbc2parquet (version 0.6.21) reports the following error:

2022-02-01T16:37:55-05:00 - WARN - State: HY004, Native error: 0, Message: [Oracle][ODBC]Invalid SQL data type <-25>.
Error: ODBC emitted an error calling 'SQLFetch':
State: HY004, Native error: 0, Message: [Oracle][ODBC]Invalid SQL data type <-25>.

None of the columns appear to have unsupported datatypes given that they are just numbers and varchars. Any assistance you can provide with this issue would be greatly appreciated. Thanks!

FCIHISTV
-----------
ACC_RN                            NUMBER(10)   
CIHIS_COMP                        NUMBER(2)    
CIHIS_APPLICATION                 NUMBER(2)    
CIHIS_CUST                        NUMBER(12)   
CIHIS_DATE                        NUMBER(8)    
CIHIS_SEQ                         NUMBER(7)    
CIHIS_ELEMENT                     NUMBER(4)    
CIHIS_TYPE_CODE                   NUMBER(4)    
CIHIS_READ_SEQ                    NUMBER(4)    
CIHIS_TRANS_TYPE                  NUMBER(2)    
CIHIS_BILL_DATE                   NUMBER(9)    
CIHIS_AMT                         NUMBER(11,2) 
CIHIS_FACTOR                      NUMBER(9,6)  
CIHIS_MANUAL                      NUMBER(2)    
CIHIS_ADJDT                       NUMBER(9)    
CIHIS_TYPE                        NUMBER(2)    
CIHIS_COUNT                       NUMBER(7)    
CIHIS_UT_TAX1_AMT                 NUMBER(11,2) 
CIHIS_UT_TAX2_AMT                 NUMBER(11,2) 
CIHIS_BANK                        NUMBER(4)    
CIHIS_RATE                        NUMBER(4)    
CIHIS_TAX_CD                      NUMBER(2)    
CIHIS_TAX_TYPE                    NUMBER(2)    
CIHIS_MTAX_KIND                   NUMBER(2)    
CIHIS_BDOWN                       NUMBER(2)    
CIHIS_BUDGET_CD                   NUMBER(2)    
CIHIS_FROM_DATE                   NUMBER(9)    
CIHIS_WO_NUM                      NUMBER(10)   
CIHIS_CLASS_TYPE                  NUMBER(4)    
CIHIS_CLASS_NUM                   NUMBER(4)    
CIHIS_PRO                         NUMBER(2)    
CIHIS_POSTMARK_DT                 NUMBER(9)    
CIHIS_TX_CODE                     NUMBER(2)    
CIHIS_DELINQ_FLAG                 VARCHAR2(1)  
CIHIS_DISPUTE_FLAG                VARCHAR2(1)  
CIHIS_ACTION                      NUMBER(2)    
CIHIS_DESC                        VARCHAR2(20) 
CIHIS_USE                         NUMBER(9)    
CIHIS_ACCT_TP                     VARCHAR2(1)  
CIHIS_ALT_CUST                    NUMBER(12)   
CIHIS_TAXABLE_SALES               NUMBER(11,2) 
CIHIS_METERED_ITEM_RATE           NUMBER(4)    
CIHIS_NONTAXABLE_SALES            NUMBER(11,2) 
CIHIS_TAX_INSTALLMENT             NUMBER(2)    
CIHIS_TSS_BOD                     NUMBER(11,2) 
CIHIS_MIN_ADD                     NUMBER(11,2) 
FILLER_CD1                        NUMBER(2)    
FILLER_CD2                        NUMBER(2)    
CIHIS_OYR                         NUMBER(4)    
CIHIS_TO_DATE                     NUMBER(9)    
CIHIS_ITEM_SEQ                    NUMBER(7)    
CIHIS_PPAC_TYPE                   NUMBER(4)    
CIHIS_DEFER_DUE_DT                NUMBER(9)    
CIHIS_SERV_VOLT                   NUMBER(9)    
CIHIS_SERVICE                     NUMBER(4)    
CIHIS_BUDPAY                      NUMBER(11,2) 
CIHIS_BILL_CONTRACT               VARCHAR2(10) 
CIHIS_BLOCK_AMT1                  NUMBER(11,2) 
CIHIS_BLOCK_USE1                  NUMBER(11)   
CIHIS_BLOCK_AMT2                  NUMBER(11,2) 
CIHIS_BLOCK_USE2                  NUMBER(11)   
CIHIS_CRB_PCT                     NUMBER(9,6)  
CIHIS_CRB_NUM                     NUMBER(2)    
CIHIS_BALANCE_AMOUNT              NUMBER(11,2) 
CIHIS_WORKORDER_APPLICATION       NUMBER(2)    
CIHIS_FIXEDID                     NUMBER(10)

odbc2parquet command and output

E:\history>odbc2parquet -vvv query  --column-compression-default gzip -c "Driver={Oracle in instantclient_19_13};Dbq=WMPTST;Uid=redacted;Pwd=redacted;" test.parquet  "select * from cis.fcihistv"
2022-02-01T16:37:53-05:00 - DEBUG - ODBC Environment created.
2022-02-01T16:37:53-05:00 - WARN - State: 01000, Native error: 0, Message: [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).
2022-02-01T16:37:53-05:00 - WARN - State: 01004, Native error: 0, Message: [Oracle][ODBC]String data, right truncated.doesn't support the version of ODBC behavi
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 1: ColumnDescription { name: [65, 67, 67, 95, 82, 78], data_type: Decimal { precision: 10, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC buffer description for column 1: BufferDescription { nullable: true, kind: I64 }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 2: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 67, 79, 77, 80], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC buffer description for column 2: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 3: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 65, 80, 80, 76, 73, 67, 65, 84, 73, 79, 78], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC buffer description for column 3: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 4: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 67, 85, 83, 84], data_type: Decimal { precision: 12, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC buffer description for column 4: BufferDescription { nullable: true, kind: I64 }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 5: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 68, 65, 84, 69], data_type: Decimal { precision: 8, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC buffer description for column 5: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 6: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 83, 69, 81], data_type: Decimal { precision: 7, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC buffer description for column 6: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 7: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 69, 76, 69, 77, 69, 78, 84], data_type: Decimal { precision: 4, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC buffer description for column 7: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 8: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 84, 89, 80, 69, 95, 67, 79, 68, 69], data_type: Decimal { precision: 4, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC buffer description for column 8: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 9: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 82, 69, 65, 68, 95, 83, 69, 81], data_type: Decimal { precision: 4, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC buffer description for column 9: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 10: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 84, 82, 65, 78, 83, 95, 84, 89, 80, 69], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC buffer description for column 10: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 11: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 66, 73, 76, 76, 95, 68, 65, 84, 69], data_type: Decimal { precision: 9, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC buffer description for column 11: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 12: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 65, 77, 84], data_type: Decimal { precision: 11, scale: 2 }, nullability: Nullable }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC buffer description for column 12: BufferDescription { nullable: true, kind: Text { max_str_len: 13 } }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 13: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 70, 65, 67, 84, 79, 82], data_type: Decimal { precision: 9, scale: 6 }, nullability: Nullable }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC buffer description for column 13: BufferDescription { nullable: true, kind: Text { max_str_len: 11 } }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 14: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 77, 65, 78, 85, 65, 76], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC buffer description for column 14: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 15: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 65, 68, 74, 68, 84], data_type: Decimal { precision: 9, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC buffer description for column 15: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 16: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 84, 89, 80, 69], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC buffer description for column 16: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 17: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 67, 79, 85, 78, 84], data_type: Decimal { precision: 7, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC buffer description for column 17: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:53-05:00 - DEBUG - ODBC column description for column 18: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 85, 84, 95, 84, 65, 88, 49, 95, 65, 77, 84], data_type: Decimal { precision: 11, scale: 2 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 18: BufferDescription { nullable: true, kind: Text { max_str_len: 13 } }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 19: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 85, 84, 95, 84, 65, 88, 50, 95, 65, 77, 84], data_type: Decimal { precision: 11, scale: 2 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 19: BufferDescription { nullable: true, kind: Text { max_str_len: 13 } }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 20: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 66, 65, 78, 75], data_type: Decimal { precision: 4, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 20: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 21: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 82, 65, 84, 69], data_type: Decimal { precision: 4, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 21: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 22: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 84, 65, 88, 95, 67, 68], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 22: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 23: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 84, 65, 88, 95, 84, 89, 80, 69], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 23: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 24: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 77, 84, 65, 88, 95, 75, 73, 78, 68], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 24: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 25: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 66, 68, 79, 87, 78], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 25: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 26: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 66, 85, 68, 71, 69, 84, 95, 67, 68], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 26: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 27: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 70, 82, 79, 77, 95, 68, 65, 84, 69], data_type: Decimal { precision: 9, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 27: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 28: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 87, 79, 95, 78, 85, 77], data_type: Decimal { precision: 10, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 28: BufferDescription { nullable: true, kind: I64 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 29: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 67, 76, 65, 83, 83, 95, 84, 89, 80, 69], data_type: Decimal { precision: 4, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 29: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 30: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 67, 76, 65, 83, 83, 95, 78, 85, 77], data_type: Decimal { precision: 4, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 30: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 31: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 80, 82, 79], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 31: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 32: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 80, 79, 83, 84, 77, 65, 82, 75, 95, 68, 84], data_type: Decimal { precision: 9, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 32: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 33: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 84, 88, 95, 67, 79, 68, 69], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 33: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 34: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 68, 69, 76, 73, 78, 81, 95, 70, 76, 65, 71], data_type: Varchar { length: 1 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 34: BufferDescription { nullable: true, kind: WText { max_str_len: 2 } }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 35: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 68, 73, 83, 80, 85, 84, 69, 95, 70, 76, 65, 71], data_type: Varchar { length: 1 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 35: BufferDescription { nullable: true, kind: WText { max_str_len: 2 } }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 36: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 65, 67, 84, 73, 79, 78], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 36: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 37: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 68, 69, 83, 67], data_type: Varchar { length: 20 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 37: BufferDescription { nullable: true, kind: WText { max_str_len: 40 } }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 38: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 85, 83, 69], data_type: Decimal { precision: 9, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 38: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 39: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 65, 67, 67, 84, 95, 84, 80], data_type: Varchar { length: 1 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 39: BufferDescription { nullable: true, kind: WText { max_str_len: 2 } }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 40: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 65, 76, 84, 95, 67, 85, 83, 84], data_type: Decimal { precision: 12, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 40: BufferDescription { nullable: true, kind: I64 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 41: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 84, 65, 88, 65, 66, 76, 69, 95, 83, 65, 76, 69, 83], data_type: Decimal { precision: 11, scale: 2 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 41: BufferDescription { nullable: true, kind: Text { max_str_len: 13 } }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 42: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 77, 69, 84, 69, 82, 69, 68, 95, 73, 84, 69, 77, 95, 82, 65, 84, 69], data_type: Decimal { precision: 4, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 42: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 43: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 78, 79, 78, 84, 65, 88, 65, 66, 76, 69, 95, 83, 65, 76, 69, 83], data_type: Decimal { precision: 11, scale: 2 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 43: BufferDescription { nullable: true, kind: Text { max_str_len: 13 } }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 44: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 84, 65, 88, 95, 73, 78, 83, 84, 65, 76, 76, 77, 69, 78, 84], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 44: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 45: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 84, 83, 83, 95, 66, 79, 68], data_type: Decimal { precision: 11, scale: 2 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 45: BufferDescription { nullable: true, kind: Text { max_str_len: 13 } }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 46: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 77, 73, 78, 95, 65, 68, 68], data_type: Decimal { precision: 11, scale: 2 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 46: BufferDescription { nullable: true, kind: Text { max_str_len: 13 } }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 47: ColumnDescription { name: [70, 73, 76, 76, 69, 82, 95, 67, 68, 49], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 47: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 48: ColumnDescription { name: [70, 73, 76, 76, 69, 82, 95, 67, 68, 50], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 48: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 49: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 79, 89, 82], data_type: Decimal { precision: 4, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 49: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 50: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 84, 79, 95, 68, 65, 84, 69], data_type: Decimal { precision: 9, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 50: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 51: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 73, 84, 69, 77, 95, 83, 69, 81], data_type: Decimal { precision: 7, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 51: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 52: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 80, 80, 65, 67, 95, 84, 89, 80, 69], data_type: Decimal { precision: 4, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 52: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 53: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 68, 69, 70, 69, 82, 95, 68, 85, 69, 95, 68, 84], data_type: Decimal { precision: 9, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 53: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 54: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 83, 69, 82, 86, 95, 86, 79, 76, 84], data_type: Decimal { precision: 9, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 54: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 55: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 83, 69, 82, 86, 73, 67, 69], data_type: Decimal { precision: 4, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 55: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 56: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 66, 85, 68, 80, 65, 89], data_type: Decimal { precision: 11, scale: 2 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 56: BufferDescription { nullable: true, kind: Text { max_str_len: 13 } }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 57: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 66, 73, 76, 76, 95, 67, 79, 78, 84, 82, 65, 67, 84], data_type: Varchar { length: 10 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 57: BufferDescription { nullable: true, kind: WText { max_str_len: 20 } }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 58: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 66, 76, 79, 67, 75, 95, 65, 77, 84, 49], data_type: Decimal { precision: 11, scale: 2 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 58: BufferDescription { nullable: true, kind: Text { max_str_len: 13 } }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 59: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 66, 76, 79, 67, 75, 95, 85, 83, 69, 49], data_type: Decimal { precision: 11, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 59: BufferDescription { nullable: true, kind: I64 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 60: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 66, 76, 79, 67, 75, 95, 65, 77, 84, 50], data_type: Decimal { precision: 11, scale: 2 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 60: BufferDescription { nullable: true, kind: Text { max_str_len: 13 } }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 61: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 66, 76, 79, 67, 75, 95, 85, 83, 69, 50], data_type: Decimal { precision: 11, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 61: BufferDescription { nullable: true, kind: I64 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 62: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 67, 82, 66, 95, 80, 67, 84], data_type: Decimal { precision: 9, scale: 6 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 62: BufferDescription { nullable: true, kind: Text { max_str_len: 11 } }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 63: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 67, 82, 66, 95, 78, 85, 77], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 63: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 64: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 66, 65, 76, 65, 78, 67, 69, 95, 65, 77, 79, 85, 78, 84], data_type: Decimal { precision: 11, scale: 2 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 64: BufferDescription { nullable: true, kind: Text { max_str_len: 13 } }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 65: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 87, 79, 82, 75, 79, 82, 68, 69, 82, 95, 65, 80, 80, 76, 73, 67, 65, 84, 73, 79, 78], data_type: Decimal { precision: 2, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 65: BufferDescription { nullable: true, kind: I32 }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC column description for column 66: ColumnDescription { name: [67, 73, 72, 73, 83, 95, 70, 73, 88, 69, 68, 73, 68], data_type: Decimal { precision: 10, scale: 0 }, nullability: Nullable }
2022-02-01T16:37:54-05:00 - DEBUG - ODBC buffer description for column 66: BufferDescription { nullable: true, kind: I64 }
2022-02-01T16:37:54-05:00 - INFO - Memory usage per row is 1127 bytes. This excludes memory directly allocated by the ODBC driver.
2022-02-01T16:37:54-05:00 - INFO - Batch size set to 65535 rows.
2022-02-01T16:37:55-05:00 - WARN - State: HY004, Native error: 0, Message: [Oracle][ODBC]Invalid SQL data type <-25>.
Error: ODBC emitted an error calling 'SQLFetch':
State: HY004, Native error: 0, Message: [Oracle][ODBC]Invalid SQL data type <-25>.
pacman82 commented

Hello @chrisfw thanks for opening this issue, and providing the detailed log output. The datatype (-25) in the error message likely relates to BigInt, which is a hint, that the driver does not like the fact, that the tool is trying to fetch the columns with scale 0 as 64Bit integers. It seems the driver does at least not support this conversion for the SQL DataType, maybe the buffer can stay i64. Don't know yet.

I won't have time to investigate this deeper until the weekend, however in the meanwhile it would be helpful to verify the hypothesis, by trying to do this query without the columns those buffer description have an i64 in the kind attribute (see log output).

Ps. Am I right to assume you run this under windows?

Cheers, Markus

chrisfw commented
chrisfw commented
pacman82 commented

Is there a free version of the database you are using available, so we can include it in the test suite? I'd very much like to have a reproducing example?

chrisfw commented
pacman82 commented

Setting up an Oracle is quite involved, was hoping for a ready made container easy to integrate into CI 😅 .

Anyhow, I think I now get at least the error messeg. Contrary to what it says, it is not about the SQL Data Type, but about the C Buffer Type we are binding. As seen in the linked reference above. Note that the Constant is declared in a header file name sqlext.h, meaning that it is an extension to the standard.

So far however 64Bit integers were supported by every database I encounterd. However a search revealed this stack overflow question.

https://stackoverflow.com/questions/338609/binding-int64-sql-bigint-as-query-parameter-causes-error-during-execution-in-o

Seemingly indicating that Oracle does not support bindig variables to 64 Bit integer buffers. Please note that the question is kinda old. Are you running the neweset version? Maybe Oracle added support in the meanwhile. So far however it seems the biggest improvement is that they added the constant -25 to the error message, giving users who browse C headers and add constantst an idea of what might be going on.

Regards what can be done in terms of this tool. I think it is a reasonable default behaviour to query Numeric(1..=18,0) as i64. It's also very sensible to let the driver do the conversion. Yet I could think about adding a flag, allowing to query those as text, and convert them from text to i64 within the tool itself. However setting a magic flag after interpreting an obscure error message is not the kind of UX im aiming for.

As a workaroud you could think a about casting every Numeric column with scale zero and precision greater than 9 (so starting at 10) into a Varchar. That way these would be fetched as text. You would have to post process the resulting parquet file though, to turn these back into integer colums, as they would be treated as text.

The sudden stoppage of the tool is worrysome. It can mean one of two things. The Oracle ODBC driver does causes an invalid memory access, double free or other kind of segmentation fault. Or one of the invariants I thought always to be true during writing the code, turns out to be violated.

Since I still have trouble with my test setup, I would kindly ask you to urn the debug build of the tool. Instruction of how to achieve that will follow shortly, but I now have to leave my PC so my GF can use it for gaming.

Cheers, Markus

pacman82 commented

Hello @chrisfw ,

In case you find the time to help out diagnosing this thing.

To run obtain a debug build of the tool, you would need a rust toolchain.

https://rustup.rs/ contains instruction of how to install one. It's just executing the script, and at one point you need to download either Visual Studio, or the Visual Studio Build tools to get the msvc compiler backend.

Checking out the repository and typing cargo build will result in the debug executable present in the target\debug subdirectory.

I could also just mail, or upload the executable to you, if you give me some coordinates.

Cheers, Markus

chrisfw commented
pacman82 commented

Hello Chris,

Thanks for your thoughts on possible workarounds, but I’m really hoping that a better solution will present itself after some further debugging. Oracle is one of the most heavily utilized databases in the world, so it would be a shame if a workaround is required in order to utilize the tool with it.

Very much thinking the same thing.

With regards to the conversion:

The error state reported by the driver should be HY003 IMHO and not HY004. (Meaning it's the buffer type which can't be bound. If it were about the SQL Type it should have said -5.). Also the error should be returned on SQLBindParameter instead of SQLFetch.

Maybe that's worth an upstream bug ticket.

Anyhow, with regards to the conversion, the solution might be to query the NUMERIC columns as text, and perform the conversion within the tool, instead of relying on the driver. This switch of behaviour could be activated with a flag on the command line. However, it would be a better UX if odbc2parquet would detect the error and tried again automatically with the adapted behaviour. Preconditions for the better UX are however:

  1. We can test the error handling code paths for Oracle on CI
  2. First call to SQLFetch did not change the state of the cursor, so we can just try again with a different set of bound buffers, without losing data.

Early termination of the tool so far is a mystery. Curious on what kind of stack trace I'll see.

FYI: $env:RUST_BACKTRACE="1" sets the environment in power shell, so we see more, if it fails with the debug version of the tool.

Cheers, Markus

chrisfw commented
pacman82 commented

Hello Chris,

One thought I had that may be a possibility for you is to simply create a free oracle cloud infrastructure account and spin up a free tier oracle autonomous database.

Nice idea. I'll look into that. Especially for CI. I did manage to build an oracle XE locally by the way. Yet even my humple attempts to create a table failed with a syntax error. At least I managed to figure out the connection string and did successfully connect.

Same here, it will be quite a few days before I can look deeper into this again.

In terms of experiments (only of course if you find the time):

There are four types of columns in your query.

  1. Character columns which will be queried as text.
  2. Numeric columns with scale zero which fit into a 32Bit integer.
  3. Numeric columns with scale zero which fit into a 64Bit integer.
  4. Numeric columns with scale != 0, which are queried as text.

I wonder if there is a subset of columns, for which the tool would work?

Cheers, Markus

pacman82 commented

Yet even my humple attempts to create a table failed with a syntax error.

Writing this I just realized that round brackets are magic in bash. 🤦

chrisfw commented
chrisfw commented
pacman82 commented

Thanks Chris,

Boy, that's a relief. Well the mapping issue is well understood. You can even read in the official documentation that 64Bit Integers are not supported. The public Oracle Forums are mourning this since decades it seems. Workaround is also understood, query these as string and convert. I'll add a flag to the tool. I am a bit undecided if I should just check for Oracle in the database name, or give the user a better error message (with a hint to the flag) if he/she encounters an error.

Cheers, Markus

chrisfw commented
pacman82 commented

I should just check for Oracle in the database name

I ment to write driver name.

However I think I use the flag, so if any other ODBC drivers out there, don't like 64 Bit integers it can also be used for them.

Cheers, Markus

chrisfw commented
pacman82 commented

Released version 0.6.22 with the --driver-does-not-support-64bit-integers flag. Not closing the issue, because I haven't done the helpful error message yet. However the flag itself I can introduce and test without having Oracle part of the test setup, so I figured I unblock your use case right now.

Tested the insert path with a 64Bit. Expectedly the Oracle driver also failed, but without emitting any error at all, which I find a bit disheartening, on how to best tackle that.

chrisfw commented
chrisfw commented
  • 🎉1
pacman82 commented

It seems history (unsuprisingly) repeats itself here: blue-yonder/turbodbc#142

I'll go add the better error message upstream in odbc-api, this way useres of arrow-odbc and arrow-odbc-py, won't be left to interpret that all by themeselfes.

chrisfw commented
pacman82 commented

Hello @chrisfw,

I have created a free tier account in the oracle cloud; I created a database; I also have an ADMIN user with a password. Now I am trying to figure out, how the ODBC connection string would look like. Managed to setup Oracle locally and test, but for the CI I would prefer the cloud solution. Mostly because Oracle does not seem to have ready made containers on Docker Hub.

Pretty sure I can get this done on my own, but I thought I might save some time asking you, since you seem to have experience with that.

Thanks for nothing Oracle.

To be fair, it is very unlikely that it is ill will on their side. It is a hard problem to structure a big organization, so a group of people both understands the buisness value of something technical like a good ODBC driver and has the autonomy, the resources and skills to act on it.
Stuff like good error messages and 64Bit support seldomly make it into a sales call, but in a world there developers have increasing autonomy to choose the tech they use, it starts to matter. However much we suffer, my guess is, there is a person in Oracle who has the skills, and will to improve that driver, but just can't convince his/her manager to spend time on that. I am also guessing that person feels the pain more than we do.

chrisfw commented
pacman82 commented

I may be able to get you a docker image of 19c.

FYI: I've been able to build a docker image using this repository https://github.com/oracle/docker-images .

Yet I find checking this repository out. Calling the script, to build the docker container, to involved for CI. I just want a ready image with an empty database and a premade user, which I can just docker pull.

However publishing the one I have to docker hub, I'm to scared to do, because I do not want to get in hot water with regards to licensing.

I'll be okay with either just calling docker build on some nice docker file for the CI, or using the ATP instance I spun up on Oracle Cloud, whichever seems less effort.

Preferably still the dockerfile, because there is less friction with tests, if somebody wants to fork and run tests.

chrisfw commented
pacman82 commented

Closing this issue, because I'll probably won't find the time to include Oracle in the test setup in a sensible way. However, the tool will emit a hint for Oracle users regardless.

odbc2parquet/src/query.rs

Line 253 in de06683

"Looks like you are using an Oracle database. Try the \