Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.lang.clipper.visual-objects > #5665 > unrolled thread

SQLSelect does not return results when calling a stored procedure in VO 2.8

Started bysudden2011@gmail.com
First post2013-08-09 20:34 -0700
Last post2013-08-26 19:30 -0700
Articles 18 — 6 participants

Back to article view | Back to comp.lang.clipper.visual-objects


Contents

  SQLSelect does not return results when calling a stored procedure in VO 2.8 sudden2011@gmail.com - 2013-08-09 20:34 -0700
    Re: SQLSelect does not return results when calling a stored procedure in VO 2.8 sudden2011@gmail.com - 2013-08-11 16:49 -0700
    Re: SQLSelect does not return results when calling a stored procedure in VO 2.8 "Willie Moore" <williem@wmconsulting.com> - 2013-08-13 09:56 -0500
      Re: SQLSelect does not return results when calling a stored procedure in VO 2.8 sudden2011@gmail.com - 2013-08-13 14:32 -0700
        Re: SQLSelect does not return results when calling a stored procedure in VO 2.8 "Willie Moore" <williem@wmconsulting.com> - 2013-08-13 17:17 -0500
          Re: SQLSelect does not return results when calling a stored procedure in VO 2.8 sudden2011@gmail.com - 2013-08-13 17:52 -0700
          Re: SQLSelect does not return results when calling a stored procedure in VO 2.8 sudden2011@gmail.com - 2013-08-21 15:17 -0700
            Re: SQLSelect does not return results when calling a stored procedure in VO 2.8 Willie Moore <williem@wmconsulting.com> - 2013-08-24 17:05 +0000
              Re: SQLSelect does not return results when calling a stored procedure in VO 2.8 sudden2011@gmail.com - 2013-08-25 18:35 -0700
                Re: SQLSelect does not return results when calling a stored procedure in VO 2.8 "Willie Moore" <williem@wmconsulting.com> - 2013-08-27 13:06 -0500
                  Re: SQLSelect does not return results when calling a stored procedure in VO 2.8 sudden2011@gmail.com - 2013-08-27 15:21 -0700
                    Re: SQLSelect does not return results when calling a stored procedure in VO 2.8 Willie Moore <williem@wmconsulting.com> - 2013-08-27 22:26 +0000
                      Re: SQLSelect does not return results when calling a stored procedure in VO 2.8 D.J.W. van Kooten <public@ic2remove.this.com> - 2013-09-11 23:19 +0200
    Re: SQLSelect does not return results when calling a stored procedure in VO 2.8 sudden2011@gmail.com - 2013-08-13 18:03 -0700
    Re: SQLSelect does not return results when calling a stored procedure in VO 2.8 codesys75@orange.fr - 2013-08-23 06:24 -0700
      Re: SQLSelect does not return results when calling a stored procedure in VO 2.8 sudden2011@gmail.com - 2013-08-25 18:34 -0700
        Re: SQLSelect does not return results when calling a stored procedure in VO 2.8 Daniel Costagliola <codesys75@orange.fr> - 2013-08-26 01:47 -0700
          Re: SQLSelect does not return results when calling a stored procedure in VO 2.8 sudden2011@gmail.com - 2013-08-26 19:30 -0700

#5665 — SQLSelect does not return results when calling a stored procedure in VO 2.8

Fromsudden2011@gmail.com
Date2013-08-09 20:34 -0700
SubjectSQLSelect does not return results when calling a stored procedure in VO 2.8
Message-ID<8c0142d0-4124-4d96-b593-fbb713b6c51a@googlegroups.com>
This (simple example) works in V0 2.7

-- sql code
CREATE PROCEDURE myProc
AS
BEGIN
    SELECT 'A' as Column1, 'B' AS Column2
END

//-- VO code -------
oSel := SQLSelect("exec myProc",oConn) 
oSel:FieldGet(1)
//------------------


In VO 2.8 the same code executes fine but returns nothing.

Any ideas?

Gerard

[toc] | [next] | [standalone]


