Column Alias Error

[LHought]

1. What version of ODPI-C are you using (see dpi.h)? dpi.h

2. What OS (and version) is your application on? Redhat 8

3. What compiler (and version) did you use? gcc 8.5.0

4. What is your version of the Oracle Client (e.g. Instant Client)? 19.1 How was it installed? from source Where it is installed?

5. What is your Oracle Database version? Connecting to remote server, not sure their version

6. What is the PATH environment variable (on Windows) or LD_LIBRARY_PATH (on Linux) set to?

7. What environment variables did you set? How exactly did you set them?

8. What problem or error(s) you are seeing? When submitting a large query with long column names, I used a column alias. Works find in most circumstances BUT if the alias name is 8 characters, the library does not null terminate the column name. Check the dpiStmt_getQueryInfo(stmt, colnum, dpiQeryInfo) function and the queryInfo.name variable.

9. Do you have a runnable code snippet to describe the problem?

[anthony-tuininga]

Please provide an example that demonstrates the problem. For example, this works with cx_Oracle (which internally uses ODPI-C):

cursor.execute("select 1 as N2345678 from dual")

That is an alias with 8 characters in its name. I'm not sure what the reference to dpiStmt_getQueryInfo() means either. Can you elaborate further? ODPI-C never null-terminates any of its strings. A length is always provided. For this particular case, look at the documentation.

[LHought]

The issue was my failure to notice in the documentation that the returned string was not null terminated. I have corrected my code, and offer my humble apologies for bothering you with this.

Lowell Hought 651-494-2174

On Thu, Apr 7, 2022 at 2:26 PM Anthony Tuininga @.***> wrote:

Please provide an example that demonstrates the problem. For example, this works:

cursor.execute("select 1 as N2345678 from dual")

That is an alias with 8 characters in its name. I'm not sure what the reference to dpiStmt_getQueryInfo() means either. Can you elaborate further?

— Reply to this email directly, view it on GitHub https://github.com/oracle/odpi/issues/171#issuecomment-1092119529, or unsubscribe https://github.com/notifications/unsubscribe-auth/AYTM6T7MFLKR7ZF55LYERF3VD4ZF7ANCNFSM5SZ5ZSWA . You are receiving this because you authored the thread.Message ID: @.***>

[anthony-tuininga]

No problem. Glad you figured it out. :-)

[LHought]

Here is the request string:

sql: WITH m AS (SELECT facility_key, MAX(COALESCE(facility_start_date,TO_DATE('1/1/1970','MM/DD/YYYY'))) AS facility_start_date, MAX(COALESCE(facility_changed_date,TO_DATE('1/1/1970','MM/DD/YYYY'))) AS facility_changed_date, MAX(COALESCE(facility_name_start_date,TO_DATE('1/1/1970','MM/DD/YYYY'))) AS facility_name_start_date, MAX(COALESCE(facility_district_start_date,TO_DATE('1/1/1970','MM/DD/YYYY'))) AS facility_district_start_date, MAX(COALESCE(service_area_start_date,TO_DATE('1/1/1970','MM/DD/YYYY'))) AS service_area_start_date, MAX(COALESCE(facility_address_start_date,TO_DATE('1/1/1970','MM/DD/YYYY'))) AS facility_address_start_date, MAX(COALESCE(facility_type_start_date,TO_DATE('1/1/1970','MM/DD/YYYY'))) AS facility_type_start_date, MAX(COALESCE(facility_atc_level_start_date,TO_DATE('1/1/1970','MM/DD/YYYY'))) AS facility_atc_level_start_date FROM cdc.ntd_swb_facility_lookup_vw f GROUP BY facility_key)SELECT f.facility_key, f.facility_code, TO_CHAR(f.facility_start_date,'MM/DD/YYYY')AS facility_start_date, TO_CHAR(f.facility_end_date,'MM/DD/YYYY')AS facility_end_date, TO_CHAR(f.facility_changed_date,'MM/DD/YYYY')AS facility_changed_date, f.facility_name, TO_CHAR(f.facility_name_start_date,'MM/DD/YYYY')AS facility_name_start_date, TO_CHAR(f.facility_name_end_date,'MM/DD/YYYY')AS facility_name_end_date, f.districtrecordkey, TO_CHAR(f.facility_district_start_date,'MM/DD/YYYY')AS facility_district_start_date, TO_CHAR(f.facility_district_end_date,'MM/DD/YYYY')AS facility_district_end_date, f.servicearea_key, f.serviceareacode, TO_CHAR(f.service_area_start_date,'MM/DD/YYYY')AS service_area_start_date, TO_CHAR(f.service_area_end_date,'MM/DD/YYYY')AS service_area_end_date, f.streetaddress, f.city, f.state, f.zipcode, f.utc_offset, f.uses_dst, f.dst_utc_offset, TO_CHAR(f.facility_address_start_date,'MM/DD/YYYY')AS facility_address_start_date, TO_CHAR(f.facility_address_end_date,'MM/DD/YYYY')AS facility_address_end_date, f.facility_type_number, f.facility_type_description, TO_CHAR(f.facility_type_start_date,'MM/DD/YYYY')AS facility_type_start_date, TO_CHAR(f.facility_type_end_date,'MM/DD/YYYY')AS facility_type_end_date, f.facility_atc_level, TO_CHAR(f.facility_atc_level_start_date,'MM/DD/YYYY')AS facility_atc_level_start_date, TO_CHAR(f.facility_atc_level_end_date,'MM/DD/YYYY')AS facility_atc_level_end_date, f.orgcodetype, '2022-04-06' infodate FROM cdc.ntd_swb_facility_lookup_vw f JOIN m ON(f.facility_key = m.facility_key AND COALESCE(f.facility_start_date,TO_DATE('1/1/1970','MM/DD/YYYY')) = m.facility_start_date AND COALESCE(f.facility_changed_date,TO_DATE('1/1/1970','MM/DD/YYYY')) = m.facility_changed_date AND COALESCE(f.facility_name_start_date,TO_DATE('1/1/1970','MM/DD/YYYY')) = m.facility_name_start_date AND COALESCE(f.facility_district_start_date,TO_DATE('1/1/1970','MM/DD/YYYY')) = m.facility_district_start_date AND COALESCE(f.service_area_start_date,TO_DATE('1/1/1970','MM/DD/YYYY')) = m.service_area_start_date AND COALESCE(f.facility_address_start_date,TO_DATE('1/1/1970','MM/DD/YYYY')) = m.facility_address_start_date AND COALESCE(f.facility_type_start_date,TO_DATE('1/1/1970','MM/DD/YYYY')) = m.facility_type_start_date AND COALESCE(f.facility_atc_level_start_date,TO_DATE('1/1/1970','MM/DD/YYYY')) = m.facility_atc_level_start_date ) WHERE (CASE WHEN f.facility_key=1789 AND f.facility_code != f.facility_name THEN 0 ELSE 1 END) = 1 ORDER BY facility_key

Here is what I get when printing out the column names:

column[0]: 'FACILITY_KEY'

column[1]: 'FACILITY_CODE'

column[2]: 'FACILITY_START_DATE'

column[3]: 'FACILITY_END_DATE'

column[4]: 'FACILITY_CHANGED_DATE'

column[5]: 'FACILITY_NAME'

column[6]: 'FACILITY_NAME_START_DATEFACILITY_NAME_END_DATE'

column[7]: 'FACILITY_NAME_END_DATE'

column[8]: 'DISTRICTRECORDKEY'

column[9]: 'FACILITY_DISTRICT_START_DATE'

column[10]: 'FACILITY_DISTRICT_END_DATE'

column[11]: 'SERVICEAREA_KEY'

column[12]: 'SERVICEAREACODE'

column[13]: 'SERVICE_AREA_START_DATE'

column[14]: 'SERVICE_AREA_END_DATE'

column[15]: 'STREETADDRESS'

column[16]: 'CITY'

column[17]: 'STATE'

column[18]: 'ZIPCODE'

column[19]: 'UTC_OFFSET'

column[20]: 'USES_DST'

column[21]: 'DST_UTC_OFFSET'

column[22]: 'FACILITY_ADDRESS_START_DATE'

column[23]: 'FACILITY_ADDRESS_END_DATE'

column[24]: 'FACILITY_TYPE_NUMBER'

column[25]: 'FACILITY_TYPE_DESCRIPTION'

column[26]: 'FACILITY_TYPE_START_DATEFACILITY_TYPE_END_DATE'

column[27]: 'FACILITY_TYPE_END_DATE'

column[28]: 'FACILITY_ATC_LEVEL'

column[29]: 'FACILITY_ATC_LEVEL_START_DATE'

column[30]: 'FACILITY_ATC_LEVEL_END_DATE'

column[31]: 'ORGCODETYPE'

column[32]: 'INFODATExz"'

If I change the column alias ‘INFODATE’ to any 8 character string, the results are always a malformed column name. The position in the list doesn’t matter either. If I change the column alias to a 7 or 9 character string, it works as expected.

On Thu, Apr 7, 2022 at 2:26 PM Anthony Tuininga @.***> wrote:

Please provide an example that demonstrates the problem. For example, this works:

cursor.execute("select 1 as N2345678 from dual")

That is an alias with 8 characters in its name. I'm not sure what the reference to dpiStmt_getQueryInfo() means either. Can you elaborate further?

— Reply to this email directly, view it on GitHub https://github.com/oracle/odpi/issues/171#issuecomment-1092119529, or unsubscribe https://github.com/notifications/unsubscribe-auth/AYTM6T7MFLKR7ZF55LYERF3VD4ZF7ANCNFSM5SZ5ZSWA . You are receiving this because you authored the thread.Message ID: @.***>

[cjbj]

@LHought to be able to resolve this, we need

• the DB and Oracle client version.
• the CREATE TABLE statement
• a runnable C code file that makes ODPI-C calls and has the problem.

[LHought]

This issue was resolved on April 7th. The issue was my failure to notice in the documentation that the returned string was not null terminated. I have corrected my code, and offer my humble apologies for bothering you with this.

On Tue, Oct 11, 2022 at 4:34 AM Christopher Jones @.***> wrote:

@LHought https://github.com/LHought to be able to resolve this, we needplease give us the DB and Oracle client version. Can you also share the CREATE TABLE statement?

— Reply to this email directly, view it on GitHub https://github.com/oracle/odpi/issues/171#issuecomment-1274407626, or unsubscribe https://github.com/notifications/unsubscribe-auth/AYTM6TZOFD7H632VGGJLAM3WCUYDVANCNFSM5SZ5ZSWA . You are receiving this because you were mentioned.Message ID: @.***>

[cjbj]

@LHought No problems.