MySQL Retrieve Value with pmADO

This is the main technical forum for discussing problems that were encountered by users while developing PROMOTIC applications, and their possible solutions.

MySQL Retrieve Value with pmADO

Postby Beth » Fri 18. May 2018 16:11:04

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 ?
Beth
 
Posts: 3
Joined: Fri 18. May 2018 15:51:58

Re: MySQL Retrieve Value with pmADO

Postby admin » Wed 23. May 2018 8:03:37

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
admin
Administrátor
 
Posts: 59
Joined: Wed 12. Dec 2012 15:43:09

Re: MySQL Retrieve Value with pmADO

Postby Beth » Wed 23. May 2018 11:42:27

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
Beth
 
Posts: 3
Joined: Fri 18. May 2018 15:51:58

Re: MySQL Retrieve Value with pmADO

Postby Kazimierz Heczko » Fri 25. May 2018 7:55:35

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
Kazimierz Heczko
 
Posts: 60
Joined: Thu 13. Dec 2012 9:28:56

Re: MySQL Retrieve Value with pmADO

Postby Beth » Fri 25. May 2018 13:15:22

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
Beth
 
Posts: 3
Joined: Fri 18. May 2018 15:51:58

Re: MySQL Retrieve Value with pmADO

Postby Kazimierz Heczko » Fri 08. Jun 2018 11:51:15

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
Kazimierz Heczko
 
Posts: 60
Joined: Thu 13. Dec 2012 9:28:56

Re: MySQL Retrieve Value with pmADO

Postby tara » Tue 26. Mar 2019 10:07:07

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:
tara
 
Posts: 1
Joined: Tue 26. Mar 2019 9:56:15

Re: MySQL Retrieve Value with pmADO

Postby VinayParde » Tue 06. Aug 2019 11:08:37

Great information it was very useful..
VinayParde
 
Posts: 2
Joined: Mon 05. Aug 2019 7:37:50


Return to I don't know how to...

Who is online

Users browsing this forum: No registered users and 1 guest

cron