How to retrive information from database using queries in class files

[Sabarish Venkat]

Hi all,

How to retrive information from database using queries in class files. Can i directly write queries in class files for grails or do we have some special way to do it.

[Ashish Agre]

Means you have a class named class Queries { def q = "select * from mytable" }
you want to use this q variable or what..??? question not clear..???

[Sabarish Venkat]

exactly !... How i will get that query to execute now

[Ashish Agre]

import groovy.sql.Sql
import yourpackage.Queries

class Utility {
def sql = Sql.newInstance("jdbc:,mysql://localhost:3306/myapp", "root", "yourpwd", "com.mysql.jdbc.Driver")
def q = new Queries().getQ() // this will automatically invoke getter method on Q and u will get value here
def isExecuted = sql.executeUpdate(q)
if(isExecuted == 1) {
println "${q} executed successfully"
} else {
println "Failed to execute ${q}"
}
}

This way i am doing

[Sabarish Venkat]

As am using Netbeans to develop , it shows me an error in the line

def sql = Sql.newInstance("jdbc:,mysql://localhost:3306/myapp", "root", "yourpwd", "com.mysql.jdbc.Driver")

saying unable to resolve class Sql even after i had import the

import groovy.sql.Sql

file. Do i need to add any library. Also instead of giving the whole url path, username and password of the sql, why cant i give the "datasource" as an instance. Do that grails know the path of datasource configured in datasource.groovy file

[Ashish Agre]

Actually i am beginner. i am also using netbeans i dont know how to use that datasource in .groovy file
I thought this is basic so will help you, you need to import groovy.sql somthing jar file in your library. I too had that problem now it is solved

[Ashish Agre]

Ashish Agre wrote:

def sql = Sql.newInstance("jdbc:mysql://localhost:3306/myapp", "root", "yourpwd", "com.mysql.jdbc.Driver")

this is correct line remove , from above that is a mistake

[Sabarish Venkat]

I tried it already ashish, How many files you will keep on giving that path in all class files. Wont it reduce the performance of application. surely it will. Instead of giving path each time just give the name "datasource" alone as a reference to it. This will be good one but unfortunately its not recognizing the reference and i donno why its so..
for example:
Instead of ,

def sql = Sql.newInstance("jdbc:mysql://localhost:3306/myapp", "root", "yourpwd", "com.mysql.jdbc.Driver")

can give like,

def sql = Sql(datasource)

Where this datasource is what we configured already in datasource.groovy file

[Ashish Agre]

i said it was just for basics. Have tried this line of code you just gave me is it working fine or not ???

[Sabarish Venkat]

ya i already worked out it. but yet not getting the way what i said.. If i get the prob surely ill let you know how it worked

[Ashish Agre]

use variable : static dataSource

then try
def sql = Sql.newInstance(dataSource) //
Class java.sql.SQLException
Message No suitable driver found for com.mysql.jdbc.Driver

This is the error can you figure out

[Ashish Agre]

This might help us ;)

Ref : http://stackoverflow.com/questions/425294/sql-database-views-in-grails

[Sabarish Venkat]

Do you get the error like

Message No suitable driver found for com.mysql.jdbc.Driver

if so include the mysql connector jar file in the library of your application the problem will get solved. I have seen that link earlier with that only i got to know about directly giving reference to sql but when i use the same method it doesnt know what is that datasource and also asking to resolve the sql class.

def sql = Sql(datasource)

May be any experts or Moderator have to help