I can't get it to work and I can't find any documentation or any references at all in the forums or in the knowledge base!
What version of Metastorm BPM are you using?
Can you give us an idea of what you are trying to do?
o you have problems with ado?
try ework.SelectSQL("SELECT * FROM Table"), it run query via standard engine connection
I am using BPM v 7.6. Here's my vbScript, select a couple of fields, update the tables where they came from and then do a couple of inserts:
sub vbsEnterGRx (PatNameLast,PatNameFirst,PatNameMI,SSN,PatEmail,PatSex,PatDOB,Lang) dim GRxAcct, PatID, DateNow
DateNow = ework.System.Time GRxAcct = ework.SelectSQL("SELECT next_id_int+1 FROM CP_SEQ_acct_id","DSN=CPTest;UID=;PWD=") ework.ExecSQL("UPDATE CP_SEQ_acct_id SET next_id_int = " + GRxAcct,"DSN=CPTest;UID=;PWD=") PatID = ework.SelectSQL("SELECT next_id_int+1 FROM CP_SEQ_pat_id","DSN=CPTest;UID=;PWD=") ework.ExecSQL("UPDATE CP_SEQ_pat_id SET next_id_int = " + PatID,"DSN=CPTest;UID=;PWD=") ework.ExecSQL("INSERT INTO cppat (pat_id,lname,fname,mname,ssn,email,gender_cd,birth_date,primary_lang_cd,add_date,registration_date,pat_status_cn,pat_type_cn) VALUES (" + PatID + ",'" + PatNameLast + "','" + PatNameFirst + "','" + PatNameMI + "','" + SSN + "','" + PatEmail + "','" + PatSex + "','" + PatDOB + "','"Lang + "',getdate(),getdate(),1,1)","DSN=CPTest;UID=;PWD=") ework.ExecSQL("INSERT INTO cp_acct (pat_id,id,acct_type_cn,resp_pty_yn) VALUES (PatID,GRxAcct,0,0)","DSN=CPTest;UID=;PWD=") ework.ExecSQL("INSERT INTO cust (id,pat_id) VALUES (GRxAcct,PatID)","DSN=CPTest;UID=;PWD=")
end sub
Everything works except this one line:
ret = ework.ExecSQL("INSERT INTO cppat " + _ "(pat_id,lname,fname,mname,ssn,email,gender_cd,birth_date,primary_lang_cd,add_date,registration_date,pat_status_cn,pat_type_cn" _ + ") VALUES (" + _ PatID + ",'" + PatNameLast + "','" + PatNameFirst + "','" + PatNameMI + "','" + SSN + "','" + PatEmail + "','" + PatSex + "','" + _ PatDOB + "','"Lang + "',getdate(),getdate(),1,1)","DSN=CPTest;UID=;PWD=")
I would suspect it is the date "PatDOB" myself. VBScript is probably translating that into a format that SQL Server does not understand.
Is there a designer log entry? There normaly is one for SelectSQL() in scripts, not sure about ExecSQL().
It doesn't get that far. It doesn't even pass the syntax check, but, of course, there are no error messages. You just can't choose it in the formula wizard for inline script.
BTW, the format of the PatDOB is the same as getdate() and it works fine in the DoThis property of the Action that invokes it. But it is getting complex, with other SQL that will be conditional, in small groups of statements, that doesn't fit well into DoThis...
Sorry for the stupid mistake. I just saw the missing + (plus sign):
ret = ework.ExecSQL("INSERT INTO cppat " + _ "(pat_id,lname,fname,mname,ssn,email,gender_cd,bir th_date,primary_lang_cd,add_date,registration_date,pat_status_cn,pat_type_cn" _ + ") VALUES (" + _ PatID + ",'" + PatNameLast + "','" + PatNameFirst + "','" + PatNameMI + "','" + SSN + "','" + PatEmail + "','" + PatSex + "','" + _ PatDOB + "','" + Lang + "',getdate(),getdate(),1,1)","DSN=CPTest;UID=;PWD=")
All so easy to do.
Gotta love the C# syntax checking in version 9!
Yeah!!! Can't wait. But there is vbScript syntax checking here, in 7.6, only that the details are all secret.
Try create new file with vbs extension. Paste code into thi file. duble click on it. Windows will try to execute it. If you have syntax error, you will see where There are some other methods, but this is quite easy and fast
What about stuff like ework...?
With variables, you can just replace ework. with ewor_. Without option explicit, all should works fine. With methods like selectslq you have to put dummy functions, or comment. It's only solution for simple syntax check.
I guess that's all we've got and it's better than nothing (the alternative).
Could you give me a hand with calling server-side vbScript with arguments? I am going crazy. Nothing works (for me) and the documentation is really sparse...
