JavaScript не может получить данные поля JSON из HTML и выполнить SQL-запрос

JavaScript не может получить данные поля JSON из HTML и выполнить SQL-запрос ⇐ Html

1 сообщение • Страница 1 из 1

Anonymous

JavaScript не может получить данные поля JSON из HTML и выполнить SQL-запрос

Цитата

Сообщение Anonymous » 03 янв 2026, 14:55

У меня возникла проблема с моим кодом JavaScript, который неправильно извлекает и использует данные, отправленные в формате JSON из HTML-формы. Вот процесс:
Пользователь вводит один или несколько адресов в HTML-форму.
Адреса анализируются и отправляются на сервер в формате JSON.
Предполагается, что JavaScript извлекает «streetName» из JSON, использует его в запросе SQL для сопоставления поля «NAME» и возвращает на HTML-страницу такие значения, как FULL_ADD, LATITUDE и LONGITUDE.
Проблема в том, что JavaScript не получает поля из JSON, хотя я вижу, что данные отправляются в консоль. SQL-запрос не возвращает никаких результатов, поскольку данные передаются неправильно. Я также пробовал жестко закодировать значения в запросе SQL, но это тоже не сработало.
Вот ссылка на мой код:
https://jsfiddle.net/6owvz91y

Код: Выделить всё

//JavaScript Code:
const express = require('express');
const cors = require('cors');
const {
Connection,
Request,
TYPES
} = require('tedious'); // Ensure TYPES is imported
const path = require('path'); // Import path module to resolve file paths

// Initialize Express app
const app = express();
app.use(cors());
app.use(express.json()); // To parse JSON request bodies

// Server configuration
const PORT = 3000;

// Database configuration using NTLM (Windows Authentication)
const config = {
server: 'REXT21',
options: {
database: 'SPATIAL',
port: 1433,
encrypt: false,
trustServerCertificate: true,
},
authentication: {
type: 'ntlm',
options: {
domain: 'TEST-NET',
userName: 'MILO',
password: 'Setter4',
},
},
};

// Create the connection
const connection = new Connection(config);

let isConnected = false;

// Ensure the connection is fully established before starting any requests
connection.on('connect', (err) => {
if (err) {
console.error('Connection Failed:', err);
} else {
console.log('Connected to SQL Server');
isConnected = true;
}
});

// Connect to the database
connection.connect();

// Endpoint to handle geocoding requests
app.post('/geocode', async (req, res) => {
const {
addresses
} = req.body;

if (!addresses || !Array.isArray(addresses) || address.length === 0) {
return res.status(400).json({
error: 'At least one address is required'
});
}

if (!isConnected) {
return res.status(500).json({
error: 'Database not connected'
});
}

let results = [];
let processedCount = 0;

// Process each address asynchronously
for (const streetName of addresses) {

// SQL query with additional logic to handle addresses without commas
const sqlQuery = `
SELECT TOP 1 LATITUDE, LONGITUDE, FULL_ADD, NAME
FROM Addresses_p
WHERE @streetName = NAME
`;

const request = new Request(sqlQuery, (err) => {
if (err) {
console.error('Error executing query:', err);
results.push({
streetName,
error: 'Error executing query'
});
processedCount++;
}
});

request.addParameter('streetName', TYPES.NVarChar, streetName);

request.on('row', (columns) => {
const result = {
streetName,
latitude: columns[0].value,
longitude: columns[1].value,
FULL_ADD: columns[3].value,
};

// Log the extracted fields to the console
console.log('Extracted fields for address:', streetName);
console.log(result);

results.push(result);
});

request.on('requestCompleted', () => {
processedCount++;

// Once the request is completed, send the response
if (processedCount === addresses.length) {
res.json(results);
}
});

connection.execSql(request);
}
});

// Serve the index.html file
app.use(express.static(__dirname));
app.get('/', (req, res) => {
res.sendFile(path.join(__dirname, 'index.html'));
});

// Start the Express server
app.listen(PORT, () =>  {
console.log(`Server is running on http://localhost:${PORT}`);
});

Код: Выделить всё






Address Geocoding and CSV Export

body {
font-family: Arial, sans-serif;
margin: 20px;
}

textarea {
width: 100%;
height: 150px;
margin-bottom: 20px;
}

table {
width: 100%;
border-collapse: collapse;
margin-top: 20px;
}

table,
th,
td {
border: 1px solid black;
}

th,
td {
padding: 8px;
text-align: left;
}

.invalid {
background-color: lightcoral;
}

button {
margin-top: 10px;
padding: 10px 15px;
font-size: 16px;
cursor: pointer;
}

