Jump to content


Photo
- - - - -

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


  • Please log in to reply
No replies to this topic

#1 webbhost

webbhost

    Dedicated Member

  • Members
  • PipPip
  • 225 posts
  • Experience:Intermediate
  • Area of Expertise:Designer/Coder

Posted 18 June 2017 - 05:53 PM

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



 






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users