Help with view and stored procedure..

Dev Shed Forums Sponsor:

Generate data entry and reporting .NET Web apps in minutes, straight from your database. Read our FREE whitepaper “Build Web 2.0 Applications Without Hand-Coding” Download now!

April 15th, 2008, 09:48 AM

vkaluv

Registered User

Join Date: Apr 2008

Posts: 11

Time spent in forums: 3 h 44 m 37 sec
Reputation Power: 0

Help with view and stored procedure..

hi everyone
I got a stored procedure which converts int to IP address format. I have a view with ip address displayed in int format now.
so, i have to call this stored procedure and input the int value from the view and change it to IP address format and place this string the view updating the int column...

so i am not able to call this sp from view..
CREATE VIEW PF (HOSTNAME_DNS, IP_V4_ADDRESS, MAC_ADDRESS, ...)
AS
SELECT
NA.HOSTNAME_DNS,
GENINTTOQUAD(NA.IP_ADDRESS_INT),
NA.MAC_CANONICAL,
NA.SYSTEM_NAME,
NP.MANUFACTURER,
NP.MODEL_NAME,
NP.SERIAL_NUMBER,
NP.MEMORY,
NP.LIFETIME_PAGE_COUNT,
NP.FIRMWARE_REVISION
FROM
NETWORK_ADAPTER NA,
NETWORK_PRINTER NP
GENINTTOQUAD <--this the Stored procedure
WHERE
NA.ADAPTER_ID = NP.ADAPTER_ID
ORDER BY
NA.HOSTNAME_DNS,
NA.IP_ADDRESS_INT;
its sayin input mismatch even though both the input of SP and column are integer formats...

so,pls help me out

thank you in advance

April 15th, 2008, 09:56 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 6th Plane (7500 - 7999 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 7,686

Time spent in forums: 2 Months 2 Weeks 5 Days 16 h 27 m 51 sec
Reputation Power: 259

I'd check this as it looks like you're calling the stored proc the wrong way.

__________________
My blog about OpenSource Databases
PDF tutorials about OSS databases, DBMonster ...
Please contribute to Open Source Development, fill bug reports!!!
Developer Shed eSupport
Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins

April 15th, 2008, 09:57 AM

vkaluv

Registered User

Join Date: Apr 2008

Posts: 11

Time spent in forums: 3 h 44 m 37 sec
Reputation Power: 0

thank you

thanks alot dude i will be waiting for it..cheers

April 15th, 2008, 10:00 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 7,686

Time spent in forums: 2 Months 2 Weeks 5 Days 16 h 27 m 51 sec
Reputation Power: 259

Should be something like

sql Code:

Original - sql Code

SELECT
    NA.HOSTNAME_DNS,
-- the actual output of the sp
    GENINTTOQUAD.IP_ADDRESS_INT,
    NA.MAC_CANONICAL,
    NA.SYSTEM_NAME,
    NP.MANUFACTURER,
    NP.MODEL_NAME,
    NP.SERIAL_NUMBER,
    NP.MEMORY,
    NP.LIFETIME_PAGE_COUNT,
    NP.FIRMWARE_REVISION
FROM
    NETWORK_ADAPTER NA 
        INNER JOIN 
    NETWORK_PRINTER NP
        ON NA.ADAPTER_ID = NP.ADAPTER_ID
    LEFT OUTER JOIN
        GENINTTOQUAD(NA.IP_ADDRESS_INT)
        ON 1 = 1
ORDER BY
    NA.HOSTNAME_DNS,
    NA.IP_ADDRESS_INT;

April 15th, 2008, 10:16 AM

vkaluv

Registered User

Join Date: Apr 2008

Posts: 11

Time spent in forums: 3 h 44 m 37 sec
Reputation Power: 0

i am using Firebird 2.0.3 and its giving out this error
"Dynamic SQL Error feature is not supported"

April 15th, 2008, 10:24 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 7,686

Time spent in forums: 2 Months 2 Weeks 5 Days 16 h 27 m 51 sec
Reputation Power: 259

Would you mind posting the actual code for the view, the stored procedure and tables involved?

Are you using the suspend keyword in it?

Last edited by pabloj : April 15th, 2008 at 10:26 AM.

April 15th, 2008, 10:30 AM

vkaluv

Registered User

Join Date: Apr 2008

Posts: 11

Time spent in forums: 3 h 44 m 37 sec
Reputation Power: 0

code for view:

Code:

CREATE VIEW PRINTER_FACADE (HOSTNAME_DNS, IP_V4_ADDRESS, MAC_ADDRESS, SYSTEM_NAME, MANUFACTURER, MODEL_NAME, SERIAL_NUMBER, AVAILABLE_MEMORY, LIFETIME_PICKED_PAGE_COUNT, FIRMWARE_REVISION)
AS    
              SELECT
                NA.HOSTNAME_DNS,
                NA.IP_ADDRESS_INT,
                NA.MAC_CANONICAL,
                NA.SYSTEM_NAME,
                NP.MANUFACTURER,
                NP.MODEL_NAME,
                NP.SERIAL_NUMBER,
                NP.MEMORY,
                NP.LIFETIME_PAGE_COUNT,
                NP.FIRMWARE_REVISION
              FROM
                NETWORK_ADAPTER NA,
                NETWORK_PRINTER NP
              WHERE
                NA.ADAPTER_ID = NP.ADAPTER_ID
              ORDER BY
                NA.HOSTNAME_DNS,
                NA.IP_ADDRESS_INT;

code for sp:

Code:

SET TERM ^ ;
ALTER PROCEDURE GENINTTOQUAD (
    INTIP Integer )
RETURNS (
    IP Varchar(16) )
AS
DECLARE VARIABLE ip1 NUMERIC;
DECLARE VARIABLE ip2 NUMERIC;
DECLARE VARIABLE ip3 NUMERIC;
DECLARE VARIABLE ip4 NUMERIC;
DECLARE VARIABLE ANS NUMERIC;

DECLARE variable IP1CHAR varchar(4);
DECLARE variable IP2CHAR varchar(4);
DECLARE variable IP3CHAR varchar(4);
DECLARE variable IP4CHAR varchar(4);

BEGIN
IP = null;
  IF (INTIP < 0 ) THEN
    BEGIN
        INTIP = SUBSTRING(INTIP FROM 2 FOR 11);
        
    END
    ip1 = INTIP / 16777216;

  IF (ip1 = 0) THEN
    BEGIN
        ip1 = 255;
        INTIP = INTIP + 16777216;
    END
  ELSE IF (ip1 < 0) THEN
    BEGIN
        ANS=INTIP/16777216;
            IF(INTIP-(ANS*16777216)=0) THEN
            BEGIN
                ip1 = ip1 + 256;
            END
        ELSE 
            BEGIN
                ip1 = ip1 + 255;
                IF (ip1 = 128) THEN
                    BEGIN
                        INTIP = INTIP + 2147483648;
                    END
                ELSE
                    BEGIN
                        INTIP = INTIP + (16777216 * (256 - ip1));
                    END
            END
    END
   ELSE
      BEGIN
        INTIP = INTIP - (16777216 * ip1);
      END
    ANS=INTIP/16777216;
    INTIP = (INTIP-(ANS*16777216));
    ip2 = INTIP / 65536;
    INTIP = (INTIP-(ip2*65536));
    ip3 = INTIP / 256;
    INTIP = (INTIP-(ip3*256));
    ip4 = INTIP;
IP1CHAR = (CAST(ip1 AS varchar(4))) ;
IP2CHAR = (CAST(ip2 AS varchar(4))) ;
IP3CHAR = (CAST(ip3 AS varchar(4))) ;
IP4CHAR = (CAST(ip4 AS varchar(4))) ;

IP = (IP1 || '.') || (IP2 || '.') || (IP3 || '.') || IP4;

IP=INTIP;
SUSPEND ;
END^
SET TERM ; ^

I dnt have code for tables..but,the IP address column of table is int...and its not confined to this idea itself..you can suggest me others.....i thought of writing a function and call it in a view and also thought of writing a trigger for the view...i thgt i could do it with sp and wrote that sp and now it isnt wrkin....