query a database on a different server

plook · Jun 25, 2002

Hi everybody,

I have a database running on a MS SQL Server (let's call it Server A), with a web site on the same server.

How can I set up a web page on a different server (Server B)to be able to query my database on Server A without adding an ODBC on Server B ? Like, some code embedded in the CF script (or ASP...) ,with username and password, that will query Server A from Server B.

It does not have to be in CF, It can be in ASP as well. Can anybody point me to the right place or tell me what I can do ?

Thanks for your help

Dominic

Silvano · Jun 25, 2002

the newest way is to do that is to use .net web services; it is simple and quite promising; it gives you the ability to query the database, and pass the data in XML format to any application, written in any language (that is the application that understands XML) via the internet using SOAP, XML and http;
just search for '.net web services'... Sylvano
dsylvano@hotmail.com

pcorreia · Jun 26, 2002

Well, you could write a page on Server A that queries the database and returns the result as a WDDX packet. Something like this:

ON SERVER A (queryDB.cfm):

Code:

<cfif CGI.REMOTE_ADDR EQ &quot;addressOfServerB&quot;>
   <cfquery name=&quot;qryMyQuery&quot; datasource=&quot;myDSN&quot; dbtype=&quot;ODBC&quot;>
      #FORM.sqlstring#
   </cfquery>
   <cfheader name=&quot;Content-Type&quot; value=&quot;text/xml&quot;>
   <cfwddx action=&quot;CFML2WDDX&quot; input=&quot;#qryMyQuery#&quot;>
<cfelse>
   <cfheader statuscode=&quot;404&quot; statustext=&quot;Page Not Found&quot;>
</cfif>

ON SERVER B (queryDB.cfm):

Code:

<cfhttp url=&quot;[URL unfurl="true"]http://ServerA/queryDB.cfm&quot;[/URL] method=&quot;POST&quot;>
   <cfhttpparam name=&quot;sqlstring&quot; value=&quot;SELECT * FROM tblUsers WHERE userid=8&quot;>
</cfhttp>
<cfwddx action=&quot;WDDX2CFML&quot; input=&quot;#CFHTTP.FileContent#&quot; output=&quot;qryMyQuery&quot;>

Congratulations, you've built a web service. The page on Server A accepts a form variable, "sqlstring", executes that SQL against the database, and returns a WDDX-serialized result set. The page on Server B uses <CFHTTP> to call the page on Server A, then deserializes the WDDX string back into a query result set which can be used in the page on Server B just like a native query.

Of course, this is a bare-bones example. You should implement exception handling to deal with the possibility that Server A may not be reachable from Server B, or it may take a long time to respond, or it may return an error message which the page on Server B will try to interpret as a WDDX string... or any number of other possibilities.

pcorreia · Jun 26, 2002

I should also have noted that if you're running ColdFusion MX, it's a lot easier to implement this as a ColdFusion component. But I can't tell you how... still languishing in the world of CF4.5...

plook · Jun 27, 2002

Thank you both for your replies.

I tried pcorreia's post. But I am getting a little error returned by ColdFusion..

"XML parsing error: syntax error (error code 2, line 1, column 63, buffer offset 63)

The error occurred while processing an element with a general identifier of (CFWDDX), ....
"

Does that mean that my server does not support XML ?
If so, do both servers need to support it or only the Server A?

I have to admit that this way of doing it seems very easy.

Once again, thanks for your help

Dominic

pcorreia · Jun 27, 2002

It's possible that this problem is coming from the fact that we're interpreting the entire response from the page on Server A as a WDDX packet. You may find the answer if you comment out the <cfwddx> on the Server B page and instead output the CFHTTP.FileContent into a textarea or something. It should be a well-formed XML document. Post it back to this thread if you would like me to look at it and see if I can find any problems. Even white space is of particular concern, so be sure you know exactly where the response begins and ends.

With respect to XML support, all versions of ColdFusion since at least 4.5 have supported WDDX, which is actually a kind of XML.

plook · Aug 11, 2002

I have been trying to figure out a way to get this to work for about a month now... since end of June actually. Unfortunately, I have yet to win.

In the previous post from pcorreia, I was told the following

"You may find the answer if you comment out the <cfwddx> on the Server B page and instead output the CFHTTP.FileContent into a textarea or something. "

So I tried the following..

<CFOUTPUT>
#CFhttp.FileContent#
</CFOUTPUT>

hoping it would display me something, however, I still get the XML parser error... (same error as above)..

looking for suggestions...

Thanks

pcorreia · Aug 12, 2002

Try this as a debugging step. Put this page on Server B and execute it. Then, post the contents of the <textarea> back to this forum and that will help us to figure out what the problem is.

Note that the comment tags are ColdFusion comments () and not HTML comments () as in your example. This will prevent the <CFWDDX> tag from executing at all, thus avoiding the XML error that is causing your problems.

Code:

<cfhttp url=&quot;[URL unfurl="true"]http://ServerA/queryDB.cfm&quot;[/URL] method=&quot;POST&quot;>
   <cfhttpparam name=&quot;sqlstring&quot; value=&quot;SELECT * FROM tblUsers WHERE userid=8&quot;>
</cfhttp>
<!--- <cfwddx action=&quot;WDDX2CFML&quot; input=&quot;#CFHTTP.FileContent#&quot; output=&quot;qryMyQuery&quot;> --->
<form>
  <textarea>
    <cfoutput>#CFHTTP.FileContent#</cfoutput>
  </textarea>
</form>

plook · Aug 12, 2002

thanks for your help, I actually have a different error message now inside the <textarea>. All it says now in it it's "connection failure". Normally I use the following to connect to the DB
<CFQUERY PASSWORD="#Application.DBPW#"
USERNAME="#Application.DBUSER#"
DBTYPE="ODBC"
DATASOURCE="#Application.DB#"
NAME="query_name"
datasource="DSN-name">
</CFQUERY>
So right now, in this file I use

<cfquery name="qryMyQuery"
datasource="DSN-name"
username="#Application.DBUSER#"
password="#Application.DBPW#"
DBTYPE="ODBC">
#FORM.sqlstring#
</cfquery>

Right now, for testing purposes, both files are on the same server, could that be why I'm getting a "connection failure" ? I don't know where this "connection failure" comes from...

pcorreia · Aug 12, 2002

Be aware that when you invoke the page with <CFHTTP>, your application variables will not be transmitted. So, you need to either set up static credentials on the page that hits the database, or pass credentials explicitly with <CFHTTPPARAM> tags. Aything you pass as <CFHTTPPARAM> is sent in clear text across the network, though, so think about that security risk carefully if you are sending database passwords.

plook · Aug 12, 2002

Thanks for the security tip pcorreia.

how would one try to find how to solve "connection failure" in such case ?

I looked at the passwords several times, looked to make sure the datasource name and all are written exactly the same way...(in the same order even)..but.. I still get the message "connection failure".

But since both files are on the same server as any other .cfm files, I don't see why there could be a "connection failure"... Unless it cannot connect to itself..

Anybody has a suggestion ? could the problem be simply the fact that i'm trying to connect a server to itself through CFWDDX ?

Thanks

plook · Aug 13, 2002

Ok, I did some tests... and it does not seem to matter whether I try to connect from the same server or from a remote server. I always get "connection failure".

Now, I am wondering if it's because I need to install or configure something... like WDDX's SDK ? Do I have to or it's part of ColdFusion Server?

Thanks

pcorreia · Aug 14, 2002

I don't believe the problem is with WDDX... it's installed as part of CF Server, and there is nothing you need to do to enable it.

Here's my next debugging suggestion: put a <CFPARAM> at the top of the page that queries the database (I guess we're calling it the "Server A" page) that initializes the FORM.sqlstring with a default, like this:

Code:

   <cfparam name=&quot;FORM.sqlstring&quot; default=&quot;SELECT * FROM tblUsers WHERE userid=8&quot;>

Then request the Server A page directly, in your browser. That will tell you if there's a problem querying the database. You should see a raw WDDX packet in your browser. Also, be sure you're not using any application-scope variables in the Server A page. When you call it with CFHTTP, it is not part of the calling application so application-scope variables from the Server B application will not be available.

Feel free at any point to post the code of the two pages you are using, or especially the full text of the error you are receiving.

plook · Aug 14, 2002

Thank you for the time you're taking to help me !

ok.. I tried what you suggested..but I I must have understood it wrong because it didn't work.

So, here is the code I use.

Server with the DB (Server A) : test_db_server.cfm
Remote file (Server B) : test_db.client.cfm

(test_db_client.cfm)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html><head><title>Untitled</title></head><body>

<cfhttp url="test_db_server.cfm" method="POST">
<cfhttpparam type="FORMFIELD" name="sqlstring" value="SELECT * FROM tbluser">
</cfhttp>

<cfwddx action="WDDX2CFML" input="#CFhttp.FileContent#" output="qryMyQuery">

<form>
<textarea>
<cfoutput>#CFhttp.FileContent#</cfoutput>
</textarea>
</form>

