Request for Simple ColdFusion/Microsoft SQL Server Setup

Suite and Optimus Suite 5
#1

While I find the video on setting up the connector very simple to follow for PHP and a MySQL database, I am not getting what I am missing in setting this up for ColdFusion and Microsoft SQL Server. I am trying to use the Contacts application that was built in the sample coding and modifying to just learn this, but not getting something.

I have the grid initialized and appearing in the layout, and have the grid looking to load from a cfm file called griddata.cfm.

In the griddata.cfm file, I have the include to the config.cfm file and the following statements that I am guessing provide the connection string information for the database:

<cfset grid = createObject(“component”,“dhtmlxConnectors.GridConnector”).init(“#AppCon#”,“MSSQL”)>

<cfset config = createObject(“component”,“dhtmlxConnectors.GridConfiguration”).init(“#AppCon#”,“MSSQL”)>

with a render table statement as follows:

<cfset grid.render_table(“Facility”,“numberlea”,“school_name,psc_number,life_cycle”)>

In the config.cfm file, I have:
<cfset request.dhtmlxConnectors[“datasource”] = “AppCon”> and
<cfset request.dhtmlxConnectors[“db_type”] = “MSSQL”>

Again not sure what else that I am supposed to do with this. I have the following file structure:

At the root:
index.html
griddata.cfm
config.cfm
dhtmlx.css
dhtmlx.js

[imgs]
[xml]
[dhtmlConnector]-with all of the CFC files.

I had contacted support last year and have a string of emails that lead me to believe I was supposed to do something within ColdFusion Administration.

I have a 32 bit and 64 bid ODBC connector on the webserver to connect to the SQL server that both work in other applications. I have a Data Source within CF Administration using the ODBC Socket. All three called AppCon.

What am I missing or have I not configured properly.

#2

Hi.
A bit information: config.cfm is not required, but can be used just to store settings in one place.
According to your code you set there two variables:

<cfset request.dhtmlxConnectors["datasource"] = "AppCon"> 
<cfset request.dhtmlxConnectors["db_type"] = "MSSQL">[/code]

But then you doesn't use any of them:

[code]
<cfset grid = createObject("component","dhtmlxConnectors.GridConnector").init("#AppCon#","MSSQL")>
[/code]

It should be in this place in one of the following ways:
Either
1) [code]
<cfset grid = createObject("component","dhtmlxConnectors.GridConnector").init("AppCon","MSSQL")>

OR

  1. <cfset grid = createObject("component","dhtmlxConnectors.GridConnector").init(request.dhtmlxConnectors["datasource"],request.dhtmlxConnectors["db_type"])>

Coudl you change this and then return back here? If there are other problems - could you may be send some files which were coded by you: config.cfm and griddata.cfm)

#3

I have attached the three files that I am trying to set up. I changed the griddata.cfm file to no avail.
example.zip (3 KB)

#4

Hi
According to your code everything should be fine… But some things must be set correctly:

  1. do you have “dhtmlxConnectors” mapping in your cfadmin set correctly? Or do you have “dhtmlxConnectors” subfodler in the folder with your griddata.cfm?
  2. In current solution you don’t need config.cfm at all as you have settings directly in the code of griddata.cfm and nothing from config.cfm is used.

Could you open the template griddata.cfm in browser (instead of index.html) and you will see errors that you have.

#5

I have the index.html and griddata.cfm files at the root and then a dhtmlxConnector folder.

The error that I get when I browse to the griddata.cfm file is:

This XML file does not appear to have any style information associated with it. The document tree is shown below.

I actually see data, but I don’t know how to fix.

Thank you for any help that you can provide.

#6

So in trying to research the error myself, I am guessing that the html file has the XML style format that is needed, but for some reason the information is not getting passed to that file. The html page renders properly with the structure, but no data. Thought that I should make that clarification.

#7

By renders properly see image of the page containing the structure as set up, but not containing the data that appears to be stuck in the griddata.cfm file.

Capture.JPG783×204 13.7 KB

#8

