smithproject.org

Download the Source and Binaries (zip) file from http://dev.mysql.com/downloads/connector/j/5.0.html

You only need one file from this .zip: mysql-connector-java-5.0.8-bin.jar

Put mysql-connector-java-5.0.8-bin.jar in your Smith \lib directory, then restart Smith.

In the Smith Administrator, on the Data Sources page, fill in the following fields:

name: mysql

JDBC URL: jdbc:mysql://mySystem/myDatabase (substitute the remote MySQL computer name for mySystem and your database name for myDatabase)

JDBC Driver full class name: com.mysql.jdbc.Driver

Username: your MySQL username

Password: your MySQL password
... then click on the [Save data source] button.

Be careful about upper and lower case - most of these fields are case-sensitive.

After you do this, you can click on the [Val] button next to the mysql datasource to test if it is set up correctly and can access your database.
A green check mark indicates that all is OK. If you get a red X instead, click on the red X to see the cause of the error.

-tom-

It looks like there are a few bugs in the Smith (version 1.3b5) Encrypt/Decrypt functions.

Smith can only use Base64 or Hex encoding. Smith cannot use the default UU encoding, so you must supply the 3rd and 4th arguments, like this:
Code:

 <cfset key = "MySeCrEtPaSsWoRd">
 <cfset ciphertext = Encrypt("test one two three",  key, "CFMX_COMPAT", "Base64")>
 <cfset cleartext = Decrypt(ciphertext, key,  "CFMX_COMPAT", "Base64")>
 <cfoutput> #cleartext# <==> #ciphertext# <br></cfoutput>

The Smith CFMX_COMPAT algorithm is not compatible with traditional CF encryption, and cryptographically it is very weak.
hmmmm... looking at the code, it is very very weak! I suggest you do not use it for sensitive data.

A second problem is that when Java algorithms are used, Smith incorrectly switches the key and algorithm arguments. I logged a bug for this problem.
For now, you can use the Java algorithms by switching these two arguments -- but be prepared to change your code when this problem gets fixed in Smith.
For example - to use the password-based PBEwithMD5andDES algorithm, do this:
Code:

 <cfset key = "MySeCrEtPaSsWoRd">
 <cfset ciphertext = Encrypt("test one two three", "PBEwithMD5andDES", key, "Base64")>
 <cfset cleartext = Decrypt(ciphertext,"PBEwithMD5andDES",  key, "Base64")>
 <cfoutput> #cleartext# <==>  #ciphertext# <br></cfoutput>

Encrypting your data this way is secure, and it is compatible with other CF engines and with other Java software. If you plan to store your encrypted data this is a much better choice.
Note that when you use the PBEwithMD5andDES algorithm; the same cleartext will encrypt to a different ciphertext every time you encrypt it - even using the same key. Nevertheless, it will still decrypt correctly to the original cleartext.

-tom-

A simple solution might be to use cfsqltype="CF_SQL_VARCHAR" and let MySQL automatically convert from a string to a date. MySQL will do this.

re: the Java code:
Using the Java SimpleDateFormat looks like it might be a mistake.
The default format for SimpleDateFormat is often an unusual one - for example, in the U.S. locale it requires a string like "12/01/06 00:00 AM".
Even if this string is used, it is illegal to cast from a java.util.Date to a java.sql.Date, so it still fails.

I'll test some more and enter a bug if I confirm this. Too bad it missed being in time for 1.3b5.

-tom-

The current version of Smith can only retrieve web pages as ISO-8859-1.
This is why the Portuguese characters which are not the same in ISO-8859-1 and in UTF-8 are messed up.

FYI - Smith uses Apache HttpClient to retrieve pages for <cfhttp>.
Because Smith does not currently set or check the response character set, HttpClient defaults to ISO-8859-1.

I proposed a patch to Smith to fix this problem.

Testing your example with the patch, I get:

Já tinha chamado a atenção para "Yours as Fast as Mine", dos The Main Drag, mas a cada audição, fico a gostar mais e mais. Daí sentir a obrigação de vos chamar novamente a atenção, pois este álbum é de facto muito Bom! Sem dúvida, um dos meus preferidos deste ano! Similar Artists: Death Cab for Cutie, Broken

