Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
johan_wigert
Active Participant
3,333
As I was developing an OData service related to contact persons in SAP CRM, I was faced with the scenario that the user wanted to search based on e-mail addresses of the contact persons. The e-mail addresses are stored in the database with a mixture of uppercase and lowercase letters. In this post, I’ll explain how the requirement can be met by using native SQL.

Scenario


The e-mail addresses of the contact persons are stored in table ADR6. The field where the e-mail address is stored is SMTP_ADDR, which has a data element with a case-sensitive domain handling 241 characters. Since the field is case-sensitive, we have e-mail addresses with a mixture of uppercase and lowercase letters, e.g.:

  • TestUser1@somedomain.com

  • testUSER2@somedomain.com

  • testuser3@SomeDomain.com


In order to search irrespective of the casing, we want the e-mail addresses to be uppercase only when performing the search.

Search field SMTP_SRCH


There is a field SMTP_SRCH in table ADR6 which is provided to simplify the search by storing the e-mail address uppercase. However, there is a serious limitation to this solution. The field is only 20 characters long. Since many e-mail addresses are longer than 20 characters, the field isn’t very useful.

Native SQL when selecting from the database table


With the help of native SQL we can transform SMTP_ADDR ot uppercase in the SELECT statement when selecting directly from the database table ADR6:


DATA user_email TYPE ad_smtpadr.

TRY.

" Note that table and field names must be uppercase
EXEC SQL.
SELECT
SMTP_ADDR
INTO :user_email
FROM ADR6
WHERE CLIENT = :sy-mandt
AND UPPER(SMTP_ADDR) = 'Q.W@R.S'
ENDEXEC.

CATCH cx_sy_native_sql_error INTO DATA(native_sql_excecption).
DATA(error_text) = |{ native_sql_excecption->get_text( ) } SQL CODE: { native_sql_excecption->sqlcode } MSG: { native_sql_excecption->sqlmsg }|.
MESSAGE error_text TYPE 'E'.
ENDTRY.
WRITE: / user_email.



So if the e-mail address in database table ADR6 is lowercase q.w@r.se, the entry is still found.

Native SQL when selecting from the DDL SQL View


Native SQL can also be used when selecting from the DDL SQL View of the CDS containing table ADR6:


DATA user_email TYPE ad_smtpadr.

TRY.

" Using the DDL SQL View of the CDS works
EXEC SQL.
SELECT
EMAIL
INTO :user_email
FROM ZCDSCPEMAIL
WHERE MANDT = :sy-mandt
AND UPPER(EMAIL) = 'Q.W@R.S'
ENDEXEC.

CATCH cx_sy_native_sql_error INTO DATA(native_sql_excecption).
DATA(error_text) = |{ native_sql_excecption->get_text( ) } SQL CODE: { native_sql_excecption->sqlcode } MSG: { native_sql_excecption->sqlmsg }|.
MESSAGE error_text TYPE 'E'.
ENDTRY.
WRITE: / user_email.



Native SQL when selecting from the CDS


Native SQL can not be used when selecting from the CDS containing table ADR6:


DATA user_email TYPE ad_smtpadr.

TRY.

" Using the CDS name does not work
EXEC SQL.
SELECT
EMAIL
INTO :user_email
FROM ZCDS_CONTACT_EMAIL
WHERE MANDT = :sy-mandt
AND UPPER(EMAIL) = 'Q.W@R.S'
ENDEXEC.

CATCH cx_sy_native_sql_error INTO DATA(native_sql_excecption).
DATA(error_text) = |{ native_sql_excecption->get_text( ) } SQL CODE: { native_sql_excecption->sqlcode } MSG: { native_sql_excecption->sqlmsg }|.
MESSAGE error_text TYPE 'E'.
ENDTRY.
WRITE: / user_email.



The code above results in the following error: You tried to work with the name of a table or view that does not exist in the database SQL CODE: 208 MSG: Invalid object name 'ZCDS_CONTACT_EMAIL'.

Conclusion


Even though it would have been possible to meet the requirement by using native SQL, we decided to implement the solution outlined in Note 1664239 instead. We opted for this solution since it is more general, and we avoid the dependency to a specific database implied by native SQL. I still think it was educational to explore the native SQL route.

This blog post first appeared on the Developer Voyage blog at https://www.developervoyage.com/2021/02/12/using-native-sql-for-case-insensitive-search.html
5 Comments
Labels in this area