:: Visual Foxpro, Foxpro for DOS
Как запросить данные в одном SQL-запросе
shumik73
Автор

Сообщений: 554
Откуда: Алматы
Дата регистрации: 10.05.2006
Добрый день всем в новом году!

Предлагаю обсудить простую тему - думаю будет интересно ее реализовать:
Есть много таблиц, к примеру
CREATE TABLE_MAIN (ID int, NAME char(100))
CREATE TABLE_1 (ID int, PARENT_ID int, NAME_TIP char(30))
CREATE TABLE_2 (ID int, PARENT_ID int, NAME_KAT char(50), SUM int)
CREATE TABLE_3 (ID int, PARENT_ID int, NAME_STATUS char(50), FLAG_ENABLED bit)
Где есть одна главная "TABLE_MAIN", а остальные дочерние связанные по ключу "PARENT_ID"

Чтобы получить данные необходимо отобрать данные по фильтру:
SELECT * FROM TABLE_MAIN WHERE ID=xxxx
SELECT * FROM TABLE_1 WHERE PARENT_ID=xxxx
SELECT * FROM TABLE_2 WHERE PARENT_ID=xxxx
SELECT * FROM TABLE_3 WHERE PARENT_ID=xxxx
и передать с SQL-сервера на клиентские компы.
Да создать десяток запросов не сложно, но хотелось обойтись одним SQL-запросом

Как возможно реализовать следующую конструкцию

    1) Сформировать запрос с одним хххх, чтобы собрать все данные в одном пакете данных
    2) Как передать этот пакет в клиентскую программу (XML, курсор)
    3) Как из полученного пакета выделить только те данные которые касаются одной таблицы, на пример "TABLE_1". Возможно это будет кусок XML-кода или курсор тока с данными из TABLE_1

Ratings: 0 negative/0 positive
Re: Как запросить данные в одном SQL-запросе
alex;

Сообщений: 3402
Откуда: Москва
Дата регистрации: 23.11.2004
Добрый день.
TEXT TO m.lcSQL TEXTMERGE NOSHOW
SELECT * FROM TABLE_MAIN WHERE ID=<<m.ID>>
SELECT * FROM TABLE_1 WHERE PARENT_ID=<<m.ID>>
SELECT * FROM TABLE_2 WHERE PARENT_ID=<<m.ID>>
SELECT * FROM TABLE_3 WHERE PARENT_ID=<<m.ID>>
ENDTEXT
SQLEXEC(m.lnCnn, m.lcSQL)

Всё остальное от лукавого.



Исправлено 3 раз(а). Последнее : alex;, 06.01.25 12:56
Ratings: 0 negative/0 positive
Re: Как запросить данные в одном SQL-запросе
krin

Сообщений: 190
Дата регистрации: 08.02.2005
задаёшь несколько запросов через ";" в одном SQLEXEC(), получаешь несколько курсоров на выходе.
в переводе хелп "В случае, когда выгружается несколько наборов данных (несколько курсоров), имя каждого нового курсора определяется по правилу: к "основному" имени курсора (заданному) добавляется порядковый номер: sqlresult2, sqlresult3... ."
на самом деле "For multiple result sets, new cursor names are derived by appending an incremented number to the name of the first cursor." - то есть sqlresult1, sqlresult2...

подробные примеры здесь в "что нового в девятке", стр.228
foxclub.ru
Ratings: 0 negative/0 positive
Re: Как запросить данные в одном SQL-запросе
shumik73
Автор

Сообщений: 554
Откуда: Алматы
Дата регистрации: 10.05.2006
Команда выполняется в пакетном режиме и возвращает несколько наборов результатов,
VFP автоматически создает имя курсора для набора результатов 2 и выше, добавляя «1», «2» и т. д. к имени первого курсора.
С помощью laCount вы можете получить список имен курсоров, не угадывая их.
TEXT TO lcCmd NOSHOW
UPDATE customer SET min_order_amt = min_order_amt + .01 WHERE min_order_amt <=100;
SELECT * FROM customer WHERE country = 'UK'
ENDTEXT
lnRes = SQLEXEC(lnConn, lcCmd, "tmpCust", laCount)
DISPLAY MEMORY LIKE laCount

Интересно. А выполнить в одном запросе сценарий
1) А посчитать следующий номер ID
2) Добавить строчку с новым ID
3) Записать данные в строку с новым ID
Получится?

А если ошибка будет ROLL BACK?
Ratings: 0 negative/0 positive
Re: Как запросить данные в одном SQL-запросе
MikhsR

Сообщений: 14
Откуда: Москва
Дата регистрации: 19.12.2022
1) Сформировать запрос с одним хххх, чтобы собрать все данные в одном пакете данных
2) Как передать этот пакет в клиентскую программу (XML, курсор)
3) Как из полученного пакета выделить только те данные которые касаются одной таблицы, на пример "TABLE_1". Возможно это будет кусок XML-кода или курсор тока с данными из TABLE_1



1)
Select a.*, b.NAME_TIP, c.NAME_KAT,c.SUM, d.NAME_STATUS, d.FLAG_ENABLED
FROM TABLE_MAIN a
Left Join TABLE_1 b On a.id=b.PARENT_ID
Left Join TABLE_2 c On a.id=c.PARENT_ID
Left Join TABLE_3 d On a.id=d.PARENT_ID
WHERE a.ID=xxxx

2) SQLEXEC() Третий параметр - имя курсора

3) по названию полей.



Исправлено 2 раз(а). Последнее : MikhsR, 07.01.25 17:03
Ratings: 0 negative/0 positive
Re: Как запросить данные в одном SQL-запросе
MikhsR

Сообщений: 14
Откуда: Москва
Дата регистрации: 19.12.2022
shumik73
Интересно. А выполнить в одном запросе сценарий
1) А посчитать следующий номер ID
А этим должен заниматься SQL сервер.
Ratings: 0 negative/0 positive
Re: Как запросить данные в одном SQL-запросе
leonid

Сообщений: 3227
Откуда: Рига
Дата регистрации: 03.02.2006
MikhsR
1)
Select a.*, b.NAME_TIP, c.NAME_KAT,c.SUM, d.NAME_STATUS, d.FLAG_ENABLED
FROM TABLE_MAIN a
Left Join TABLE_1 b On a.id=b.PARENT_ID
Left Join TABLE_2 c On a.id=c.PARENT_ID
Left Join TABLE_3 d On a.id=d.PARENT_ID
WHERE a.ID=xxxx

Плохой вариант. Надеюсь, не надо объяснять, почему.
Ratings: 0 negative/1 positive
Re: Как запросить данные в одном SQL-запросе
shumik73
Автор

Сообщений: 554
Откуда: Алматы
Дата регистрации: 10.05.2006
Для выполнения одного простого запроса выполняется всем привычный код.
При выполнение этой одной SQL-команды открывается транзакция, выполняется команда, и в случае удачного возвращается 1, неудачного -1, транзакция закрывается.

gnHandle = SQLCONNECT('.....') или gnHandle = SQLSTRINGCONNECT('.....')
IF gnHandle > 0
&& Процессинг транзакций управляется автоматически
= SQLSETPROP(gnHandle, 'Transactions', 1)
= SQLEXEC(gnHandle, "<SQL-команда>")
= SQLDISCONNECT(gnHandle)
ELSE
=MESSAGEBOX("Нет коннекта")
ENDIF


Но если требуется выполнение списка команд, и при это нужно чтобы либо они выполнились все или отменились и вернулись как было.
То есть будут вызываться несколько команд SQLEXEC() и ошибка может возникнуть в одном из них, и при этом нужно отменить все.
Вот тут сталкиваемся с транзакциями

gnHandle = SQLCONNECT('.....') или gnHandle = SQLSTRINGCONNECT('.....')
IF gnHandle > 0
&& Процессинг транзакций управляется вручную
= SQLSETPROP(gnHandle, 'Transactions', 2)
= SQLEXEC(gnHandle, "<SQL-команда 1>")
= SQLEXEC(gnHandle, "<SQL-команда 2>")
= SQLEXEC(gnHandle, "<SQL-команда 3>")
Если все команды прошли без ошибок - фиксируем транзакцию.
= SQLCOMMIT(gnHandle)
Если есть хоть одна ошибка - откатываемся на исходную
= SQLROLLBACK(gnHandle)
&& Процессинг транзакций управляется автоматически
= SQLSETPROP(gnHandle, 'Transactions', 1)
ELSE
=MESSAGEBOX("Нет коннекта")
ENDIF

Получается что команда SQLEXEC(gnHandle, "<SQL-команда 1>;<SQL-команда 2>;<SQL-команда 3>;")
реализует тот же самый код, тока более изящно

Осталось только понять как можно результаты вычислений <SQL-команда 1> можно вычислить и передать для вычисления последующих команд <SQL-команда 2> ?



Исправлено 1 раз(а). Последнее : shumik73, 08.01.25 10:28
Ratings: 0 negative/0 positive
Re: Как запросить данные в одном SQL-запросе
AndyNigmatec

Сообщений: 1675
Откуда: Волгоград
Дата регистрации: 28.06.2015
На это в самом начале ответили - делать это на сервере - т.е. соорудить более сложный запрос, типа
select ... from (select ... from) или используя join-ы ежели логика и уникальность данных это позволяет
Ratings: 0 negative/0 positive
Re: Как запросить данные в одном SQL-запросе
PaulWist

Сообщений: 14740
Дата регистрации: 01.04.2004
Вопрос, конечно про "сферического коня"

Для тестового скрипта (c кучей ограничений)

SELECT 'TABLE_MAIN' as TableName, * FROM TABLE_MAIN WHERE ID=xxxx
union all
SELECT 'TABLE_1' as TableName,* FROM TABLE_1 WHERE PARENT_ID=xxxx
union all
SELECT 'TABLE_2' as TableName,* FROM TABLE_2 WHERE PARENT_ID=xxxx
union all
SELECT 'TABLE_3' as TableName,* FROM TABLE_3 WHERE PARENT_ID=xxxx


------------------
Есть многое на свете, друг Горацио...
Что и не снилось нашим мудрецам.
(В.Шекспир Гамлет)




Исправлено 2 раз(а). Последнее : PaulWist, 09.01.25 09:13
Ratings: 0 negative/0 positive
Re: Как запросить данные в одном SQL-запросе
lemenev

Сообщений: 229
Дата регистрации: 23.06.2022
Предложенный PaulWist вариант по идее правильный, но работать не будет. Объединять с помощью UNION можно только таблицы/курсоры одинаковой структуры. Значит, в запросе нужно приводить исходные таблицы/курсоры к единой структуре путём добавления пустых полей с нужными именами и нужной шириной. Впрочем, он оговорился – «c кучей ограничений».

Такая проблема - объединение данных из таблиц с разными структурами - уже разбиралась: forum.foxclub.ru

Такая задача часто возникает при подготовке отчётов, когда хочется в один отчёт объединить данные из совершенно разных и не связанных друг с другом таблиц. Например, есть 20 счетов поставки, 30 накладных и 40 платежей. Общее у них только то, что все они относятся к одному контрагенту.



Исправлено 1 раз(а). Последнее : lemenev, 09.01.25 13:43
Ratings: 0 negative/0 positive
Re: Как запросить данные в одном SQL-запросе
PaulWist

Сообщений: 14740
Дата регистрации: 01.04.2004
shumik73
Осталось только понять как можно результаты вычислений <SQL-команда 1> можно вычислить и передать для вычисления последующих команд <SQL-команда 2> ?

Используйте ХП/ф-ии в зависимости от типа СУБД, тогда будет достаточно одного вызова ХП/ф-ии, которые сделают 100500 select/update/delete.


------------------
Есть многое на свете, друг Горацио...
Что и не снилось нашим мудрецам.
(В.Шекспир Гамлет)
Ratings: 0 negative/1 positive
Re: Как запросить данные в одном SQL-запросе
of63

Сообщений: 26001
Откуда: Н.Новгород
Дата регистрации: 13.02.2008
Павел, использовать ХП (конкретной БД), это насколько полезно при смене БД ? Вопрос нативный, без подвоха
Ratings: 0 negative/0 positive
Re: Как запросить данные в одном SQL-запросе
AndyNigmatec

Сообщений: 1675
Откуда: Волгоград
Дата регистрации: 28.06.2015
думается мне что смена типа БД в любом случае геморой, и возможно ХП даже проще будет переписать
Ratings: 0 negative/0 positive
Re: Как запросить данные в одном SQL-запросе
of63

Сообщений: 26001
Откуда: Н.Новгород
Дата регистрации: 13.02.2008
() Не слусфй нас, зассанцеев менять БД, и наоборотистов, которые "давайте все менять", дело не в БД-шке *спасибо иностранцам, поучили ). Епаться прижется в своем, или завоеваьть мир силой.
Ratings: 0 negative/0 positive
Re: Как запросить данные в одном SQL-запросе
PaulWist

Сообщений: 14740
Дата регистрации: 01.04.2004
of63
Павел, использовать ХП (конкретной БД), это насколько полезно при смене БД ? Вопрос нативный, без подвоха

1. Если "дергать" ХП взрослой СУБД, то в клиенте (фоксе) теоретически переделывать ничего не придётся (за исключением самих вызовов а-ля exec/call/select что там допускает синтаксис конкретной СУБД), бонусом получаем уже откомпилированные планы запросов, те не тратятся ресурсы сервера на компиляцию одного и того же запроса (ну там есть нюансы, как это можно обойти).

2. Практически всякие 1С, Навижены, Инфоры итп СУБД используют в качестве табличек для данных, использование ХП я в них не видел. НО как только записей хоть в одной табличке становится +-1 млрд шт, то все такие системы "ложатся", поскольку не умеют работать с секционированными табличками и начинаются танцы с бубном, а давайте "свернём БД" (удалим, что бы осталоь 1 млн/10 тыс записей), а давайте перельём данные в DWH, а давайте горизонтально масштабируем (докупим ещё 10-100 серверов)


------------------
Есть многое на свете, друг Горацио...
Что и не снилось нашим мудрецам.
(В.Шекспир Гамлет)
Ratings: 0 negative/0 positive
Re: Как запросить данные в одном SQL-запросе
shumik73
Автор

Сообщений: 554
Откуда: Алматы
Дата регистрации: 10.05.2006
Цитата:
Правильно поставленный вопрос - это половина ответа

Вопрос в том, что я уже давно ушел от визульного формирования форм.
Для этого я создал один журнал "LIST_FORM" и кучу связанных с ним таблиц "FILL_DOC", "FILL_TABLE",""FILL_REPORT" и тд.
Когда мне нужно запустить одну форму ХХХ, нужно из главной таблицы запросить 1 строку из "SELECT * FROM LIST_FORM WHERE ID=XXX" c параметрами формы и другие связанные с этой формой записи из дочерних таблиц "SELECT * FROM FILL_DOC WHERE PARENT_ID=XXX" и тд.

То есть для формирования одной формы нужно много маленьких и часто пустых таблиц.
Выполнить такую SQL-атаку было актуально когда канал сервер-клиент широкий. Но с переходом от меди витой пары на Wi-Fi в локальной сети компании канал стал уже. И для запуска формы бывает требуется 1-6 секунд. Поэтому чтобы клиент получил быстрее и сразу все таблицы нужно сбор данных выполнить одним запросом, а уж потом клиент сформирует форму быстро.

Таблицы разные и UNION ALL тут не нужен. А если склеивать LEFT OUTER JOIN то получится один трёхэтажный SQL-запрос.
Я вообще думал что получится собрать данные можно только если SQL-сервер вернет эти данные как XML-текст. А потом разбирать XML на части чтобы построить тот или иной элемент формы. Но как оказалось что SQLEXEC() может запросить все это в одной транзакции и это отлично.

То есть можно запускать не просто "SELECT * FROM YYYY" для получения одной таблицы, а создавать целый сложный текст TEXT ... ENDTEXT в котором можно записать не только шапку накладной, но и всю спецификацию накладной и еще кучу чего. И если возникнет ошибка - то откатиться как будто ничего не было и преступить к отладке. То есть отладка уже не зависит от моего кода а зависит от самой SQLEXEC()

Но одно дело выполнить группу не связанных SQL-команд. Другое когда когда нужно вычислить 1 строка, а полученные данные подставить во 2 строку. Ориентировочно так:

TEXT TO lcSQL TEXTMERGE NOSHOW
SELECT MAX(ID) FROM Table_1 TO nID_Old_1
SELECT MAX(ID) FROM Table_2 TO nID_Old_2
INSERT INTO [Table_1] (ID,NAME) VALUES (nID_Old_1+1,"Текст")
INSERT INTO [Table_2] (ID, PARENT_ID, SPS, KAT) VALUES (nID_Old_2+1, nID_Old_1+1, "Текст", "Text")
INSERT INTO [Table_2] (ID, PARENT_ID, SPS, KAT) VALUES (nID_Old_2+1, nID_Old_1+1, "Текст", "Text")
ENDTEXT
=SQLEXEC(lcSQL)

Если не получится то придется сработать в 2 запрос: первый вычисляет, второй подставляет
TEXT TO lcSQL_1 TEXTMERGE NOSHOW
SELECT MAX(ID) FROM Table_1 TO nID_Old_1
SELECT MAX(ID) FROM Table_2 TO nID_Old_2
ENDTEXT
=SQLEXEC(lcSQL_1)
TEXT TO lcSQL_2 TEXTMERGE NOSHOW
INSERT INTO [Table_1] (ID,NAME) VALUES (<<nID_Old_1>>+1,"Текст")
INSERT INTO [Table_2] (ID, PARENT_ID, SPS, KAT) VALUES (<<nID_Old_2>>+1, <<nID_Old_1>>+1, "Текст", "Text")
INSERT INTO [Table_2] (ID, PARENT_ID, SPS, KAT) VALUES (<<nID_Old_2>>+1, <<nID_Old_1>>+1, "Текст", "Text")
ENDTEXT
=SQLEXEC(lcSQL_1)



Исправлено 2 раз(а). Последнее : shumik73, 10.01.25 12:03
Ratings: 0 negative/0 positive
Re: Как запросить данные в одном SQL-запросе
PaulWist

Сообщений: 14740
Дата регистрации: 01.04.2004
shumik73
И для запуска формы бывает требуется 1-6 секунд. Поэтому чтобы клиент получил быстрее и сразу все таблицы нужно сбор данных выполнить одним запросом, а уж потом клиент сформирует форму быстро.

Вопросы:

1. DDL табличек + Планы запросов для формирования "формы" в студию.

2. В итоге, сколько байт/Мбайт передаётся по WiFi для формирования формы/заполнения данными??


------------------
Есть многое на свете, друг Горацио...
Что и не снилось нашим мудрецам.
(В.Шекспир Гамлет)
Ratings: 0 negative/0 positive
Re: Как запросить данные в одном SQL-запросе
shumik73
Автор

