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.