I'd love to do this: UPDATE table SET blobCol = HTTPGET(urlCol) WHERE whatever LIMIT n; Is there code available to do this? I known this should be possible as the MySQL Docs include an example of adding a function that does a DNS lookup. MySQL / windows / Preferably without having to compile stuff, but I can. (If you haven't heard o...
I am using the LIST function to create a ';' delimited list of values. The type is numeric (19,2). For some reason the precision appears to be ignored when using the list function. When performing a simple select on this column the values look good, ie "12.00". However, if I use a LIST() my results are of format "12.000000" This is my L...
If you set up a table's column to be a computed column whose Formula calls a Function, it becomes a pain to change that underlying Function. With every change, you have to find every single column whose Formula that references the Function, remove the reference, save the Table, alter the Function, add everything back, and save again. Ev...
I am using Nhibernate 2.1.0 in my project. I have the an Item class with property Path and the following named query: <hibernate-mapping xmlns='urn:nhibernate-mapping-2.2'> <sql-query name='updateUNC'> <query-param name='oldUNC' type='String'/> <query-param name='newUNC' type='String'/> <![CDATA[ update Item s s...
Hi, I've been learning Functions and Stored Procedure for quite a while but I don't know why and when exactly I should use functions or stored procedure instead of one another. They look same to me maybe because I am kinda newbie about that. Can some one tell me why ? Thanks in advance. ...
Following on from one of the answers in this thread; http://stackoverflow.com/questions/214060/using-xquery-in-linq-to-sql/214435#214435 I'm trying to create a sql table function that will take parameters in order to perform queries on xml data. The function can then be consumed by a linq query. Issue's i'm having are ; If i take th...
This is based on a similar question How to Replace Multiple Characters in Access SQL? I wrote this since sql server 2005 seems to have a limit on replace() function to 19 replacements inside a where clause. I have the following task: Need to perform a match on a column, and to improve the chances of a match stripping multiple un-neede...
Hi SO, I am currently working on a particularly complex use-case. Simplifying below :) First, a client record has a many-to-one relationship with a collection of services, that is, a single client may have multiple services associated with it. Within my trigger, I am writing a Query that returns a client's id based on certain criteria....
PostgreSQL 8.4 on Linux - I have a function - CREATE OR REPLACE FUNCTION production.add_customer ( name varchar(100), email_address varchar(300), street_address text, city varchar(50), state varchar(2), zip varchar(10), secret1 bytea, secret2 bytea, secret3 bytea, secret4 bytea, referrer text) RETURNS integer...
I have simplified my function to the following: create function [dbo].[UserSuperTeams](@ProjectId int) returns table as return if @ProjectId=0 begin select TeamId from TblTeam t union select 0 as TeamId end else begin select t.TeamId from TblTeam t union select 1 as TeamId ...
I'm trying to make what seems to be an advanced use of NHibernate with sql server functions. I'm using NHibernate's ICriteria interface to supply paging, sorting and filtering for my listviews. one of business objects is an aggregation of items from 3 different tables. in order to do this aggregation in the DB I've used a transact-sql fu...
I have to keep track of revisions of records in a table. What I've done is create a second table that inherits from the first and adds a revision counter. CREATE TABLE A ( id SERIAL, foo TEXT, PRIMARY KEY (id)); CREATE TABLE B ( revision INTEGER NOT NULL) INHERITS (A); Then I created a trigger that would update table B everytime A i...
Hey, I want to convert the following SQL Query to a SubSonic Query. SELECT [dbo].[tbl_Agency].[ParentCompanyID] FROM [dbo].[tbl_Agency] WHERE REPLACE(PhoneNumber, ' ', '') LIKE REPLACE('%9481 1111%', ' ', '') I thought I would do it like below, but I just can't get it to produce valid SQL. //SubSonic string agencyPhone...
I am a little new to store procedures in MySQL and was wondering if I can SELECT multiple columns into multiple variables within the same select query. for example (iName is the input to the function): DECLARE iId INT(20); DECLARE dCreate DATETIME; SELECT Id INTO iId, dateCreated INTO dCreate FROM products WHERE pName=iName; Thank...
Iam quite new to functions in SQL and I would like to create a function to compare values in a MySQL table against previous and I am not sure how to do this. For example (iId is the input value) DECLARE pVal INT(20); DECLARE val INT(20); SELECT price INTO pVal FROM products WHERE Id=iId; SELECT price FROM products; IF price == pVal...
I have an MS SQL function that is called with the following syntax: SELECT Field1, COUNT(*) AS RecordCount FROM GetDecileTable('WHERE ClientID = 7 AND LocationName = ''Default'' ', 10) The first parameter passes a specific WHERE clause that is used by the function for one of the internal queries. When I call this function in the fron...
I have a table that contains the next columns: ip(varchar 255), index(bigint 20), time(timestamp) each time something is inserted there, the time column gets current timestamp. I want to run a query that returns all the rows that have been added in the last 24 hours. This is what I try to execute: SELECT ip, index FROM users WHERE ip...
Hello im trying to make a Function that retruns a value. in my function i have this script: WITH t_new AS ( SELECT PersIDOLD, PersIDNEW, RightsMUT, SUM(gap) over(ORDER BY PersIDOLD, PersIDNEW) grp FROM ( SELECT h1.*, CASE WHEN h1.PersIDNEW = lag(h1.PersIDNEW) ...
I have the following recursive function: ALTER FUNCTION [dbo].[ListAncestors] ( @Id int ) RETURNS TABLE As RETURN ( WITH cte As ( SELECT UserId, ManagerId, Forename, Surname FROM dbo.Users WHERE Use...
I have a recursive scalar function that needs to update a record in another table based on the value it is returning, however UPDATE statements are not allowed in the function. How can I update the table from within the function? ...