It looks better to me (but I don't know any Portuguese...)

-tom-

re: "Can I use ANT on a debian server?"

Sure, no problem! There is a .tar.gz for ANT.
You'll probably want to read the installation instructions.
Ditto for the JDK, since I described the Windows version.
The JDK was called the J2SE SDK back in the 1.4 days - make sure you get the SDK, not the JRE.

Also - I entered the patch into the Smith SourceForge Patch Tracker, if you want to see the .patch file.

-tom-

Must be something amiss with the forum "upload file" feature.
I haven't noticed anyone using it before - so maybe I'm the first.

If you are unfamiliar with patch files, you might be better off just making this 2-line fix by hand.

Make sure you have the Java 1.4 JDK installed and in your PATH (both the j2sdk1.4.?_??\bin and the j2sdk1.4.?_??\jre\bin directory)

Make sure you have Apache ANT installed and its \bin directory in your PATH, and the ANT_HOME env var is set

Download and extract the Smith source code

Edit the file src/web/src/com/youngculture/smith/engine/util/ServerInfo.java and make two changes:
change line 46 to
Code:

		int index = s.lastIndexOf('@');

change line 62 to
Code:

		int colIndex = secondPart.lastIndexOf(':');

type ant.bat to build dist\smith-1.3b4.jar (you may need to do this twice before it completes with no errors)

replace WEB-INF\lib\smith-1.3b4.jar with the one you just built and start Smith

This makes Smith look for the last occurrence of @ and : in the server string, not the first, when it identifies the various parts of the string.

Be careful about case; Java is case-sensitive. The function name must be "lastIndexOf" exactly.

FYI - the Java String functions like indexOf() and lastIndexOf() are described here.

Good luck! Let us know how you make out.

-tom-

You could enter a Smith bug for this - but I don't know if the Smith folks will want to fix it.
Open Mail Relays are frowned upon.

If you want to patch the Smith 1.3b4 source code and rebuild Smith yourself - the attached patch will do what you want.

-tom-

The Gmail page says that TLS is required. TLS isn't available in CF7 and earlier versions (or in Smith).

I can get Gmail smtp to work only with CF8 - by checking the new CF8 "Enable TLS connection to mail server" box in the CF8 Admin Mail page,
or by using the new CF8 "useTLS=yes" attribute in the <cfmail> tag.

I am unable to find a way to connect to Gmail smtp with CF7 and earlier versions (or with Smith) because there is no way to specify TLS.

Are you sure you connected to Gmail smtp with CF6 & CF7?

If you do have TLS (i.e. if you are using Adobe CF8), there is no need to use an extra @ sign.
CF8 works fine with "username:password@smtp.gmail.com", where username and password are your regular Gmail username and password.

-tom-

Smith doesn't implement the result= attribute in <cfhttp>.
The result is always put into a struct named CFHTTP, like CF6 and earlier.

If you want <cfhttp> to throw an error when it fails, add the throwonerror="yes" attribute.

-tom-

I think the problem you are seeing is MySQL bug 31499.

A note was added to the Connector/J "Upgrading" page about this.

You can work around the problem by appending:

?useOldAliasMetadataBehavior=true

to the JDBC URL for your MySQL data source. See Connector/J Configuration Properties (search for useOldAliasMetadataBehavior near the bottom of the page).

Strictly speaking, MySQL is correct about JDBC and Smith is wrong here. The column "name" is supposed to be the physical column name, and the column "label" is for the query result.

I entered a proposed patch for this problem.

-tom-

FYI - Microsoft provides a free JDBC driver which will work with SQL Server 2000 or SQL Server 2005 and Smith.
You can download Microsoft SQL Server 2005 JDBC Driver 1.1 here. Both Windows and Unix downloads are available.

To set it up:
Expand the self-extracting zip (sqljdbc_1.1.1501.101_enu.exe) or the tar file (sqljdbc_1.1.1501.101_enu.tar.gz)

Copy sqljdbc_1.1\enu\sqljdbc.jar to either the {Smith}\lib directory, or to the {Smith}\wwwroot\WEB-INF\lib directory.

Restart Smith.
Create a DataSource with:

JDBC URL: jdbc:sqlserver://localhost:1433;database=testDB

JDBC Driver full class name: com.microsoft.sqlserver.jdbc.SQLServerDriver

On Windows this driver can use Windows Integrated Authentication, as well as SQL Server Authentication.
This feature is not available if you are running Smith on Unix.
The Windows account which started Smith is used for authentication instead of a Username & Password.
To set up Windows Authentication:

Copy sqljdbc_1.1\enu\auth\x86\sqljdbc_auth.dll to a directory in your PATH, or to the current directory if you run startweb.bat from the command line.

Append ;integratedSecurity=true to the end of the JDBC URL.

Leave the DataSource Username and Password blank.

The Microsoft documentation for this JDBC driver is here.

-tom-

Smith doesn't have query-of-queries like CF does.

One thing you can do which is similar - but not really the same - is to use an in-memory database. The Mckoi db which comes with the standalone Smith package will do this.

To set it up in standalone Smith, create a new datasource like the default mckoi datasource with these differences:

the JDBC URL is: jdbc:mckoi:local://wwwroot/db.conf?storage_system=v1javaheap&create_or_boot=true

the Connection Checkout Timeout (ms) is: 10000 (because the first connection can take several seconds)

Keep idle connections for: 0 (i.e. connections last forever)

There is no need to run startdb.bat or startdb.sh to use this in-memory Mckoi database. It exists entirely in the Smith process' memory. No db server process is required. All the data in this database will evaporate when Smith is shut down, so you will need to populate tables like this:
Code:

<cflock name="temp_table" timeout="30" type="EXCLUSIVE">
     <cfquery datasource="mckoi_mem" name="q1">
         DROP TABLE IF EXISTS temp_table
     </cfquery>
     <cfquery datasource="mckoi_mem" name="q1">
         CREATE TABLE temp_table (id INTEGER, stuff VARCHAR(128), PRIMARY KEY (id))
     </cfquery>
     <!--- populate the table --->
 </cflock>

and perform queries on the table like this:
Code:

<cflock name="temp_table" timeout="30" type="READONLY">
     <cfquery datasource="mckoi_mem" name="q1">
         SELECT * FROM temp_table WHERE id BETWEEN #a# AND #b#
     </cfquery>
 </cflock>

This is unlike query-of-queries because all the simultaneous pages share the same database, and share the tables - just like a real database. Hence the need for <cflock>.

Queries on an in-memory database are typically very fast, and it is safe for any number of simultaneous pages to perform read-only queries at the same time.

Hope this is helpful.

-tom-

setCharacterEncoding was added to ServletResponse in J2EE 1.4.
The container should provide javax.servlet.http.HttpServletResponse.setCharacterEncoding, not Smith.

ref: http://java.sun.com/j2ee/1.4/docs/api/javax/servlet/ServletResponse.html#setCharacterEncoding(java.lang.String)

Perhaps you were using a J2EE 1.3-only (or earlier...) server?

-tom-

Using this code to access a MySQL database with a large value as CF_SQL_INTEGER:
Code:

 <cfset a = 192>
 <cfset b = 168>
 <cfset c = 1>
 <cfset d = 1>
 <cfset ipnumber = Int((16777216 * a) + (65536 * b) + (256 * c) + d)>
 <cfoutput>
     ipnumber #ipnumber#<br>
     formatted #NumberFormat(ipnumber,"_")#<br>
     type #ipnumber.getClass().toString()#<br>
 </cfoutput>
 <cfquery name="q1" datasource="mysqlTest">
     SELECT * from tbl01 WHERE id = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ipnumber#">
 </cfquery>

The results for several servers are:
Code:

 Server        Internal Type       Default Format   Formatted    Query
 ------------- -----------------   --------------   -----------  ------------------------------------------------------
 Smith 1.3b    java.lang.Double    3.232235777E9    3232235777   Unable to convert String 3.232235777E9 to number!
 BlueDragon 7  java.lang.String    3232235777       3232235777   successful
 CF8           java.lang.Double    3232235777       3232235777   Invalid data 3.232235777E9 for CFSQLTYPE CF_SQL_INTEGER.
 CF7.0.2       java.lang.Double    3232235777       3232235777   Invalid data 3.232235777E9 for CFSQLTYPE CF_SQL_INTEGER.

It appears to be a unique behavior of BlueDragon that it allows CF_SQL_INTEGER values larger than the integer maximum as a queryparam.

When no formatting function is used, Smith formats calculated numeric values differently from CF and BD by default.
There may be a formatting bug here, although fixing it won't make values > 2,147,483,647 work with <cfqueryparam>:

Smith uses the standard Java algorithm to format calculated numeric values - so any value 10,000,000 or greater will be in scientific notation.

Unlike Java, Smith will trim off a trailing ".0" fractional portion for numbers smaller than 10,000,000.

Both CF and BD seem to use 10^12 instead of 10^7 as the cutoff for using scientific notation for large values.
CF and BD differ in the cutoff for small values (near zero).
I cannot find any mention in the CF docs that specifies exactly when scientific notation is expected.

-tom-

FYI - Adobe CF8 won't accept this number either:
Code:

<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="3493104135">

The CF8 error is:

Invalid data 3493104135 for CFSQLTYPE CF_SQL_INTEGER.

The CFML definition of Integer is

ColdFusion supports integers between -2,147,483,648 and 2,147,483,647 (32-bit signed integers).

.
If the datatype in your database is not really Integer, but some other Numeric or Decimal datatype
- you could try using CF_SQL_DECIMAL or CF_SQL_NUMBER instead of CF_SQL_INTEGER.

-tom-