# calc macro select two or more fields from MYSQL

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

edit retag close merge delete

Sort by » oldest newest most voted

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


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.

more

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

( 2017-08-30 20:35:56 +0200 )edit

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.

more

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

Many thanks Neil

( 2017-08-30 20:38:05 +0200 )edit