Sign In Help
Schneider Electric
HelpLogin / Register

Invite a Co-worker

Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
You have entered an invalid email address. Please re-enter the email address.
This co-worker has already been invited to the Exchange portal. Please invite another co-worker.
Please enter email address
Send Invite Cancel

Invitation Sent

Your invitation was sent.Thanks for sharing Exchange with your co-worker.
Send New Invite Close
  • Home
  • Communities
  • :
  • SCADA & Telemetry Solutions
  • :
  • Geo SCADA Expert Forum
  • :
  • SQL insert newline / linefeed
Community Menu
  • Forums
    • By Topic
        • EcoStruxure Building
          • Field Devices Forum
        • EcoStruxure Power & Grid
          • Metering & Power Quality
        • EcoStruxure IT
          • EcoStruxure IT forum
        • Industrial Automation
          • Industry Automation and Control Forum
          • Alliance System Integrators Forum
          • Machine Solutions in the Digital Transformation
          • EcoStruxure Automation Expert / IEC 61499 Forum
          • Industrial Edge Computing Forum
          • Level and Pressure Instrumentation Forum
          • Modicon User Group
          • PLC Club Indonesia
          • SEE Automation Club Forum
          • Fabrika ve Makina Otomasyonu Çözümleri
          • Форум по промышленной автоматизации СНГ
          • Motorabgangstechnik & Antriebstechnik Forum
        • SCADA & Telemetry Solutions
          • Geo SCADA Expert Forum
          • SCADA and Telemetry Devices Forum
        • Power Distribution IEC
          • Power Distribution and Digital
          • Power Standards & Regulations
          • Solutions for Motor Management
          • Paneelbouw & Energie Distributie
          • Eldistribution & Fastighetsautomation
          • Elektrik Tasarım Dağıtım ve Uygulama Çözümleri
        • Power Distribution NEMA
          • US Utility Forum
        • Power Distribution Softwares
          • EcoStruxure Power Design Forum
          • LayoutFAST User Group Forum
        • Wireless Information Network Solutions
          • Instrument Area Network
          • Remote Monitoring
          • Tank Level Monitoring
          • Remote Data Collection
        • Solutions for your Business
          • Solutions for Food & Beverage Forum
          • Solutions for Healthcare Forum
    • By Segment
        • Food & Beverage
          • Solutions for Food & Beverage Forum
        • Healthcare
          • Solutions for Healthcare Forum
      • EcoStruxure Building
        • Field Devices Forum
      • EcoStruxure Power & Grid
        • Metering & Power Quality
      • EcoStruxure IT
        • EcoStruxure IT forum
      • Industrial Automation
        • Industry Automation and Control Forum
        • Alliance System Integrators Forum
        • Machine Solutions in the Digital Transformation
        • EcoStruxure Automation Expert / IEC 61499 Forum
        • Industrial Edge Computing Forum
        • Level and Pressure Instrumentation Forum
        • Modicon User Group
        • PLC Club Indonesia
        • SEE Automation Club Forum
        • Fabrika ve Makina Otomasyonu Çözümleri
        • Форум по промышленной автоматизации СНГ
        • Motorabgangstechnik & Antriebstechnik Forum
      • SCADA & Telemetry Solutions
        • Geo SCADA Expert Forum
        • SCADA and Telemetry Devices Forum
      • Power Distribution IEC
        • Power Distribution and Digital
        • Power Standards & Regulations
        • Solutions for Motor Management
        • Paneelbouw & Energie Distributie
        • Eldistribution & Fastighetsautomation
        • Elektrik Tasarım Dağıtım ve Uygulama Çözümleri
      • Power Distribution NEMA
        • US Utility Forum
      • Power Distribution Softwares
        • EcoStruxure Power Design Forum
        • LayoutFAST User Group Forum
      • Wireless Information Network Solutions
        • Instrument Area Network
        • Remote Monitoring
        • Tank Level Monitoring
        • Remote Data Collection
      • Solutions for your Business
        • Solutions for Food & Beverage Forum
        • Solutions for Healthcare Forum
      • Food & Beverage
        • Solutions for Food & Beverage Forum
      • Healthcare
        • Solutions for Healthcare Forum
  • Blogs
    • By Topic
        • EcoStruxure Power & Grid
          • Backstage Access Resources
        • Industrial Automation
          • Industry 4.0 Blog
          • Industrie du Futur France
        • SCADA & Telemetry Solutions
          • SCADA and Telemetry Blog
        • Power Distribution IEC
          • Power Events & Webinars
          • Power Foundations Blog
          • UK&I Residential and Commercial Electrical Distribution
        • Power Distribution NEMA
          • NEMA Power Foundations Blog
        • Power Distribution Softwares
          • EcoStruxure Power Design Blog
        • Solutions for your Business
          • Solutions for Food & Beverage Blog
          • Solutions for Healthcare Blog
          • Solutions for Retail Blog
          • Solutions for eMobility
          • Digital Hospital Solutions – UK&I
        • Knowledge Center
          • Industrial Automation How-to videos
          • Digital E-books
          • Success Stories Corner
        • Community experts & publishers
          • Publishers Community
    • By Segment
        • Food & Beverage
          • Solutions for Food & Beverage Blog
        • Healthcare
          • Solutions for Healthcare Blog
        • Retail
          • Solutions for Retail Blog
        • Retail
          • Solutions for eMobility
        • Retail
          • Digital Hospital Solutions – UK&I
      • EcoStruxure Power & Grid
        • Backstage Access Resources
      • Industrial Automation
        • Industry 4.0 Blog
        • Industrie du Futur France
      • SCADA & Telemetry Solutions
        • SCADA and Telemetry Blog
      • Power Distribution IEC
        • Power Events & Webinars
        • Power Foundations Blog
        • UK&I Residential and Commercial Electrical Distribution
      • Power Distribution NEMA
        • NEMA Power Foundations Blog
      • Power Distribution Softwares
        • EcoStruxure Power Design Blog
      • Solutions for your Business
        • Solutions for Food & Beverage Blog
        • Solutions for Healthcare Blog
        • Solutions for Retail Blog
        • Solutions for eMobility
        • Digital Hospital Solutions – UK&I
      • Knowledge Center
        • Industrial Automation How-to videos
        • Digital E-books
        • Success Stories Corner
      • Community experts & publishers
        • Publishers Community
      • Food & Beverage
        • Solutions for Food & Beverage Blog
      • Healthcare
        • Solutions for Healthcare Blog
      • Retail
        • Solutions for Retail Blog
      • Retail
        • Solutions for eMobility
      • Retail
        • Digital Hospital Solutions – UK&I
  • Ideas
        • Industrial Automation
          • Modicon Ideas & new features
        • SCADA & Telemetry Solutions
          • Geo SCADA Expert Ideas
          • SCADA and Telemetry Devices Ideas
  • Knowledge Center
    • Building Automation Knowledge Base
    • Industrial Automation Knowledge Base
    • Industrial Automation How-to videos
    • SCADA & Telemetry Solutions Knowledge Base
    • Digital E-books
    • Success Stories Corner
    • Power Talks
  • Events & Webinars
    • Innovation Talks
    • Innovation Summit
    • Let's Exchange Series
    • Technology Partners
  • Support
    • Ask Exchange
    • Leaderboard
    • Our Community Guidelines
    • Community User Guide
    • How-To & Best Practices
    • More