#5674

Fromsudden2011@gmail.com
Date2013-08-11 16:49 -0700
Message-ID<113c7c9a-c008-452b-92d0-547e42578bca@googlegroups.com>
In reply to#5665
Is anybody using the SQL classes with VO 2.8?

The above is issue is quite a show-stopper. 

You can no longer do this (in SQL Server anyway):

SQLSelect("UPDATE MyTable SET Fld1=Fld1+1;Select Fld1 FROM MyTable;")

Gerard

[toc] | [prev] | [next] | [standalone]


#5692

From"Willie Moore" <williem@wmconsulting.com>
Date2013-08-13 09:56 -0500
Message-ID<kudhe9$3rb$1@speranza.aioe.org>
In reply to#5665
Hey,

Have you run it through the debugger to make sure that you have a valid 
connection in oConn? I have not used to VO SQL classes in years (I use 
VO2ADO).

Regards,
Willie
Vulcan VIP

wrote in message 
news:8c0142d0-4124-4d96-b593-fbb713b6c51a@googlegroups.com...

This (simple example) works in V0 2.7

-- sql code
CREATE PROCEDURE myProc
AS
BEGIN
    SELECT 'A' as Column1, 'B' AS Column2
END

//-- VO code -------
oSel := SQLSelect("exec myProc",oConn)
oSel:FieldGet(1)
//------------------


In VO 2.8 the same code executes fine but returns nothing.

Any ideas?

Gerard 

[toc] | [prev] | [next] | [standalone]


#5694

Fromsudden2011@gmail.com
Date2013-08-13 14:32 -0700
Message-ID<db93f57f-fb29-4f0e-b8cf-c42cbee0cfb1@googlegroups.com>
In reply to#5692
Thanks for the post Willie. 

In answer to your question, yes, there is definitely a connection - it's a large app and everything else works. A straight 'Select FieldX from MyTable' returns expected results. Moreover the same app compiled in VO 2.7 works fine. 

Someone on the (defunct?) dev team should be able to tell me what changed between versions for such as a show-stopper. I sent Robert Van Der Hulst an email but he has not replied.

Cheers - Gerard

[toc] | [prev] | [next] | [standalone]


#5695

From"Willie Moore" <williem@wmconsulting.com>
Date2013-08-13 17:17 -0500
Message-ID<kueba2$dh2$1@speranza.aioe.org>
In reply to#5694
Gerard,

If you send me down a stripped down sample that just has how you set up the 
connection and the way you make the call I will look into it. I have SQL 
2005,8, and 12 on my machine so I can set up for any of the MS databases.

Regards,
Willie
Vulcan VIP

[toc] | [prev] | [next] | [standalone]


#5700

Fromsudden2011@gmail.com
Date2013-08-13 17:52 -0700
Message-ID<575f7645-9573-4b98-a754-8107d8038c32@googlegroups.com>
In reply to#5695
Willie - thanks heaps for helping. 

Below is some code that you can try in teh debugger. 

In VO 2.8 uValue is always zero. In 2.7b trying "Exec MyProc 101" and "Exec MyProc" produce the expected 101 and 12345 respectively.

Gerard



//---------------------
FUNCTION Start
LOCAL oConn AS SQLConnection
LOCAL oSelect AS SQLSelect
LOCAL uValue AS USUAL
LOCAL aParameter AS ARRAY

oConn := SQLConnection{}
IF !oConn:connect( "MAILmanNPBIT2005","DMS","dms")
    oConn:DriverConnect()
ENDIF
IF oConn:Connected

//- try passing parameter as part of select string
oSelect := SQLSelect{ "Exec MyProc 101" , oConn }
IF Empty(oSelect:Status)
    oSelect:GoTop()
    uValue := oSelect:FIELDGET(1) -- zero
ENDIF
oSelect:Close()

///------ try using the new SQLParameter class
aParameter := {}
AAdd(aParameter, SqlParameter{101, SQL_PARAM_INPUT, SQL_INTEGER})

