Reading variable row with ODBC

[422463]

Hello,

We configured DAQfactory to receive a number from the Mitsubishi-PLC which indicates an error message to be displayed. This errormessage should also be send via the com-port to an big LED-display.

Error messages are stored in an access-database.

We would like to use the number send by the PLC to lookup the text (4 fields) to be displayed in the access database via an ODBC-connection.

This sequence works fine if we use as 8th sentence: (see sequence transript below)

qr = db.Query(dbase,"select * from alarmList where Name = 123")

where 123 is the content of a row in the Name column.

But as the number should change depending on the number send by the PLC I want to exchange 123 for a variable.

This is what I use:

private dbase
private qr
global string foutmelding
foutmelding="225" // temporary put fixed number into string, normally DAQfactory would acquire this via channel
//open db DATA via ODBC
dbase = db.Open("DATA")
//Define which row to read
qr = db.Query(dbase,"select * from alarmList where Name = foutmelding")
// If I replace foutmelding with a number like 123 (Which is the content of one cell in alarmList table, this works fine)
Var.LK_Station = db.Field(qr,"Station") // Station is a column name
Var.LK_Regel1 = db.Field(qr,"Regel1") // Regel1 is a column name
Var.LK_Regel2 = db.Field(qr,"Regel2")
Var.LK_Regel3 = db.Field(qr,"Regel3")
delay(1)
catch()
	 System.ErrorMessage(strLastError) 
endcatch
//close db
db.CloseQuery(qr)
db.Close(dbase)

I suppose the use of a variable in this way isn't allowed, is there a solution to get this to work?

Best regards,

Roel Ossewaarde

[AzeoTech]

You can't put the variable name inside the string like that. When you do, DAQFactory just thinks its a word, not a variable. This is true in just about every programming language. If you want to insert the contents of the variable you either need to concatenate strings (using +), or use the format() function. Here are both:

qr = db.Query(dbase,"select * from alarmList where Name = " + foutmelding)

qr = db.Query(dbase,format("select * from alarmList where Name = %s", foutmelding))

The first is probably easier, but the format() gives you more options on formatting numbers and other things, so I just wanted to include it here. Note that if foutmelding is a number instead of a string, it'd be different:

qr = db.Query(dbase,"select * from alarmList where Name = " + doubletostr(foutmelding))

qr = db.Query(dbase,format("select * from alarmList where Name = %d", foutmelding))

[sreedevib]

Hi,

I have a string variable value like "PDA18-010".

global string ui = "PDA18-010"

I used below mentioned two string methods to get variable row from ODBC. Both are not working for me.

When I display that string value using variable value component, it is showing as Nan.

Could you please help me on this?

Thank you.

[AzeoTech]

Can you please show me exactly what you are doing?

[sreedevib]

Actually I have two string variables. I need to get the row from database based on those two variables. How to write the query for that?

global string pda //pda values will be like PDA18-010. In this numbers will vary like PDA03-004

global string h

pda = system.EntryDialog("PDA")

h = system.EntryDialog("h")

qr = db.Query(dbase, "Select * from table_1 WHERE PDA = "+ pda + "and" + "H=" + h)

Thank you for help.

[AzeoTech]

I think you are missing the required spaces around the "and". Its always good to print the concatted string in command alert to make sure it looks right. Just do:

? "Select * from table_1 WHERE PDA = "+ pda + "and" + "H=" + h

in the Command / Alert window and you'll see your problem. If h = "h" and pda = "pda", then the string will look like this:

Select * from table_1 WHERE PDA = pdaandH=h