问:
How to properly sanitize a filename (protect against shell injection)?
What is the common practice to sanitize a filename from an outside source (e.g.: xml file) before using it within a subprocess (shell=False)?

Update:
Before sending some parsed strings around I would like to make some basic security checks. The given example uses mpg123 (a command line audioplayer) in remote mode to play a sound file.

```<code>filename = child.find("filename").text # e.g.: filename = "sound.mp3"
pid = subprocess.Popen(["mpg123"],"-R"], stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
command = "L "+filename+"\n"
pid.stdin.write(command.encode())
</code>```

答:
Filenames doesn't need to be sanitized unless you are using a shell or executing anything. Pythons open() will not execute any commands in the filename given.

For security checks, to avoid overwriting files, you use the permission system of your OS, and make sure that the user under which the program is running only can overwrite and access files it should be able to overwrite and access.

It's generally not a good idea to let any program that takes input from the net or another process to accept absolute path names. In this case it should only be allowed to specify files under a defined music folder. I don't think the mp3 player can cause damage by giving it the wrong file, but you can crash it, at least, and that would be annoying.

Body:
What is the common practice to sanitize a filename from an outside source (e.g.: xml file) before using it within a subprocess (shell=False)?

Update:
Before sending some parsed strings around I would like to make some basic security checks. The given example uses mpg123 (a command line audioplayer) in remote mode to play a sound file.

filename = child.find("filename").text # e.g.: filename = "sound.mp3"
pid = subprocess.Popen(["mpg123"],"-R"], stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
command = "L "+filename+"\n"
pid.stdin.write(command.encode())


来源: stackexchange

问:
Replace all occurrences of a substring, one at a time
I have the following method which replaces all occurrences of a {TIMESTAMP} substring with the value returned by String.valueOf(System.nanoTime()), the intention is to get a different time mark per substring occurrence, but it results in all substrings being replaced by just the same value.

```<code>class StringUtils {
    private static String resolveTimestamp(String s) {
        String timestamp = "\\{TIMESTAMP}";
        return s.replaceAll(timestamp, String.valueOf(System.currentTimeMillis()));
    }
}

class Sample {
    public static void main(String[] args) {
        StringUtils.resolveTimestamp("{TIMESTAMP}, {TIMESTAMP}, {TIMESTAMP}")
    }
}

// The execution of the code above would result in:
// 241170886964203, 241170886964203, 241170886964203

// But I want to get this:
// 241733154573329, 241734822930540, 241751957270934
</code>```

I would like to know which is the best way of doing what I want, as there are several ways of doing that coming to my mind right now:

A loop with s.matches(timestamp) as condition to iterate over each occurrence of the substring, and s.replaceFirst(timestamp, String.valueOf(System.nanoTime())) to perform the replacement inside the loop.

Use the substring to explode the string into several chunks with s.split(timestamp), and iterate over the chunks joining each pair with the value returned by String.valueOf(System.nanoTime()))

Probably some other choices

NOTE: Please be aware that this is not the same problem addressed in this question. There they need to replace several occurrences of a fixed pattern with just the same value, while I need to replace several occurrences of the same substring, each one by a different value calculated at runtime.

答:
You can use String.replaceFirst:</p>

```<code>private static String resolveTimestamp(String s) {
    String timestamp = "\\{TIMESTAMP}";

    while(s.matches(timestamp)) {
       s = s.replaceFirst(timestamp, String.valueOf(System.nanoTime()));
    }
    return s;
}
</code>```

ContentLicense: CC BY-SA 3.0

Body:
I have the following method which replaces all occurrences of a {TIMESTAMP} substring with the value returned by String.valueOf(System.nanoTime()), the intention is to get a different time mark per substring occurrence, but it results in all substrings being replaced by just the same value.

class StringUtils {
    private static String resolveTimestamp(String s) {
        String timestamp = "\\{TIMESTAMP}";
        return s.replaceAll(timestamp, String.valueOf(System.currentTimeMillis()));
    }
}

class Sample {
    public static void main(String[] args) {
        StringUtils.resolveTimestamp("{TIMESTAMP}, {TIMESTAMP}, {TIMESTAMP}")
    }
}

// The execution of the code above would result in:
// 241170886964203, 241170886964203, 241170886964203

// But I want to get this:
// 241733154573329, 241734822930540, 241751957270934

I would like to know which is the best way of doing what I want, as there are several ways of doing that coming to my mind right now:

A loop with s.matches(timestamp) as condition to iterate over each occurrence of the substring, and s.replaceFirst(timestamp, String.valueOf(System.nanoTime())) to perform the replacement inside the loop.

Use the substring to explode the string into several chunks with s.split(timestamp), and iterate over the chunks joining each pair with the value returned by String.valueOf(System.nanoTime()))

Probably some other choices

NOTE: Please be aware that this is not the same problem addressed in this question. There they need to replace several occurrences of a fixed pattern with just the same value, while I need to replace several occurrences of the same substring, each one by a different value calculated at runtime.

Body:
You can use String.replaceFirst:

private static String resolveTimestamp(String s) {
    String timestamp = "\\{TIMESTAMP}";

    while(s.matches(timestamp)) {
       s = s.replaceFirst(timestamp, String.valueOf(System.nanoTime()));
    }
    return s;
}


问:
Getting R printed texts to have color esp. in R markdown knits?
Very simple question: I love writing R notebooks/markdowns, and with something like highlight: tango I can give background color to codes when I knit my notebook to pdfs.

However, I don't know how to give colored backgrounds or colored fonts to printed outputs in R. For example, suppose I have the following chunk.

```<code>```{r, echo = FALSE}
writeLines("Help")
```
</code>```

I'd like to see the word Help to be highlighted, say in red font with gray background. How can I achieve this?

Many thanks in advance.

答:
For a way within R, my huxtable package lets you set background and text colour for tables. I don't know of any within-R package that does it for plain text (maybe crayon?)

```<code>library(huxtable)
ht <- hux(c("Red", "Blue"), c("White bg", "Black bg"))
text_color(ht)[, 1] <- c("red", "blue")
background_color(ht)[, 2] <- c("white", "black")
to_latex(ht) # or just print within a rmarkdown document
</code>```

Body:
Very simple question: I love writing R notebooks/markdowns, and with something like highlight: tango I can give background color to codes when I knit my notebook to pdfs.

However, I don't know how to give colored backgrounds or colored fonts to printed outputs in R. For example, suppose I have the following chunk.

```{r, echo = FALSE}
writeLines("Help")
```

I'd like to see the word Help to be highlighted, say in red font with gray background. How can I achieve this?

Many thanks in advance.

Body:
For a way within R, my huxtable package lets you set background and text colour for tables. I don't know of any within-R package that does it for plain text (maybe crayon?)

library(huxtable)
ht <- hux(c("Red", "Blue"), c("White bg", "Black bg"))
text_color(ht)[, 1] <- c("red", "blue")
background_color(ht)[, 2] <- c("white", "black")
to_latex(ht) # or just print within a rmarkdown document


问:
Add a Filter for Date Range option in a list
Here is a little background on the task:

My client has a sharepoint site that has a list of data. One of the columns or fields is a date field, this date is entered manually using MM/DD/YY Format.

My client has requested me to add an option where they could filter the columns by a range of dates on the webpart view.

I read this guide: https://www.nothingbutsharepoint.com/sites/eusp/pages/sharepoint-list-filtering-by-date-range.aspx and i got it to work, but it seems to be a bit buggy, meaning that sometimes the list will not get filtered by the specified dates..

Another issue with this method is that there is no Submit/Go button.

FROM: [ Text Field ] TO: [ Text Field ] [GO Button]

My client would love to have something like the example above where there are two text fields next to each other and a button that they can click to make the filter activate.

I searched for hours and hours and was not able to find something like this.

Do you guys know of a solution and if so, provide me with a link or guide, on how to achieve this problem? Thanks

EDIT:
It seems that there is a Apply Filters Button Web Part that does what i need. I added it and it seems to work Okay. I have to click Apply Filters TWICE in order for it to work.. weird huh? is there a fix for that?

FYI: i followed the guide linked above and just added the apply filters button.

答:
Found a solution that'll load the filters on first click for me.

In SPD, under the web part that displays the list items (XsltListViewWebPart), change these two to "True":
 ClientRender="True" and IsClientRender="True"

Similar issues mentioned in this thread gave insights to button load sequences and why it doesn't render results on the first click.


问:
Testing Polly retry policy with moq
I'm trying to write a unit test for polly, but it looks like the return is cached.

Method PostAsyncWithRetry:

```<code>using Polly;
using System;
using System.Diagnostics;
using System.Net.Cache;
using System.Net.Http;
public class RetryClient
{
    private HttpClient httpClient = new HttpClient(new WebRequestHandler()
    { CachePolicy = new HttpRequestCachePolicy(HttpRequestCacheLevel.NoCacheNoStore) });

    public HttpResponseMessage PostAsyncWithRetry(
        String url,
        String path,
        StringContent httpContent)
    {
        httpClient.BaseAddress = new Uri(url);

        var retryPolicy =
            Policy.Handle<HttpRequestException>()
            .OrResult<HttpResponseMessage>(r => !r.IsSuccessStatusCode)
            .RetryAsync(3, (exception, retryCount, context) =>
            {
                Debug.WriteLine("RetryCount: {0}", retryCount);
            });

        var response = retryPolicy.ExecuteAsync(async () =>
        {
            return await httpClient.PostAsync(path, httpContent);
        }
        );

        return response.Result;
    }
}
</code>```

Test:

```<code>[TestFixture]
class Test
{
    private HttpClient mockHTTPClient;
    private Mock<WebRequestHandler> mockHttpMessageHandler;
    private RetryClient testInstance;

    private const String URL = "https://aaa.com";
    private const String PATH = "/path";
    private const String EXPECTED_STRING_CONTENT = "Some return text";

    [SetUp]
    public void SetUp()
    {
        testInstance = new RetryClient();
        mockHttpMessageHandler = new Mock<WebRequestHandler>();
        mockHttpMessageHandler.Object.CachePolicy = new HttpRequestCachePolicy(HttpRequestCacheLevel.NoCacheNoStore);

        mockHTTPClient = new HttpClient(mockHttpMessageHandler.Object);

        var type = typeof(RetryClient);
        var fields = type.GetFields(BindingFlags.NonPublic | BindingFlags.Instance);
        fields[0].SetValue(testInstance, mockHTTPClient);
    }

    [Test]
    public void TestMEEEE()
    {
        var responses = new Queue<Task<HttpResponseMessage>>();
        responses.Enqueue(Task.FromResult(new HttpResponseMessage
        {
            StatusCode = HttpStatusCode.NotFound,
            Content = new StringContent(EXPECTED_STRING_CONTENT)
        }));
        responses.Enqueue(Task.FromResult(new HttpResponseMessage
        {
            StatusCode = HttpStatusCode.OK,
            Content = new StringContent(EXPECTED_STRING_CONTENT)
        }));

        var postContent = new StringContent(EXPECTED_STRING_CONTENT);
        mockHttpMessageHandler.Protected()
           .Setup<Task>("SendAsync", ItExpr.IsAny<HttpRequestMessage>(), ItExpr.IsAny<CancellationToken>())
           .Returns(responses.Dequeue());

        var response = testInstance.PostAsyncWithRetry(
            URL, PATH, postContent);

        mockHttpMessageHandler.Verify();
        Assert.AreEqual(responses.Count, 0, "didn't dequeue");
        Assert.AreEqual(HttpStatusCode.OK, response.StatusCode, "Incorrect status code");
    }

}
</code>```

Not sure why, but it looks like the responses queue is only being Dequeue once, this leads me to believe the response is being cache. Does anyone know who is caching the response, and how do I disable it?

Thanks a bunch in advance!

答:
I figured it out. It has nothing to do with caching. During the mock setup, it stores the Dequeue value as a return instead of invoking it every time.
Changing it to () => responses.Dequeue() works now.

Thank you guys!

Title: Testing Polly retry policy with moq

Body:
I'm trying to write a unit test for polly, but it looks like the return is cached.

Method PostAsyncWithRetry:

using Polly;
using System;
using System.Diagnostics;
using System.Net.Cache;
using System.Net.Http;
public class RetryClient
{
    private HttpClient httpClient = new HttpClient(new WebRequestHandler()
    { CachePolicy = new HttpRequestCachePolicy(HttpRequestCacheLevel.NoCacheNoStore) });

    public HttpResponseMessage PostAsyncWithRetry(
        String url,
        String path,
        StringContent httpContent)
    {
        httpClient.BaseAddress = new Uri(url);

        var retryPolicy =
            Policy.Handle<HttpRequestException>()
            .OrResult<HttpResponseMessage>(r => !r.IsSuccessStatusCode)
            .RetryAsync(3, (exception, retryCount, context) =>
            {
                Debug.WriteLine("RetryCount: {0}", retryCount);
            });

        var response = retryPolicy.ExecuteAsync(async () =>
        {
            return await httpClient.PostAsync(path, httpContent);
        }
        );

        return response.Result;
    }
}

Test:

[TestFixture]
class Test
{
    private HttpClient mockHTTPClient;
    private Mock<WebRequestHandler> mockHttpMessageHandler;
    private RetryClient testInstance;

    private const String URL = "https://aaa.com";
    private const String PATH = "/path";
    private const String EXPECTED_STRING_CONTENT = "Some return text";

    [SetUp]
    public void SetUp()
    {
        testInstance = new RetryClient();
        mockHttpMessageHandler = new Mock<WebRequestHandler>();
        mockHttpMessageHandler.Object.CachePolicy = new HttpRequestCachePolicy(HttpRequestCacheLevel.NoCacheNoStore);

        mockHTTPClient = new HttpClient(mockHttpMessageHandler.Object);

        var type = typeof(RetryClient);
        var fields = type.GetFields(BindingFlags.NonPublic | BindingFlags.Instance);
        fields[0].SetValue(testInstance, mockHTTPClient);
    }

    [Test]
    public void TestMEEEE()
    {
        var responses = new Queue<Task<HttpResponseMessage>>();
        responses.Enqueue(Task.FromResult(new HttpResponseMessage
        {
            StatusCode = HttpStatusCode.NotFound,
            Content = new StringContent(EXPECTED_STRING_CONTENT)
        }));
        responses.Enqueue(Task.FromResult(new HttpResponseMessage
        {
            StatusCode = HttpStatusCode.OK,
            Content = new StringContent(EXPECTED_STRING_CONTENT)
        }));

        var postContent = new StringContent(EXPECTED_STRING_CONTENT);
        mockHttpMessageHandler.Protected()
           .Setup<Task>("SendAsync", ItExpr.IsAny<HttpRequestMessage>(), ItExpr.IsAny<CancellationToken>())
           .Returns(responses.Dequeue());

        var response = testInstance.PostAsyncWithRetry(
            URL, PATH, postContent);

        mockHttpMessageHandler.Verify();
        Assert.AreEqual(responses.Count, 0, "didn't dequeue");
        Assert.AreEqual(HttpStatusCode.OK, response.StatusCode, "Incorrect status code");
    }

}

Not sure why, but it looks like the responses queue is only being Dequeue once, this leads me to believe the response is being cache. Does anyone know who is caching the response, and how do I disable it?

Thanks a bunch in advance!

ContentLicense: CC BY-SA 4.0


问:
How to import Oracle DMP file without knowing the original tablespace?
Use case: customer's will send their backup of their DB in a dmp file. That's all they will send.

I'm building a script that will import that dmp file into Oracle using DBMS_DATAPUMP. However, as I do not know the original tablespace name, I'm getting the following error:
ORA-39083: Object type USER:"XXXXX" failed to create with error:
ORA-00959: tablespace 'XXXXXXX' does not exist

This is my PL/SQL procedure:

```<code>    PROCEDURE IMPORTING
(
  DMPFILES IN VARCHAR2,
  FROMSCHEMA IN VARCHAR2,
  TOSCHEMA IN VARCHAR2
) AS
  ind NUMBER;              -- Loop index
  h1 NUMBER;               -- Data Pump job handle
  percent_done NUMBER;     -- Percentage of job complete
  job_state VARCHAR2(30);  -- To keep track of job state
  le ku$_LogEntry;         -- For WIP and error messages
  js ku$_JobStatus;        -- The job status from get_status
  jd ku$_JobDesc;          -- The job description from get_status
  sts ku$_Status;          -- The status object returned by get_status
  array apex_application_global.vc_arr2;
BEGIN
  h1 := DBMS_DATAPUMP.open('IMPORT','FULL',NULL,'EXAMPLE1');
  dbms_datapump.add_file(handle => h1, filename => 'IMPORT.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);
  -- usign this function to split the files passed as a String to an array
  array  := apex_util.string_to_table(DMPFILES, ',');

  for i in 1 .. array.count loop
    DBMS_DATAPUMP.ADD_FILE(h1,array(i),'DATA_PUMP_DIR');
  end loop;

  DBMS_DATAPUMP.METADATA_REMAP(h1,'REMAP_SCHEMA',FROMSCHEMA,TOSCHEMA);

dbms_datapump.set_parameter(handle => h1, name =>
'INCLUDE_METADATA', value => 1);
 dbms_datapump.set_parameter(handle => h1, name =>
'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
 dbms_datapump.set_parameter(handle => h1, name =>
'REUSE_DATAFILES', value => 0);
 dbms_datapump.set_parameter(handle => h1, name =>
'SKIP_UNUSABLE_INDEXES', value => 0);

  DBMS_DATAPUMP.START_JOB(h1);

-- The import job should now be running. In the following loop, the job is
-- monitored until it completes. In the meantime, progress information is
-- displayed. Note: this is identical to the export example.

 percent_done := 0;
  job_state := 'UNDEFINED';
  while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
    dbms_datapump.get_status(h1,
           dbms_datapump.ku$_status_job_error +
           dbms_datapump.ku$_status_job_status +
           dbms_datapump.ku$_status_wip,-1,job_state,sts);
    js := sts.job_status;

-- If the percentage done changed, display the new value.

     if js.percent_done != percent_done
    then
      dbms_output.put_line('*** Job percent done = ' ||
                           to_char(js.percent_done));
      percent_done := js.percent_done;
    end if;

-- If any work-in-progress (WIP) or Error messages were received for the job,
-- display them.

       if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
    then
      le := sts.wip;
    else
      if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
      then
        le := sts.error;
      else
        le := null;
      end if;
    end if;
    if le is not null
    then
      ind := le.FIRST;
      while ind is not null loop
        dbms_output.put_line(le(ind).LogText);
        ind := le.NEXT(ind);
      end loop;
    end if;
  end loop;

-- Indicate that the job finished and gracefully detach from it.

  dbms_output.put_line('Job has completed');
  dbms_output.put_line('Final job state = ' || job_state);
  dbms_datapump.detach(h1);
END IMPORTING;
</code>```

