MySQL Retrieve Value with pmADO

[Beth]

Hello,

I am trying to connect my project to a MySQL database and retrieve a value and display it in the panel. I successfully connected to the database (dbIsOpen returns 1) but when I add a TextBox and in the PmObject property I put the Db.execute method (select max(time) from MyDB), it displays only an empty string.

Am I doing it wrong ?

[admin]

Hello,

Please do not insert the database command script directly into the graphic item. This script is executed on the client side and therefore cannot retreive the data from database.

Follow these steps:

- create PmData object. Create a variable in this object.
- create your user method in any PROMOTIC object (timer, root, etc.)
- call this method as you need to retreive the database data

Plese study following links:
https://www.promotic.eu/en/pmdoc/Subsys ... /MySql.htm
https://www.promotic.eu/en/pmdoc/Subsys ... amples.htm
https://www.promotic.eu/en/pmdoc/Object ... Cyclic.htm
https://www.promotic.eu/en/pmdoc/Subsys ... aTypes.htm
https://www.promotic.eu/en/pmdoc/Object ... bMySql.htm
https://www.promotic.eu/en/pmdoc/Subsys ... /Gr_Db.htm

[Beth]

Hello,

Yes Thank you I did it in the script and it works. I can retrieve a value. The problem I am having now is how to retrieve a row with multiple items and visualize each one of them separately. I can get each one of them in the script but the output of the method is just one variant and I don't know how to pass multiple values to it.

Here is the script :

Dim oAdo, oRs, sSQL, bRes, Value1, Value2, Value3

Set oAdo = pMe.Pm("../PmAdo")
sSQL = "select * from myTable where Time = (select max(Time) from myTable )"

'open the database, if not open
If Not oAdo.DbIsOpen() Then
bRes = oAdo.DbOpen()
Else
bRes = true
End If

'if the database is open, then continue
If bRes Then
'opening the corresponding table
Set oRs = oAdo.RsOpen("", sSQL, "")
'checking whether the opening operation was comleted successfully
If Pm.IsValid(oRs) Then
'checking whether the table is not empty
If oRs.EOF And oRs.BOF Then
'Pm.Debug "Table (recordset) contains no records"
pResult = -1
Else
'if the table is not empty then it can be read as a 2-dimensional array
Value1 = oRs.Fields.Item("I1").Value
Value2 = oRs.Fields.Item("I2").Value
Value3 = oRs.Fields.Item("I3").Value
pResult = Value1
End If
Else
'Pm.Debug "Table opening error"
pResult = -1
End If
Else
'Pm.Debug "Database opening error"
pResult = -1
End If

Can you please show me how to do it ?

Thank you

[Kazimierz Heczko]

Hello,

methods in PROMOTIC can use as parameters:
a) simple values (number, string, ...)
b) one-dimensional arrays
c) two-dimansional arrays
The same situation is in result, it can be simple value, one- or two-dimensional array.
In your situation use VBScript function Array:
https://www.promotic.eu/en/pmdoc/Script ... /Array.htm

Code: Select all

Value1 = oRs.Fields.Item("I1").Value
Value2 = oRs.Fields.Item("I2").Value
Value3 = oRs.Fields.Item("I3").Value
pResult = Array(Value1, Value2, Value3)


Hope it helps

[Beth]

Hello,

Thank you for the quick response.

I made what you advised me but how can I select the value from the graphical panel. I need a box with Value 1, another with Value 2 etc ...

Thank you

[Kazimierz Heczko]

Hello,

when you read data from database, you have to use script in some graphics item with calling method placed in PmPanel object in application editor (in my example this method has name "MethodForReadData").
This method reads data from database and return data into graphics item, in which you call this method.
I suppose your graphics panel is in JavaScript mode, then you have to do something like next:

Code: Select all

var vRes = pMe.PmPanel.Methods.MethodForReadData();

if (Pm.IsValid(vRes))        //check if returned value is valid
{
  var sType = Pm.GetVarType(vRes, 1);          // write returned data type of vRes variable to sType variable
  if ((sType == "vbarray") || (sType == "PmArray"))  //if returned value is array - suppose one-dimensonal three items array
  {
    pMe.Items("/box1").Value = vRes.GetItem(0);    //read values from array and write it to item named "box1" - in your application use correct graphics item name
    pMe.Items("/box2").Value = vRes.GetItem(1);
    pMe.Items("/box3").Value = vRes.GetItem(2);
  }
}


Links to used objects and methods:
https://www.promotic.eu/en/pmdoc/Object ... arType.htm - description of GetVarType method
https://www.promotic.eu/en/pmdoc/Object ... mArray.htm - description PmArray object and its methods/properties
https://www.promotic.eu/en/pmdoc/Object ... /Items.htm - method for work with other graphics items - works only in graphics panel - defined in all graphics items

Hope this helps

Regards

[tara]

when you read information from the database, you need to utilize content in certain designs thing with calling technique put in PmPanel object in application editorial manager (in my model this strategy has name "MethodForReadData").

This strategy peruses information from a database and returns information into designs thing, in which you call this technique.

I guess your designs board is in JavaScript mode, at that point, you need to accomplish something like straightaway:

[VinayParde]

Great information it was very useful..