We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Do a select statement

Medium Priority
232 Views
Last Modified: 2012-06-21
Hi,

I have created a select query that works fine but I need to create another one.
in a cfquery can you please tell me how to create cfquery with select statement in it.

<cfscript>
classLoader = createObject("java", "java.lang.Class");
classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver");
dm = createObject("java","java.sql.DriverManager");

con = dm.getConnection("jdbc:odbc:DRIVER={MySQL ODBC 3.51 Driver}; SERVER=xx; PORT=xx; DATABASE=xx; USER=xx; PASSWORD=xx; OPTION=16;");

st = con.createStatement();
rsCountry = st.ExecuteQuery("Select CountryName FROM CountryTbl where CountryName <> 'United States'");
GetCountryData = createObject("java", "coldfusion.sql.QueryTable").init(rsCountry);
</cfscript>

Here I want to do another select statement but I am not sure how.
another select statement that would say.

SELECT StateTbl.StateName,
CityTbl.CityName FROM StateTbl LEFT JOIN CityTbl ON
CityTbl.StateID = StateTbl.StateID
I am not sure what is the name of my datasource??

<cfquery datasource="con" name="GetCity" >
SELECT StateTbl.StateName,
CityTbl.CityName FROM StateTbl LEFT JOIN CityTbl ON
CityTbl.StateID = StateTbl.StateID
</cfquery>
Comment
Watch Question

CERTIFIED EXPERT

Commented:


this is the correct format for a cfquery...

<cfquery datasource="con" name="GetCity" >
  SELECT StateTbl.StateName,  CityTbl.CityName
  FROM StateTbl
    LEFT JOIN CityTbl ON CityTbl.StateID = StateTbl.StateID
</cfquery>

In the example above, you would have a data source called "con"

If you look in your CF admin, located at www.yourdomain.com/cfide/administrator/ 
Choose Datasources from your left menu, you should see datasources or you can add one there.

This is the connection to the database you have setup already, once setup here, you can use it throughout your app just referring to it by name


lulu50Web application

Author

Commented:
so, should I say something like this.
but it still says the datasource is not available.

<cfquery datasource="con" name="GetState" >
  SELECT StateTbl.StateName,  CityTbl.CityName
  FROM StateTbl
    LEFT JOIN CityTbl ON CityTbl.StateID = StateTbl.StateID
</cfquery>
CERTIFIED EXPERT

Commented:
That looks like the same cfquery as in the previous post... yes, that is how you do a SELECT statement in Coldfusion.

Regarding the datasource, did you check the CFIDE/administrator as I suggested?   Do you have a data source there called "con" ?     Do you have any data sources there that you can use?  

lulu50Web application

Author

Commented:
gdemaria: I am new to this.
this link takes me to a page like a hosting page!!!
www.yourdomain.com/cfide/administrator/ 

I am trying to understand.  I did create in my control panel an MySQL ODBC 3.51 driver called
lulu50_churchesbulleti  is this mean this is my datasource?

becasue if I do this:
<cfquery datasource="con"  I will get an error that Data source not found.
I think "con" is my connection string to my database not my datasource

Please help
lulu50Web application

Author

Commented:
How can I create a datasource?
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
lulu50Web application

Author

Commented:
Partially correct
CERTIFIED EXPERT

Commented:
Which part wasn't correct?

You should not close a question with a B grade if you have more questions..
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.