Weird Table not found error.

[sim...@mstrategy.co.nz]

So in my new setup of Tomcat + Railo 3.3.4.003 final, with CFWheels 1.1.8 and java 1.7.0_05, when I attempt the following code:

<cfquery name="test" datasource="#GET('datasourcename')#">

SELECT * FROM users

</cfquery><cfdump var="#test#">

<cfset user = model("user").new()>

I get a dump of the users table, and then the cfwheels error: The users table could not be found in the database.

Highlighting the line as follows:

7: 			SELECT * FROM users
8: 		</cfquery><cfdump var="#test#"><!---cfabort--->
9: 		<cfset user = model("user").new()>
10: 	</cffunction>
11: 	

My user.cfc model doesn't set the table name also I don't have any callbacks set on the model.

Anyone have any idea why this would happen? I've tried downgrading java to 1.6.0_33 to see if it was a java issue but that didn't help either. I have another server running on a different machine that runs the same thing fine (it's an exact copy thanks to SVN).

Thanks,

~Simeon.

[AJ Mercer]

until you hear a better suggestion, maybe try capitalising the table name

<cfset user = model("User").new()> 

--
You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
To view this discussion on the web visit https://groups.google.com/d/msg/cfwheels/-/wJ17LoIKNnwJ.
To post to this group, send email to cfwh...@googlegroups.com.
To unsubscribe from this group, send email to cfwheels+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/cfwheels?hl=en.

--

AJ Mercer
<webonix:net strength="Industrial" /> | <webonix:org community="Open" />
http://twitter.com/webonix

Railo Community Manager

[tpet...@gmail.com]

try do table('users') in the init method of the model. also what database are you using?

On Wednesday, July 18, 2012 8:27:30 PM UTC-4, Andrew Mercer wrote:

until you hear a better suggestion, maybe try capitalising the table name

<cfset user = model("User").new()> 

On 19 July 2012 08:01, sim...@mstrategy.co.nz <sim...@mstrategy.co.nz> wrote:

So in my new setup of Tomcat + Railo 3.3.4.003 final, with CFWheels 1.1.8 and java 1.7.0_05, when I attempt the following code:

<cfquery name="test" datasource="#GET('datasourcename')#">

SELECT * FROM users

</cfquery><cfdump var="#test#">

<cfset user = model("user").new()>

I get a dump of the users table, and then the cfwheels error: The users table could not be found in the database.

Highlighting the line as follows:

7: 			SELECT * FROM users
8: 		</cfquery><cfdump var="#test#"><!---cfabort--->
9: 		<cfset user = model("user").new()>
10: 	</cffunction>
11: 	

My user.cfc model doesn't set the table name also I don't have any callbacks set on the model.

Anyone have any idea why this would happen? I've tried downgrading java to 1.6.0_33 to see if it was a java issue but that didn't help either. I have another server running on a different machine that runs the same thing fine (it's an exact copy thanks to SVN).

Thanks,

~Simeon.

--
You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
To view this discussion on the web visit https://groups.google.com/d/msg/cfwheels/-/wJ17LoIKNnwJ.
To post to this group, send email to cfwh...@googlegroups.com.

To unsubscribe from this group, send email to cfwheels+unsubscribe@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/cfwheels?hl=en.

[sim...@mstrategy.co.nz]

Thanks for both suggestions, neither of them worked.

Those extra details I forgot earlier - Windows Server 2008 rc 2 and MSSQL on a SQL Server 10.5.

Everything, database and webserver, is on the same windows box.

Thanks, Simeon.

[Chris Peters]

Usually, this sort of thing will happen because of how permissions are configured on the database or database table. Can you play around with those and see if it affects the new() method's access to the data?

--

You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.

To view this discussion on the web visit https://groups.google.com/d/msg/cfwheels/-/jC2u3k9qyq4J.

To post to this group, send email to cfwh...@googlegroups.com.

To unsubscribe from this group, send email to cfwheels+u...@googlegroups.com.

[AJ Mercer]

so could it be the cfquery is using a different user than what is defined in wheels??

[Chris Peters]

The call to new() is probably using <cfdbinfo> to poll the table for property names. Whatever type of operation that <cfdbinfo> is using may not be permitted by the permissions.

[tpet...@gmail.com]

@chris

excellent point!

@aj

yes. remember that if you define a datasourceusername and datasourcepassword, wheels will use that for all connections to the datasource. this doesn't hold true when you do cfquery. unless you give it a username and password in the attributes, it will use the credentials supplied in the cfadministrator. this can be a problem when trying to solve security access issues.

as a rule of thumb, i never define the datasourceusername and datasourcepassword in a wheels application so that it uses the credential defined for the datasource from cfadministrator. if you need different access for your wheels application to your database, just create a new datasource in the cfadministrator specifically for your app. this approach is actually better for security since you don't have a username and password for your database stored in the config in your application.

On Thursday, July 19, 2012 10:06:10 AM UTC-4, Andrew Mercer wrote:

so could it be the cfquery is using a different user than what is defined in wheels??

On 19 July 2012 21:35, Chris Peters <chris....@liquifusion.com> wrote:

Usually, this sort of thing will happen because of how permissions are configured on the database or database table. Can you play around with those and see if it affects the new() method's access to the data?

On Wed, Jul 18, 2012 at 9:17 PM, sim...@mstrategy.co.nz <sim...@mstrategy.co.nz> wrote:

Thanks for both suggestions, neither of them worked.

Those extra details I forgot earlier - Windows Server 2008 rc 2 and MSSQL on a SQL Server 10.5.

Everything, database and webserver, is on the same windows box.

Thanks, Simeon.

--
You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.

To view this discussion on the web visit https://groups.google.com/d/msg/cfwheels/-/jC2u3k9qyq4J.

To post to this group, send email to cfwh...@googlegroups.com.

To unsubscribe from this group, send email to cfwheels+unsubscribe@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/cfwheels?hl=en.

--
You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
To post to this group, send email to cfwh...@googlegroups.com.

To unsubscribe from this group, send email to cfwheels+unsubscribe@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/cfwheels?hl=en.

[sim...@mstrategy.co.nz]

Hi All.

Thanks for the suggestions, I've checked that I'm not setting the datasourceusername and datasourcepassword in wheels anywhere so it's not that.

The suggestion about database permissions is interesting as I have another copy of the cfwheels site that I tested the connection by using the exact same datasource settings and that one works fine! The only difference I have found is that the one that works is on an Apache-Tomcat-Railo setup on OSX, where as the one doesn't work is Tomcat-Railo setup on a windows server.

Is there any way to turn on verbose debugging for cfwheels to get the actual error thats given instead of cfwheels formatted one?

Thanks, ~Simeon.

[tpet...@gmail.com]

set(showErrorInformation="false") in your config/settings.cfm

[sim...@mstrategy.co.nz]

OK, so on disabling wheels nice debug messages the error message I get is: The conversion from char to SMALLINT is unsupported.

And the details are: Line 206: <cfdbinfo attributeCollection="#arguments#">

In the file: C:\webroots\BillingsLive\wheels\global\cfml.cfm

Here's a dump of the args it is passing:

Scope Arguments
table	1	string users
datasource	2	string billings
path	3	string wheels
fileName	4	string Connection
method	5	string init
RETURNVARIABLE	6	string returnValue
COMPONENT	7	string wheels.Connection
TYPE	8	string columns
NAME	9	string loc.returnValue

Turns out the error is in the JDBC drivers, I'll have to update them. Here's where I found my answer: https://community.jboss.org/thread/170779?tstart=0

As I can't seem to get cfwheels to work with the JDBC version 4 drivers from http://www.microsoft.com/en-us/download/details.aspx?id=11774 (I get an error stating the datasource is not supported by cfwheels) I can get it to work with the jTDS vendor driver.

Thanks all for your help!

~Simeon.

[Tim B]

Hey Simeon,

Did you find the drivers or a workaround for this? I'm experiencing the same exact issue.

Let me know.

Thanks, Tim

[Jonathan Smith]

Me as well, only my issue is caused after upgrading to Railo 4.x here on my development workstation.

Datasource and password defined in Railo Admin. when I do CFQUERY on my "Contacts" table, I get data, when I use cfwheels'   model("Contact").findAll()   I get table "Contacts" not found, and wheels does have the correct data source configured.

I guess "someone" need to update cfWheels to work with the new driver, I'm going to open the hood and see-what-I-can-see.

[Jonathan Smith]

Hi,

I can get it to work with the jTDS vendor driver as well, but I have no idea if that's a production ready solution.

On Friday, 31 August 2012 16:54:59 UTC-4, Tim B wrote:

[AJ Mercer]

I have been using th jTDS driver without issue (with Farcry CMS).

Hooked it up to ms sql server 2011 yesterday - so far it looks ok.

--

You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.

To view this discussion on the web visit https://groups.google.com/d/msg/cfwheels/-/W0DClS5CPwIJ.

To post to this group, send email to cfwh...@googlegroups.com.

To unsubscribe from this group, send email to cfwheels+u...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/cfwheels?hl=en.

[Jonathan Smith]

Hi,

Still no luck getting Wheels to play fairly with Railo 4.x.   

Unless someone can find otherwise, the JTDS driver doesn't fully work with cf-wheels 1.1.8.  Wheels specifically uses the key "IDENTITYCOL"  to receive a newly inserted primary key value. If you're not using this feature of cf-wheels, you may be ok, but the "IDENTITYCOL" value is missing from a query "result" structure in the JTDS driver

The wheels/model/adapters/MicrosoftSQLServer.cfc , line 3-5 specifically defines this. (Which you --could--- edit as a work-around)

<cffunction name="$generatedKey" returntype="string" access="public" output="false">

<cfreturn "identitycol">

</cffunction>

Microsoft Driver:

Struct
cached	boolean false
executionTime	number 1444989
generatedKey	string 6
IDENTITYCOL	string 6
RECORDCOUNT	number 1
SQL	string Insert into test values ( 10)

JTDS Driver: (Notice there is no IDENTITYCOL key)

Struct
cached	boolean false
executionTime	number 1187044
generatedKey	string 10
ID	string 10
RECORDCOUNT	number 1
SQL	string Insert into test values ( 10)

[AJ Mercer]

looks like it is fixed in 4.0.3

    https://issues.jboss.org/browse/RAILO-2178

To view this discussion on the web visit https://groups.google.com/d/msg/cfwheels/-/vhrAzV4qBzEJ.

To post to this group, send email to cfwh...@googlegroups.com.
To unsubscribe from this group, send email to cfwheels+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/cfwheels?hl=en.

[Jonathan Smith]

Some working solutions:

- Update to Railo 4.0.3 (currently a bleeding-edge version), which fixes the bug in the JTDS drivers which fixes the lack of the required "identitycol" key value.   (  Select  "MSSQL - Microsoft SQL Server (Vendor jTDS)" ) 

- Using Current Stable Railo 4.0.2.002.  Update SQL JDBC driver JAR to 4.0, which fixes the bug mentioned in previous posts about the <cfdbinfo /> tag not working. However:

The SQL JDBC 4 driver, however as also mentioned above has a different driver_name string "Microsoft JDBC Driver 4.0 for SQL Server", which wheels Connection.cfc doesn't recognize as MS-SQL, 

A recent commit for this file picks it up the new name properly: https://github.com/cfwheels/cfwheels/commit/5504d37489791a5d1650c03c3b097c5fb9ee8687

JDBC 4.0 Jar available at: http://www.microsoft.com/en-us/download/details.aspx?id=11774