Ask Your Question
0

calc macro select two or more fields from MYSQL

asked 2017-08-30 04:37:45 +0200

Neil-B gravatar image

updated 2017-08-30 13:47:16 +0200

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-08-30 16:25:24 +0200

Ratslinger gravatar image

updated 2017-08-30 16:26:38 +0200

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

edit flag offensive delete link more

Comments

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

Neil-B gravatar imageNeil-B ( 2017-08-30 20:35:56 +0200 )edit
1

answered 2017-08-30 16:08:27 +0200

peterwt gravatar image

updated 2017-08-30 16:10:49 +0200

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.

edit flag offensive delete link more

Comments

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

Many thanks Neil

Neil-B gravatar imageNeil-B ( 2017-08-30 20:38:05 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-08-30 04:37:45 +0200

Seen: 200 times

Last updated: Aug 30 '17