Jump to content
Sign in to follow this  
webbhost

Cannot get a T-SQL Function (mysql routine) to work?

Recommended Posts

Hi everyone,

After having success with my first T-SQL function, I've made it so that I can url-decode data when pulling it from the database without having to it manually - great!

 

However, I'm not having the same sort of luck with trying to base64-decode the data via a T-SQL Function. I've tried various functions found on google, however they're all kicking up errors when I try to import it using phpMyAdmin

Code is below:

CREATE FUNCTION FromBase64 (@data varchar(max))

RETURNS varchar(8000)

AS

BEGIN

DECLARE

@Output varchar(8000),

@Bits varbinary(3)

— declare vars.

DECLARE @XmlData xml

— construct an xml var.

SET @XmlData = CAST(‘<data>’ + @data + ‘</data>’ as xml)

— base64 decode the @data.

SELECT @Output= CONVERT(varchar(max),

@XmlData.value(‘(data)[1]’, ‘varbinary(max)’))

RETURN @Output

END

The phpMyAdmin error I am getting is:

Static analysis:

2 errors were found during analysis.

    Unexpected character. (near "[" at position 380)
    Unexpected character. (near "]" at position 382)

SQL query: Documentation

CREATE FUNCTION FromBase64 (@data varchar(max) CHARSET utf8) RETURNS varchar(8000) BEGIN DECLARE @Output varchar(8000), @Bits varbinary(3) — declare vars. DECLARE @XmlData xml — construct an xml var. SET @XmlData = CAST(‘<data>’ + @data + ‘</data>’ as xml) — base64 decode the @data. SELECT @Output= CONVERT(varchar(max), @XmlData.value(‘(data)[1]’, ‘varbinary(max)’)) RETURN @Output END

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@data varchar(max) CHARSET utf8)

RETURNS varchar(8000)

BEGIN

DECLARE

' at line 1

I've tried messing about changing the code around to look moire like the my previous function is structured, however nothing is working, and I have tried several of these scripts from google with no success from any!?

Can someone shed some light for me please? I'm new to T-SQL so I've not really got a clue!

Thanks



Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

  • Recently Browsing

    No registered users viewing this page.

  • Member Statistics

    • Total Members
      57,534
    • Most Online
      4,970

    Newest Member
    2nd Mortgages GTA
    Joined
  • Forum Statistics

    • Total Topics
      65,734
    • Total Posts
      455,403
×