So basically my question is:
Is there a way to import a dmp file into Oracle without knowing the original tablespace...?

Thanks in advance for the help.

UPDATE:
I found when using this script, I also need to know the schema name. So that will make the following changes on the script:

```<code>     PROCEDURE IMPORTING
(
  DMPFILES IN VARCHAR2,
  FROMSCHEMA IN VARCHAR2,
  TOSCHEMA IN VARCHAR2,
  FROMTABLESPACE IN VARCHAR2,
  TOTABLESPACE IN VARCHAR2
) AS
  ind NUMBER;              -- Loop index
  h1 NUMBER;               -- Data Pump job handle
  percent_done NUMBER;     -- Percentage of job complete
  job_state VARCHAR2(30);  -- To keep track of job state
  le ku$_LogEntry;         -- For WIP and error messages
  js ku$_JobStatus;        -- The job status from get_status
  jd ku$_JobDesc;          -- The job description from get_status
  sts ku$_Status;          -- The status object returned by get_status
  array apex_application_global.vc_arr2;
BEGIN
  h1 := DBMS_DATAPUMP.open('IMPORT','FULL',NULL,'EXAMPLE1');
  dbms_datapump.add_file(handle => h1, filename => 'IMPORT.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);
  -- usign this function to split the files passed as a String to an array
  array  := apex_util.string_to_table(DMPFILES, ',');

  for i in 1 .. array.count loop
    DBMS_DATAPUMP.ADD_FILE(h1,array(i),'DATA_PUMP_DIR');
  end loop;

  DBMS_DATAPUMP.METADATA_REMAP(h1,'REMAP_TABLESPACE',FROMTABLESPACE,TOTABLESPACE);
  DBMS_DATAPUMP.METADATA_REMAP(h1,'REMAP_SCHEMA',FROMSCHEMA,TOSCHEMA);

dbms_datapump.set_parameter(handle => h1, name =>
'INCLUDE_METADATA', value => 1);
 dbms_datapump.set_parameter(handle => h1, name =>
'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
 dbms_datapump.set_parameter(handle => h1, name =>
'REUSE_DATAFILES', value => 0);
 dbms_datapump.set_parameter(handle => h1, name =>
'SKIP_UNUSABLE_INDEXES', value => 0);

  DBMS_DATAPUMP.START_JOB(h1);

-- The import job should now be running. In the following loop, the job is
-- monitored until it completes. In the meantime, progress information is
-- displayed. Note: this is identical to the export example.

 percent_done := 0;
  job_state := 'UNDEFINED';
  while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
    dbms_datapump.get_status(h1,
           dbms_datapump.ku$_status_job_error +
           dbms_datapump.ku$_status_job_status +
           dbms_datapump.ku$_status_wip,-1,job_state,sts);
    js := sts.job_status;

-- If the percentage done changed, display the new value.

     if js.percent_done != percent_done
    then
      dbms_output.put_line('*** Job percent done = ' ||
                           to_char(js.percent_done));
      percent_done := js.percent_done;
    end if;

-- If any work-in-progress (WIP) or Error messages were received for the job,
-- display them.

       if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
    then
      le := sts.wip;
    else
      if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
      then
        le := sts.error;
      else
        le := null;
      end if;
    end if;
    if le is not null
    then
      ind := le.FIRST;
      while ind is not null loop
        dbms_output.put_line(le(ind).LogText);
        ind := le.NEXT(ind);
      end loop;
    end if;
  end loop;

-- Indicate that the job finished and gracefully detach from it.

  dbms_output.put_line('Job has completed');
  dbms_output.put_line('Final job state = ' || job_state);
  dbms_datapump.detach(h1);
END IMPORTING;
</code>```

I tested that with a dmp that I exported so I knew the TableSpace and the Schema name.

Summary: any way then to know the tablespace name and the schema name from a dmp file?

Thanks.

答:
Short answer: no

Long answer: yes if you generate the ddl file and then you can actually grep the fille and search for the tablespaces and users. See bellow the script to generate the DDL file:

```<code>create or replace
PROCEDURE GENERATE_SQL
(
  DMPFILES IN VARCHAR2
, SQLFILENAME IN VARCHAR2
, VERSIONPARAM IN VARCHAR2
, JOBNAME IN VARCHAR2
) AS
  h1 number;
  array apex_application_global.vc_arr2;
  ind NUMBER;              -- Loop indexdasdads
  percent_done NUMBER;     -- Percentage of job complete
  job_state VARCHAR2(30);  -- To keep track of job state
  le ku$_LogEntry;         -- For WIP and error messages
  js ku$_JobStatus;        -- The job status from get_status
  jd ku$_JobDesc;          -- The job description from get_status
  sts ku$_Status;          -- The status object returned by get_status
BEGIN
  h1 := dbms_datapump.open(
    operation    => 'SQL_FILE',
    job_mode     => 'SCHEMA',
    remote_link  =>  null,
    job_name     => JOBNAME,
    version      => VERSIONPARAM
  );
  array  := apex_util.string_to_table(DMPFILES, ',');
  for i in 1 .. array.count loop
    dbms_output.put_line('array(i): ' || array(i));
    DBMS_DATAPUMP.ADD_FILE(h1,array(i),'DBRAT_DMP',dbms_datapump.ku$_file_type_dump_file);
  end loop;
  dbms_output.put_line('datapump_job: ' || h1);

  dbms_datapump.add_file(
    handle    =>  h1,
    filename  => SQLFILENAME,
    directory => 'DBRAT_DMP',
    filetype  =>  dbms_datapump.ku$_file_type_sql_file);

  dbms_datapump.start_job(
    handle       => h1,
    skip_current => 0,
    abort_step   => 0);

  percent_done := 0;
  job_state := 'UNDEFINED';
  while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
    dbms_datapump.get_status(h1,
           dbms_datapump.ku$_status_job_error +
           dbms_datapump.ku$_status_job_status +
           dbms_datapump.ku$_status_wip,-1,job_state,sts);
    js := sts.job_status;

-- If the percentage done changed, display the new value.

     if js.percent_done != percent_done
    then
      dbms_output.put_line('*** Job percent done = ' ||
                           to_char(js.percent_done));
      percent_done := js.percent_done;
    end if;

-- If any work-in-progress (WIP) or Error messages were received for the job,
-- display them.

       if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
    then
      le := sts.wip;
    else
      if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
      then
        le := sts.error;
      else
        le := null;
      end if;
    end if;
    if le is not null
    then
      ind := le.FIRST;
      while ind is not null loop
        dbms_output.put_line(le(ind).LogText);
        ind := le.NEXT(ind);
      end loop;
    end if;
  end loop;

-- Indicate that the job finished and gracefully detach from it.

  dbms_output.put_line('Job has completed');
  dbms_output.put_line('Final job state = ' || job_state);
  dbms_datapump.detach(h1);
END GENERATE_SQL;
</code>```

Body:
Use case: customer's will send their backup of their DB in a dmp file. That's all they will send.

I'm building a script that will import that dmp file into Oracle using DBMS_DATAPUMP. However, as I do not know the original tablespace name, I'm getting the following error:
ORA-39083: Object type USER:"XXXXX" failed to create with error:
ORA-00959: tablespace 'XXXXXXX' does not exist

This is my PL/SQL procedure:

    PROCEDURE IMPORTING
(
  DMPFILES IN VARCHAR2,
  FROMSCHEMA IN VARCHAR2,
  TOSCHEMA IN VARCHAR2
) AS
  ind NUMBER;              -- Loop index
  h1 NUMBER;               -- Data Pump job handle
  percent_done NUMBER;     -- Percentage of job complete
  job_state VARCHAR2(30);  -- To keep track of job state
  le ku$_LogEntry;         -- For WIP and error messages
  js ku$_JobStatus;        -- The job status from get_status
  jd ku$_JobDesc;          -- The job description from get_status
  sts ku$_Status;          -- The status object returned by get_status
  array apex_application_global.vc_arr2;
