VB и Базы данных

[Наглец]

Что имеется:
А имеется у нас - база данных, дорогостоящий заказ и огромное желание его выполнить))

База:
http://www.sharemania.ru/files/manage?f ... DZtScAkCJV
(Сжатый вариант базы, в оригинале она весит ~300мб и содержит огромную, неразгребаемую кучу записей)

Заказ:
Нужно написать программу которая будет работать с этой базой данных, в базе хранится информация о входящем и исходящем трафике пользователей данной сети. Суть программы - это группировка этого трафика по пользователям, т.е.:

Январь
Иванов - 174мб
Пертров - 230мб
Сидоров - 2.1гб (Ага, попался )

Имена пользователей находитсяв столбце USERNAME, количество исходящего от пользователя трафика в столбце BYTESIN, входящего в столбце BYTESOUT.
С базами данных я работаю первый раз, по этому сейчас буду задавать глупые вопросы

Как мне реализовать в программе отчет сгруппированый по пользователям? Писать свою функцию группировки или воспользоваться уже какой-либо готовой функцией VB или SQL? Если готовой то какой?

[alibek]

Какой отчет собираешься использовать?

[Наглец]

DataReport

[alibek]

Если нужен только сводный отчет, то в этом случае я бы советовал делать группировку при запросе (указав group by user_id, user_name), а в отчет выводить полученные сгруппированные результаты.

[Наглец]

Можно поподробней пожалуйста?

[iGrok]

Можно поподробней пожалуйста?

Допустим, у тебя в БД строки:

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

username, bytesin
----------------------
mark, 100
andrew, 20
mark, 16
andrew, 55
andrew, 11


Тогда запрос вида SELECT USERNAME, SUM(BYTESIN) AS SUM, COUNT(BYTESIN) AS COUNT FROM [table] GROUP BY USERNAME даст результат:

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

USERNAME, SUM, COUNT
----------------------
mark, 116, 2
andrew, 86, 3


FIX:
Поправил пост, по замечанию Тёмыча.

[tyomitch]

s/COUNT/SUM/

[iGrok]

s/COUNT/SUM/

Тьфу. Точно. Спасибо.

Чё ж меня так глючит-то..

То, что я писал даст SUM.
А COUNT выдаст кол-во строк..

[Наглец]

Спасибо!
Еще один вопрос: как добавить переменную в SQL запрос?
При таком методе выдает ошибку: "Отсутствует значение для одного или нескольких параметров"

Dim StrTime As Integer
StrTime = 3454579442
Set rs = cn.Execute("Select * from HISTORY Where [STARTTIME] > StrTime")

[Наглец]

Сам додумался
Пусть останется, вдруг кому-то пригодится.

Dim StrTime As Integer
StrTime = "3482964533"
sql = "Select * from HISTORY Where [STARTTIME] > " & StrTime
Set rs = cn.Execute(sql)

[dr.MIG]

Но обычно для избежания потенциально возможных неприятностей всё-таки лучше использовать параметрический запрос, нежели сборку строки запроса из фрагментов.

[Наглец]

При таком запросе:
Set rs = cn.Execute("SELECT USERNAME, SUM(BYTESOUT) AS SUM, COUNT(BYTESOUT) AS COUNT FROM [HISTORY] GROUP BY USERNAME")

Программа ругается на "Ошибочное или пропущенное зарезервированное слово или аргумент в инструкции SELECT или неверная пунктуация"

[alibek]

Попробуй выбрать альяс, который не совпадает с зарезервированным словом. USUM и UCNT например.
И кстати, COUNT(*) наверное было бы правильнее.

[Наглец]

Большущее спасибо Работает

[Наглец]

Возникла новая проблемка:

Private Declare Function OemToCharBuff Lib "user32.dll" Alias "OemToCharBuffA" (ByVal lpszSrc As String, ByVal lpszDst As_ String, ByVal cchDstLength As Long) As Long

Private Sub Command3_Click()
Dim a As String
Dim Name As String
a = "абвгдеёжзийклмнопрстуфхцчшщъыьэюяАБВГДЕЁЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ"

Call ConnectDB
Set rs = cn.Execute("SELECT USERNAME, SUM(BYTESOUT) AS USUM, COUNT(BYTESOUT) AS UCNT FROM [HISTORY] GROUP BY USERNAME")
While Not rs.EOF
Name = OemToCharBuff(a, rs.Fields("USERNAME"), Len(rs.Fields("USERNAME")))
List1.AddItem Name & vbTab & rs.Fields("USUM")
rs.MoveNext
Wend
cn.Close
End Sub

При таком коде ругается при коневертации из Dos кодировки в Win, а именно на строчке
"Name = OemToCharBuff(a, rs.Fields("USERNAME"), Len(rs.Fields("USERNAME")))" - "Неправильное использование Null"

[iGrok]

Проверяй строку в БД на NULL
Is Not rs.Fields(..) is Null Then ...

[Наглец]

