Сегодня, SQL рулит. Если вы хотите быть большим игроком на рынке для написания клиентов баз данных, вы должны знать SQL.
Я не говорю о разработке SQL баз данных и их администрировании. Я говорю именно о написании клиентской программы для использования базы данных, то что вы пишите на Кларионе.
Компании в наши дни отвернулись от Клариона, потому что они считают, что этого языка недостаточно для разработки сверхмощных программ. Эти компании ошибаются, но они делают свои заключения на основании того, что видят. Некоторые Кларион-программисты используют несколько шаблонов и говорят заказчику, что приложение закончено. И заказчики остаются разочарованными.
Шаблоны Клариона не могут сделать браузы и отчеты, которые вы должны предоставить заказчику. Пользователи ожидают, что программа будет быстрой и легкой в использовании, а шаблоны создавшие браузы и отчеты не удовлетворяют этому требованию.
Что поможет вам написать хорошего SQL клиента так это изменение вашего мышления, эти изменения затрагивает следующие аспекты:
• Старый SET, NEXT, NEXT, NEXT метод не работает достаточно хорошо с SQL. Вначале вам надо подумать о полной результирующей выборке, все пользователи нуждаются в данных, удовлетворяющих определенным критериям. Вам нужно закончить с обработкой каждой записи основываясь на подходе строка-за-строкой, вам, во-первых, необходимо сконцентрироваться на получении всех данных за раз и быстро. SQL идеально для этого. К несчастью, шаблоны нет.
• Стандартный брауз начинается с начала таблицы. А пользователь выбирает каким-либо путем (поисковый локатор, например) откуда он реально хочет начать просмотр данных. Это в прошлом! Для больших таблиц необходимо дать пользователю начальную точку перед тем, чем даже вы подумаете о запросе данных из таблицы. Используйте локаторные поля, и используйте SQL LIKE-выражение для получения соответствующих записей. Тогда будут показаны записи, которые соответствуют запросу, а не «первая» информация.
• Дайте серверу выполнять некоторую работу за себя. Когда шаблоны создают браузы и отчеты вы очень ограничены в том, что сервер может сделать за вас. Допустим у вас есть отчет и вы используете традиционный Кларион-подход. У вас получится что то вроде:
прочитать таблицу Customer
для каждой записи из Customer прочитать таблицу Account
для каждой записи Account прочитать таблицу Transaction
Это ужасный способ создания отчетов для SQL систем. Вы совершаете в конечном итоге десятки тысяч окружных походов на сервер. Вашему отчету понадобится час для его подготовки к печати. Но если вы пойдете правильным путем, то вы возможно уменьшите количество «походов» к серверу до трех, и превратите часовой отчет в двухминутный.
Также, сервер может выполнить некоторые расчеты вроде SUM или COUNT, что сделает вашу программу быстрой и более аккуратной.
• Заканчивайте думать, что вы знаете больше о том в чем нуждаются пользователи, чем они сами. Это их дело и они должны знать в чем они нуждаются. Они дают вам деньги. Все что вам нужно сделать, это дать им то, что они хотят, соглашайтесь на их «тупые» идеи и придержите свою гордость. Подумайте об этом. В течение рабочего дня вы продаете свою гордость. Если немного отступить, вы получите больше денег.
• Учитесь использовать базу данных, которую вы получили. Если вы работаете только с БД, которую вы сами разработали, то вы никогда не постигните материю. Но если вы хотите двигаться вперед, вы должны уметь использовать БД, разработанную кем-то еще. Долой фразы типа: «Я утверждаю, что мои ДАТА и ВРЕМЯ будут LONG, потому что Кларион работает с ними лучше». Учитесь использовать поля дат и времени, которые есть в SQL. Я их тоже не люблю, но я люблю получать деньги.
• Обрабатывайте права доступа. Не пишите программу с предположением, что она будет иметь доступ ко всей БД. Если вы сделаете так, тогда вы создадите угрозу безопасности, так как если существует супер-логин-пользователя, просто позволяющий вашей программе войти, то это подключение может быть использовано другим приложением для разрушения бизнеса компании. Если вы просто заполните в шаблонах и словаре как обычно необходимые поля, вы создадите большую дыру в безопасности для ваших пользователей. Нет шаблонов безопасности или дополнительной утилиты, которая решает эту проблему. Знайте об этом!
Изменение подхода SET, NEXT,NEXT,NEXT на ПОЛУЧИТЬ-ВСЕ ЧТО-НУЖНО болезненно для Кларион-программистов. Но это необходимо.
Если вы остановитесь и подумаете о том, что такое программирование баз данных сегодня, вы почувствуете себя в замешательстве. Но все не совсем так страшно, если вы определите в чем именно проблема. Несмотря на множество методов доступа к БД (ODBC, OLE DB, ADO и т.д.), они все всё еще основаны на общем знаменателе SQL. Если вы можете работать с SQL разумно, быстро, давая вашим пользователям скорость и мощь, в которой они нуждаются, тогда вы просто сосредоточитесь на SQL и используйте средства, которые уже есть в Кларион.
Я подготовил пример Clarion Baseball For Windows (1198), использующий эту технику. Я думаю он поможет вам. Это ABC приложение (С55) и словарь и база данных MS Access. Если у вас установленный драйвер MS Access ODBC, приложение должно работать. Оно использует шаблон FORM, но не использует шаблон для брауза. Все браузы написаны вручную с использованием шаблона WINDOW.
Возможно не одна тема не вызывала столько дискуссий в Кларион-программировании как брауз. Для дескоптных файловых систем браузы достаточно сложны, но по крайней мере они быстрые. Потому что обработка таких файлов методом SET, NEXT, NEXT, NEXT очень эффективна.
А с SQL еще больше проблем. С другой стороны есть большие возможности.
Код сгенерированный шаблоном делает часть работы, когда создает SQL брауз. Он создает VIEW и QUEUE для поддержки страничных результатов. Но брауз остается «жестким». Например, нет способа, который сказал бы шаблону использовать SUM.
Браузы со страничной загрузкой привлекательны для нас потому что мы используем их и получаем результат простым заполнением нескольких полей. На SQL, однако, файловая загрузка браузов это достаточно частый и лучший способ получить проблемы. Также, в большинстве случаев, обновление брауза по возвращению из формы это трата ресурсов и времени.
В примере Clarion Baseball For Windows (1198) вы можете видеть другой подход, использующий ручное кодирование, который не такой распространенный. Для начала посмотрите на процедуру BrowseTeams. Обратите внимание на то, что объявлена очередь для получения результатов запроса и их просмотра в листе (TheTeamQueue).
Теперь посмотрите на рутину SendQuery. В ней находится код для отправки запроса на сервер и заполнения очереди. Также там есть условия для фильтрации запроса.
Кстати, я использую объект IDynStr. Это встроенный объект для работы со строками, определенный в DYNSTR.INC в подкаталоге Клариона LIBSRC. Использование этого объекта делает построение SQL запросов намного проще и мощнее.
В рутине SendQuery обратите внимание на использовании «sqlfile» файла. Это сгенерированный рабочий файл SQL-я, который определен в словаре. Ниже его описание:
sqlFile FILE,DRIVER('ODBC'),OWNER(glo:owner),|
NAME('Teams'),PRE(sqlfile),BINDABLE,THREAD
Record RECORD,PRE()
f1 CSTRING(256),NAME('TeamID')
f2 CSTRING(256),NAME('TeamID')
f3 CSTRING(256),NAME('TeamID')
f4 CSTRING(256),NAME('TeamID')
f5 CSTRING(256),NAME('TeamID')
f6 CSTRING(256),NAME('TeamID')
f7 CSTRING(256),NAME('TeamID')
f8 CSTRING(256),NAME('TeamID')
f9 CSTRING(256),NAME('TeamID')
f10 CSTRING(256),NAME('TeamID')
END
END
Посмотрите внимательно на эту структуру, особенно на атрибут NAME. Очевидно, что такая таблица не может существовать на сервере. Но это не важно. Важно то, что это может служит действительно настраиваемым механизмом для отправки SQL запросов и получения результатов. Вы можете использовать JOINs, WHERE, ORDER BY, SUM, COUNT и все основные SQL команды, которые вы можете послать. И это может получить результат, до 10 колонок, за исключением больших полей, таких как TEXT или IMAGE.
Когда вы откроете эту файловую структуру в Кларионе, Кларион легко определит, что это таблица названная «Teams» и что у нее есть поле «TeamID». Значит все готово.
Вы можете использовать эту структуру для запроса ЛЮБОЙ таблицы, к которой у вас есть доступ, посылать запросы, которые не возвращают результата, вызывать хранимые процедуры и вещи вроде CREATE TABLE.
Ниже простой пример для заполнения очереди с остатками для всех заказчиков:
BalanceQueue Queue
CustomerID Long
CustomerName String(60)
Balance Decimal(20,2)
End
code
free(BalanceQueue)
open(sqlfile)
sqlfile{prop:select} = 'select customer.customerid,' &|
' customer.customername,' &|
' sum(account.balance) ' &|
' from customer,account ' &|
' where account.customerid = customer.customerid' &|
' group by customer.customerid,' &|
' customer.customername' &|
' order by customer.customername'
if error()
stop(error() & ' ' & fileerror())
end
loop
next(sqlfile)
if error() then break.
BalanceQueue.CustomerID = sqlfile.f1
BalanceQueue.CustomerName = sqlfile.f2
BalanceQueue.Balance = sqlfile.f3
end
Обратите внимание, что существует зависимость между колонками, которые вы выбрали (в выражении SELECT) и полями в которые они будут возвращены. Первая колонка попадает в sqlfile.f1, вторая в sqlfile.f2 и т.д.
Теперь у вас есть результат запроса, вы можете обновлять, добавлять и удалять. Посмотрите как я сделал это в процедуре BrowseTeams.
Атрибут NAME
Для уменьшения кодирования (и ошибок кодирования) очередь может построена таким образом, как определено в части выражения SELECT. По мне это очень крутая вещь.
В демо-приложении посмотрите на код процедуры BrowsePlayers. Посмотрите каким образом определяется очередь PlayerQueue. Вот так:
PlayerQueue Queue
PlayerName Like(pla:playername),Name('players.playername')
TeamName Like(tea:teamname), Name('teams.teamname')
Playerid Like(pla:playerid), Name('players.playerid')
TeamID Like(tea:teamid), Name('teams.teamid')
End
Можете ли вы это сделать, используя атрибут NAME? Да, конечно!
Теперь у нас есть очередь, которая обрабатывает результаты, может быть показана в лист-боксе и принимать участие в создании некоторых SQL-запросов.
Code
QueryFromQueue(PlayerQueue, ' from teams,players ' &|
' where players.teamid = |
teams.teamid')
Одна строка кода! И нет никаких ссылок на файловые структуры! (Процедура QueryFromQueue находится в примере, можете посмотреть как она работает). Если вы измените структуру вашей очереди, обратите внимание что результирующий запрос SQL изменится автоматически.
Я не знаю как это вам, а я считаю, что это очень мощная вещь.
Замечание: Вы можете использовать выражения в атрибуте NAME вашей очереди. Я заметил только одно ограничение на содержание этого атрибута. Он не должен быть очень очень длинным. Можете получить GPF.
Поля даты и времени
Некоторые принимают действительно тупое решение при разработке SQL, когда изобретают тип Date-Time (дата-время). Две величины в одном поле? Супер, давайте создадим тип Имя-Фамилия и тип Город-Штат-Страна-Индекс! Тупая идея, да?
Но это есть и мы живем с этим.
В демо-примере вы можете видеть один простой способ для обработки колонок дата-время для очереди брауза. В процедуре редактирования UpdatePlayers, брауз «At bats». Первая колонка в браузе это дата. Так как очередь заполняется процедурой QueryFromQueue, были предприняты некоторые решения при создании структуры очереди.
Для обработки даты, определите вашу очередь следующим образом:
atbatqueue queue
Date string(10), name('atbats.date')
OppTeam like(tea:teamname), name('teams.teamname')
Result string(10), name('atbats.result')
AtBatID like(atb:atbatid), name('atbats.atbatid')
end
Поле даты в очереди определено как строка длиной 10 символов, потому что вы собираетесь получить величину в формате «yyyy-mm-dd». Также здесь может быть временнАя часть, но о ней несколько ниже.
После того как вы заполните очередь после выполнения запроса, сделайте следующее:
loop i=1 to records(atbatqueue) get(atbatqueue, i) atbatqueue.Date = deformat(atbatqueue, @d010-) atbatqueue.Result = AtBatResultToString(atbatqueue.Result) put(atbatqueue) end
Это преобразует SQL-формат даты в стандартную дату, которую поддерживает Кларион, и вы можете использовать любой формат вывода (picture) в браузе для этой колонки.
Не беспокойтесь много по поводу обработки полей дата-время в update-процедурах. Стандартная Кларион update процедура работает хорошо для совершения всех преобразований.
Если вам также необходимо ВРЕМЯ, то будет несколько другая строка кода:
MyQueue queue
MyDate string(20), name('mytable.date')
MyTime long, name('''''')
end
Обратите внимание на атрибут NAME в поле MyTime. Ваш запрос, который будет сформирован процедурой QueryFromQueue включит » в запрос SELECT. Сколько места занимает пустая строка? Я не знаю, но думаю, что не так много. Когда у вас есть поле в очереди, которое вы заполните позже сами, вы можете устанавливать атрибут NAME таким вот образом. Или вы можете установить его в значение по умолчанию. Я не думаю, что есть большая разница между этими способами, главное чтобы вы потом обработаете это.
Также, обратите внимание, что поле MyDate теперь является строкой из 20-ти символов. Это позволяет обрабатывать полную комбинацию Дата-Время.
Теперь для преобразования полей в очереди будьте бдительны, что вы вначале конвертируете время, потому что если вы начнете с даты, то уничтожите кусок времени. Сделайте примерно так:
loop i=1 to records(MyQueue) get(MyQueue, i) MyQueue.MyTime = deformat(MyQueue.MyDate[12 : 19], @t4) MyQueue.MyDate = deformat(MyQueue.MyDate[1 : 10], @d010-) put(MyQueue) end
Теперь вернемся к определению очереди atbatsqueue. У нее есть поле:
Result string(10), name('atbats.result')
В таблице atbats эта колонка имеет числовой тип. На деле, она должна содержать только значения 0, 1, 2, 3, 4. Так почему мы же мы не определили это поле как BYTE? Потому что мы хотим получить некоторое описание этих цифр, например «Out», «Single», «Double», «Triple», или «HomeRun».
Непосредственно после запроса результирующее поле содержит цифровое значение, которое находится в таблице. При конвертации даты также происходит конвертация этого поля в процедуре AtBatResultToString.
Теперь вернемся к полям дата-время. Не полагайтесь на такой запрос:
SELECT CUSTOMER WHERE LASTBILLED >= '2000-01-01' and
LASTBILLED 1
mytagstring = mytagstring & ', '
end
mytagstring = mystagstring & BrowseQueue.CustomerID
end
end
Теперь у вас есть список кодов заказчиков CustomerID, разделенный запятыми, который вы можете использовать в SQL. Вы можете использовать его в выражении WHERE в условии IN, примерно так:
'select customername from customer' &|
' where customerid in (' & mytagstring & ')'
Теперь вы получите ТОЛЬКО ТЕХ заказчиков, которые пометил пользователь, вы сделаете это одним запросом, и, по сути, таг-файл вам вообще не нужен.
Конечно эта техника требует, чтобы вы могли обработать список для выполнения пометки записей. В мою цель не входит рассказывать об этом. Потратьте время на изучение работы с очередями и List-контролами. Почитайте о ALERT, CHOICE и всех свойствах List-контролах, относящихся к обработке нажатия кнопок мыши.
Будьте бдительны к размеру строки, которую вы можете поместить в конструкцию IN. Например, для MySQL существует ограничение в 8 кБ.
Примечание
Скот (Scott Ferrett) прислал мне замечание, которое я должен сообщить и вам также. Я знал об этом, но по некоторым причинам я решил не применять его при использовании Prop:SQL.
Используйте BUFFER. Сегодня я сделал следующее с MS SQL файлом:
open (SQLFile) buffer (SQLFile,200) ! ... и так далее
То, что ранее занимало 5.5 секунд для загрузки 6000 записей, теперь занимает только 0.8 секунд. Эксперименты с более большим размером буфера уменьшают производительность. Возможно, это не работает со всеми SQL системами.
Раздача слонов
Техника, которую я описал ранее может быть скрыта в объекте. Я сделал это в форме INTERFACE-а.
Вы можете загрузить архив cw ODBC Setup (1167). Инструкции по установке находятся в файле cwodbc.inc. Также в архиве есть PRJ-файл, который иллюстрирует несколько приемов, которые вы можете использовать с объектом cwodbc.
Я планировал написать полную документацию к объекту, но у меня нет на это времени, а документирование использования INTERFACE-а, намного более трудная задача, чем можно представить, потому что интерфейсы могут быть использованы разными способами.
Помимо всего прочего, cwodbc-объект позволит вам сделать следующие вещи:
• послать запрос и обработать результат, основываясь на метода «строка за строкой»;
• получить значения колонок из запроса по позиции;
• получить значения колонок из запроса по имени;
• получить имена колонок, которые возвратил запрос;
• получить имена таблиц (и представлений) на сервере;
• получить колонки и их типы для таблицы;
• легко заполнить очереди результатами запросов. Существует даже один метод, который позволит структуре очереди участвовать в SQL-запросе;
• получить TEXT и IMAGE данные
cwodbc-объект в основном зависит от функциональности, которая обеспечивается драйверами, поставляемыми вместе с Кларионом. Объект был протестирован на ODBC и MS SQL драйверах, и он работал хорошо с обеими. Он должен работать со всеми SQL-драйверами, которые есть у Клариона, за исключением Oracle-а. С Oraclе-ом объект может работать через ODBC.
В дополнении к функциональности, обеспечиваемой Кларионом драйверами, cwodbc-объект делает несколько ODBC API вызовов для некоторых дополнительных возможностей, которые вы можете использовать (например: получение типа колонки).
Я просил совета у некоторых людей, и даже у своей жены, насчет того, продавать ли этот продукт или нет. Но я хочу, чтобы он был доступен всем Кларион-программистам, поэтому этот продукт бесплатен. Продукт представлен как open-source, код написан на чистом Кларионе (минимум необходима версия C5.5). Используя этот архив, вы сможете теперь получить доступ к SQL БД как это делается, например, через OLE DB или ADO, и вы сможете сделать все, что необходимо, не используя ничего кроме Клариона.
Есть два момента. Если вы решите распространять архив, то распространяйте оригинальный архив. Если вы сделали какое-то дополнение, дайте мне знать, не плодите разные ответвления от моего кода без моего разрешения. Также, если у вас есть какие-либо вопросы или предложения пишите в новостную конференцию.
Использование нескольких соединений к серверу
Когда вы используете Кларион ODBC-драйвер в своей программе, вы получаете одно соединение (к серверу) за раз. При использовании MSSQL-драйвера, вы можете соединиться к различным серверам в одно и тоже время. Возможно это возможно и с другими Кларион-драйверами, но я об этом не знаю.
Для того, чтобы установить и поддерживать два соединения в одно и тоже время, вы можете использовать объект cwodbc. Прием заключается написании небольшого кода, который использует гибкость INTERFACE-а (cwodbc-объект написан как INTERFACE).
Необходимо написать две небольших процедуры. Их прототипы:
map
NewServerAConnection(), *cwodbc
NewServerBConnection(), *cwodbc
end
Теперь код для каждой процедуры:
NewServerAConnection procedure
MyFile file, driver('mssql')
record ! используйте поля, которые вы хотите
! это для иллюстрации
end
end
conn &cwodbc
code
myfile{prop:owner} = 'ServerA,mydatabase'
conn &= newcwodbc()
conn.setfile(myfile)
conn.settableinfo('sometablename', 'somefieldname')
return conn
NewServerBConnection procedure
MyFile file, driver('mssql')
record
end
end
conn &cwodbc
code
myfile{prop:owner} = 'ServerB,mydatabase'
conn &= newcwodbc()
conn.setfile(myfile)
conn.settableinfo('sometablename', 'somefieldname')
return conn
Теперь, когда вы хотите сделать запрос к серверу A, вы создаете экземпляр объекта cwodbc следующим образом:
sql &= newServerAConnection() ! ваш код здесь sql.Release()
Когда вы хотите сделать запрос к серверу B, код похожий:
sql &= newServerBConnection() ! ваш код здесь sql.Release()
Хотя вы не можете поддерживать одновременно два соединения с ODBC-драйвером, эта техника поможет обойти это ограничение.
Далее, если вы хотите чтобы у вас был объект, который обрабатывает запросы, которые не возвращают TEXT и IMAGE полей (у других больших типов), и вы хотите, чтобы он был оптимизирован для скорости, но не хотите явно задавать имена полей. Вы можете написать две процедуры, примерно следующего содержания:
NewSpeedySQL procedure
MyFile file, driver('odbc')
record
cstring(256)
cstring(256)
cstring(256)
cstring(256)
cstring(256) ! add as many of these as
! you will need to handle
! your largest column count
! (Press Ctrl-2)
end
end
conn &cwodbc
code
myfile{prop:owner} = 'myconnectstring'
conn &= newcwodbc()
conn.setfile(myfile)
conn.settableinfo('sometablename', 'somefieldname')
return conn
NewBlobSQL procedure
MyFile file, driver('odbc')
record
end ! не используйте имена полей
! вы сможете обрабатывать "большие поля"
! и любое количество колонок
end
conn &cwodbc
code
myfile{prop:owner} = 'myconnectstring'
conn &= newcwodbc()
conn.setfile(myfile)
conn.settableinfo('sometablename', 'somefieldname')
return conn
Теперь, когда вам нужен «скоростной» cwodbc-объект, вы создаете объект так:
sql &= newSpeedySQL()
А когда вы хотите обрабатывать «большие» типы, так:
sql &= newBlobSQL()
Использование SEND
Иногда вам необходимо возвратить все записи таблицы, удовлетворяющие некоторому условию, и вам нужны все поля таблицы.
Обычные команды Клариона set/next могут это сделать для вас в таком случае. Допустим, вам необходимо получить все операции для клиента с номером 234. Это очень легко сделать:
myQueue queue(tra:record) ! эта очередь полностью
! дублирует структуру файла
! file Transactions
end
code
free(myQueue)
open(Transactions)
buffer(Transactions, 200) ! намного увеличивает скорость
set(Transactions)
send(Transactions, '/where customerid = ' & 234)
loop
next(transactions)
if error() then break.
myQueue :=: tra:record
add(myQueue)
end
Как видите это очень просто. Обратите внимание на команду SEND. Она устанавливает условие WHERE для получения данных. Она должна быть использована после SET и перед NEXT. Команда SET формирует следующую строку:
... WHERE (customerid = 234)
Этот метод предпочтительнее использования Prop:SQL (запрос типа SELECT * FROM Transactions), потому что он гарантирует, что вы пошлете запрос, который возвратит вам результат в файловый буфер корректно, и поля DATE-TIME будут уже конвертированы, в том случае если вы определили их правильно в файловой структуре.
При использовании SET(TRA:MyKey), в вашем запросе будет использовано выражение ORDER BY, но это не так необходимо для использования с SET/NEXT. Вы можете использовать небольшой трюк, потому что Кларион оформляет выражение WHERE в скобки. Если по каким-либо причинам вам необходимо использовать ORDER BY в вашем запросе, вы можете написать следующее:
SEND(Transactions, '/where customerid = ' & 234 &|
') order by (transactiondate')
Если необходимо использовать несколько полей сортировки, то можно написать так:
SEND(Transactions, '/where customerid = ' & 234 &|
') order by (transactiondate), (status')
Не полагайтесь на это слишком сильно. Вы можете использовать этот прием только, когда вам необходимо получить все колонки. Возврат всех колонок, когда на самом деле вам нужны только несколько, значительно замедлит работу.