UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Store A Carriage Return In An Oracle Field So It Works In Access Textbox, Access 2016    
 
   
whdyck
post Jul 29 2020, 10:58 PM
Post#1



Posts: 334
Joined: 20-July 11
From: Winnipeg, MB, Canada


What should I store in an Oracle field so that when it displays in an Access textbox it displays as a carriage return?

Or must I use a sentinel character in the Oracle field, then replace it with a vbCrlf on the Access side?

Thanks.

Wayne
Go to the top of the page
 
jleach
post Jul 30 2020, 08:54 AM
Post#2


UtterAccess Administrator
Posts: 10,644
Joined: 7-December 09
From: St. Augustine, FL


I don't have a lot of Oracle experience, but would guess that the typical Chr(10)Chr(13) (e.g., vbCrLf) would work.

--------------------
Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
 
whdyck
post Jul 30 2020, 12:52 PM
Post#3



Posts: 334
Joined: 20-July 11
From: Winnipeg, MB, Canada


Yeah, I tried those, but no joy.
Go to the top of the page
 
FrankRuperto
post Jul 30 2020, 01:18 PM
Post#4



Posts: 1,219
Joined: 21-September 14
From: Tampa, Florida USA


If the Oracle db lives on Windows, which I doubt, you can concatenate: \r\n = DOS end of line, where \r = chr(13), \n = chr(10).
If the Oracle db lives on Linux, \n = Unix end of line.

However, If you're using Windows workstations on a LAN for the Access FE's and theyre linked to the Oracle BE on a Linux server,
Then the SAMBA SMB software running on the Linux server will automatically map the DOS CrLf to the Unix newline character, and vice versa.
This post has been edited by FrankRuperto: Jul 30 2020, 01:30 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
whdyck
post Jul 30 2020, 03:56 PM
Post#5



Posts: 334
Joined: 20-July 11
From: Winnipeg, MB, Canada


Our Oracle DB lives on a Linux box and Access runs on Windows workstations. However, I'm not clear on what you're proposing I do when building the string fields in Oracle. Could you elaborate?

To be clear, I'm creating the string entirely in Oracle package code, but when the string appears in Access FE, I want carriage returns to display at various points in the string, carriage returns that were added in the package code.

Thanks.

Wayne
Go to the top of the page
 
FrankRuperto
post Jul 30 2020, 06:57 PM
Post#6



Posts: 1,219
Joined: 21-September 14
From: Tampa, Florida USA


Understood. In your original post you stated
QUOTE
What should I store in an Oracle field so that when it displays in an Access textbox it displays as a carriage return?
Or must I use a sentinel character in the Oracle field, then replace it with a vbCrlf on the Access side?

Since you're creating the text with the newlines in an Oracle package, you really dont have to do anything, its all transparent with SAMBA, or whatever other util your Linux server is using for SMB/CIFS interop communication with Windows. It will do the conversion for you when it sends the data to Windows and Access displays it in the form, report, etc.

Regardless of an Oracle table, text file, etc., on Linux all newline characters are stored just as an ascii 10 (LineFeed), so if for example youre entering text with vbCrLF's into an Access LongText field on Windows, the SAMBA SMB software running on the Linux server will automatically convert those Windows CrLf's to just a LineFeed. The Oracle ODBC is just mapping Access LongText fields to Oracle LONG fields, or to an Oracle VARCHAR2 field if youre using an Access ShortText field. when Access displays a Linux Oracle text field that has newline characters in it, SAMBA will convert it to vbCrLf when it sends it to your Access Form in Windows, and vice versa.
This post has been edited by FrankRuperto: Jul 30 2020, 07:28 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
whdyck
post Jul 30 2020, 08:29 PM
Post#7



Posts: 334
Joined: 20-July 11
From: Winnipeg, MB, Canada


Here's the code segment where I want additional carriage returns inserted into fldComment:

