CF 9, iText PDF creation, MySQL database: Apostrophe disappears in output PDF

Report · Jan 31, 2011

I create PDF files using iText. I insert variable data from a MySQL database into form fields (in an existing PDF file). I have an entry in the database "O'Malley" and (only on the output PDF file) the apostrophy is dropped, resulting in "OMalley". The name displays correctly in the database and in online cfm pages. All other special characters that I have tried display correctly in the PDF files as well as online pages. Any assistance would be appreciated.

Report · Jan 31, 2011

Can you post a small (stand-alone) example that reproduces the problem?

Report · Jan 31, 2011

Are you requesting a URL or the code that is used to create the PDF file?

Report · Jan 31, 2011

The code. Just put together a small (self-contained) example that reproduces the problem. ie Something we can copy, paste and run locally.

Report · Jan 31, 2011

Below is sample code. I also created a page that correctly displays the data (with apostrophe) on a standard webpage and with a link to the iText generated PDF file (that drops the apostrophe): www.accountmembers.com/register/name.cfm

You will need to create two PDF files each with a text field labeled "name" (case-sensitive). One will be the input file and one will be the output file.

<CFQUERY NAME="inputdata" DATASOURCE="xxxxxx" username="xxxxxxx" password="xxxxxxxxx">
SELECT fullname FROM agents
WHERE ID = 'xxxxx'
</CFQUERY>

</cfoutput>
<cfset inputFilePath = expandPath(".") &"\folder\filenamein.pdf">
<cfset outputFilePath = expandPath(".") &"\folder\filenameout.pdf">
<cfscript>
reader = createObject("java", "com.lowagie.text.pdf.PdfReader").init(inputFilePath);
fos = createObject("java", "java.io.FileOutputStream").init(outputFilePath);
stamp1 = createObject("java", "com.lowagie.text.pdf.PdfStamper").init(reader, fos);
acrForm = stamp1.getAcroFields();
acrForm.setField("name", fullname);

stamp1.setFormFlattening(true);
stamp1.close();
fos.close();
</cfscript>

Report · Jan 31, 2011

Let me test it.

BTW, I meant to ask - since you are running CF9 is there a reason you cannot use cfpdfform?

Report · Jan 31, 2011

I haven't tried it. Does it only handle forms? The PDF files I generate also include variable images and merge multiple PDF templates into one.

Report · Jan 31, 2011

Cfpdfform supports form fields and should support images too. For merging, use cfpdf. Both should do the job, but obviously iText provides more granular control over the process.

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7995.html

Report · Jan 31, 2011

Are you sure that character is really a single quote? Loop through the variable and check the ascii values

(The forum software may have butchered the code .. )

arr = listToArray( fullName, "");

for (x in arr) {

WriteOutput( asc(x) &"="& x &"<br>");

}

</cfscript>

Report · Jan 31, 2011

I'm quite sure it is a single quote. I will try to run the test (I'm not that fluent in CF scripting).

I decided to test cfpdfform and the result displays correctly with that. I may go that route for changing the text fields and continue to use iText for the rest if I can't get iText to display correctly.

Report · Jan 31, 2011

Well unless it is some weird "smart" character pasted in from MS Word or a rich text editor, there should not be a problem with apostrophes. You might also cfdump the query on your test page. Just to re-verify things before you generate the pdf.

<cfoutput query="inputdata">
<cfset fullname = "#fullname#">
</cfoutput>

This has nothing to do with your issue, but you do not need to use cfoutput. If the query only contains one record, just use the variable directly in your code. ie

acrForm.setField("person.name", inputdata.fullName);

Report · Jan 31, 2011

Were you able to get it to display correctly on your server with your test?

It's just a regular apostrophe, not a "smart" apostrophe. I added it to the database with a regular input form (by typing the ' key, not pasting it from another program).

I am aware that I don't need the cfoutput tag, but in the regular script, I have many variables and it was more efficient at the time to write it that way.

Thank you. I appreciate your time and assistance.

Report · Jan 31, 2011

Yes, it worked fine for me CF9/MySQL5. So there must be some difference in either the data or maybe the form itself. But the data seems the more likely cause.

Did you output the ascii values and dump the query? Just as a sanity check.