</body></html>

When this code is used I get the following error message :
------------------------
Error Diagnostic Information
XML parsing error: syntax error (error code 2, line 1, column 0, buffer offset 0)

The error occurred while processing an element with a general identifier of (CFWDDX), occupying document position (13:1) to (13:76).
--------------------------------
When I comment out the WDDX part as you suggested, I get a text area box... and all it says in it is "Connection Failure"

Regarding test_db_server.cfm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<cfhttp url="test_db_server.cfm" method="POST">
<cfhttpparam type="FORMFIELD" name="#FORM.sqlstring#" value="SELECT * FROM tbluser">
</cfhttp>

<html><head><title>Untitled</title></head><body>

<cfif CGI.REMOTE_ADDR EQ "IP_ADDRESS_OF_REMOTE_SERVER">

<cfquery name="qryMyQuery" datasource="DSN-name" username="#Application.DBUSER#" password="#Application.DBPW#" DBTYPE="ODBC">
#FORM.sqlstring#
</cfquery>

<cfheader name="Content-Type" value="text/xml">
<cfwddx action="CFML2WDDX" input="#qryMyQuery#">

<cfelse>
<cfheader statuscode="404" statustext="Page Not Found">
</cfif>

</body></html>

When I browse to test_db_server.cfm, it produces the following error :
----------------
An error occurred while evaluating the expression:
"#FORM.sqlstring#"
Error near line 3, column 38.
-------------------------------

So I tried removing the hash sign around "FORM.sqlstring" ..
and..when I browse to that server file again, it gives me a blank page.
When I view the html source
it really is blank.. and..here is what it contains

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head><title>Untitled</title>
</head>
<body>
</body>
</html>

Thanks again for your help...

pcorreia · Aug 15, 2002

You don't need the <CFHTTP> section in test_db_server.cfm. The reason you're getting the error you described when you browser test_db_server.cfm directly is because the <CFHTTP> is trying to request itself and pass the value of the form variable #sqlstring# with the request. But #FORM.sqlstring# doesn't exist when you request the page directly. In fact, if you solved this problem, you'd have an infinite loop because the page requests itself. When you request test_db_client.cfm, instead of a WDDX packet the page is returning an error, which can't be parsed as an XML packet (hence your XML parsing error). Take out the <CFHTTP> section in test_db_server.cfm and you should be working fine.

If you still need to debug by requesting test_db_server.cfm directly, put a <CFPARAM> at the top of that page as I described in the last post. This will substitute for the form variable that would be passed by the <CFHTTP> call from test_db_client.cfm.

I think we're getting closer to a solution.

plook · Oct 6, 2002

ok, I tried with the "<cfparam name="FORM.sqlstring" default="SELECT * FROM tbluser">" at the top.

That way I don't get the error regarding for "#form.sqlstring#", but I cannot get the sqlstring to be passed from the other page.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

query a database on a different server

plook

IS-IT--Management

Silvano

Programmer

pcorreia

Programmer

pcorreia

Programmer

plook

IS-IT--Management

pcorreia

Programmer

plook

IS-IT--Management

pcorreia

Programmer

plook

IS-IT--Management

pcorreia

Programmer

plook

IS-IT--Management

plook

IS-IT--Management

pcorreia

Programmer

plook

IS-IT--Management

pcorreia

Programmer

plook

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor