Page 1 of 1
[Solved] SQL Result Set
Posted: Mon Jan 10, 2011 10:48 pm
by AutoKAD01
Please bear with me, I'm pretty new at this.
Here is my code so far...
Code: Select all
Sub GETCityStateZip
dim Context
dim DB
dim Conn
dim Stmt
dim Result
dim strSQL As String
dim oForm As Object
dim Company As Object
dim CityStateZip As Object
oForm = ThisComponent.Drawpage.Forms.GetByName("Main")
Company = oForm.getByName("CompanyName")
CityStateZip = oForm.getByName("CityStateZip")
if Company.CurrentValue <> "" then
Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
DB = Context.getByName("EJK_JOBS")
Conn = DB.getConnection("","")
Stmt = Conn.createStatement()
strSQL = "SELECT City, State, ZIPCode FROM CompanyClient WHERE CompanyClientName = " + "'"+Company.CurrentValue+"'"
Result = Stmt.executeQuery(strSQL)
Conn.close()
CityStateZip.updatestring(Result)
else
endif
End Sub
The end result is that I want to put the found City, State, ZIPCode from the SQL statement into the CityStateZip text box control. I know I need some additional code, but I am not sure what at this point.
Please point me in the right direction.
Thanks!
Re: SQL Result Set
Posted: Tue Jan 11, 2011 1:31 pm
by rudolfo
I think the
Conn.close() that closes the database connection is at the wrong place. Actually because you did not create the connection, but re-used the existing one, you should not close it at all.
In all the languages that I know closing the connection will also close/destroy child objects like statements and result sets(1). But you still need an active result set for the
updatestring() method. So try:
Code: Select all
Result = Stmt.executeQuery(strSQL)
CityStateZip.updatestring(Result)
' and for the sake of a clean programming style
Result.close()
Stmt.close()
------------------
1) That's also true for C: Depending on the database driver it will do an internal deep de-allocation of objects and resources or it will dump core. In the second case the OS will close your process and then of course the statement and cursor handles are gone as well

Re: SQL Result Set
Posted: Tue Jan 11, 2011 1:47 pm
by rudolfo
Hm, I did not really inspect your code closely enough. Actually you are not
re-using the existing connection but creating a new one. So you have good reasons to close the connection object. But you will introduce a lot of hassle if you open a second connection to the same database. As you already have the form object in the macro code, it is easy to retrieve the handle of the currently active connection:
And in this case you should definitely not use
oConn.close()
Re: SQL Result Set
Posted: Tue Jan 11, 2011 6:51 pm
by AutoKAD01
Thanks rudolfo! Your explanations are very much appreciated and I understood the majority. I did a bit more investigating and after reading
http://wiki.services.openoffice.org/wik ... ase_Access I got my code to work.
Code: Select all
Sub GETCityStateZip
dim Conn
dim Stmt
dim Result
dim strSQL As String
dim oForm As Object
dim Company As Object
dim CityStateZip As Object
dim strResult As String
oForm = ThisComponent.Drawpage.Forms.GetByName("Main")
Company = oForm.getByName("CompanyName")
CityStateZip = oForm.getByName("CityStateZip")
if Company.CurrentValue <> "" then
Conn = oForm.ActiveConnection
Stmt = Conn.createStatement()
strSQL = "SELECT City, State, ZIPCode FROM CompanyClient WHERE CompanyClientName = " + "'"+Company.CurrentValue+"'"
Result = Stmt.executeQuery(strSQL)
Result.next
strResult = Result.getstring(1) + ", " + Result.getstring(2) + ", " + Result.getstring(3)
CityStateZip.Text = strResult
Result.close()
Stmt.close()
else
endif
End Sub
However, the updatestring method was not being recognized and i get the error:
Property or method not found: updatestring.
Hence using:
Is this improper? Is the updatestring method not bound to a textbox control?
Thanks!
Re: SQL Result Set
Posted: Tue Jan 11, 2011 8:15 pm
by RPG
Hello
Before you start with macros try to understand how forms are working. I think you can do it with a simple query.
If I'm wrong then study the the tutorials of
Benitez.
Romke
Re: SQL Result Set [Solved]
Posted: Tue Jan 11, 2011 8:58 pm
by AutoKAD01
Thanks Romke. I'll study the tutorials.
I was able to accomplish the same results using a simple query, but the resulting form was not flexible enough for its users. That and I am really enjoying learning more about oOO Basic.
Re: SQL Result Set [Solved]
Posted: Tue Jan 11, 2011 9:13 pm
by RPG
Hello
When you want learn more about macro's then you try to understand how services and interfaces are working. Then your code can be more easy.
See
here. Also the basetools of Benitez kan help you learning macro's
Romke
Re: [Solved] SQL Result Set
Posted: Wed Jan 12, 2011 5:32 pm
by AutoKAD01
Thanks again, Romke!