Hi
You have correct client side file (index.html). Problem is on server-side (griddata.cfm) thats why structure is good but data is not loaded.
It seems that you can’t make settings correctly in the griddata.cfm connector file. Could you load it ino the addrss bar and send screenshot here?

not
…/folder/index.html
but
…/folder/griddata.cfm

Coldfusion will throw error here that you can see. If everything is correct here you should see xml output.
One direction to check for a problem. You told that you have folder dhtmlxConnectors/ in the same level as griddata.cfm. Could you check - are there files like “GridConnector.cfc” and other in it or there is subfolder “codebase” and no cfc files? When you create component like
createObject(“component”,“dhtmlxConnectors.GridConnector”)
you must have either mapping dhtmlxConnectors which links to the fodler where GridConnector.cfc can be found. Or on the same level there is “dhtmlxConnectors” folder, where the file “GridConnector.cfc” is located. Could you check this? And could you send me screenshot of error as I described above?

#9

GridDataError.JPG823×565 59.7 KB

Ivan,

The error that I get is when I navigate to the griddata.cfm page as you suggest. In the dhtmlxConnector folder which is at the same level as the griddata.cfm and index.html file there are 59 files which are CFC files or js files including the gridconnector.cfc file.

#10

Hi. So we know now that there are no errors - xm is correct. Could you save it as xml and send here so I can check how it loads without installing your DB here?

#11

What do you want me to save as xml? The griddata.cfm page with the data in it or some other file?

#12

The output you have in last screenshot. I need this as xml, so I can check it localy and findout the reason. Or another way - you can send me the sql of the DB which is used so I can also restore it localy and try. But I think the way with just xml can be enough to detect the problem.

#13

Ivan,
Hopefully the attached file will work for you. I basically saved the griddata.cfm file and it created a griddata.cfm.xml file which is (I guess) what you would work with. I can provide you something else if this does not meet your needs.
Griddata.zip (2.51 KB)

#14

Hi.
With this xml I succeeded to load the grid. The only problem I see - there is double config: in cfm (with the help of “GridConfiguration” component and on client side with javascript (I mean headers)
And they are different…
Client side:

contactsGrid.setHeader("Facility Name,PSC Number,Life Cycle"); contactsGrid.setInitWidths("100,100,*"); contactsGrid.setColAlign("left,left,left"); contactsGrid.setColTypes("ro,ro,ro"); contactsGrid.setColSorting("str,str,str"); contactsGrid.attachHeader("#text_filter,#text_filter,#text_filter");

and server side:

<cfset config.setHeader("Item,##cspan")> <cfset config.attachHeader("Item Name,Item CD")> <cfset config.attachFooter("Item Name,Item CD", "background: ##ff0000;,background: ##00ff00;")> <cfset config.attachFooter("Item Name Test,##rspan", "background: ##0000ff;color:white;")> <cfset config.setColIds("col1,col2")> <cfset config.setInitWidths('120,*')> <cfset config.setColSorting("connector,connector")> <cfset config.setColColor(",##dddddd")> <cfset config.setColHidden("false,false")> <cfset config.setColTypes("ro,ed")> <cfset config.setColAlign('center,center')> <cfset config.setColVAlign('bottom,middle')>

#15

Ivan,

Excuse my ignorance, but I am not sure what I need to do to modify either of these pages. Could you please explain how I should change the two code snippets that you included to match?

#16

Hi
As far as I see in your index.html config you have three columns and in griddata.cfm server-side config you have two columns and xml for two columns. So you have to remove the js config. It should look like:
index.htm:

contactsGrid = layout.cells("a").attachGrid(); contactsGrid.init(); contactsGrid.load("griddata.cfm");

And your cfm code should leave as it is.

If you could send me the DB so I can check exactly with DB connection may be it would be easier. But with your xml (that you’ve sent me) everything works fine.

#17

Can you send me the index.html and griddata.cfm files that you used for the XML file?

#18

Here is index file only. The xml should be located in the same folder (file: griddata.xml).
I also commented the menu.xml and toolbar.xml as I don’t have them localy so I ignored them in this index file. The xml - the one you’ve sent me some time ago.
index.zip (1.77 KB)