oSelect := SQLSelect{ "Exec MyProc ?" , oConn }
IF oSelect:Execute(aParameter)
    IF Empty(oSelect:Status)
	oSelect:GoTop()
	uValue := oSelect:FIELDGET(1) -- zero
    ENDIF
ENDIF
oSelect:Close()

///------ try without any parameters
oSelect := SQLSelect{ "Exec MyProc" , oConn }
IF Empty(oSelect:Status)
    oSelect:GoTop()
    uValue := oSelect:FIELDGET(1) --zero
ENDIF
oSelect:Close()

ENDIF
RETURN NIL    

-- SQL SERVER SP
CREATE Procedure MyProc( @nParam1 INT = 0 )
AS
BEGIN
IF Coalesce(@nParam1,0) > 0
   SELECT @nParam1 AS Col1,'XYZ' AS Col2
ELSE
   SELECT 12345 AS Col1,'XYZ' AS Col2
END

[toc] | [prev] | [next] | [standalone]


#5718

Fromsudden2011@gmail.com
Date2013-08-21 15:17 -0700
Message-ID<ef36bfe2-12c2-4d55-97ae-10400f880f64@googlegroups.com>
In reply to#5695
Hey Willie,
Any further thoughts on this issue (on the sample code I put together to prove there is a problem)? If you're too busy with other things that's cool.
Cheers - Gerard

On Wednesday, August 14, 2013 8:17:37 AM UTC+10, Willie Moore wrote:
> Gerard,
> 
> 
> 
> If you send me down a stripped down sample that just has how you set up the 
> 
> connection and the way you make the call I will look into it. I have SQL 
> 
> 2005,8, and 12 on my machine so I can set up for any of the MS databases.
> 
> 
> 
> Regards,
> 
> Willie
> 
> Vulcan VIP

[toc] | [prev] | [next] | [standalone]


#5727

FromWillie Moore <williem@wmconsulting.com>
Date2013-08-24 17:05 +0000
Message-ID<2039902398399056638.958873williem-wmconsulting.com@news.aioe.org>
In reply to#5718
Gerard,

I should have time this weekend to work with your sample. I have been tied
up with a large programming project in Vulcan. 

Regards,
Willie
Vulcan VIP

[toc] | [prev] | [next] | [standalone]


#5730

Fromsudden2011@gmail.com
Date2013-08-25 18:35 -0700
Message-ID<6854cdae-cae6-47c2-bdf3-17834ae2fc9c@googlegroups.com>
In reply to#5727
Thanks Willie - assumed you were tied up with the Vulcan release, and appreciate your time, whenever you can.
Gerard

On Sunday, August 25, 2013 3:05:11 AM UTC+10, Willie Moore wrote:
> Gerard,
> 
> 
> 
> I should have time this weekend to work with your sample. I have been tied
> 
> up with a large programming project in Vulcan. 
> 
> 
> 
> Regards,
> 
> Willie
> 
> Vulcan VIP

[toc] | [prev] | [next] | [standalone]


#5738

From"Willie Moore" <williem@wmconsulting.com>
Date2013-08-27 13:06 -0500
Message-ID<kviprm$6vj$1@speranza.aioe.org>
In reply to#5730
Gerard,

have reviewed your sample and could recreate it on my SQL 2012 box. Please 
make the following change to your source and your sample will run. I have 
included the complete sample that I used for running it through the 
debugger.

    oConn := SQLConnection{}
    oConn:ODBCCursors    := SQL_CUR_USE_ODBC

Regards,
Willie
Vulcan VIP