#progress-container {
margin-top: 20px;
width: 100%;
background-color: #f3f3f3;
border: 1px solid #ccc;
border-radius: 5px;
overflow: hidden;
height: 25px;
}

#progress-bar {
height: 100%;
width: 0%;
background-color: #4caf50;
text-align: center;
line-height: 25px;
color: white;
font-weight: bold;
}




Address Geocoding and CSV Export
Enter addresses (one per line):

Geocode Addresses
Download as CSV


0%


Geocoded Results:



Input Address
Street Number
Street Name
Street Type
Post Direction
Unit
Municipality
Province
Postal Code
Matched Address
Latitude
Longitude







const streetTypes = ["AV", "AVE", "AVENUE", "BLVD", "BOULEVARD", "BV", "CE", "CENTRE", "CIR", "CIRCLE", "CL", "CLOSE", "CM", "CMN", "COMMON", "COURT", "CR", "CRES", "CRESCENT", "CROSSING", "CRT", "CS", "CT", "CTR", "DR", "DRIVE", "GARDEN", "GARDENS", "GATE", "GDN", "GDNS", "GR", "GREEN", "GROVE", "GT", "GV", "HEIGHTS", "HIGHWAY", "HILL", "HL", "HOLLOW", "HT", "HW", "HWY", "LANDING", "LANE", "LG", "LI", "LINE", "LN", "LNDG", "MANOR", "MR", "PARK", "PARKWAY", "PASS", "PATH", "PH", "PK", "PKWY", "PL", "PLACE", "POINT", "PS", "PT", "PY", "RD", "RN", "ROAD", "RUN", "SIDEROAD", "SQ", "SQUARE", "SR", "ST", "STREET", "TER", "TERRACE", "TL", "TLIN", "TOWNLINE", "TR", "TRAIL", "VIEW", "VW", "WALK", "WAY", "WK", "WO", "WOOD", "WY"];
const directions = ["E", "EAST", "N", "NORTH", "S", "SOUTH", "W", "WEST"];
const exceptions = [{
regex: /\bHWY\s+(?:[1-9]|[1-9]\d|4\d{2})\b/,
words: 2
},
{
regex: /\bHIGHWAY\s+(?:[1-9]|[1-9]\d|4\d{2})\b/,
words: 2
},
{
regex: /\b(?:[1-9]|[1-9]\d|4\d{2})\s+HWY\b/,
words: 2
},
{
regex: /\b(?:[1-9]|[1-9]\d|4\d{2})\s+HIGHWAY\b/,
words:  2
}
];

function isValidStreetType(word) {
return streetTypes.includes(word.toUpperCase());
}

function processAddress(input) {
return input
.replace(/,/g, ' ')
.replace(/[\/\\]/g, ' ')
.replace(/[^a-zA-Z0-9\s-]/g, '')
.replace(/(\d+)\s+(\d+)/g, '$1-$2')
.replace(/\s-\s/g, '-')
.toUpperCase()
.trim();
}

async function geocodeAddresses() {
const addressInput = document.getElementById("addressInput").value.trim();
const addresses = addressInput.split('\n');
const resultsTable = document.getElementById("resultTable").getElementsByTagName("tbody")[0];
const progressBar = document.getElementById("progress-bar");
resultsTable.innerHTML = '';

const totalAddresses = addresses.length;
let currentIndex = 0;

function updateProgress() {
const progress = Math.round((currentIndex / totalAddresses) * 100);
progressBar.style.width = `${progress}%`;
progressBar.textContent = `${progress}%`;
}

for (const address of addresses) {
try {
let processedAddress = processAddress(address);
let unit = '',
streetNumber = '',
streetName = '',
streetType = '',
postDirection = '',
province = '',
postalCode = '',
municipality = '';

// Step 1: Handle exceptions, assign address and unit number
let exceptionHandled = false;
if (/^\d/.test(processedAddress)) {
for (const exception of exceptions) {
const match = processedAddress.match(exception.regex);
if (match) {
const matchedWords = match[0];
streetName = matchedWords;
streetType = "";
processedAddress = processedAddress.replace(matchedWords, '').trim();
exceptionHandled = true;
break;
}
}

} else {
// If the address does not start with a number, apply new logic
const parts = processedAddress.split(' ');
let firstStreetTypeIndex = -1;
for (let i = 0; i < parts.length;  i++) {
if (isValidStreetType(parts[i])) {
firstStreetTypeIndex = i;
break;
}
}
//More Parsing logic

// Send JSON POST request for each field
const response = await fetch('http://localhost:3000/geocode', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({
parsedData: {
streetNumber,
streetName,
streetType,
postDirection,
unit,
municipality,
}
}),
});

const responseData = await response.json();

// Extract data from the response
const matchedAddress = responseData.FULL_ADD || 'No match found';
const latitude = responseData.latitude || 'N/A';
const longitude = responseData.longitude || 'N/A';

// Add a new row to the results table
const row = resultsTable.insertRow();
row.insertCell(0).textContent = address;
row.insertCell(1).textContent = streetNumber;
row.insertCell(2).textContent = streetName;
row.insertCell(3).textContent = streetType;
row.insertCell(4).textContent = postDirection;
row.insertCell(5).textContent = unit;
row.insertCell(6).textContent = municipality;
row.insertCell(7).textContent = province;
row.insertCell(8).textContent = postalCode;
row.insertCell(9).textContent = matchedAddress;
row.insertCell(10).textContent = latitude;
row.insertCell(11).textContent = longitude;

} catch (error) {
console.error('Error geocoding address:', error);

// Add a row with an error message
const row = resultsTable.insertRow();
row.insertCell(0).textContent = address;
row.insertCell(1).colSpan = 11;
row.insertCell(1).textContent = 'Error processing this address';
row.classList.add('invalid');
}

currentIndex++;
updateProgress();
}
}

function downloadCSV() {
const table = document.getElementById("resultTable");
const rows = table.querySelectorAll("tr");
const csv = [];

// Include the header row from the table
const headers = table.querySelectorAll("th");
const headerRow = Array.from(headers).map(header => header.innerText.trim());
csv.push(headerRow.join(',')); // Adding header row to CSV

// Include each data row from the table
rows.forEach((row, index) => {
// Skip the header row, we already added it
if (index > 0) {
const data = Array.from(row.querySelectorAll("td"))
.map(cell => {
let cellValue = cell.innerText.trim();
return cellValue === "" ? "" : `"${cellValue}"`; // Wrap non-empty values in quotes, keeping empty values as they are
});
csv.push(data.join(',')); // Adding the data row to CSV
}
});

// Convert the array to a CSV string and trigger a download
const csvString = csv.join('\n');
const blob = new Blob([csvString], {
type: 'text/csv'
});
const link = document.createElement("a");
link.href = URL.createObjectURL(blob);
link.download = 'geocoded_addresses.csv';
link.click();
}

Подробнее здесь: https://stackoverflow.com/questions/793 ... -sql-query

1767441301

Anonymous

У меня возникла проблема с моим кодом JavaScript, который неправильно извлекает и использует данные, отправленные в формате JSON из HTML-формы. Вот процесс:
Пользователь вводит один или несколько адресов в HTML-форму.
Адреса анализируются и отправляются на сервер в формате JSON.
Предполагается, что JavaScript извлекает «streetName» из JSON, использует его в запросе SQL для сопоставления поля «NAME» и возвращает на HTML-страницу такие значения, как FULL_ADD, LATITUDE и LONGITUDE.
Проблема в том, что JavaScript не получает поля из JSON, хотя я вижу, что данные отправляются в консоль. SQL-запрос не возвращает никаких результатов, поскольку данные передаются неправильно.  Я также пробовал жестко закодировать значения в запросе SQL, но это тоже не сработало.
Вот ссылка на мой код:
https://jsfiddle.net/6owvz91y


[code]//JavaScript Code:
const express = require('express');
const cors = require('cors');
const {
Connection,
Request,
TYPES
} = require('tedious'); // Ensure TYPES is imported
const path = require('path'); // Import path module to resolve file paths

// Initialize Express app
const app = express();
app.use(cors());
app.use(express.json()); // To parse JSON request bodies

// Server configuration
const PORT = 3000;

// Database configuration using NTLM (Windows Authentication)
const config = {
server: 'REXT21',
options: {
database: 'SPATIAL',
port: 1433,
encrypt: false,
trustServerCertificate: true,
},
authentication: {
type: 'ntlm',
options: {
domain: 'TEST-NET',
userName: 'MILO',
password: 'Setter4',
},
},
};

// Create the connection
const connection = new Connection(config);

let isConnected = false;

// Ensure the connection is fully established before starting any requests
connection.on('connect', (err) => {
if (err) {
console.error('Connection Failed:', err);
} else {
console.log('Connected to SQL Server');
isConnected = true;
}
});

// Connect to the database
connection.connect();

// Endpoint to handle geocoding requests
app.post('/geocode', async (req, res) => {
const {
addresses
} = req.body;

if (!addresses || !Array.isArray(addresses) || address.length === 0) {
return res.status(400).json({
error: 'At least one address is required'
});
}

if (!isConnected) {
return res.status(500).json({
error: 'Database not connected'
});
}

let results = [];
let processedCount = 0;

// Process each address asynchronously
for (const streetName of addresses) {

// SQL query with additional logic to handle addresses without commas
const sqlQuery = `
SELECT TOP 1 LATITUDE, LONGITUDE, FULL_ADD, NAME
FROM Addresses_p
WHERE @streetName = NAME
`;

const request = new Request(sqlQuery, (err) => {
if (err) {
console.error('Error executing query:', err);
results.push({
streetName,
error: 'Error executing query'
});
processedCount++;
}
});

request.addParameter('streetName', TYPES.NVarChar, streetName);

request.on('row', (columns) => {
const result = {
streetName,
latitude: columns[0].value,
longitude: columns[1].value,
FULL_ADD: columns[3].value,
};

// Log the extracted fields to the console
console.log('Extracted fields for address:', streetName);
console.log(result);

results.push(result);
});

request.on('requestCompleted', () => {
processedCount++;

// Once the request is completed, send the response
if (processedCount === addresses.length) {
res.json(results);
}
});

connection.execSql(request);
}
});

// Serve the index.html file
app.use(express.static(__dirname));
app.get('/', (req, res) => {
res.sendFile(path.join(__dirname, 'index.html'));
});

// Start the Express server
app.listen(PORT, () =>  {
console.log(`Server is running on http://localhost:${PORT}`);
});[/code]
[code]





Address Geocoding and CSV Export

body {
font-family: Arial, sans-serif;
margin: 20px;
}

textarea {
width: 100%;
height: 150px;
margin-bottom: 20px;
}

table {
width: 100%;
border-collapse: collapse;
margin-top: 20px;
}

table,
th,
td {
border: 1px solid black;
}

th,
td {
padding: 8px;
text-align: left;
}

.invalid {
background-color: lightcoral;
}

button {
margin-top: 10px;
padding: 10px 15px;
font-size: 16px;
cursor: pointer;
}

#progress-container {
margin-top: 20px;
width: 100%;
background-color: #f3f3f3;
border: 1px solid #ccc;
border-radius: 5px;
overflow: hidden;
height: 25px;
}

#progress-bar {
height: 100%;
width: 0%;
background-color: #4caf50;
text-align: center;
line-height: 25px;
color: white;
font-weight: bold;
}




Address Geocoding and CSV Export
Enter addresses (one per line):

Geocode Addresses
Download as CSV


0%


Geocoded Results:



Input Address
Street Number
Street Name
Street Type
Post Direction
Unit
Municipality
Province
Postal Code
Matched Address
Latitude
Longitude







const streetTypes = ["AV", "AVE", "AVENUE", "BLVD", "BOULEVARD", "BV", "CE", "CENTRE", "CIR", "CIRCLE", "CL", "CLOSE", "CM", "CMN", "COMMON", "COURT", "CR", "CRES", "CRESCENT", "CROSSING", "CRT", "CS", "CT", "CTR", "DR", "DRIVE", "GARDEN", "GARDENS", "GATE", "GDN", "GDNS", "GR", "GREEN", "GROVE", "GT", "GV", "HEIGHTS", "HIGHWAY", "HILL", "HL", "HOLLOW", "HT", "HW", "HWY", "LANDING", "LANE", "LG", "LI", "LINE", "LN", "LNDG", "MANOR", "MR", "PARK", "PARKWAY", "PASS", "PATH", "PH", "PK", "PKWY", "PL", "PLACE", "POINT", "PS", "PT", "PY", "RD", "RN", "ROAD", "RUN", "SIDEROAD", "SQ", "SQUARE", "SR", "ST", "STREET", "TER", "TERRACE", "TL", "TLIN", "TOWNLINE", "TR", "TRAIL", "VIEW", "VW", "WALK", "WAY", "WK", "WO", "WOOD", "WY"];
const directions = ["E", "EAST", "N", "NORTH", "S", "SOUTH", "W", "WEST"];
const exceptions = [{
regex: /\bHWY\s+(?:[1-9]|[1-9]\d|4\d{2})\b/,
words: 2
},
{
regex: /\bHIGHWAY\s+(?:[1-9]|[1-9]\d|4\d{2})\b/,
words: 2
},
{
regex: /\b(?:[1-9]|[1-9]\d|4\d{2})\s+HWY\b/,
words: 2
},
{
regex: /\b(?:[1-9]|[1-9]\d|4\d{2})\s+HIGHWAY\b/,
words:  2
}
];

