topic Re: Native SQL (MS SQL) Help in Application Development and Automation Discussions

Native SQL (MS SQL) Help

bryan_cain — Tue, 17 Jul 2012 20:05:49 GMT

Hi All,

I have a need to reach into an external Vertex DB on SQLServer 2008. Easy enough on the surface, but the business wants to use select options to filter the data and I'd rather pass them into the query rather than filter after select for performance reasons.

So this works fine:

          OPEN dbcur FOR
          SELECT *
                  FROM [Vertex_RPT].[dbo].[LineItemView]
                 where usertransidcode = :<billing_doc>-vbeln
                   and lineitemnumber = :<billing_doc>-posnr

However, if I try to add this:

           OPEN dbcur FOR
          SELECT *
                 FROM [Vertex_RPT].[dbo].[LineItemView]
                where usertransidcode = :<billing_doc>-vbeln
                  and lineitemnumber = :<billing_doc>-posnr
                  and maindivname in :s_state

It fails because the underlying DB doesn't understand range tables. OK fine. So I change to this (pretend it's not hard coded):

l_state = '(''VIRGINIA,'' ''NORTH CAROLINA'')'
EXEC SQL.
OPEN dbcur FOR
Select *
       FROM [Vertex_RPT].[dbo].[LineItemView]
                where usertransidcode = :<billing_doc>-vbeln
                  and lineitemnumber = :<billing_doc>-posnr
                  and maindivname in ( :l_state )
END EXEC.

And this fails too because apparently I can only put one value in l_state for the DB to accept the query.

So finally, after much googling and banging my head against the wall, I came up with the below. As far as I can tell, it works if I open SQL Server management studio and execute the commands directly. When called from ABAP, it short dumps on the FETCH NEXT statement with 102 error. I know 102 is bad syntax, but I can't figure out where it is.

     data: l_sql type string.
     data: l_cursor(15) type c.
     data: l_params(100) type c.
     
l_params = '@lineOUT nvarchar(40) OUTPUT'.
l_cursor = 'dbcur'
CONCATENATE      'DECLARE'
                                   l_cursor
                                  'CURSOR FOR SELECT usertransidcode from [vertex_rpt].[dbo].LineItemView where usertransidcode ='
                                  <billing_doc>-vbeln
                                 'and lineitemnumber ='
                                 <billing_doc>-posnr
                                'and ltrim(rtrim(mainDivName)) in ('
                                 l_states
                                ')'
                                into l_sql
                                separated by space.
 
 
       EXEC SQL.
 
         exec sp_executesql :l_sql
 
       ENDEXEC.
 
         clear l_sql.
         CONCATENATE 'OPEN' l_cursor into l_sql SEPARATED BY space.
 
       exec sql.
 
         exec sp_executesql :l_sql
 
       endexec.
 
       IF sy-subrc <> 0.
         MESSAGE ID sy-msgid TYPE 'I' NUMBER sy-msgno
                 WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
       ENDIF.
       clear l_sql.
       CONCATENATE 'FETCH NEXT FROM' l_cursor 'INTO @lineOUT' into l_sql SEPARATED BY space.
       DO.
         EXEC SQL.
            exec sp_executesql :l_sql, :l_params, @lineOUT = :line ouptut
         ENDEXEC.
         IF sy-subrc = 0.
           APPEND line TO t_vertex.
         ELSE.
           EXIT.
         ENDIF.
       ENDDO.
 
       clear l_sql.
       CONCATENATE 'CLOSE' l_cursor INTO l_sql SEPARATED BY space.
 
       EXEC SQL.
         exec sp_executesql :l_sql
 
       ENDEXEC.
       IF sy-subrc <> 0.
         MESSAGE ID sy-msgid TYPE 'I' NUMBER sy-msgno
                 WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
       ENDIF.

What am I missing here? Thanks in advance.

Bryan

Re: Native SQL (MS SQL) Help

Former Member — Wed, 18 Jul 2012 14:01:39 GMT

Can you paste an example of what l_sql is on line 59 prior to execution? I have a feeling it's still the "IN" clause, but your final chunk of code doesn't have the states listed. I could probably spot the problem if I could see what's in l_sql.

Re: Native SQL (MS SQL) Help

bryan_cain — Wed, 18 Jul 2012 14:12:13 GMT

Sure:

Line 23:

DECLARE my_cursor100450 CURSOR FOR SELECT usertransidcode from [vertex_rpt].[dbo].LineItemView
 where usertransidcode = 0090000000 and lineitemnumber = 000010 and ltrim(rtrim(mainDivName)) in ( 'NORTH CAROLINA', 'VIRGINIA' )

Line 32:

OPEN my_cursor100450

Line 46 (where it dumps):

FETCH NEXT FROM my_cursor100450 INTO @lineOUT

l_params is :

@lineOUT nvarchar(40) OUTPUT

It never actually gets to line 59, so I'm guessing somewhere above is what you're looking for.

Thanks!

Re: Native SQL (MS SQL) Help

Former Member — Wed, 18 Jul 2012 14:24:18 GMT

Did you try enclosing usertransidcode and lineitemnumber values in single quotes? What is the data type of those fields in LineItemView?

Re: Native SQL (MS SQL) Help

bryan_cain — Wed, 18 Jul 2012 14:44:11 GMT

Enclosing them in quotes didn't help.

usertransidcode - nvarchar(40)

lineItemNumber - numeric(18,0)

Re: Native SQL (MS SQL) Help

Former Member — Wed, 18 Jul 2012 16:10:38 GMT

Could it be your misspelled "ouptut" on line 46?

Re: Native SQL (MS SQL) Help

bryan_cain — Wed, 18 Jul 2012 16:25:24 GMT

I'm not in front of my desk right now, but oof! That's probably what it is. I'll post back later today.

Thanks!

Re: Native SQL (MS SQL) Help

bryan_cain — Wed, 18 Jul 2012 18:07:33 GMT

Sure enough, that was the cause of the short dump. Thanks!

Now to make it actually return data... Although I'm finding (probably since it's off key) that it's far faster to retrieve the data and filter than it is to do it this way.