技术求助：通过Node.js利用SQL Server数据绘制饼图失败

Hey there! Let's work through your pie chart issue step by step. I noticed a few potential hiccups in your code that might be keeping you from getting that chart to render properly, plus a possible mix-up between SQL Server and PostgreSQL mentioned in your comments. Let's break this down:

1. Async Timing & Outdated AJAX Pattern #

Using async: false to force synchronous AJAX requests is not only outdated but risky—it can freeze the browser and might cause your D3 code to run before the data has actually loaded. Instead, move all your pie chart rendering logic inside the AJAX success callback, or use modern async/await syntax.

2. Redundant JSON Handling & Unscoped Variables #

Since you set dataType: 'json', jQuery automatically parses the response into a JavaScript object for you. There's no need to run JSON.stringify(msg) followed by JSON.parse(msg1)—you can use the msg object directly. Also, msg1 isn't declared with let/const, so it becomes a global variable which can lead to unexpected bugs.

3. Incomplete D3 Pie Chart Implementation #

Your current D3 code only creates an SVG container, but it's missing the core pie chart logic: calculating the pie layout, defining arc shapes, binding data to elements, and actually drawing the slices.

4. Database Source Mix-Up #

You mentioned using SQL Server in your question, but your code comments reference PostgreSQL. Double-check that your /piedata endpoint is pulling data from the correct database (SQL Server) and returning properly formatted JSON.

Fixed Code Example #

Here's a revised version of your code that addresses all these issues, with a complete D3 pie chart implementation:

<!-- Include required libraries -->
<script src="https://code.jquery.com/jquery-3.6.4.min.js"></script>
<script src="https://d3js.org/d3.v7.min.js"></script>

<script>
$(document).ready(function () {
  // Fetch data and trigger chart rendering on success
  $.ajax({
    url: "/piedata",
    type: "get",
    dataType: 'json',
    contentType: "application/json",
    success: function (dataset) {
      // Pass the parsed data directly to the chart renderer
      renderPieChart(dataset);
    },
    error: function (xhr, status, err) {
      console.error("Failed to load pie chart data:", err);
    }
  });
});

function renderPieChart(data) {
  // Set up chart dimensions
  const width = 1000;
  const height = 600;
  const radius = Math.min(width, height) / 2;

  // Create SVG container and center it
  const svg = d3.select('#piec')
    .append('svg')
    .attr('width', width)
    .attr('height', height)
    .append('g')
    .attr('transform', `translate(${width/2}, ${height/2})`);

  // Define pie layout (assumes data has a 'value' property)
  const pieLayout = d3.pie()
    .value(d => d.value);

  // Define arc generator for slice shapes
  const arcGenerator = d3.arc()
    .innerRadius(0) // Use a value >0 for a donut chart
    .outerRadius(radius);

  // Bind data to arc elements
  const slices = svg.selectAll('path')
    .data(pieLayout(data))
    .enter()
    .append('path');

  // Draw slices with colors
  slices.attr('d', arcGenerator)
    .attr('fill', (d, i) => d3.schemeCategory10[i]) // Use D3's built-in color scheme
    .attr('stroke', '#fff')
    .attr('stroke-width', 2);

  // Add optional labels for each slice
  slices.append('title')
    .text(d => `${d.data.name}: ${d.data.value}`);

  // Add center label (optional)
  svg.append('text')
    .attr('text-anchor', 'middle')
    .attr('font-size', '1.5em')
    .text('Total Distribution');
}
</script>

<!-- Container for the pie chart -->
<div id="piec"></div>

Backend Node.js/SQL Server Note #

Ensure your /piedata endpoint returns data in the expected format (e.g., [{name: "Category A", value: 25}, {name: "Category B", value: 75}]). Here's a quick example using mssql for SQL Server:

const express = require('express');
const sql = require('mssql');
const app = express();

// SQL Server connection config
const dbConfig = {
  user: 'your_username',
  password: 'your_password',
  server: 'your_server_address',
  database: 'your_database',
  options: {
    encrypt: true, // Required for Azure SQL
    trustServerCertificate: true
  }
};

app.get('/piedata', async (req, res) => {
  try {
    await sql.connect(dbConfig);
    const result = await sql.query(`
      SELECT category_name AS name, category_count AS value 
      FROM your_pie_chart_table
    `);
    res.json(result.recordset);
  } catch (err) {
    console.error('Database error:', err);
    res.status(500).json({ error: 'Failed to retrieve data' });
  } finally {
    await sql.close();
  }
});

app.listen(3000, () => console.log('Server running on port 3000'));

Don't forget to check your browser's developer console for any error messages—they're invaluable for debugging issues like missing data, incorrect D3 syntax, or CORS problems.

内容的提问来源于stack exchange，提问作者user9465847