Hi,
Suppose I have a table something like:
name (VARCHAR(64)) age (INT) quotation (TEXT)
=============== =============== =============== ====
Donald Trump 50 I am rich
Bugs Bunny 26 What's up doc
...
and a template string something like:
SET @template = 'My name is {name}, my age is {age}, and I always say
"{quotation }".'
I'd like to be able to dynamically replace the placeholders in the
template string with values extracted from the corresponding columns in
the table, so I'd get a set of results like:
'My name is Donald Trump, my age is 50, and I always say "I am rich".'
The best I've come up with so far is:
SET @Query = 'SELECT '''
+ REPLACE(REPLACE (@String, '{', '''+CONVERT(var char,'),
'}', ')+''')
+ ''' FROM Table'
EXEC (@Query)
This converts the template string into a query string, casting
everything to a
varchar to allow numeric values to work. In this case it would be:
SELECT 'My name is '+CONVERT(varch ar,name)+', my age is '
+CONVERT(varcha r,age)+', and I always say "'
+CONVERT(varcha r,quotation)+'" .'
The problem with this is that if the length of a varchar is
unspecified, it defaults to 30, which truncates long string values.
Can anyone figure out a way round this, or perhaps an alternative
method entirely?
--
Oli 5 2302
The fundamental principle of a tiered architecture is that you format
data for display in the front end and never in the database. Why do
you actively want to violate this?
(ca***@olifilth .co.uk) writes: Suppose I have a table something like:
name (VARCHAR(64)) age (INT) quotation (TEXT) =============== =============== =============== ==== Donald Trump 50 I am rich Bugs Bunny 26 What's up doc ...
and a template string something like:
SET @template = 'My name is {name}, my age is {age}, and I always say "{quotation }".'
I'd like to be able to dynamically replace the placeholders in the template string with values extracted from the corresponding columns in the table, so I'd get a set of results like:
'My name is Donald Trump, my age is 50, and I always say "I am rich".'
The best I've come up with so far is:
SET @Query = 'SELECT ''' + REPLACE(REPLACE (@String, '{', '''+CONVERT(var char,'), '}', ')+''') + ''' FROM Table' EXEC (@Query)
This converts the template string into a query string, casting everything to a varchar to allow numeric values to work. In this case it would be:
SELECT 'My name is '+CONVERT(varch ar,name)+', my age is ' +CONVERT(varcha r,age)+', and I always say "' +CONVERT(varcha r,quotation)+'" .'
The problem with this is that if the length of a varchar is unspecified, it defaults to 30, which truncates long string values.
SELECT replace(replace (replace(@templ ate, '(name)', name),
'(age)', ltrim(str(age)) ),
'(quotation)', quotation)
FROM tbl
WHERE ...
I can't see any need for dynamic SQL. Or converting to varchar what is
already varchar. Or why you can't just say varchar(8000) instead of
just varchar if you must convert.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
--CELKO-- said the following on 10/10/2005 20:39: The fundamental principle of a tiered architecture is that you format data for display in the front end and never in the database. Why do you actively want to violate this?
I agree that this is less than ideal.
The reason is that I want to auto-generate e-mails within a job (or
possibly a SP called from various jobs/triggers), based on the contents
of a particular table. So for lack of any real "front-end", this is
where I'm currently doing the processing.
--
Oli
Erland Sommarskog said the following on 10/10/2005 22:49: (ca***@olifilth .co.uk) writes:
<...SNIP...> The best I've come up with so far is:
SET @Query = 'SELECT ''' + REPLACE(REPLACE (@String, '{', '''+CONVERT(var char,'), '}', ')+''') + ''' FROM Table' EXEC (@Query)
This converts the template string into a query string, casting everything to a varchar to allow numeric values to work. In this case it would be:
SELECT 'My name is '+CONVERT(varch ar,name)+', my age is ' +CONVERT(varcha r,age)+', and I always say "' +CONVERT(varcha r,quotation)+'" .'
The problem with this is that if the length of a varchar is unspecified , it defaults to 30, which truncates long string values.
SELECT replace(replace (replace(@templ ate, '(name)', name), '(age)', ltrim(str(age)) ), '(quotation)', quotation) FROM tbl WHERE ...
I can't see any need for dynamic SQL.
I don't like the idea of dynamically-generated code either ;), however...
The idea is that I want to automatically generate e-mails, based on the
contents of particular tables (the example above was just a trivial
example of the behaviour I want). The template strings need to be as
human-readable as possible, and could be updated on a regular basis
(they're likely to be stored in a table as well). Equally, new tables
could be added to the database in the future, and I want this procedure
to be as flexible as possible.
Therefore, I want to avoid hard-coding in any column names into the
job/proc.
Or converting to varchar what is already varchar. Or why you can't just say varchar(8000) instead of just varchar if you must convert.
I guess this is one possibility. However, is this likely to be inefficient?
I was hoping, perhaps erroneously, that there would be a way to obtain
any data type as a string-based type, formatted in the same way as when
one does:
SELECT name, age, quotation FROM table
When executed by SQL manager, query analyser, etc., the results are
shown as text, so something must have converted them from their native
data types!
--
Oli
Oli Filth (ca***@olifilth .co.uk) writes: The idea is that I want to automatically generate e-mails, based on the contents of particular tables (the example above was just a trivial example of the behaviour I want). The template strings need to be as human-readable as possible, and could be updated on a regular basis (they're likely to be stored in a table as well). Equally, new tables could be added to the database in the future, and I want this procedure to be as flexible as possible.
Therefore, I want to avoid hard-coding in any column names into the job/proc.
That is a mindset for which relational databases are not really built.
The idea is that the schema of a database - that is tables and columns -
are static. They may change if you install a new version of the application,
upon which you change your existing code. But tables and columns don't
come and go like a commuter train.
If the possible fields for the templates can vary over time, they
should be rows in a table, not colummns. You could have:
CREATE TABLE templates (tmplid int NOT NULL,
tmplname varchar(40) NOT NULL,
....
CONSTRAINT pk_templates PRIMARY KEY (templd))
CREATE TABLE templatefields
(tmplid int NOT NULL,
field varchar(12) NOT NULL,
datatype char(1) NOT NULL,
-- Permit integer, dates and varchar.
CONSTRAINT ckc_datatype CHECK (datatype IN ('I', 'D', 'V')),
CONSTRAINT pk_templatefiel ds PRIMARY KEY (templid, field),
CONSTRAINT fk_templatefiel ds FOREIGN KEY (tmplid)
REFERENCES templates (tmplid))
CREATE TABLE templatevalues
(tmplid int NOT NULL,
field varchar(12) NOT NULL,
valueno smallint NOT NULL,
value sql_variant NOT NULL,
CONSTRAINT pk_templatevalu es
PRIMARY KEY (tmplid, field, valueno),
CONSTRAINT fk_templatevalu es FOREIGN KEY (tmplid, field)
REFERENCES templatefields (tmplid, field))
Thus, the first table describes all templates. The second all fields in
a template, and the third all available values for a template.
To expand a template string you would have to loop over the fields
table and run a replace() for each possible field value in the template.
The point here is that users can add templates, fields and values as they
see fit, but the tables and columns are the same. Or converting to varchar what is already varchar. Or why you can't just say varchar(8000) instead of just varchar if you must convert.
I guess this is one possibility. However, is this likely to be inefficient?
Nothing of what you are doing right now is likely to be effecient.
And the cost of saying convert(varchar (8000) over convert(varchar (30)
is negligble.
I was hoping, perhaps erroneously, that there would be a way to obtain any data type as a string-based type, formatted in the same way as when one does:
SELECT name, age, quotation FROM table
When executed by SQL manager, query analyser, etc., the results are shown as text, so something must have converted them from their native data types!
Ohoh - you are not in Kansas anymore, you are down in the server. The
presentation you see in Query Analyzer and other tools is performed in
these tools. They get binary data from SQL Server. If you insist of doing
this in the server (a client is much more apt for this), you will have
use what is availble down there. And it's a bit primitive. (If you thought
this is messy, just wait until you have a field with a binary value!)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Trevor Lango |
last post by:
What is the appropriate syntax for placing a friend function that includes
as one of it's parameters a pointer to the class object itself within the
template class?
I have the following:
//****************************************************
// testClass.h
//****************************************************
#ifndef TESTCLASS_H
|
by: Suzanne Vogel |
last post by:
Is it possible to store a pointer to a template function?
eg,
template<class T> fooFunc() {...}
fptr = fooFunc; // <--
I couldn't find any info here, not even in the forums:
http://www.function-pointer.org/
|
by: Thomi Richards |
last post by:
Hi,
I'm trying to create a simple stack class using C++ and templates.
Everything works well and good if I amke the class totally inline.
However, as soon as I try to seperate the class into a .h and a .cpp
file, gcc throws some strange linking errors.
Compiling like this:
|
by: Lionel B |
last post by:
Greetings.
The following code compiles ok and does what I'd expect it to do:
---------- START CODE ----------
// test.cpp
|
by: Marek Vondrak |
last post by:
Hello.
I have written the following program and am curious why it prints "1" "2".
What are the exact effects of explicitly providing function template
parameters at the call? Is the second assign() function really a
specialization of the first assign() or is it an assign() overload?
Thank you.
-- Marek
| |
by: quarup |
last post by:
I want to specialize a template function that lives inside a class, but
am getting a compile error in VS.net 2003. Here's my code:
template <class T>
class A {
public:
template <class U>
void f() const;
};
|
by: stefan.bruckner |
last post by:
Hi,
I am looking for a way to achieve the following. I've tried a couple
of things, but they all ended up being too complicated:
I have a templated class A. I want another class B to be able to call
a method defined in A's base class which at runtime determines the
template parameters (I know ahead what is allowed) and calls a
templated...
|
by: StephQ |
last post by:
This is from a thread that I posted on another forum some days ago.
I didn't get any response, so I'm proposing it in this ng in hope of
better luck :)
The standard explanation is that pointer to functions are hard to
inline for the compiler, and so you won't be able to avoid function
call overhead.
This is an important aspect when you are...
|
by: Jess |
last post by:
Hi,
I have a template function that triggered some compiler error. The
abridged version of the class and function is:
#include<memory>
using namespace std;
template <class T>
|
by: H9XLrv5oXVNvHiUI |
last post by:
Hi, I have a question about injecting friend functions within template
classes. My question is specific to gcc (version 3.4.5) used in
combination with mingw because this code (or at least code that gets
the same result) works as expected in visualc++. I know that this is
probably not the right behavior for a compiler but it's the kind of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |