Calc macro select two or more fields from MYSQL

[Neil-B]

Hi all,

I have a macro in a Calc file that pulls info from a Base file which works well for me, however I am only pulling a single field from the query, all this does is pull a list of supplier from the base file and insert them into cells

This is the code I am using, I havent included the database connection part of it as I have no problem with that

---

Stmt = Conn.createStatement()
strSQL1 = " SELECT  Supplier_Name from Suppliers where Active = 'Active' " 
rem execute SQL statement;"
oResult =   Stmt.executequery(strSQL1)
 while	oResult.next
outputvalue = oResult.getstring(1)
list = list+1
print outputvalue
Sheet = ThisComponent.Sheets(8)
Sheet.getCellByposition (6,list).setstring  outputvalue
wend

what I need is to pull a second field from the database called ‘supplier_code’

I have tried altering the line below to include two fields but this only selects the first one

 strSQL1 = " SELECT  Supplier_Name, supplier_code from Suppliers where Active = 'Active' "

I then tried this below

    strSQL1 = " SELECT Supplier_Name from Suppliers where Active = 'Active' " 
oResult1 =   Stmt.executequery(strSQL1)
     while	oResult1.next
outputvalue1= oResult1.getstring(1)

strSQL2 = " SELECT supplier_code from Suppliers where Supplier_Name = '" & outputvalue1 & "' "
oResult2 =   Stmt.executequery(strSQL2)
    while 	oResult2.next
outputvalue2 = oResult2.getstring(1)
print outputvalue1
print outputvalue2
list = list+1

Sheet = ThisComponent.Sheets(8)
Sheet.getCellByposition (6,list).setstring  outputvalue1
Sheet.getCellByposition (5,list).setstring  outputvalue2
wend
wend

This gives me “Type: com.sun.star.lang.DisposedException” error, but it does give me the firs record set and errors on the second.

If I take the second "while " and “wend” comands out I get “Unexpected symbol: End Sub.”

Next i tried with the “do” comand to make it loop insted of the wend.

  	Do
strSQL1 = " SELECT Supplier_Name from Suppliers where Active = 'Active' " 
oResult1 =   Stmt.executequery(strSQL1)
oResult1.next
outputvalue1= oResult1.getstring(1)

strSQL2 = " SELECT supplier_code from Suppliers where Supplier_Name = '" & outputvalue1 & "' "
oResult2 =   Stmt.executequery(strSQL2)
oResult2.next
outputvalue2 = oResult2.getstring(1)

print outputvalue1
print outputvalue2

list = list+1
Sheet = ThisComponent.Sheets(8)
Sheet.getCellByposition (6,list).setstring  outputvalue1
Sheet.getCellByposition (5,list).setstring  outputvalue2


loop until outputvalue1 = ""

this did loop without errors but it only showed the first record set each time.

I did consider making two macros one for the supplier name and the second for the supplier code but I couldn’t be sure the records would be pulled in the same order.

I hope this makes sense to everyone

Any Ideas anyone?

Many Thanks Neil

[peterwt]

Your

strSQL1 = " SELECT  Supplier_Name, supplier_code from Suppliers where Active = ‘Active’ "

should produce a Result Set with two columns, one for the Supplier_Name and one for the supplier_code.
You have used

outputvalue = oResult.getstring(1)

to get the Supplier_Name into outputvalue. You need to add this line below

outputcode = oResult.getstring(2)

to get the supplier_code into the variable outputcode. That is assuming that supplier_code is a string type. If it is not a string type you need to use getXXX() where for an integer it would be getint().
See here for details of field Types.

[Neil-B]

Hi Peterwt, thank you for your answer, this worked very well for me with the minimum of additional coding aswell.

Many thanks Neil

[Ratslinger]

It seems you don’t have a complete picture on a result set. Each Result you retrieve is a record which may contain one or more fields from your Select SQL. Before retrieving another Result you need to get all required fields using the proper get() method (meaning string, int, long, double etc.). Also when first retrieving a result set, you should verify it actually contains something. Here is a complete working sample which works with four fields:

Sub DisplayUsingSQL
	Dim oForm as Object
	Dim oStatement as Object
	Dim oResult As Object
	Dim bCursorTest As Boolean
	DIM stName AS STRING
	DIM stSQL AS STRING
	DIM loID AS LONG
	DIM loValue1 AS Long
	DIM loValue2 AS Long
REM Get access to the current form
	oForm = ThisComponent.Drawpage.Forms.getByName("Form") 'Get Form to access payment grid
REM prepare for SQL statement
	oStatement = oForm.ActiveConnection.createStatement() 'Create an SQL statement object
REM Set to Scroll_Sensitive
	oStatement.ResultSetType = 1005
REM Prepare the text of the SQL
REM  NOTE:  FSTABLE1 is not ""FSTABLE1"" because all capital letters were used
	stSQL = "SELECT * FROM FSTABLE1"
REM Get the first record returned
	oResult = oStatement.executeQuery(stSQL)
REM Check if record set returned
    bCursorTest = oResult.first
REM Exit if record set is empty
    If bCursorTest = "False" Then
    	MsgBox "No Records Found"
     	Exit Sub
    End If
REM Cycle through all records
	While oResult.next
REM Next four lines are the fields within the record returned
		loID = oResult.getLong(1)
		stName = oResult.getString(2)
		loValue1 = oResult.getLong(3)
		loValue2 = oResult.getLong(4)
REM Displays the record returned
		MsgBox "ID = " & loID & chr$(13) _
				& "Name = " & stName & chr$(13) _
				& "Value1 = " & loValue1 & chr$(13) _
				& "Value2 = " & loValue2
	Wend
	If oResult.last Then
		MsgBox "Number of Records in Query is: " & oResult.getRow()
	End If
End Sub

For your select:

strSQL1 = " SELECT  Supplier_Name, supplier_code from Suppliers where Active = 'Active' "

when you have

SupplierName = oResult2.getstring(1)

This retrieved the Supplier_Name field. To then retrieve the supplier_code field the next line would be something like:

SupplierCode = oResult2.double(2)

I say ‘something like’ and use ‘double’ because it is not actually known from the post the field type you are retrieving.

Here is a further explanation of result sets - click here and within that discussion (here - near bottom of page) is a matrix of the get() types.

If this answers your question please click on the (upper left area of answer).

[Neil-B]

Hi Ratslinger, Thank you for your answer, the "strSQL1 = " SELECT Supplier_Name, supplier_code from Suppliers where Active = ‘Active’ " section of your answer worked very well and is only a line or two more in my code aswell.

Many Thanks Neil