Join Now
How can we help?
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
52706members
Join Now
247947posts
Join Now

SQL insert newline / linefeed

Options
  • Subscribe to RSS Feed
  • Mark Topic as New
  • Mark Topic as Read
  • Float this Topic for Current User
  • Bookmark
  • Subscribe
  • Mute
  • Printer Friendly Page
Solved Go to Solution
Back to Geo SCADA Expert Forum
Solved
BevanWeiss
Sisko BevanWeiss
Sisko
‎2020-03-03 04:56 PM
0 Likes
5
1061
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-03-03 04:56 PM

SQL insert newline / linefeed

Ok, I can't believe I couldn't just find the answer to this.

 

I'm trying to add a new line in an SQL string, and getting complaints from the QP about the brackets when trying to use CHR(10).

 

So far the things I've tried...

CHR(10), CHAR(10), ASCII(10), $L, $0A, \n

 

The help really wasn't much help on this.

 

It's such a simple thing, and I'm sure I've done it before without much problem.

I don't think it's a bug, but for the record it's ClearSCADA 2017 May 2018 update.

 

Help please 🙂


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
Labels
  • SCADA
Tags (1)
  • Tags:
  • sql
Share
Reply

Accepted Solutions
sbeadle
Sisko sbeadle Sisko
Sisko
‎2020-03-04 01:44 AM
0 Likes
1
1034
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-03-04 01:44 AM