Report · Jan 31, 2011

I wonder if it could be database settings? What character set are you using for the field? I also wonder if the version of iText or Acrobat you are using could make a difference? Is it possible for you to email me the PDF files, so I can load them and test?

Report · Jan 31, 2011

Can you post the results of the dump and ascii values first? Just to make sure we are on the same page. Sanitize the query to just show "FullName" if needed.

Report · Jan 31, 2011

I tried, but encountered an error. I don't know what I'm doing.

Report · Jan 31, 2011

You have attempted to dereference a scalar variable of type class coldfusion.runtime.Array as a structure with members.

5 : <cfscript>
6 : arr = listToArray(inputdata.fullname, "");
7 : for (x in arr) {
8 :     WriteOutput( asc(x) &"="& x &"<br>");
9 : }

Report · Jan 31, 2011

It does run under CF9.0.1. Though the syntax was borderline / lazy of me 😉

Anyway, you are just trying to loop through the string, and output each character plus its ascii value. Try a regular loop with string functions.

<cfloop from="1" to="#len(inputdata.fullName)#" index="x">
    <cfset currChar = mid(inputData.fullName, x, 1)>
    <cfoutput>
    #asc(currChar)# = #currChar#<br>
    </cfoutput>
</cfloop>

Report · Jan 31, 2011

That worked. It shows the apostrophe as " 39 = ' ".

Report · Jan 31, 2011

Since it is not a special character, I am not sure why you are getting different results. Are you sure the code posted is exactly the same as what you are using to populate the form? Because with the exception of the field name, my test code is exactly what you posted and it works fine. While there is always a chance it is something exotic.. the more likely cause is some other code is removing the character.

sample file used: datasheet.pdf

http://itextpdf.com/examples/iia.php?id=121

BTW: I do believe in sanitizing information for the web 😉 But seeing the full results of the tests, not just a snippet, is more helpful. We might catch something in the raw data you may have overlooked. At the very least, it can help eliminate possible causes. That is why I asked if you could post a dump of a) the query and b) all of the ascii codes on your test page. Using dummy data is fine too. As long as the sample values reproduce the issue.

Report · Jan 31, 2011

Here is the complete results of the test:

74 = J
101 = e
102 = f
102 = f
32 =
79 = O
39 = '
77 = M
97 = a
108 = l
108 = l
101 = e
121 = y

So, the code works on your server, but we don't know if our configurations are the same. By default, my fields are setup as "latin1_swedish_ci" collation and latin1 character set, but I have also tried "utf8_general_ci" collation and utf8 character set with the same results. I am using Acrobat 7 (Version 1.6) to create the PDF files. And, iText version 2.1.0 is what my host has installed on the server.

The code I posted is not exact with regard to the variable names, but it is the same amount of code. There is not any additional code.

I think it makes sense to test your files on my site's server to see if the same results are rendered, if you'd be willing to share them. I think there is a possibility that our servers are configured differently. If your code works on my server, then I can examine the two sets of code for differences. If your code doesn't work on my server, then we know it is an issue with the server or its configuration.

Report · Feb 01, 2011

I am not near to CF at the moment. But IIRC, my test settings were very similar. Try it with the sample pdf from the iText site (see previous link). Just change the form field name to "title".

CF: 9.0.1

iText: Built in version (2.1.0, I think?)

MySQL5: utf8_general_ci and charset utf8

Report · Feb 01, 2011

So, I have isolated it to the PDF form fields and/or file.

When I use the datasheet.pdf file, the apostrophe shows. So, I copied the "title" form field from the datasheet.pdf and pasted it into my PDF, and the apostrophe disappears. Then, I copied and pasted the "name" form field out of my PDF document into the datasheet.pdf file and in my form field the apostrophe is absent, but in the "title" form field, the apostrophe shows.

So, that leads me to believe it is the form field that is the issue. I have checked the settings for both form fields and they look to be the same. I checked the version of the PDF files, both are version 1.6 (Acrobat 7.x). I'm at a loss, unless the forms I create with Acrobat version 7.1.0 are somehow the root of the problem.

Report · Feb 01, 2011

I'm going to try updating to Acrobat 7.1.4 and see what happens.