function isValidStreetType(word) {
return streetTypes.includes(word.toUpperCase());
}

function processAddress(input) {
return input
.replace(/,/g, ' ')
.replace(/[\/\\]/g, ' ')
.replace(/[^a-zA-Z0-9\s-]/g, '')
.replace(/(\d+)\s+(\d+)/g, '$1-$2')
.replace(/\s-\s/g, '-')
.toUpperCase()
.trim();
}

async function geocodeAddresses() {
const addressInput = document.getElementById("addressInput").value.trim();
const addresses = addressInput.split('\n');
const resultsTable = document.getElementById("resultTable").getElementsByTagName("tbody")[0];
const progressBar = document.getElementById("progress-bar");
resultsTable.innerHTML = '';

const totalAddresses = addresses.length;
let currentIndex = 0;

function updateProgress() {
const progress = Math.round((currentIndex / totalAddresses) * 100);
progressBar.style.width = `${progress}%`;
progressBar.textContent = `${progress}%`;
}

for (const address of addresses) {
try {
let processedAddress = processAddress(address);
let unit = '',
streetNumber = '',
streetName = '',
streetType = '',
postDirection = '',
province = '',
postalCode = '',
municipality = '';

// Step 1: Handle exceptions, assign address and unit number
let exceptionHandled = false;
if (/^\d/.test(processedAddress)) {
for (const exception of exceptions) {
const match = processedAddress.match(exception.regex);
if (match) {
const matchedWords = match[0];
streetName = matchedWords;
streetType = "";
processedAddress = processedAddress.replace(matchedWords, '').trim();
exceptionHandled = true;
break;
}
}

} else {
// If the address does not start with a number, apply new logic
const parts = processedAddress.split(' ');
let firstStreetTypeIndex = -1;
for (let i = 0; i < parts.length;  i++) {
if (isValidStreetType(parts[i])) {
firstStreetTypeIndex = i;
break;
}
}
//More Parsing logic

// Send JSON POST request for each field
const response = await fetch('http://localhost:3000/geocode', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({
parsedData: {
streetNumber,
streetName,
streetType,
postDirection,
unit,
municipality,
}
}),
});

const responseData = await response.json();

// Extract data from the response
const matchedAddress = responseData.FULL_ADD || 'No match found';
const latitude = responseData.latitude || 'N/A';
const longitude = responseData.longitude || 'N/A';

// Add a new row to the results table
const row = resultsTable.insertRow();
row.insertCell(0).textContent = address;
row.insertCell(1).textContent = streetNumber;
row.insertCell(2).textContent = streetName;
row.insertCell(3).textContent = streetType;
row.insertCell(4).textContent = postDirection;
row.insertCell(5).textContent = unit;
row.insertCell(6).textContent = municipality;
row.insertCell(7).textContent = province;
row.insertCell(8).textContent = postalCode;
row.insertCell(9).textContent = matchedAddress;
row.insertCell(10).textContent = latitude;
row.insertCell(11).textContent = longitude;

} catch (error) {
console.error('Error geocoding address:', error);

// Add a row with an error message
const row = resultsTable.insertRow();
row.insertCell(0).textContent = address;
row.insertCell(1).colSpan = 11;
row.insertCell(1).textContent = 'Error processing this address';
row.classList.add('invalid');
}

currentIndex++;
updateProgress();
}
}

function downloadCSV() {
const table = document.getElementById("resultTable");
const rows = table.querySelectorAll("tr");
const csv = [];

// Include the header row from the table
const headers = table.querySelectorAll("th");
const headerRow = Array.from(headers).map(header => header.innerText.trim());
csv.push(headerRow.join(',')); // Adding header row to CSV

// Include each data row from the table
rows.forEach((row, index) => {
// Skip the header row, we already added it
if (index > 0) {
const data = Array.from(row.querySelectorAll("td"))
.map(cell => {
let cellValue = cell.innerText.trim();
return cellValue === "" ? "" : `"${cellValue}"`; // Wrap non-empty values in quotes, keeping empty values as they are
});
csv.push(data.join(',')); // Adding the data row to CSV
}
});

// Convert the array to a CSV string and trigger a download
const csvString = csv.join('\n');
const blob = new Blob([csvString], {
type: 'text/csv'
});
const link = document.createElement("a");
link.href = URL.createObjectURL(blob);
link.download = 'geocoded_addresses.csv';
link.click();
}



[/code]


 

Подробнее здесь: [url]https://stackoverflow.com/questions/79347640/javascript-failing-to-retrieve-json-field-data-from-html-and-execute-sql-query[/url]