Post reply

binary string

  • February 3, 2004 at 11:45 am

    #107522

    I am trying to extract the binary string representation of the data pointer in a binary type column in a SQL 7 database. I can't convert this number to an int with Cast or Convert and it does not always convert the correct infromation. I have used programs such as MS query from a client machine and it displaying the binary field in it's binary string form:

    "0x00000E0000120002"

    This is the form I am trying to input into a varchar type field. But, I can't find a way via T-SQL to display this binary string. There must be a way to do it, because it's being translated at least by MS Query from a client machine.

    Does anyone have a real time example or idea on how I can extract that information?

    Thanks

    Dave

  • February 4, 2004 at 6:00 am

    #492738

    I'll try to put the request in another way. How do you export a blob into hex form?

    Maybe that can get my result I am looking for.

    Thanks

    Dave

  • February 4, 2004 at 8:29 am

    #492758

    If you are using SQL Server 2000

    This is a great shortcut:

    DECLARE @i varbinary(20)

    SET @i = 0x00000E0000120002

    SELECT master.dbo.fn_varbintohexstr(@i)

     


    * Noel

  • February 4, 2004 at 9:34 am

    #492769

    I am using SQL 7 for this, I did find there is an Extended Stored Procedure named xp_varbartohexstr in there, but I have not been able to get this to work for me.

     

    I am trying to do this within a Stored Procedure if that helps at all.

     

    Thanks

    Dave

  • February 4, 2004 at 9:57 am

    #492770

    I don't have SQL 7 here but I tryed this on 2000 and it works  

     

    DECLARE @in   varbinary(20),

     @strOut varchar(20)

    SET @in = 0x00000E0000120002

    EXEC master..xp_varbintohexstr @in, @strOut out

    SELECT @strOut


    * Noel

  • February 4, 2004 at 10:05 am

    #492773

    noeld, thanks for your help. I think we are getting close here.

    Not, I guess my noviceness of MS SQL is going to come out now, but how would you insert this into a Trigger such as:

    CREATE TRIGGER cps_customer_tg ON company FOR INSERT AS INSERT

               SELECT NULL, registration_id, inserted.Company_Name,

              inserted.Address_1, inserted.Address_2,

              inserted.Address_3, inserted.Country, inserted.City,

              inserted.State_, inserted.Zip, inserted.Phone,

              inserted.Industry_Type, inserted.Use_As_Reference,

              inserted.Account_Manager_Id, inserted.Type,

              inserted.Customer_Category,

              inserted.Reseller_ID, NULL, NULL, NULL, NULL, NULL, NULL,

               NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

               '1'

          FROM inserted

    I did find someone who mentioned I can insert code such as you have into a Tigger block code, but I can't find any reference to what that is or where it is within the trigger statement. I have tried to insert it before the Select, but that does not work.

    Thanks

    Dave

  • February 4, 2004 at 10:45 am

    #492778

    Dave,

    1. Your Statement is Missing the Destination Table and Fields!

    CREATE TRIGGER cps_customer_tg ON company FOR INSERT

    AS

    INSERT

    DETINATIONTABLENAME ( FIELD1,FIELD2,......)

               SELECT NULL, registration_id, inserted.Company_Name,

              inserted.Address_1, inserted.Address_2,

              inserted.Address_3, inserted.Country, inserted.City,

              inserted.State_, inserted.Zip, inserted.Phone,

              inserted.Industry_Type, inserted.Use_As_Reference,

              inserted.Account_Manager_Id, inserted.Type,

              inserted.Customer_Category,

              inserted.Reseller_ID, NULL, NULL, NULL, NULL, NULL, NULL,

               NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

               '1'

          FROM inserted

    2. You could Read the value from the inserted table first and then call your block with the value changed to Varchar like:

    CREATE TRIGGER cps_customer_tg ON company FOR INSERT

    AS

    DECLARE @in   varbinary(20),

     @strOut varchar(20), @RowCnt int

    SET @RowCnt = @@ROWCOUNT

    IF @RowCnt = 0

       RETURN

    IF @RowCnt  > 1

     BEGIN

      RAISERROR(16,1,'Only One row can be inserted at a time!')

      ROLLBACK TRANSACTION

      RETURN

     END

    SELECT @in = inserted.VarBinaryFiled FROM inserted

    EXEC master..xp_varbintohexstr @in, @strOut out

    INSERT

    DETINATIONTABLENAME ( FIELD1,FIELD2,......)

               SELECT NULL, registration_id, inserted.Company_Name,

              inserted.Address_1, inserted.Address_2,

              inserted.Address_3, inserted.Country, inserted.City,

              inserted.State_, inserted.Zip, inserted.Phone,

              inserted.Industry_Type, inserted.Use_As_Reference,

              inserted.Account_Manager_Id, inserted.Type,

              inserted.Customer_Category,

              inserted.Reseller_ID,

    ....

     @strOut  -- I Put this here because I don't know your exact DDL

    ...

    NULL, NULL, NULL, NULL, NULL, NULL,

               NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

               '1'

          FROM inserted

     

    I am not sure if that is what you wanted. It is not clear for me from your previous post


    * Noel

  • February 4, 2004 at 11:57 am

    #492781

    I was able to modifiy this and get it to do what I want. Thanks for all your help. This was a life saver!

     

    Thanks

    Dave

  • February 4, 2004 at 12:00 pm

    #492782

    you are welcome!


    * Noel

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply