[Solved] SQL Result Set

[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!

[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

[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:

Code: Select all

oConn = oForm.ActiveConnection

And in this case you should definitely not use oConn.close()

[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:

Code: Select all

CityStateZip.Text = strResult

Is this improper? Is the updatestring method not bound to a textbox control?

Thanks!

[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

[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.

[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

[AutoKAD01]

Thanks again, Romke!