JavaScript variable to ColdFusion variable

I have a tricky one.

By means of a <cfoutput query="…"> I list some records in the page from a SQL Server datab开发者_如何学运维ase.

By the end of each line viewing I try to add this in to a record in a MySQL database. As you see is simple, because I can use the exact variables from the output query in to my new INSERT INTO statement.

BUT: the rsPick.name comes from a database with a different character set and the only way to get it right into my new database is to read it from the web page and not from the value came in the output query.

So I read this value with that little JavaScript I made and put it in the myValue variable and then I want ColdFusion to read that variable in order to place it in my SQL statement.

 <cfoutput query="rsPick">
  <tr>
    <td>#rsPick.ABBREVIATION#</td>
    <td id="square"> #rsPick.name# </td>

    <td>#rsPick.Composition#</td>
    <td> Transaction done...
      <script type="text/javascript">
        var myvalue = document.getElementById("square").innerHTML
      </script>
    </td>

  <cfquery datasource="#Request.Order#">
     INSERT INTO products (iniid, abbreviation, clsid, cllid, dfsid, dflid, szsid, szlid, gross, retail, netvaluebc, composition, name)
     VALUES ( #rsPick.ID#, '#rsPick.ABBREVIATION#', #rsPick.CLSID#, #rsPick.CLLID#, #rsPick.DFSID#, #rsPick.DFLID#, #rsPick.SZSID#, #rsPick.SZLID#, #rsPick.GROSSPRICE#, #rsPick.RETAILPRICE#, #rsPick.NETVALUEBC#, '#rsPick.COMPOSITION#','#MYVALUE#' )
  </cfquery>
 </tr>
</cfoutput>

ColdFusion is processed on the server before the page is served. JavaScript is processed in the browser after the page is served. Therefore, the only way for JavaScript to serve a value to ColdFusion is either:

a) Set the variable to a form field and then post the page back to the server.

b) Send the variable back to Coldfusion via Ajax.

I think there is a safer and more reliable way to do what you need without the JavaScript.

If you are creating the page you would know what is in the DOM element "square" because you put it there. If you are doing this based on the dynamic input of the user then you would need to use a CFAJAXPROXY or simply a form post to a different page for doing the insert.

Also it's frightening to take unsanitized input from a DOM and insert into a database. Use cfqueryparam to protect against injection attacks.

Your misconception starts with this thought:

"The only way to get it right is to read the value from a web page via JavaScript."

Sorry if this sounds harsh now, but this is complete nonsense. Forget that idea. It is neither possible to read a JavaScript variable from ColdFusion the way you try it, nor is it even necessary.

For starters, I'm not sure why it must be via ColdFusion that you move values from one database to another. Database servers are perfectly able to talk directly to each other, for example via the "Linked Server" feature in MS SQL Server (see this howto).

If it must be ColdFusion, try this:

<cfoutput query="rsPick">
  <cfquery datasource="#Request.Order#">   
     INSERT INTO products (
       iniid, 
       /* … etc … */
       name
     )
     VALUES (
       <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#rsPick.ID#">,
       /* … etc … */
       <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#rsPick.name#">
     )
  </cfquery>
</cfoutput>

As long as the table data types and database connections to both databases are set up correctly, ColdFusion will handle the character encoding transparently. Note that the target DB fields should not be "less capable" than the source fields, e.g. the source should not be Unicode when the target is US ASCII.

Well, your question is really this: How do I take a string from one character encoding to another in ColdFusion?

Can you use the NVARCHAR type in SQL Server (not sure what the same thing in MySQL is) - and just store the character data as UTF8?

Also, Java is perfectly capable of dealing with the different encodings, and even provides help in the String class and the CharsetDecoder class to deal with different encodings:

String s = new String("Hello, World!");
byte[] bytes = s.getBytes("UTF-8");
String utf = new String(bytes, "UTF-8");

ColdFusion has very limited support for dealing with multiple character sets in a single request. You are almost certainly going to have to rely on the underlying Java classes to solve this problem inside of ColdFusion.

So, if you wanna to convert JS value to CFM, I feel a few steps need to do:

first of all, assign your JS value into input via javascript. after that, retrieve value from this input by submitting form.

Use JSString to convert Javascript variable to CFML variable.

<cfoutput>
    var #toScript(myCFMLVaraible, "jsVar")#;
</cfoutput>