CODE
UPDATE usrFuel.tblFuelPurchases
SET fldDollarAmount = vProdAmt(ndx) + vPstAmt(ndx) + vGstAmt(ndx)
  , fldGstAdjustment = vGstAmt(ndx)
  , fldComment =    NVL2(fldComment, fldComment || '    ', '')
                 || CASE ndx
                       WHEN 1 THEN
                          NVL2(nProd1AmtImported
                             ,    'Prod1Amt(Imported): '
                               || TO_CHAR(nProd1AmtImported
                                        , '$9,990.99')
                               || '    '
                             , '')
                       ELSE
                          ''
                    END
                 || 'Product Amt: '
                 || TO_CHAR(vProdAmt(ndx)
                          , '$9,990.99')
                 || 'PST Amt: '
                 || TO_CHAR(vPstAmt(ndx)
                          , '$9,990.99')
                 || '    GST Amt: '
                 || TO_CHAR(vGstAmt(ndx)
                          , '$9,990.99')
                 || '    Reference Total Tax: '
                 || TO_CHAR(recCCrdTxnReference.fldReferenceTotalTax
                          , '$9,990.99')
                 || '    Reference Trans Amt: '
                 || TO_CHAR(recCCrdTxnReference.fldReferenceTotal
                          , '$9,990.99')
                 || CASE nTaxAnomalyFlag
                       WHEN -1 THEN
                          '    TAX ANOMALY for this Reference'
                       ELSE
                          ''
                    END


I'd like each label (like 'Product Amt: ') to begin on a new line. Whether I just press the enter key in TOAD after the first single quote or whether I insert a CHR(10) or CHR(13) in the above, concatenated with the string, I don't see a CR in Access. Any idea what's wrong?

Thanks.

Wayne
Go to the top of the page
 
FrankRuperto
post Jul 30 2020, 09:42 PM
Post#8



Posts: 1,219
Joined: 21-September 14
From: Tampa, Florida USA


Try using one of the Oracle functions shown in the examples in this link: https://community.oracle.com/blogs/bbrumm/2...s-in-oracle-SQL

EDIT: Utter-Access' weblink tool is not working properly with this link.
Copy and paste the following link into your browser: https://community.oracle.com/blogs/bbrumm/2...s-in-oracle-SQL

EDIT: Geez, that didn't work either!... see image for the google search I did.
This post has been edited by FrankRuperto: Jul 30 2020, 09:59 PM
Attached File(s)
Attached File  Capture.PNG ( 82.95K )Number of downloads: 8
Attached File  Capture2.PNG ( 78.23K )Number of downloads: 7
 

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
FrankRuperto
post Aug 1 2020, 05:56 AM
Post#9



Posts: 1,219
Joined: 21-September 14
From: Tampa, Florida USA


SAMBA is a file system compatibility product that allows an interface between Windows and Linux file systems. Windows started with SMB (Server Message Block) and has now switched to CIFS (Common Internet File System). Microsoft added NFS (Network File System) Server to Windows Server 2008. Similarly, Apple uses AFP (Apple Filing Protocol) as their network file system but NFS file systems can also be mounted. The only network file system for Linux is NFS. These network file systems are not to be confused with the underlying file systems like NTFS, ext4, btrfs, etc.

ODBC is a network protocol with a server and client but it's sole intent is for database connectivity. ODBC is Microsoft technology that Linux developers have adapted. There are other protocols like OCI and OLE DB. It just happens that ODBC is still the preferred protocol for MS Access.

This screenshot below shows some of the settings from the PostgreSQL ODBC configuration. Oracle has similar settings. LF to CR/LF conversion is the default. The setting indicates that it performs the conversion in both directions. However, I checked a text column in one of our Postgres databases and found that the chr(13) & chr(10) combination still exists so it may just apply to passing query strings.


Attached File(s)
Attached File  pgODBC.PNG ( 20.87K )Number of downloads: 2
 

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
FrankRuperto
post Aug 1 2020, 11:00 AM
Post#10



Posts: 1,219
Joined: 21-September 14
From: Tampa, Florida USA


Rather than updating the Oracle data, why not just format your display/report on the Access side?

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th August 2020 - 07:38 AM