Re: [QF-Test] Using a generic procedure for SQL UPDATE and INSERT where number of elements is variable.

• Subject: Re: [QF-Test] Using a generic procedure for SQL UPDATE and INSERT where number of elements is variable.
• From: Martin Moser <martin.moser@?.de>
• Date: Fri, 06 Feb 2015 16:33:15 +0100

Hi,

you can make such procedures dynamic.

Let's say you specify a procedure with the parameters:

column1 = col1
value1 = val1
column2 = col2
value2 = val2
etc.

Then your procedure could contain a script building the SQL statement
like this:

Server script (Jython):

maxVars = 100
dbUpdateCall_Part1 = "insert into table values("
cols = []
values = []
for i in range(1,maxVars):
    try:
        column = rc.lookup("column" + str(i))
        value = rc.lookup("value" + str(i))
        if column:
            cols.append(column)
            values.append(value)
    except:
        break

fullCall = dbUpdateCall_Part1
for col in cols:
    fullCall = fullCall + col + ","

#:-1 should delete the last comma which is too much
fullCall = fullCall[:-1] + ") VALUES ("

for val in values:
    fullCall = fullCall + "'" + val + "',"

#:-1 should delete the last comma which is too much
fullCall = fullCall[:-1] + ")"

#print "Full Call", fullCall
rc.setLocal("fullDBCall", fullCall)


--> At end the variable "fullDBCall" should contain a command like:

insert into table values(column1,column2) values ('val1','val2')

Then put that call to a procedure call of
"qfs.database.executeStatement" and send the statement to your database.


As you can see in the script above, you can specify at maximumum 100
columnX and valueX pairs, which will be treated in the for loop.


Best Regards,
Martin


Am 31.01.2015 um 00:20 schrieb Rick Brasche:

Happy Friday, everyone.

In a number of recent automation scripts we’ve been requested to create,
text information on the screen is fetched and written to a SQL table. In
the past, we had 4 text elements we read per page, and created a generic
INSERT INTO procedure that listed 4 columns and 4 variables. We have a
similar one that does UPDATE as well.

My current project has one page that has 5 text elements to a single
record, another page that has three, another that has 10. Another
project has similar variety.

Can I write a generic database procedure that will allow me to assign
the necessary columns at the specific test procedure call? So far it
only allows what I explicitly write the SQL to be. If I specify 4
columns and 4 values, it has to be 4 in the procedure call.

Or do I just have to accept that I will write separate statements for
each page as needed, or write three generics (three columns, two
columns, 1 column) and repeatedly call as many as needed to get the
desired number of UPDATE or INSERTs?

I’m really new at SQL so if there’s an obvious query solution, please
don’t judge too harshly J

Thanks in advance, have a good weekend.

Rick Brasche,

Information Systems Business Process and Data Entry Automation

Home of Application Testing

------------------------------------------------------------------------------------------------------------------------------

“The difficult we do immediately, the impossible just takes a bit
longer.” – US Navy SeaBees.

“If it ain’t broke, you’re not trying hard enough.” – “the Red Green Show”

NOTICE: This email message and/or its attachments may contain
information that is confidential or restricted. It is intended only for
the individuals named as recipients in the message. If you are NOT an
authorized recipient, you are prohibited from using, delivering,
distributing, printing, copying, or disclosing the message or content to
others and must delete the message from your computer. If you have
received this message in error, please notify the sender by return
email.



_______________________________________________
qftest-list mailing list
qftest-list@?.de
http://www.qfs.de/mailman/listinfo/qftest-list

--
Martin Moser

E: martin.moser@?.de
T: +49 (0)8171 38648-14
F: +49 (0)8171 38648-16

Quality First Software GmbH | www.qfs.de
Tulpenstr. 41 | 82538 Geretsried | Germany
GF Gregor Schmid, Karlheinz Kellerer
HRB München 140833

• References:
  • [QF-Test] Using a generic procedure for SQL UPDATE and INSERT where number of elements is variable.
    • From: Rick Brasche

• Prev by Date: Re: [QF-Test] Table in GWT
• Next by Date: Re: [QF-Test] Regarding QF support for 64 bit browsers and JDK
• Previous by thread: [QF-Test] Using a generic procedure for SQL UPDATE and INSERT where number of elements is variable.
• Next by thread: [QF-Test] Regarding QF support for 64 bit browsers and JDK
• Index(es):
  • Date
  • Thread