FUNCTION Start
    LOCAL oConn as SQLConnection
    LOCAL oSelect as SQLSelect
    LOCAL uValue as USUAL
    LOCAL aParameter as ARRAY

    oConn := SQLConnection{}
    oConn:ODBCCursors    := SQL_CUR_USE_ODBC
    IF !oConn:connect( "Test")
        oConn:DriverConnect()
    ENDIF
    IF oConn:Connected

        //- try passing parameter as part of select string
        oSelect := SQLSelect{ "Exec MyProc 101" , oConn }
        IF Empty(oSelect:Status)
            oSelect:GoTop()
            uValue := oSelect:FIELDGET(1) // zero
        ENDIF
        oSelect:Close()
        ? " passing number as part of string " + AsString(uValue)

        ///------ try using the new SQLParameter class
        aParameter := {}
        AAdd(aParameter, SqlParameter{101, SQL_PARAM_INPUT, SQL_INTEGER})

        oSelect := SQLSelect{ "Exec MyProc ?" , oConn }
        IF oSelect:Execute(aParameter)
        IF Empty(oSelect:Status)
            oSelect:GoTop()
            uValue := oSelect:FIELDGET(1) // zero
        ENDIF
        ENDIF
        oSelect:Close()
        ? " passing number as a parameter " + AsString(uValue)

        ///------ try without any parameters
        oSelect := SQLSelect{ "Exec MyProc" , oConn }
        IF Empty(oSelect:Status)
            oSelect:GoTop()
           uValue := oSelect:FIELDGET(1) // zero
        ENDIF
        oSelect:Close()
        ? " passing number without parameter " + AsString(uValue)
    else
        ? "no SQL Connection"
    ENDIF
    WAIT
    RETURN nil

[toc] | [prev] | [next] | [standalone]


#5739

Fromsudden2011@gmail.com
Date2013-08-27 15:21 -0700
Message-ID<64581ca4-f06c-43c7-b541-296c1d152136@googlegroups.com>
In reply to#5738
Willie - thanks HEAPS! That's awesome. Tried lots of more complex variations - works a treat.

Just one question - what are the implications of leaving the SQL_CUR_USE_ODBC setting on permanently (for SQL Server)? VO works without it on some versions of Windows/SQL Server, so the question I guess is, will it break the the application in environments where there were previously no issues? 

Again, really appreciate the support.

Cheers - Gerard

On Wednesday, August 28, 2013 4:06:50 AM UTC+10, Willie Moore wrote:
> Gerard,
> 
> 
> 
> have reviewed your sample and could recreate it on my SQL 2012 box. Please 
> 
> make the following change to your source and your sample will run. I have 
> 
> included the complete sample that I used for running it through the 
> 
> debugger.
> 
> 
> 
>     oConn := SQLConnection{}
> 
>     oConn:ODBCCursors    := SQL_CUR_USE_ODBC
> 
> 
> 
> Regards,
> 
> Willie
> 
> Vulcan VIP
> 
> 
> 
>

[toc] | [prev] | [next] | [standalone]


#5740

FromWillie Moore <williem@wmconsulting.com>
Date2013-08-27 22:26 +0000
Message-ID<1915853531399334986.661255williem-wmconsulting.com@news.aioe.org>
In reply to#5739
Gerald,

That is the first time I have used the VO SQL classes in 6 or 7 years. I
use vo2ado with VO and ado.net with Vulcan. I was just trying to help.  
Maybe someone else who uses those classes can answer your question. I would
ask Dick. I believe that he uses those classes. 

Regards,
Willie
Vulcan VIP

[toc] | [prev] | [next] | [standalone]


#5826

FromD.J.W. van Kooten <public@ic2remove.this.com>
Date2013-09-11 23:19 +0200
Message-ID<enm139t8lvgfd5avjk78jokn5455hcpln7@4ax.com>
In reply to#5740
On Tue, 27 Aug 2013 22:26:48 +0000 (UTC), Willie Moore
<williem@wmconsulting.com> wrote:

Hello Willie, Gerard,

>That is the first time I have used the VO SQL classes in 6 or 7 years. I
>use vo2ado with VO and ado.net with Vulcan. I was just trying to help.  
>Maybe someone else who uses those classes can answer your question. I would
>ask Dick. I believe that he uses those classes. 

I do have one working program which writes large amounts of data on a
MySQL database on a clients website, using ODBC. Apart from some basic
SQL command and a few lines of code around I am not doing much with
it:

oConn:=SQLConnection{...}
IF !oConn:Connected
...
ENDIF
oStatement:=SQLStatement{cSQL,oConn}
IF !oStatement:Execute("")	
...
ENDIF

and that it's important to add

oStatement:FreeStmt(SQL_DROP)

That's about all I know about VO SQL I'm afraid...

Dick

[toc] | [prev] | [next] | [standalone]


#5701

Fromsudden2011@gmail.com
Date2013-08-13 18:03 -0700
Message-ID<ac3be8f8-abe1-4ed1-a25b-2454b3caa1af@googlegroups.com>
In reply to#5665
One further observation - see the SQL procs below. One works, the other doesn't.

Gerard


----------------------------
CREATE Procedure MyProc2
AS
-- works fine!
BEGIN
   SELECT 'ABC' AS Col2
END


CREATE Procedure MyProc3
AS
-- NULL_STRING returned!
BEGIN
IF 2=1 -- (any sort of condition)
   SELECT 'XYZ' AS Col2
ELSE
   SELECT 'ABC' AS Col2
END


[toc] | [prev] | [next] | [standalone]


#5721

Fromcodesys75@orange.fr
Date2013-08-23 06:24 -0700
Message-ID<e992c711-f806-4dd0-89f3-1ef7bbd17554@googlegroups.com>
In reply to#5665
I am not a fan of Microsoft Sql Server 
But in VO 2.8 you can try

//-- VO code -------
oSel := SQLSelect("SELECT A,B FROM myProc()",oConn)
//------------------


[toc] | [prev] | [next] | [standalone]


#5729

Fromsudden2011@gmail.com
Date2013-08-25 18:34 -0700
Message-ID<b5cd7e06-8581-4e2c-8baf-cbd4c0dc887f@googlegroups.com>
In reply to#5721
Thanks for the input Daniel. But the sql code you suggested would only work for a function, not a stored procedure. I have no trouble calling sql functions in VO 2.8.
Gerard

[toc] | [prev] | [next] | [standalone]


#5733

FromDaniel Costagliola <codesys75@orange.fr>
Date2013-08-26 01:47 -0700
Message-ID<bca41ea6-2b6f-4614-8fc9-024a2ea1c9f7@googlegroups.com>
In reply to#5729
Sorry I have make a mistake in the sample. You have to write

//-- VO code -------
oSel := SQLSelect("SELECT Column1,Column2 FROM myProc()",oConn)
//------------------

This work in SQL Anywhere but in the procedure code I have an instruction RESULT to describe (column name, column datatype) the result set.


[toc] | [prev] | [next] | [standalone]


#5736

Fromsudden2011@gmail.com
Date2013-08-26 19:30 -0700
Message-ID<e6e8554b-a3ad-419b-858d-22593d35646b@googlegroups.com>
In reply to#5733
Daniel, I tried what you suggested. It does not work in SQL Server.  In SQL Server this would have to be a function, such as:

-----------
CREATE FUNCTION MyProc3()
RETURNS @myTable TABLE ( 
Col2 Char(10))
AS 
BEGIN
  INSERT INTO @myTable SELECT 'ABC'
  RETURN;
END
GO
-----------
SELECT Col2 FROM dbo.MyProc3()
-----------

Unfortunately I cannot just change my stored proc to a function due to all the restrictions on what can be done in functions. Thanks for your suggestion though, I appreciate it.

Gerard


On Monday, August 26, 2013 6:47:39 PM UTC+10, Daniel Costagliola wrote:
> Sorry I have make a mistake in the sample. You have to write
> 
> 
> 
> //-- VO code -------
> 
> oSel := SQLSelect("SELECT Column1,Column2 FROM myProc()",oConn)
> 
> //------------------
> 
> 
> 
> This work in SQL Anywhere but in the procedure code I have an instruction RESULT to describe (column name, column datatype) the result set.

[toc] | [prev] | [standalone]


Back to top | Article view | comp.lang.clipper.visual-objects


csiph-web