Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.clipper.visual-objects > #5665 > unrolled thread
| Started by | sudden2011@gmail.com |
|---|---|
| First post | 2013-08-09 20:34 -0700 |
| Last post | 2013-08-26 19:30 -0700 |
| Articles | 18 — 6 participants |
Back to article view | Back to comp.lang.clipper.visual-objects
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
| From | sudden2011@gmail.com |
|---|---|
| Date | 2013-08-09 20:34 -0700 |
| Subject | SQLSelect 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]
| From | sudden2011@gmail.com |
|---|---|
| Date | 2013-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]
| From | "Willie Moore" <williem@wmconsulting.com> |
|---|---|
| Date | 2013-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]
| From | sudden2011@gmail.com |
|---|---|
| Date | 2013-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]
| From | "Willie Moore" <williem@wmconsulting.com> |
|---|---|
| Date | 2013-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]
| From | sudden2011@gmail.com |
|---|---|
| Date | 2013-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]
| From | sudden2011@gmail.com |
|---|---|
| Date | 2013-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]
| From | Willie Moore <williem@wmconsulting.com> |
|---|---|
| Date | 2013-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]
| From | sudden2011@gmail.com |
|---|---|
| Date | 2013-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]
| From | "Willie Moore" <williem@wmconsulting.com> |
|---|---|
| Date | 2013-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]
| From | sudden2011@gmail.com |
|---|---|
| Date | 2013-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]
| From | Willie Moore <williem@wmconsulting.com> |
|---|---|
| Date | 2013-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]
| From | D.J.W. van Kooten <public@ic2remove.this.com> |
|---|---|
| Date | 2013-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]
| From | sudden2011@gmail.com |
|---|---|
| Date | 2013-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]
| From | codesys75@orange.fr |
|---|---|
| Date | 2013-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]
| From | sudden2011@gmail.com |
|---|---|
| Date | 2013-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]
| From | Daniel Costagliola <codesys75@orange.fr> |
|---|---|
| Date | 2013-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]
| From | sudden2011@gmail.com |
|---|---|
| Date | 2013-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