Сообщений: 554
Откуда: Алматы
Дата регистрации: 10.05.2006
PaulWist
1. DDL табличек + Планы запросов для формирования "формы" в студию.
Список форм 2200. Чтобы собрать одну из форм запрашиваются данные с 25 таблиц. Если перенести все эти данные то получится XML-файл размером от 40 до 600 кБт. Это только данные для формирования формы.

А сами рабочие коммерчески данные это отдельно. Чтобы минимизировать рабочие данные приходится - прописывать поля, а не как SELECT * FROM YYY и накладывать фильтр по времени (отображать только текущий год) или отображать строки созданные только этим пользователем.

При копирование 1000 файлов размером с 1Кбт каждый, копируются намного дольше чем 1 файл размером с 1 МБт.
Так думаю и 1 SQL-запрос к 25 таблицам думаю пройдет быстрее 25 SQL-запросов по 1 таблице.

У пользователей в локальной сети на проводах проблем нет. А вот тот кто сидит на wi-fi у того тормозит в зависимости от его компа, удаленности от роутера или репитера. Но терпимо - не жалуются. Пользователи кто сидит по VPN в другом офисе тем остается по RDP подключаться. Есть кто со своим домашним ноутбуком уедит на камчатку и от туда бухтит на зависания и старый интерфейс и какие хорошие сайты . Так что борьба за ресурс (скорость) продолжается

Был бы индикатор который показывал скорость не интернета, а от клиента до сервер в локалке (ping не предолагать) чтобы в программе на заставке каждому пользователю показывать

И вообще в планах базу перетащить из офиса на хостинг или разделить на 2 базы и там и там.
Для этого обычный виртуальный хостинг не подойдет, так как доступ с офиса к базе на хостинге блокируют.
Требуется VPS-хостинг или статичный IP-адрес.
Знаю что будут проблемы с файлами - зато прикрутить web-сайты как здрасьте



Исправлено 2 раз(а). Последнее : shumik73, 10.01.25 12:10
Ratings: 0 negative/0 positive
Re: Как запросить данные в одном SQL-запросе
PaulWist

Сообщений: 14740
Дата регистрации: 01.04.2004
Так, надо определиться.

1. Как сейчас происходит доступ к данным - это файл-сервер, клиент-сервер (тогда какая СУБД)?

shumik73
А сами рабочие коммерчески данные это отдельно. Чтобы минимизировать рабочие данные приходится - прописывать поля, а не как SELECT * FROM YYY и накладывать фильтр по времени (отображать только текущий год) или отображать строки созданные только этим пользователем.

2. Ммм, тормоза возникают когда? когда строится форма или когда она заполняется данными?

shumik73
Так думаю и 1 SQL-запрос к 25 таблицам думаю пройдет быстрее 25 SQL-запросов по 1 таблице.

3. Не факт, зависит от многих вещей.

shumik73
У пользователей в локальной сети на проводах проблем нет. А вот тот кто сидит на wi-fi у того тормозит в зависимости от его компа, удаленности от роутера или репитера. Но терпимо - не жалуются. Пользователи кто сидит по VPN в другом офисе тем остается по RDP подключаться. Есть кто со своим домашним ноутбуком уедит на камчатку и от туда бухтит на зависания и старый интерфейс и какие хорошие сайты . Так что борьба за ресурс (скорость) продолжается


Дык, за 6 сек сколько данных скачивается 1М и какова скорость на роутере WiFi ??

4. Похоже всё таки файл-сервер

Тогда поможет только Rushmore


------------------
Есть многое на свете, друг Горацио...
Что и не снилось нашим мудрецам.
(В.Шекспир Гамлет)
Ratings: 0 negative/0 positive


Извините, только зарегистрированные пользователи могут оставлять сообщения в этом форуме.

On-line: 25 (Гостей: 25)

© 2000-2025 Fox Club 
Яндекс.Метрика