При таком способе:
If Not rs.Fields("USERNAME") Is Null Then
Name = OemToCharBuff(a, rs.Fields("USERNAME"), Len(rs.Fields("USERNAME")))
End If
Пишет: "Необходим обьект"

При таком способе:
If rs.Fields("USERNAME") <> 0 Then
После обработки этой строчки:
Name = OemToCharBuff(a, rs.Fields("USERNAME"), Len(rs.Fields("USERNAME")))
Переменная Name = "1"

[iGrok]

Блин. Сорри. Давно с БД не работал.
If Not IsNull(rs.Fields(...)) Then

[Andrey Fedorov]

Попробуй выбрать альяс, который не совпадает с зарезервированным словом. USUM и UCNT например.

Ну, можно просто писать поле в квадратных скобках, например так: SUM(BYTESOUT) AS [SUM]

[Andrey Fedorov]

Более правильный вариант:

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

Public Declare Function "OemToCharA" (ByVal lpszSrc As String, ByVal lpszDst As String) As Long
....
Dim rs As New ADODB.Recordset, s As string, ss As String
....
Set rs As New ADODB.Recordset
rs.Open"SELECT USERNAME, SUM(BYTESOUT) AS [SUM], COUNT(BYTESOUT) AS UCNT FROM [HISTORY] GROUP BY USERNAME", cn, adOpenStatic, adLockReadOnly
DO Until rs.EOF
    If Not IsNull(rs(0)) Then
        s = rs!USERNAME: ss = s
        OemToCharA  s, ss
        List1.AddItem ss & vbTab & rs(1)
        rs.MoveNext
    End If
Loop
...

А вообще для DBF-ки достаточно правильно установить 29-ый байтик и все данные в ней сразу будут в нужной кодировке...

[Наглец]

Спасибо! Но ничего не работает
Name все еще возвращает единицы, а в "Более правильном варианте" куча ошибок.

[Andrey Fedorov]

Спасибо! Но ничего не работает
Name все еще возвращает единицы, а в "Более правильном варианте" куча ошибок.

Пришлось таки смотреть твою DBF-ку...

Значит так - идешь и скачиваешь провайдер по ссылке: http://www.microsoft.com/downloads/deta ... layLang=en
После чего устанавливаешь его. Ну а далее все примитивно:

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

Public Sub Test()
    Dim cn As New ADODB.Connection, r As New ADODB.Recordset
    Dim s As String, ss As String
   
    Debug.Print
    Debug.Print
   
    cn.CursorLocation = adUseClient
    cn.Open "Provider=VFPOLEDB.1;Data Source=""" & CurrentProject.Path & """;Password="""";Collating Sequence=RUSSIAN"

    r.Open "SELECT * FROM History", cn, adOpenStatic, adLockReadOnly
    Do Until r.EOF
        Debug.Print r!id, r!compname, r!username
        r.MoveNext
    Loop
End Sub


[Andrey Fedorov]

Спасибо! Но ничего не работает
Name все еще возвращает единицы, а в "Более правильном варианте" куча ошибок.

Пришлось таки смотреть твою DBF-ку...

Значит так - идешь и скачиваешь провайдер по ссылке: http://www.microsoft.com/downloads/deta ... layLang=en
После чего устанавливаешь его. Ну а далее все примитивно:

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

Public Sub Test()
    Dim cn As New ADODB.Connection, r As New ADODB.Recordset
    Dim s As String, ss As String
   
    Debug.Print
    Debug.Print
   
    cn.CursorLocation = adUseClient
    cn.Open "Provider=VFPOLEDB.1;Data Source=""" & CurrentProject.Path & """;Password="""";Collating Sequence=RUSSIAN"

    r.Open "SELECT * FROM History", cn, adOpenStatic, adLockReadOnly
    Do Until r.EOF
        Debug.Print r!id, r!compname, r!username
        r.MoveNext
    Loop
End Sub


P.S Если заказ серъезный, то тебе его не потянуть...

[Наглец]

Я с ним уже заканчиваю, остался последний штрих - кодировка из доса в вин и дизайн. Могу выложить проект с открытым кодом, может кто-то что-то подскажет по поводу оптимизации.

Скачать файл ITC.rar

[Andrey Fedorov]

Я с ним уже заканчиваю,

Ну тогда у нас просто разное понятие о дорогостоящих проектах...

остался последний штрих - кодировка из доса в вин...

Вообще-то у тебя таблица не в OEM кодировке, а самой настоящей ANSII (1251).
Табличка создана в Visual FoxPro и чтобы с ней нормально работать нужно всего лишь установить провайдер об котором я писал выше. Больше ничего ине нужно...

И для начала - вставь в начало своеего кода Option Explicit. И отформатируй его так чтобы он был читабельным...

[Andrey Fedorov]

может кто-то что-то подскажет по поводу оптимизации.

Изучить Help по функциям DateAdd и DateDiff - они резко сократят твой код.

Ну и обзывай контролы понятней - тебе же удобней...