Highlighted
Honored Contributor.
Honored Contributor.
698 views

SQL script thru UFT (14.02) - how to?

Hi.

Is it possible to execute the SQL script in UFT?

user_privileges_script = "DECLARE " &_
                        "    l_user_id  varchar2(9); " &_
                        "    l_count    number; " &_
                        "        BEGIN " &_
                        "        l_user_id := '" & usr_id & "'; " &_
                          "          select count(*) " &_
                        "          into l_count " &_
                        "          from bb_code_table " &_
                        "          where code_type = 993 " &_
                        "            and code_desc = l_user_id " &_
                        "            and nvl(end_date,sysdate+1)>sysdate; " &_
                        "          if l_count = 0 then " &_
                        "            insert into bb_code_table bb " &_
                        "            (bb.code_type, bb.code, code_desc, start_date, update_date, update_user) " &_
                        "            values (993, " &_
                        "                (select max(code)+1 " &_
                        "                 from bb_code_table " &_
                        "                 where code_type = 993), " &_
                        "                 l_user_id, " &_
                        "                 sysdate, " &_
                        "                 sysdate, " &_
                        "                 l_user_id); " &_
                        "          end if; " &_
                        "          commit; " &_
                        "        END;"

I am trying to use my regular database connection function but I get an error so I am not sure it actually does something.

 Set objDB = CreateObject ("ADODB.Connection")
        objDB.Open ("Driver={Microsoft ODBC for Oracle}; Server = "& dB_ENV &";Uid= " & dB_Usr &"; Pwd = " & dB_Pswd & ";")
        set recordset = CreateObject ("ADODB.Recordset")  
        On error resume next
        ' run the query and disable the error control
        set recordset = objDB.execute(dB_Query)

 

Is there a *certain* way to run this??

 

0 Likes
9 Replies
Highlighted
Honored Contributor.
Honored Contributor.

Hello... anyone ??!?!

This PL/SQL script is the only thing now that obligates me to run manually before my automation can start...

 

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

hi, there

To my understanding, UFT is irrelevant executing COM-based object in VBS. The syntax looks good to me. I suggest you check the error code of the error instead of bypassing it and see what the issue is.

Thank you.

James

0 Likes
Highlighted
Honored Contributor.
Honored Contributor.

Hi James, as always, thank-you for stepping up and helping.

I dont understand something... "UFT is irrelevant executing COM-based object in VBS" what do you mean??

The syntax of the PL/SQL script is surely valid, it runs fine thru a SQL editor (TOAD, PL/SQL developer etc...)

However, when I run it in my code that handles SQL queries, I check this :

Set objDB = CreateObject ("ADODB.Connection")
        objDB.Open ("Driver={Microsoft ODBC for Oracle}; Server = "& dB_ENV &";Uid= " & dB_Usr &"; Pwd = " & dB_Pswd & ";")
set recordset = CreateObject ("ADODB.Recordset")  
On error resume next
' run the query and disable the error control
set recordset = objDB.execute(dB_Query)
error_holder = recordset.EOF
' if the 'error_holder' returns empty then most probably the table does not exist!!
If (error_holder = "") Then
    Set recordset = nothing
    objDB.close
    Set objDB = nothing
    Get_Data_from_SQL = "TBL_ROWS"
    Exit function
End If

and since this script has no rows to return then my function exits without me knowing "what went wrong"...

Any other ideas? Perhaps I can run it thru external files? (from the UFT)

 

 

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

hi, 

In your code, you have 'On error resume next' which bypasses any error. Please try to remove that and run the code again. I suspect there is an error in this script. 

set recordset = objDB.execute(dB_Query)

Also, you have the Err object to check the error detail even you set the script to resume on error. 

On Error Resume Next

Your code.

MsgBox "Error # " & CStr(Err.Number) & " " & Err.Description

Err.Clear ' Clear the error.

Thanks.

James

0 Likes
Highlighted
Super Contributor.
Super Contributor.

Lior,

From past experience, it might be that you are using an ODBC client that is not supported by UFT (32 bit vs. 64 bit).

I suggest you do a short search (even in this forum) for some previous answers and queries around this topic.

Good luck!

0 Likes
Highlighted
Honored Contributor.
Honored Contributor.

Thanks Tal.

 

The connection string to the Oracle DB works well for "regular" queries (select..., insert..., update...) , they run just fine and provide results.
Do you think that another conn. string is required for scripts..? I believe not. However I will search here, again, and see if there is something useful.

 

Thank-you.

 

0 Likes
Highlighted
Super Contributor.
Super Contributor.

Lior,

Please double check the ODBC Client aspects...

I also found some more data that may be relevant as well:

I hope it will help...

https://community.softwaregrp.com/t5/UFT-Practitioners-Forum/UFT-12-02-connection-to-Oracle-database/td-p/255495 

If this is not in the direction of what you get as an error, I suggest to open a support ticket.

Good luck!

0 Likes
Highlighted
Honored Contributor.
Honored Contributor.

I will check this put.

Thank-you Tal.

0 Likes
Highlighted
Honored Contributor.
Honored Contributor.

Hello Tal.

 

Checked this out thouroughly, that article speaks about the different version between 32 and 64 bit systems, and the approach on how to use the Oracle ODBC driver, via UFT .

But thanks all the same. The issue is within the SQL code itself, not the drivers. This is what I think.

 

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.