BEGIN
  h1 := DBMS_DATAPUMP.open('IMPORT','FULL',NULL,'EXAMPLE1');
  dbms_datapump.add_file(handle => h1, filename => 'IMPORT.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);
  -- usign this function to split the files passed as a String to an array
  array  := apex_util.string_to_table(DMPFILES, ',');

  for i in 1 .. array.count loop
    DBMS_DATAPUMP.ADD_FILE(h1,array(i),'DATA_PUMP_DIR');
  end loop;

  DBMS_DATAPUMP.METADATA_REMAP(h1,'REMAP_SCHEMA',FROMSCHEMA,TOSCHEMA);

dbms_datapump.set_parameter(handle => h1, name =>
'INCLUDE_METADATA', value => 1);
 dbms_datapump.set_parameter(handle => h1, name =>
'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
 dbms_datapump.set_parameter(handle => h1, name =>
'REUSE_DATAFILES', value => 0);
 dbms_datapump.set_parameter(handle => h1, name =>
'SKIP_UNUSABLE_INDEXES', value => 0);

  DBMS_DATAPUMP.START_JOB(h1);

-- The import job should now be running. In the following loop, the job is
-- monitored until it completes. In the meantime, progress information is
-- displayed. Note: this is identical to the export example.

 percent_done := 0;
  job_state := 'UNDEFINED';
  while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
    dbms_datapump.get_status(h1,
           dbms_datapump.ku$_status_job_error +
           dbms_datapump.ku$_status_job_status +
           dbms_datapump.ku$_status_wip,-1,job_state,sts);
    js := sts.job_status;

-- If the percentage done changed, display the new value.

     if js.percent_done != percent_done
    then
      dbms_output.put_line('*** Job percent done = ' ||
                           to_char(js.percent_done));
      percent_done := js.percent_done;
    end if;

-- If any work-in-progress (WIP) or Error messages were received for the job,
-- display them.

       if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
    then
      le := sts.wip;
    else
      if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
      then
        le := sts.error;
      else
        le := null;
      end if;
    end if;
    if le is not null
    then
      ind := le.FIRST;
      while ind is not null loop
        dbms_output.put_line(le(ind).LogText);
        ind := le.NEXT(ind);
      end loop;
    end if;
  end loop;

-- Indicate that the job finished and gracefully detach from it.

  dbms_output.put_line('Job has completed');
  dbms_output.put_line('Final job state = ' || job_state);
  dbms_datapump.detach(h1);
END IMPORTING;

So basically my question is:
Is there a way to import a dmp file into Oracle without knowing the original tablespace...?

Thanks in advance for the help.

UPDATE:
I found when using this script, I also need to know the schema name. So that will make the following changes on the script:

     PROCEDURE IMPORTING
(
  DMPFILES IN VARCHAR2,
  FROMSCHEMA IN VARCHAR2,
  TOSCHEMA IN VARCHAR2,
  FROMTABLESPACE IN VARCHAR2,
  TOTABLESPACE IN VARCHAR2
) AS
  ind NUMBER;              -- Loop index
  h1 NUMBER;               -- Data Pump job handle
  percent_done NUMBER;     -- Percentage of job complete
  job_state VARCHAR2(30);  -- To keep track of job state
  le ku$_LogEntry;         -- For WIP and error messages
  js ku$_JobStatus;        -- The job status from get_status
  jd ku$_JobDesc;          -- The job description from get_status
  sts ku$_Status;          -- The status object returned by get_status
  array apex_application_global.vc_arr2;
BEGIN
  h1 := DBMS_DATAPUMP.open('IMPORT','FULL',NULL,'EXAMPLE1');
  dbms_datapump.add_file(handle => h1, filename => 'IMPORT.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);
  -- usign this function to split the files passed as a String to an array
  array  := apex_util.string_to_table(DMPFILES, ',');

  for i in 1 .. array.count loop
    DBMS_DATAPUMP.ADD_FILE(h1,array(i),'DATA_PUMP_DIR');
  end loop;

  DBMS_DATAPUMP.METADATA_REMAP(h1,'REMAP_TABLESPACE',FROMTABLESPACE,TOTABLESPACE);
  DBMS_DATAPUMP.METADATA_REMAP(h1,'REMAP_SCHEMA',FROMSCHEMA,TOSCHEMA);

dbms_datapump.set_parameter(handle => h1, name =>
'INCLUDE_METADATA', value => 1);
 dbms_datapump.set_parameter(handle => h1, name =>
'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
 dbms_datapump.set_parameter(handle => h1, name =>
'REUSE_DATAFILES', value => 0);
 dbms_datapump.set_parameter(handle => h1, name =>
'SKIP_UNUSABLE_INDEXES', value => 0);

  DBMS_DATAPUMP.START_JOB(h1);

-- The import job should now be running. In the following loop, the job is
-- monitored until it completes. In the meantime, progress information is
-- displayed. Note: this is identical to the export example.

 percent_done := 0;
  job_state := 'UNDEFINED';
  while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
    dbms_datapump.get_status(h1,
           dbms_datapump.ku$_status_job_error +
           dbms_datapump.ku$_status_job_status +
           dbms_datapump.ku$_status_wip,-1,job_state,sts);
    js := sts.job_status;

-- If the percentage done changed, display the new value.

     if js.percent_done != percent_done
    then
      dbms_output.put_line('*** Job percent done = ' ||
                           to_char(js.percent_done));
      percent_done := js.percent_done;
    end if;

-- If any work-in-progress (WIP) or Error messages were received for the job,
-- display them.

       if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
    then
      le := sts.wip;
    else
      if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
      then
        le := sts.error;
      else
        le := null;
      end if;
    end if;
    if le is not null
    then
      ind := le.FIRST;
      while ind is not null loop
        dbms_output.put_line(le(ind).LogText);
        ind := le.NEXT(ind);
      end loop;
    end if;
  end loop;

-- Indicate that the job finished and gracefully detach from it.

  dbms_output.put_line('Job has completed');
  dbms_output.put_line('Final job state = ' || job_state);
  dbms_datapump.detach(h1);
END IMPORTING;

I tested that with a dmp that I exported so I knew the TableSpace and the Schema name.

Summary: any way then to know the tablespace name and the schema name from a dmp file?

Thanks.

Body:
Short answer: no

Long answer: yes if you generate the ddl file and then you can actually grep the fille and search for the tablespaces and users. See bellow the script to generate the DDL file:

create or replace
PROCEDURE GENERATE_SQL
(
  DMPFILES IN VARCHAR2
, SQLFILENAME IN VARCHAR2
, VERSIONPARAM IN VARCHAR2
, JOBNAME IN VARCHAR2
) AS
  h1 number;
  array apex_application_global.vc_arr2;
  ind NUMBER;              -- Loop indexdasdads
  percent_done NUMBER;     -- Percentage of job complete
  job_state VARCHAR2(30);  -- To keep track of job state
  le ku$_LogEntry;         -- For WIP and error messages
  js ku$_JobStatus;        -- The job status from get_status
  jd ku$_JobDesc;          -- The job description from get_status
  sts ku$_Status;          -- The status object returned by get_status
BEGIN
  h1 := dbms_datapump.open(
    operation    => 'SQL_FILE',
    job_mode     => 'SCHEMA',
    remote_link  =>  null,
    job_name     => JOBNAME,
    version      => VERSIONPARAM
  );
  array  := apex_util.string_to_table(DMPFILES, ',');
  for i in 1 .. array.count loop
    dbms_output.put_line('array(i): ' || array(i));
    DBMS_DATAPUMP.ADD_FILE(h1,array(i),'DBRAT_DMP',dbms_datapump.ku$_file_type_dump_file);
  end loop;
  dbms_output.put_line('datapump_job: ' || h1);

  dbms_datapump.add_file(
    handle    =>  h1,
    filename  => SQLFILENAME,
    directory => 'DBRAT_DMP',
    filetype  =>  dbms_datapump.ku$_file_type_sql_file);

  dbms_datapump.start_job(
    handle       => h1,
    skip_current => 0,
    abort_step   => 0);

  percent_done := 0;
  job_state := 'UNDEFINED';
  while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
    dbms_datapump.get_status(h1,
           dbms_datapump.ku$_status_job_error +
           dbms_datapump.ku$_status_job_status +
           dbms_datapump.ku$_status_wip,-1,job_state,sts);
    js := sts.job_status;

-- If the percentage done changed, display the new value.

     if js.percent_done != percent_done
    then
      dbms_output.put_line('*** Job percent done = ' ||
                           to_char(js.percent_done));
      percent_done := js.percent_done;
    end if;

-- If any work-in-progress (WIP) or Error messages were received for the job,
-- display them.

       if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
    then
      le := sts.wip;
    else
      if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
      then
        le := sts.error;
      else
        le := null;
      end if;
    end if;
    if le is not null
    then
      ind := le.FIRST;
      while ind is not null loop
        dbms_output.put_line(le(ind).LogText);
        ind := le.NEXT(ind);
      end loop;
    end if;
  end loop;

-- Indicate that the job finished and gracefully detach from it.

  dbms_output.put_line('Job has completed');
  dbms_output.put_line('Final job state = ' || job_state);
  dbms_datapump.detach(h1);
END GENERATE_SQL;


问:
Web Audio to visualize and interact with waveforms
How do I write a JavaScript program to display a waveform from an audio file? I want to use Web Audio and Canvas.

I tried this code:

```<code>(new window.AudioContext).decodeAudioData(audioFile, function (data) {
   var channel = data.getChannelData(0);
   for (var i = 0; i < channel; i++) {
       canvas.getContext('2d').fillRect(i, 1, 40 - channel[i], 40);
   }
});
</code>```

But the result is far from what I want (namely, the image is not smooth since I'm drawing with rectangles). I want it to look smooth like this image:

Any hints on how to implement the waveform?

答:
Your render code is extremely inefficient because it will render 44100 pixels for each second of audio. You want to preferably render at most the viewport width with a reduced data set.

The per pixel sample range needed to fit the waveform in the viewport can be calculated with audioDurationSeconds * samplerate / viewPortWidthPx. So for a viewport of 1000px and an audio file of 2 second at 44100 samplerate the samples per pixel = (2 * 44100) / 1000 = ~88.
For each pixel on screen you take the min and max value from that sample range, you use this data to draw the waveform.

Here is an example algorithm that does this but allows you to give the samples per pixel as argument as well as a scroll position to allow for virtual scroll and zooming. It includes a resolution parameter you can tweak for performance, this indicates how many samples it should take per pixel sample range:
Drawing zoomable audio waveform timeline in Javascript

The draw method there is similar to yours, in order to smooth it you need to use lineTo instead of fillRect.This difference shouldn't actually be that huge, I think you might be forgetting to set the width and height attributes on the canvas. Setting this in css causes for blurry drawing, you need to set the attributes.

```<code>let drawWaveform = function(canvas, drawData, width, height) {
   let ctx = canvas.getContext('2d');
   let drawHeight = height / 2;

   // clear canvas incase there is already something drawn
   ctx.clearRect(0, 0, width, height);

   ctx.beginPath();
   ctx.moveTo(0, drawHeight);
   for(let i = 0; i < width; i++) {
      // transform data points to pixel height and move to centre
      let minPixel = drawData[i][0] * drawHeigth + drawHeight;
      ctx.lineTo(i, minPixel);
   }
   ctx.lineTo(width, drawHeight);
   ctx.moveTo(0, drawHeight);
   for(let i = 0; i < width; i++) {
      // transform data points to pixel height and move to centre
      let maxPixel = drawData[i][1] * drawHeigth + drawHeight;
      ctx.lineTo(i, maxPixel);
   }
   ctx.lineTo(width, drawHeight);
   ctx.closePath();
   ctx.fill(); // can do ctx.stroke() for an outline of the waveform
}
</code>```

Body:
How do I write a JavaScript program to display a waveform from an audio file? I want to use Web Audio and Canvas.

I tried this code:

(new window.AudioContext).decodeAudioData(audioFile, function (data) {
   var channel = data.getChannelData(0);
   for (var i = 0; i < channel; i++) {
       canvas.getContext('2d').fillRect(i, 1, 40 - channel[i], 40);
   }
});

But the result is far from what I want (namely, the image is not smooth since I'm drawing with rectangles). I want it to look smooth like this image:

Any hints on how to implement the waveform?

Body:
Your render code is extremely inefficient because it will render 44100 pixels for each second of audio. You want to preferably render at most the viewport width with a reduced data set.

The per pixel sample range needed to fit the waveform in the viewport can be calculated with audioDurationSeconds * samplerate / viewPortWidthPx. So for a viewport of 1000px and an audio file of 2 second at 44100 samplerate the samples per pixel = (2 * 44100) / 1000 = ~88.
For each pixel on screen you take the min and max value from that sample range, you use this data to draw the waveform.

Here is an example algorithm that does this but allows you to give the samples per pixel as argument as well as a scroll position to allow for virtual scroll and zooming. It includes a resolution parameter you can tweak for performance, this indicates how many samples it should take per pixel sample range:
Drawing zoomable audio waveform timeline in Javascript

The draw method there is similar to yours, in order to smooth it you need to use lineTo instead of fillRect.This difference shouldn't actually be that huge, I think you might be forgetting to set the width and height attributes on the canvas. Setting this in css causes for blurry drawing, you need to set the attributes.

let drawWaveform = function(canvas, drawData, width, height) {
   let ctx = canvas.getContext('2d');
   let drawHeight = height / 2;

   // clear canvas incase there is already something drawn
   ctx.clearRect(0, 0, width, height);

   ctx.beginPath();
   ctx.moveTo(0, drawHeight);
   for(let i = 0; i < width; i++) {
      // transform data points to pixel height and move to centre
      let minPixel = drawData[i][0] * drawHeigth + drawHeight;
      ctx.lineTo(i, minPixel);
   }
   ctx.lineTo(width, drawHeight);
   ctx.moveTo(0, drawHeight);
   for(let i = 0; i < width; i++) {
      // transform data points to pixel height and move to centre
      let maxPixel = drawData[i][1] * drawHeigth + drawHeight;
      ctx.lineTo(i, maxPixel);
   }
   ctx.lineTo(width, drawHeight);
   ctx.closePath();
   ctx.fill(); // can do ctx.stroke() for an outline of the waveform
}


问:
display message in button click during postback
Hai frnds , i want to show the message on button click....please help advance thanks</p>

答:
You can use OnClientClick attribute to specify javascript that will runs before postback request

if you want change text on button:

```<code><asp:Button Text="Click me!" runat="server" ID="TestButton" OnClick="TestButton_Click" OnClientClick="(this).value = 'new message'" />
</code>```

if you want show message box :

```<code><asp:Button Text="Click me!" runat="server" ID="TestButton" OnClick="TestButton_Click" OnClientClick="alert('message')" />
</code>```

ContentLicense: CC BY-SA 2.5

Body:
You can use OnClientClick attribute to specify javascript that will runs before postback request

if you want change text on button:

<asp:Button Text="Click me!" runat="server" ID="TestButton" OnClick="TestButton_Click" OnClientClick="(this).value = 'new message'" />

if you want show message box :

<asp:Button Text="Click me!" runat="server" ID="TestButton" OnClick="TestButton_Click" OnClientClick="alert('message')" />


问:
Oracle SQL - Eliminate values that are not equal on a join
Here's how my tables look: Both tables have a composite primary key on all 3 columns. And the SecondaryId will not repeat from these two tables. This is obviously not enforced by a DB constraint but by business rules on the front-end.

```<code>Table1:
PrimaryID|SecondaryID|Email
1   9999    ABC@GMAIL.COM
1   9999    DEF@GMAIL.COM
2   8888    CCC@GMAIL.COM
3   7777    CBA@GMAIL.COM

Table2:
PrimaryID|SecondaryID|Email
1   1111    ABC@GMAIL.COM
2   2222    XYZ@GMAIL.COM
3   3333    CBA@GMAIL.COM
</code>```

I want the secondaryid in both tables returned, joining on the same primaryid where the emails are different on all occurrences of the primary id.

For PrimaryID = 1:
Table1 has two rows, table 2 has one row. But that one email from table2 matches with the one on table1 so I don't want that back. Here's my query but that doesn't eliminate the above duplicate which is what I need.

```<code> SELECT DISTINCT T1.SECONDARYID, T2.SECONDARYID
    FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON T1.PRIMARYID = T2.PRIMARYID
    AND T1.EMAIL <> T2.EMAIL;
</code>```

Returns

```<code>9999    1111
8888    2222
</code>```

答:
Here's another way without needing to query both tables twice:

```<code>WITH table1 AS (SELECT 1 PrimaryID, 9999 SecondaryID, 'ABC@GMAIL.COM' Email FROM dual UNION ALL
                SELECT 1 PrimaryID, 9999 SecondaryID, 'DEF@GMAIL.COM' Email FROM dual UNION ALL
                SELECT 2 PrimaryID, 8888 SecondaryID, 'CCC@GMAIL.COM' Email FROM dual UNION ALL
                SELECT 3 PrimaryID, 7777 SecondaryID, 'CBA@GMAIL.COM' Email FROM dual),
     table2 AS (SELECT 1 PrimaryID, 1111 SecondaryID, 'ABC@GMAIL.COM' Email FROM dual UNION ALL
                SELECT 2 PrimaryID, 2222 SecondaryID, 'XYZ@GMAIL.COM' Email FROM dual UNION ALL
                SELECT 3 PrimaryID, 3333 SecondaryID, 'CBA@GMAIL.COM' Email FROM dual)
SELECT primaryid,
       t1_secondaryid,
       t2_secondaryid
FROM   (SELECT coalesce(t1.primaryid, t2.primaryid) primaryid,
               t1.secondaryid t1_secondaryid,
               t2.secondaryid t2_secondaryid,
               MAX(CASE WHEN t1.email = t2.email THEN 'Y' ELSE 'N' END) OVER (PARTITION BY coalesce(t1.primaryid, t2.primaryid)) same_email_present
        FROM   table1 t1
               FULL OUTER JOIN table2 t2 ON t1.primaryid = t2.primaryid AND t1.email = t2.email)
WHERE  same_email_present != 'Y';

 PRIMARYID T1_SECONDARYID T2_SECONDARYID
---------- -------------- --------------
         2                          2222
         2           8888
</code>```

This uses a full outer join to pull all rows back regardless of whether they match or not. Then we can use an analytic function (I chose MAX() but you could easily switch to COUNT() or SUM() if you wanted to - you'd have to change the final filter to reflect that though) to output 'Y' for all rows if at least one of them has a matching email address.

Then it's just a matter of filtering out those rows which have a 'Y' present.

Body:
Here's how my tables look: Both tables have a composite primary key on all 3 columns. And the SecondaryId will not repeat from these two tables. This is obviously not enforced by a DB constraint but by business rules on the front-end.

Table1:
PrimaryID|SecondaryID|Email
1   9999    ABC@GMAIL.COM
1   9999    DEF@GMAIL.COM
2   8888    CCC@GMAIL.COM
3   7777    CBA@GMAIL.COM

Table2:
PrimaryID|SecondaryID|Email
1   1111    ABC@GMAIL.COM
2   2222    XYZ@GMAIL.COM
3   3333    CBA@GMAIL.COM

I want the secondaryid in both tables returned, joining on the same primaryid where the emails are different on all occurrences of the primary id.

For PrimaryID = 1:
Table1 has two rows, table 2 has one row. But that one email from table2 matches with the one on table1 so I don't want that back. Here's my query but that doesn't eliminate the above duplicate which is what I need.

 SELECT DISTINCT T1.SECONDARYID, T2.SECONDARYID
    FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON T1.PRIMARYID = T2.PRIMARYID
    AND T1.EMAIL <> T2.EMAIL;

Returns

9999    1111
8888    2222

Body:
Here's another way without needing to query both tables twice:

WITH table1 AS (SELECT 1 PrimaryID, 9999 SecondaryID, 'ABC@GMAIL.COM' Email FROM dual UNION ALL
                SELECT 1 PrimaryID, 9999 SecondaryID, 'DEF@GMAIL.COM' Email FROM dual UNION ALL
                SELECT 2 PrimaryID, 8888 SecondaryID, 'CCC@GMAIL.COM' Email FROM dual UNION ALL
                SELECT 3 PrimaryID, 7777 SecondaryID, 'CBA@GMAIL.COM' Email FROM dual),
     table2 AS (SELECT 1 PrimaryID, 1111 SecondaryID, 'ABC@GMAIL.COM' Email FROM dual UNION ALL
                SELECT 2 PrimaryID, 2222 SecondaryID, 'XYZ@GMAIL.COM' Email FROM dual UNION ALL
                SELECT 3 PrimaryID, 3333 SecondaryID, 'CBA@GMAIL.COM' Email FROM dual)
SELECT primaryid,
       t1_secondaryid,
       t2_secondaryid
FROM   (SELECT coalesce(t1.primaryid, t2.primaryid) primaryid,
               t1.secondaryid t1_secondaryid,
               t2.secondaryid t2_secondaryid,
               MAX(CASE WHEN t1.email = t2.email THEN 'Y' ELSE 'N' END) OVER (PARTITION BY coalesce(t1.primaryid, t2.primaryid)) same_email_present
        FROM   table1 t1
               FULL OUTER JOIN table2 t2 ON t1.primaryid = t2.primaryid AND t1.email = t2.email)
WHERE  same_email_present != 'Y';

 PRIMARYID T1_SECONDARYID T2_SECONDARYID
---------- -------------- --------------
         2                          2222
         2           8888

This uses a full outer join to pull all rows back regardless of whether they match or not. Then we can use an analytic function (I chose MAX() but you could easily switch to COUNT() or SUM() if you wanted to - you'd have to change the final filter to reflect that though) to output 'Y' for all rows if at least one of them has a matching email address.

Then it's just a matter of filtering out those rows which have a 'Y' present.


问:
Can I extract a file from a foxpro general field?
I'm porting a VFP 9 application to SQL Server. The VFP app has some tables with "general" fields in them. I get a byte array when query the field, and when I save it to disk, I can look inside and see it's a word document, or a Paint BMP, etc.

From reading around, I've found that the general field is a proprietary format and contains a thumbnail image of the preview of the document (amongst other things, I'm sure).

Can somebody point me to some code that would extract firstly the type of file, and then the actual file data that I can save as the original file. (Getting the preview image out would be nice too.)

Apparently back in the day, somebody wrote a program in foxpro called GENTOFIL.PRG which sounds like it converts general fields to a file. But, google doesn't help much when trying to find that!

答:
The "General" field type in VFP is a bit of an oddity...

From the VFP Help docs:

The General field contains a ten-byte
  reference to the actual contents of
  the field: a spreadsheet, a word
  processor document, or a picture,
  created by another application. The
  actual type and amount of data,
  however, depends on the Automation
  server that creates the object and
  whether you link or embed the OLE
  object.

If you link an OLE object, your table
  contains only the reference to the
  data and to the application that
  created it. If you embed an OLE
  object, the table contains a copy of
  the data as well as a reference to the
  application that created it. The size
  of a General field is limited only by
  the amount of available disk space.

The key thing to note here is that the "general" field type of VFP deals with Microsoft OLE objects and they can be either linked or embedded.  Also, VFP's ability to directly manipulate OLE objects appears to be minimal because when invoking actions on contained OLE objects, the associated application is actually run to open/edit the contents of the OLE-bound "general" field.

If, as you have said, you are able to extract the file by hand, that's probably the best way to go about getting the files out, as even VFP provides minimal ways to interact with the data contained in general type fields.

OwnerDisplayName: Craig Shearer

OwnerDisplayName: Kit Roed

LastEditorDisplayName: Kit Roed


问:
Is there a simple CLI command to copy just the speed of a SELECT statement into a text file (without the data) in MySQL?
Let's say I run:

```<code>CREATE TABLE t1 AS
SELECT ssn, title
FROM employees
WHERE title = "Dictator"
</code>```

I want to automatically save into a text file:

```<code>Query OK, 1118933 rows affected (3.84 sec)
Records: 1118933  Duplicates: 0  Warnings: 0
</code>```

Bonus points if you can also save the query above it :)!

```<code>CREATE TABLE t1 AS
SELECT ssn, title
FROM employees
WHERE title = "Dictator"

Query OK, 1118933 rows affected (3.84 sec)
Records: 1118933  Duplicates: 0  Warnings: 0
</code>```

答:
In this case MacOS. However, I would also be curious to know other
  systems as well. May benefit the community.

The tee command also works on Windows.

Note you need to use the complete path and the file needs to be created

```<code>mysql> tee C:/out.txt
Logging to file 'C:/out.txt'
mysql> select * from stackoverflow.newtable;
+---------+---------+---------+
| Column1 | Column2 | Column3 |
+---------+---------+---------+
| Data1   | Data3   | Data5   |
| Data2   | Data4   | Data6   |
| 1       | 2       | NULL    |
| 1       | 2       | NULL    |
| 2       | 1       | NULL    |
+---------+---------+---------+
5 rows in set (0.00 sec)
</code>```

Content off C:/out.txt

```<code>mysql> select * from stackoverflow.newtable;
+---------+---------+---------+
| Column1 | Column2 | Column3 |
+---------+---------+---------+
| Data1   | Data3   | Data5   |
| Data2   | Data4   | Data6   |
| 1       | 2       | NULL    |
| 1       | 2       | NULL    |
| 2       | 1       | NULL    |
+---------+---------+---------+
5 rows in set (0.00 sec)
</code>```

Body:
Let's say I run:

CREATE TABLE t1 AS
SELECT ssn, title
FROM employees
WHERE title = "Dictator"

I want to automatically save into a text file:

Query OK, 1118933 rows affected (3.84 sec)
Records: 1118933  Duplicates: 0  Warnings: 0

Bonus points if you can also save the query above it :)!

CREATE TABLE t1 AS
SELECT ssn, title
FROM employees
WHERE title = "Dictator"

Query OK, 1118933 rows affected (3.84 sec)
Records: 1118933  Duplicates: 0  Warnings: 0

Body:
In this case MacOS. However, I would also be curious to know other
  systems as well. May benefit the community.

The tee command also works on Windows.

Note you need to use the complete path and the file needs to be created

mysql> tee C:/out.txt
Logging to file 'C:/out.txt'
mysql> select * from stackoverflow.newtable;
+---------+---------+---------+
| Column1 | Column2 | Column3 |
+---------+---------+---------+
| Data1   | Data3   | Data5   |
| Data2   | Data4   | Data6   |
| 1       | 2       | NULL    |
| 1       | 2       | NULL    |
| 2       | 1       | NULL    |
+---------+---------+---------+
5 rows in set (0.00 sec)

Content off C:/out.txt

mysql> select * from stackoverflow.newtable;
+---------+---------+---------+
| Column1 | Column2 | Column3 |
+---------+---------+---------+
| Data1   | Data3   | Data5   |
| Data2   | Data4   | Data6   |
| 1       | 2       | NULL    |
| 1       | 2       | NULL    |
| 2       | 1       | NULL    |
+---------+---------+---------+
5 rows in set (0.00 sec)


问:
Loop through all JSON on page and find specific id
I have two JSON on my page, the second contains the specific object 'articleSection' and the first does not. I am attempting to loop through all scripts until JSON is found, then loop through each JSON to determine if 'articleSection exists'. If it does exists, I want to return the value of 'articleSection.

I've gotten this far, but the return is always undefined.

```<code>function getJSON() {
  var JSONS = document.getElementsByTagName("Script")

  for (i=0; i<JSONS.length;i++){
    if (JSONS[i].getAttribute("type") == "application/ld+json"){
      var jsonParsed = JSON.parse(JSONS[i].innerText);
          if (jsonParsed.articleSection != null && jsonParsed.articleSection != ""){
            return jsonParsed.articleSection;
            }
      }
 }
}
</code>```

My thinking is that I am not correctly looping through each of the JSON scripts, but rather only through the first one that is found in the initial if JSONS[i].getAttribute("type") == "application/ld+json

JSON For Refrence:

JSON ONE:

```<code><script type="application/ld+json">
      {
        "@context" : "http://schema.org",
        "@type" : "Organization",
      }
    </script>
</code>```

JSON TWO:

```<code><script type="application/ld+json">
        {
          "@context": "http://schema.org",
          "@type": "Article",
          "keywords": "",
          "author": {
            "@type": "Person",
            "name": "Staff"
          },
          "articleSection": "Christmas, Festivals, Events, Life, Arts"
}
</code>```

答:
To check articleSection in jsonParsed you can use if (jsonParsed.articleSection){, this will check for null, undefined and empty string.
The reason your code was not working is JSON was malformed as in the JSON

```<code><script type="application/ld+json">
  {
    "@context" : "http://schema.org",
    "@type" : "Organization",
  }
</script>
</code>```

you have , after 'Organization', remove it and it will work.

LIVE DEMO

function getJSON() {
  var JSONS = document.querySelectorAll("script[type='application/ld+json']");
  for (var i=0; i<JSONS.length;i++){
      var jsonParsed = JSON.parse(JSONS[i].innerHTML);
      if (jsonParsed.articleSection){
        return jsonParsed.articleSection;
      }
   }
}
console.log(getJSON());```
<pre class="snippet-code-html lang-html prettyprint-override"><code><script type="application/ld+json">
  {
    "@context": "http://schema.org",
    "@type": "Organization"
  }
</script>

<script type="application/ld+json">
  {
    "@context": "http://schema.org",
    "@type": "Article",
    "keywords": "",
    "author": {
      "@type": "Person",
      "name": "Staff"
    },
    "articleSection": "Christmas, Festivals, Events, Life, Arts"
  }
</script></code>```

Body:
I have two JSON on my page, the second contains the specific object 'articleSection' and the first does not. I am attempting to loop through all scripts until JSON is found, then loop through each JSON to determine if 'articleSection exists'. If it does exists, I want to return the value of 'articleSection.

I've gotten this far, but the return is always undefined.

function getJSON() {
  var JSONS = document.getElementsByTagName("Script")

  for (i=0; i<JSONS.length;i++){
    if (JSONS[i].getAttribute("type") == "application/ld+json"){
      var jsonParsed = JSON.parse(JSONS[i].innerText);
          if (jsonParsed.articleSection != null && jsonParsed.articleSection != ""){
            return jsonParsed.articleSection;
            }
      }
 }
}

My thinking is that I am not correctly looping through each of the JSON scripts, but rather only through the first one that is found in the initial if JSONS[i].getAttribute("type") == "application/ld+json

JSON For Refrence:

JSON ONE:

JSON TWO:

Body:
To check articleSection in jsonParsed you can use if (jsonParsed.articleSection){, this will check for null, undefined and empty string.
The reason your code was not working is JSON was malformed as in the JSON

<script type="application/ld+json">
  {
    "@context" : "http://schema.org",
    "@type" : "Organization",
  }
</script>

you have , after 'Organization', remove it and it will work.

LIVE DEMO

function getJSON() {
  var JSONS = document.querySelectorAll("script[type='application/ld+json']");
  for (var i=0; i<JSONS.length;i++){
      var jsonParsed = JSON.parse(JSONS[i].innerHTML);
      if (jsonParsed.articleSection){
        return jsonParsed.articleSection;
      }
   }
}
console.log(getJSON());

<script type="application/ld+json">
  {
    "@context": "http://schema.org",
    "@type": "Organization"
  }
</script>

<script type="application/ld+json">
  {
    "@context": "http://schema.org",
    "@type": "Article",
    "keywords": "",
    "author": {
      "@type": "Person",
      "name": "Staff"
    },
    "articleSection": "Christmas, Festivals, Events, Life, Arts"
  }
</script>


问:
Merge PDF files
Is it possible, using Python, to merge separate PDF files?

Assuming so, I need to extend this a little further.  I am hoping to loop through folders in a directory and repeat this procedure.

And I may be pushing my luck, but is it possible to exclude a page that is contained in each of the PDFs (my report generation always creates an extra blank page).

答:
Merge all pdf files that are present in a dir

Put the pdf files in a dir. Launch the program. You get one pdf with all the pdfs merged.

```<code>import os
from PyPDF2 import PdfMerger

x = [a for a in os.listdir() if a.endswith(".pdf")]

merger = PdfMerger()

for pdf in x:
    merger.append(open(pdf, 'rb'))

with open("result.pdf", "wb") as fout:
    merger.write(fout)
</code>```

How would I make the same code above today

```<code>from glob import glob
from PyPDF2 import PdfMerger

def pdf_merge():
    ''' Merges all the pdf files in current directory '''
    merger = PdfMerger()
    allpdfs = [a for a in glob("*.pdf")]
    [merger.append(pdf) for pdf in allpdfs]
    with open("Merged_pdfs.pdf", "wb") as new_file:
        merger.write(new_file)

if __name__ == "__main__":
    pdf_merge()
</code>```

Title: Merge PDF files

Body:
Merge all pdf files that are present in a dir

Put the pdf files in a dir. Launch the program. You get one pdf with all the pdfs merged.

import os
from PyPDF2 import PdfMerger

x = [a for a in os.listdir() if a.endswith(".pdf")]

merger = PdfMerger()

for pdf in x:
    merger.append(open(pdf, 'rb'))

with open("result.pdf", "wb") as fout:
    merger.write(fout)

How would I make the same code above today

from glob import glob
from PyPDF2 import PdfMerger

def pdf_merge():
    ''' Merges all the pdf files in current directory '''
    merger = PdfMerger()
    allpdfs = [a for a in glob("*.pdf")]
    [merger.append(pdf) for pdf in allpdfs]
    with open("Merged_pdfs.pdf", "wb") as new_file:
        merger.write(new_file)

if __name__ == "__main__":
    pdf_merge()


问:
SHA certificate firebase Debug/Release
My android app is using firebase realtime Database,
i've putted the app debug SHA certificate fingerprints , and when I test my app with debug mode it works and I get data from DB , now when I want to sign and release the app , " what i've done with my previous app : I delete the debug SHA certificate fingerprint from firebase project console and then I put the release SHA certificate fingerprint and the app is on the market now and it's OK"
but I want to know :

Q1)when I deleted the debug SHA certificate fingerprint with the release one , it will still work when I run my app in debug mode ?

Q2) should i put debug and release SHA certificate fingerprint in the firebase console ?

Q3) If i put debug and release SHA certificate fingerprint in the firebase console then download the new configuration file provided from firebase , my database connexion will still work either on release and debug mode ?

答:
Here are the answer to your questions.

It will work only if you still have the debug SHA certificate fingerprint for debug mode. Otherwise, it won't work.

Yes, you should put in your Firebase Console, both debug and release SHA certificate fingerprints.

Yes indeed, if you put the debug and release SHA certificate fingerprint in the Firebase Console and if you download the google-services.json file again and put it in the right place in your project, your database connection will still work either on release and debug mode.

Title: SHA certificate firebase Debug/Release


问:
Hibernate configuration - NoCacheRegionFactoryAvailableException
Im getting this error:

```<code>Caused by: org.hibernate.cache.NoCacheRegionFactoryAvailableException: Second-level cache is used in the application, but property hibernate.cache.region.factory_class is not given; please either disable second level cache or set correct region factory using the hibernate.cache.region.factory_class setting and make sure the second level cache provider (hibernate-infinispan, e.g.) is available on the classpath.
</code>```

But i have that property set:

```<code><property name="hibernate.cache.use_query_cache">true</property>
<property name="hibernate.cache.use_second_level_cache">true</property>
<property name="hibernate.cache.provider_class">net.sf.hibernate.cache.EhCacheProvider</property>
<property name="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory</property>
</code>```

Im Using Hibernate 4.3.10.Final and i have all the dependencies.

答:
I got the same error with SpringBoot and Hibernate. There was some issue with the versions. This link helped me.

I used Spring Boot 1.5.1 and Hibernate 5.2.8 and modified hibernate.properties file as below:

```<code> hibernate.cache.use_second_level_cache=true
 hibernate.cache.region.factory_class=org.hibernate.cache.ehcache.EhCacheRegionFactory
</code>```

Add caching annotations on the entity:

```<code>@Entity
@Table(name="test")
@Cacheable
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class Test{
}
</code>```

Body:
Im getting this error:

Caused by: org.hibernate.cache.NoCacheRegionFactoryAvailableException: Second-level cache is used in the application, but property hibernate.cache.region.factory_class is not given; please either disable second level cache or set correct region factory using the hibernate.cache.region.factory_class setting and make sure the second level cache provider (hibernate-infinispan, e.g.) is available on the classpath.

But i have that property set:

<property name="hibernate.cache.use_query_cache">true</property>
<property name="hibernate.cache.use_second_level_cache">true</property>
<property name="hibernate.cache.provider_class">net.sf.hibernate.cache.EhCacheProvider</property>
<property name="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory</property>

Im Using Hibernate 4.3.10.Final and i have all the dependencies.

Body:
I got the same error with SpringBoot and Hibernate. There was some issue with the versions. This link helped me.

I used Spring Boot 1.5.1 and Hibernate 5.2.8 and modified hibernate.properties file as below:

 hibernate.cache.use_second_level_cache=true
 hibernate.cache.region.factory_class=org.hibernate.cache.ehcache.EhCacheRegionFactory

Add caching annotations on the entity:

@Entity
@Table(name="test")
@Cacheable
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class Test{
}


问:
defining default values
Im a new SAS User and I have a small problem

I have one large empty table A with lets say 100 columns that I have created with a simple proc sql; create table

I have another table B with lets say 40 columns and table C with 55 columns.
I want to add these two tables into table A, basically I want a table with 100 columns containing the data from table B & C and I'm doing this with a Union command.

Since I dont have values for all 100 variables I have to set default values.
Lets say I have a column named nourishment in table A, food in table B and has no equivalent in table C. I have rules like "If the data comes from table B then value =xxx if its from table C then Value="DefaultValue"

I'd do this easily with R or python but Im struggling with sas.

I'm using SAS sql commands (a Union command)

How do you set default values ? (for all data types : character, numeric or dateI'm using SAS sql commands )

答:
I would not recommend using UNION in PROC SQL at any point in your SAS usage.  UNION is almost always inferior to a simple data step, or a data step view.

That's because the data step seamlessly handles issues like differing variables on different tables.  SAS is quite comfortable with vertically combining datasets; SQL is always a bit trickier when they're not identical.

```<code>data c;
  set a b;
run;
</code>```

That runs whether or not a and b are identical, so long as a and b don't have conflicting variable names (that aren't intended to be in the same column); and if they do, just use the rename dataset option to resolve it.

If you do as the above, and don't use union, you'll get a missing value automatically for those dates.

Title: defining default values

Body:
I would not recommend using UNION in PROC SQL at any point in your SAS usage.  UNION is almost always inferior to a simple data step, or a data step view.

That's because the data step seamlessly handles issues like differing variables on different tables.  SAS is quite comfortable with vertically combining datasets; SQL is always a bit trickier when they're not identical.

data c;
  set a b;
run;

That runs whether or not a and b are identical, so long as a and b don't have conflicting variable names (that aren't intended to be in the same column); and if they do, just use the rename dataset option to resolve it.

If you do as the above, and don't use union, you'll get a missing value automatically for those dates.


问:
Loop through foreach without knowing the index
I would like to Loop throught services but I don't know the index name. They come randomly, example I got 8 and 9 but I do not know them.

```<code>"2": {
    "first_name": "khalfan",
    "last_name": "mussa",
    "date": "2017-06-06 09:21:36",
    "gender": "male",
    "services": {
        "8": {
            "name": "See a Doctor",
            "results": ""
    },
         "9": {
            "name": "Kichocho",
            "results": "FD- 73"
              }
    }
},
</code>```

答:
Suppose you have json stored in $json variable.

```<code>$json = json_decode($json);
foreach($json as $entry) {
 foreach($entry['services'] as $services) {
   //$services['name']
   //and other data here
 }
}
</code>```

You don't need to know the index while using foreach but you can get index from it.

Body:
I would like to Loop throught services but I don't know the index name. They come randomly, example I got 8 and 9 but I do not know them.

"2": {
    "first_name": "khalfan",
    "last_name": "mussa",
    "date": "2017-06-06 09:21:36",
    "gender": "male",
    "services": {
        "8": {
            "name": "See a Doctor",
            "results": ""
    },
         "9": {
            "name": "Kichocho",
            "results": "FD- 73"
              }
    }
},

Body:
Suppose you have json stored in $json variable.

$json = json_decode($json);
foreach($json as $entry) {
 foreach($entry['services'] as $services) {
   //$services['name']
   //and other data here
 }
}

You don't need to know the index while using foreach but you can get index from it.


问:
FaceBook query using FQL
While fetching:
https://api.facebook.com/method/fql.query?query=Select+page_id,page_url,name,pic,website,type,location,fan_count,phone+from+page+where+name%20%20=%22SPA%22
i am getting all exact match results with "SPA"
But how to get other which will match "sp" as well. Like when we use like operator in fql i am getting problem.
Please suggest me with solution</p>

答:
here you go:  </p>

```<code>WHERE strpos(lower(name), 'sp') >= 0
</code>```

Title: FaceBook query using FQL

Body:
here you go:

WHERE strpos(lower(name), 'sp') >= 0


问:
HTML radio buttons allow the selection of multiple options
I am trying to work on making a test for a website using JavaScript. I am not good at javascript though, so I am at a complete loss.

I don't know why but my radio buttons will let me select multiple in the same section, I thought this was only supposed to happen with checkboxes.

On the issue of the javascript I would like to be able to do something like saying if yes, no, yes are seleted display this results a.with each ansswer being from the next section. I don't know how to reference them though.

This is my HTML:

<form>
<fieldset>

<legend>Have you had your record expunged before?</legend>
<input type="radio" name="yes" id="yes" value="yes" />
 yes

<input type="radio" name="no" id="no" value="no" />
  no
</fieldset>
</form>```

Thank you!

答:
MDN says:

A radio group is defined by giving each of radio buttons in the group the same name. Once a radio group is established, selecting any radio button in that group automatically deselects any currently-selected radio button in the same group.

  For example, if your form needs to ask the user for their preferred contact method, you might create three radio buttons, each with the name property set to "contact" but one with the value "email", one with the value "phone", and one with the value "mail". The user never sees the value or the name (unless you expressly add code to display it).

So, it is because your radio buttons have different names, they should have the same name and different values.

Try giving radio buttons of the same group the same name value:

<form>
<fieldset>

<legend>Have you had your record expunged before?</legend>
<input type="radio" name="yes_or_no" id="yes" value="yes" />
 yes

<input type="radio" name="yes_or_no" id="no" value="no" />
  no

</fieldset>
</form>```

Body:
I am trying to work on making a test for a website using JavaScript. I am not good at javascript though, so I am at a complete loss.

I don't know why but my radio buttons will let me select multiple in the same section, I thought this was only supposed to happen with checkboxes.

On the issue of the javascript I would like to be able to do something like saying if yes, no, yes are seleted display this results a.with each ansswer being from the next section. I don't know how to reference them though.

This is my HTML:

<form>
<fieldset>

<legend>Have you had your record expunged before?</legend>
<input type="radio" name="yes" id="yes" value="yes" />
 yes

<input type="radio" name="no" id="no" value="no" />
  no
</fieldset>
</form>

Thank you!

Body:
MDN says:

A radio group is defined by giving each of radio buttons in the group the same name. Once a radio group is established, selecting any radio button in that group automatically deselects any currently-selected radio button in the same group.

  For example, if your form needs to ask the user for their preferred contact method, you might create three radio buttons, each with the name property set to "contact" but one with the value "email", one with the value "phone", and one with the value "mail". The user never sees the value or the name (unless you expressly add code to display it).

So, it is because your radio buttons have different names, they should have the same name and different values.

Try giving radio buttons of the same group the same name value:

<form>
<fieldset>

<div class="col">
<legend>Have you had your record expunged before?</legend>
<input type="radio" name="yes_or_no" id="yes" value="yes" />
<label> yes</label>
</div>

<input type="radio" name="yes_or_no" id="no" value="no" />
<label>  no </label>

</fieldset>
</form>


问:
how to sum records in columns of a particular row with the answer in same row
How can I sum the fees, bus, and snacks and the result to show on same row under total? e.g 100+20+500 = 620.

The data

```<code>salary recurrent repairs_maint .....   TOTAL
=============================================
100       20      250    250  .....     620
==============================================
20   10  200      100    100.....      430
==============================================
</code>```

The PHP code

```<code><?php
      $add=mysqli_query($conn,'SELECT SUM(salary),SUM(recurrent),SUM(repairs_maint),SUM(creditors) ,SUM(petty_cash) ,SUM(capital_expenses)from `monthly_expenditure`');
      while($row1=mysqli_fetch_array($add))
      {
        $mark=$row1['SUM(salary)'];
        $mark1=$row1['SUM(recurrent)'];
        $mark3=$row1['SUM(repairs_maint)'];
        $mark4=$row1['SUM(creditors)'];
        $mark5=$row1['SUM(petty_cash)'];
        $mark6=$row1['SUM(capital_expenses)'];
     ?>

      <tr>
      <th></th>
      <th>Total  =</th>
        <th><?php echo $mark ?></th>
        <th><?php echo $mark1 ?></th>
        <th><?php echo $mark3 ?></th>
        <th><?php echo $mark4 ?></th>
        <th><?php echo $mark5 ?></th>
        <th><?php echo $mark6 ?></th>
</code>```

答:
You would start from here instead:</p>

```<code>id expense value
 1 FEES      100
 1 BUS        20
 1 SNACKS    500
 2 FEES       20
 2 BUS        10
 2 SNACKS    400
</code>```

Body:
How can I sum the fees, bus, and snacks and the result to show on same row under total? e.g 100+20+500 = 620.

The data

salary recurrent repairs_maint .....   TOTAL
=============================================
100       20      250    250  .....     620
==============================================
20   10  200      100    100.....      430
==============================================

The PHP code

<?php
      $add=mysqli_query($conn,'SELECT SUM(salary),SUM(recurrent),SUM(repairs_maint),SUM(creditors) ,SUM(petty_cash) ,SUM(capital_expenses)from `monthly_expenditure`');
      while($row1=mysqli_fetch_array($add))
      {
        $mark=$row1['SUM(salary)'];
        $mark1=$row1['SUM(recurrent)'];
        $mark3=$row1['SUM(repairs_maint)'];
        $mark4=$row1['SUM(creditors)'];
        $mark5=$row1['SUM(petty_cash)'];
        $mark6=$row1['SUM(capital_expenses)'];
     ?>

      Total  =
        <?php echo $mark ?>
        <?php echo $mark1 ?>
        <?php echo $mark3 ?>
        <?php echo $mark4 ?>
        <?php echo $mark5 ?>
        <?php echo $mark6 ?>

Body:
You would start from here instead:

id expense value
 1 FEES      100
 1 BUS        20
 1 SNACKS    500
 2 FEES       20
 2 BUS        10
 2 SNACKS    400