• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Do a select statement

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>
0
lulu50
Asked:
lulu50
  • 4
  • 4
1 Solution
 
gdemariaCommented:


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


0
 
lulu50Author 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>
0
 
gdemariaCommented:
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?  

0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
lulu50Author 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
0
 
lulu50Author Commented:
How can I create a datasource?
0
 
gdemariaCommented:
> I am new to this.   this link takes me to a page like a hosting page!!!
www.yourdomain.com/cfide/administrator/ 


Lol, you are a newbie! :)   When someone gives you a link with "yourdomain" in it... you should change "yourdomain" to whatever YOUR DOMAIN NAME is !  :)

> I did create in my control panel an MySQL ODBC 3.51 driver called   lulu50_churchesbulleti

It *might be* if it was also the name given for your CF data source, just try it and see !

If not, then you need to go into your  CF admin and create it there...

http://www.quackit.com/coldfusion/tutorial/coldfusion_datasource.cfm




> How can I create a datasource?

You need to go into your  CF admin located at    /cfide/administrator/  


0
 
lulu50Author Commented:
Partially correct
0
 
gdemariaCommented:
Which part wasn't correct?

You should not close a question with a B grade if you have more questions..
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now