Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Embedded SQL Issue / Guidance

  1. How-to
0
Votes
Undo
  1. Paul Murray
    Paul Murray
  2. PowerBuilder
  3. Monday, 11 October 2021 20:34 PM UTC

Greetings All,

I have an SQL call that I would like to execute in my script.  It works fine in the SQL Manager, but PowerBuilder is not happy about it.

PowerBuilder did not like the Declare statement, so I tried moving 'POINT' into the select statement.  But I am having trouble figuring out how to escape it.

Not sure if this should be in a stored procedure or if there is some way to embed this in a data window?

Thanks in advance for any help!!

Best, Paul

DECLARE @me geography = 'POINT (13.8 51.3833 4326)';
SELECT TOP(1) country_code, city, latitude, longitude, geography.STDistance(@me) AS distance_meters
FROM city_centers
ORDER BY distance_meters

...or...

SELECT TOP(1) country_code, city, latitude, longitude, geography.STDistance('Point(:place_longitude :place_latitude 4326)') AS distance

 

 

  1. Embedded SQL
Where is the "Subversion (SVN)" choice/option?
Powerbuilder 2019 R3 :Calling my exe from Oracle F...
Responses (3)
Paul Murray
Paul Murray Accepted Answer Pending Moderation
  1. Wednesday, 13 October 2021 16:48 PM UTC
  2. PowerBuilder
  3. # 1

Thanks again for your help, Chris.

I get that 'Point' may not be ANSI standard, but having the dialects for certain popular databases like SQL Server would make life easier.

And I found it easier to create a stored procedure for it which I will list here for those who are interested...

The great thing was that the Stored Procedure Datawindow works like a champ!!

Best, Paul

***

CREATE PROCEDURE get_closest_city_center
@long float,
@lat float,
@country_code char(2) output,
@city char(50) output,
@latitude float output,
@longitude float output,
@distance float output
AS
DECLARE @me geography = 'POINT(' + CAST(@long AS VARCHAR(20)) + ' ' + CAST(@lat AS VARCHAR(20)) + ' 4326)';
SELECT TOP(1) country_code, city, latitude, longitude, round(geography.STDistance(@me),2)/1000 AS distance_meters
FROM city_centers
ORDER BY distance_meters
GO

 

Comment
There are no comments made yet.
Paul Murray
Paul Murray Accepted Answer Pending Moderation
  1. Tuesday, 12 October 2021 00:35 AM UTC
  2. PowerBuilder
  3. # 2

As Always, Thanks for your help, Chris!!

What do I do now?

Best,

Paul

 

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 12 October 2021 00:07 AM UTC
  2. PowerBuilder
  3. # 3

Hi Paul;

  That is most likely because the Point() command is specific to SS and not standard ANSI SQL plus, the main reason is that the command returns a "Geography" Data Type which is not a known standard PB data type.

Regards .... Chris

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Tuesday, 12 October 2021 02:39 AM UTC
Hi Paul;

As one way, convert the Point DT to string....

DECLARE @g geography;

SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);

SELECT @g.ToString();



----- Process as a PB String -----



Then convert the string back to a Point data type on the way back (if required)...

https://stackoverflow.com/questions/44048954/convert-string-value-to-geography-data-type-in-sql-server



HTH - Food for thought

Regards ... Chris
  1. Helpful 1
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.