Re: SQL insert newline / linefeed

There's no way in the SQL syntax to insert a new line as a literal.

 

The \n sequence in a column name is one of the special trips in column names to alter the appearance in lists - in this case a multi-row column header. (See help  Core Reference > Coding > SQL Guide > SELECT Clause)

 

Others are the ~ prefix (fix column in view), _ prefix (hide column) and > prefix (right-justify column content).

 

Also, there's a way to change the colour of a value according to an RGB value expression (See help  Core Reference > Lists > Custom Lists > Lists in Which only the Entries in Particular Columns Change Color).

 

I said there's no way, but there always is!

You can create a data table with a row and column containing a \n using a script, then join to that table in queries to output a new line where needed. While ViewX will display a '.' for it, the SQL Export driver will write that new line in.

Sample c# code:

using ClearScada.Client;
// etc etc, and put user and pass into strings
var node = new ClearScada.Client.ServerNode(ClearScada.Client.ConnectionType.Standard, "127.0.0.1", 5481);
ClearScada.Client.Simple.Connection connection = new ClearScada.Client.Simple.Connection("Utility");
connection.Connect(node);
using (var spassword = new System.Security.SecureString())
{
  foreach (var c in pass) { spassword.AppendChar(c); }
  connection.LogOn(user, spassword);
}
ClearScada.Client.Simple.DBObject NewlineTable = connection.GetObject("Test General.Newline");
Object[] p1 = new Object[0];
int record = (int)NewlineTable.InvokeMethod("AddRecord", p1);
Object[] p2 = new Object[3];
p2[0] = "String";
p2[1] = record;
p2[2] = "\n";
NewlineTable.InvokeMethod("SetValue", p2);

See Answer In Context

Share
Reply
  • All forum topics
  • Previous Topic
  • Next Topic
5 Replies 5
AdamWoodland
Commander AdamWoodland Commander
Commander
‎2020-03-03 11:18 PM
0 Likes
4
1050
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-03-03 11:18 PM

Re: SQL insert newline / linefeed

\n works in the column header, not the content, e.g.

 

SELECT
'Hello' || '\n' || 'Bevan'
FROM
CROOTGROUP

 

Can't work it out either, pretty sure you used to be able to do something. $n is for logic (and tooltips apparently)

Share
Reply
BevanWeiss
Sisko BevanWeiss
Sisko
‎2020-03-03 11:40 PM
0 Likes
3
1044
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-03-03 11:40 PM

Re: SQL insert newline / linefeed

I'm somewhat glad that puts two of us in the same boat as not quite knowing the solution (right now).

I really feel this would have been an issue sooner if it just wasn't possible though.  Like I really recall having done this several times before, and that like CHR(10) 'just worked'... but it clearly doesn't (at least not for me anymore).

 

Interesting that '\n' works in the column header.  I would have hoped it was consistent within the Query Processor on the whole.  But I guess not.

 

@sbeadle  / @JChamberlain  help please 🙂


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
Share
Reply
JChamberlain
Lt. Commander JChamberlain Lt. Commander
Lt. Commander
‎2020-03-03 11:54 PM
0 Likes
2
1040
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-03-03 11:54 PM

Re: SQL insert newline / linefeed

CHR(10) is supported in expressions, so that might be what you're remembering.

 

I don't think there is a way to do what you're after but will defer to Steve.

Share
Reply
sbeadle
Sisko sbeadle Sisko
Sisko
‎2020-03-04 01:44 AM
0 Likes
1
1035
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-03-04 01:44 AM

Re: SQL insert newline / linefeed

There's no way in the SQL syntax to insert a new line as a literal.

 

The \n sequence in a column name is one of the special trips in column names to alter the appearance in lists - in this case a multi-row column header. (See help  Core Reference > Coding > SQL Guide > SELECT Clause)

 

Others are the ~ prefix (fix column in view), _ prefix (hide column) and > prefix (right-justify column content).

 

Also, there's a way to change the colour of a value according to an RGB value expression (See help  Core Reference > Lists > Custom Lists > Lists in Which only the Entries in Particular Columns Change Color).

 

I said there's no way, but there always is!

You can create a data table with a row and column containing a \n using a script, then join to that table in queries to output a new line where needed. While ViewX will display a '.' for it, the SQL Export driver will write that new line in.

Sample c# code:

using ClearScada.Client;
// etc etc, and put user and pass into strings
var node = new ClearScada.Client.ServerNode(ClearScada.Client.ConnectionType.Standard, "127.0.0.1", 5481);
ClearScada.Client.Simple.Connection connection = new ClearScada.Client.Simple.Connection("Utility");
connection.Connect(node);
using (var spassword = new System.Security.SecureString())
{
  foreach (var c in pass) { spassword.AppendChar(c); }
  connection.LogOn(user, spassword);
}
ClearScada.Client.Simple.DBObject NewlineTable = connection.GetObject("Test General.Newline");
Object[] p1 = new Object[0];
int record = (int)NewlineTable.InvokeMethod("AddRecord", p1);
Object[] p2 = new Object[3];
p2[0] = "String";
p2[1] = record;
p2[2] = "\n";
NewlineTable.InvokeMethod("SetValue", p2);

See Answer In Context

Share
Reply
BevanWeiss
Sisko BevanWeiss
Sisko
‎2020-03-04 02:08 AM
0 Likes
0
1031
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-03-04 02:08 AM

Re: SQL insert newline / linefeed

Thanks Steve,

I like the effort with the workaround, but I really don't like the workaround still.

I've just sent off an email to Tech Support to raise an enhancement for it.  I think it would make sense to have the CHR() function available, likely also the ASCII() function.  Although I only care about the CHR() right now, so that's all I had in my request.

 

I was almost going to suggest, perhaps updating to a more recent 'syntax' of SQL, like ANSI SQL:2011... but I've just had a quite peruse of the standard docs for the updated versions.  They're horrible.  So I think extensions to ANSI-92 is the most sensible way.

 

For examples of why I consider it horrible (for ClearSCADA use anyway)...

CONSTRUCT
INSERT <pattern> // add new vertices and edges
MERGE <pattern> // add existing and deduplicate elements
MERGE GRAPH <graph> // add all elements from <graph>
DELETE <elements> // delete elements
DETACH DELETE <elements> // delete vertices with their edges
...
RETURN [COPY OF] GRAPH // return projected graph

 

 

FROM friends
MATCH (a:Person)-[:TRAVELLED_TOGETHER]-(b:Person)
WHERE a.age = b.age
AND a.country = $country
AND b.country = $country
FROM census($country)
MATCH SHORTEST (a)-[:BORN_IN|MOVED_TO*]->(p)<-[:BORN_IN|MOVED_TO*]-(b)
MATCH (p)-[:LOCATED_IN]->(c:City)
RETURN a.age AS age, c.name AS city, count(*) AS pairs GROUP BY age

 I think it starts to get a bit meta for typical SCADA works..


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
Share
Reply
Top Experts
User Count
sbeadle
Sisko sbeadle Sisko
188
BevanWeiss
Sisko BevanWeiss
56
AndrewScott
Lt. Commander AndrewScott
17
AdamWoodland
Commander AdamWoodland Commander
17
JChamberlain
Lt. Commander JChamberlain Lt. Commander
16
geoffpatton
Lt. Commander geoffpatton
10
See More Top Experts
Find a Service Provider
Find a certified partner to help you address your integration, installation, maintenance and project needs.
View all Providers
Support

Have a question? Please contact us with details, and we will respond.

Contact Us
FAQ

Look through existing questions to find popular answers.

Learn More
About

Want to know more about Exchange and its possibilities?

Learn More

Full access is just steps away!

Join Exchange for FREE and get unlimited access to our global community of experts.

Connect with Peers & Experts

Discuss challenges in energy and automation with 30,000+ experts and peers.

Get Support in Our Knowledge Base

Find answers in 10,000+ support articles to help solve your product and business challenges.

Ask Questions. Give Solutions

Find peer based solutions to your questions. Provide answers for fellow community members!

Register today for FREE

Register Now

Already have an account?Log in

About Us FAQ Terms & Conditions Privacy Notice Change your cookie settings
©2021, Schneider Electric