I want to write a little progam to insert tickets by selecting a Mail in the
Outlook Client and writing it into the otrs database
The ado connection already works very good -- also with blob data so that I
could insert a row into article attachments
I try to
insert a Ticket
+ an Article with the related Ticked Id
A the moment i find the ticket when i go to the search page and search for
the Ticketnumber.
But the Ticket is totally empty:
All the ticket information is empty even the Ticketnumber is empty
Do I have to update also othe tables ??
Maybe you can give me some help on this ---
P.S I definitly cannot use pop3 to get the mails
Here's my code -- I simply added it to a module in Microsoft Excel and added
a Reference to Microsoft ADO
Private Sub myodbc_ado_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim fld As ADODB.Field
Dim mystream As ADODB.Stream
Dim sql As String
'connect to MySQL server using MySQL ODBC 3.51 Driver
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=localhost;" _
& " DATABASE=otrs;" _
& "UID=otrs;PWD=yourpw; OPTION=3"
conn.Open
'create table
conn.Execute "DROP TABLE IF EXISTS my_ado"
conn.Execute "CREATE TABLE my_ado(id int not null primary key, name
varchar(20)," _
& "txt text, dt date, tm time, ts
timestamp)"
'direct insert
conn.Execute "INSERT INTO my_ado(id,name,txt) values(1,100,'venu')"
conn.Execute "INSERT INTO my_ado(id,name,txt) values(2,200,'MySQL')"
conn.Execute "INSERT INTO my_ado(id,name,txt) values(3,300,'Delete')"
Dim SQLSTR As String
SQLSTR = "INSERT INTO
ticket(tn,queue_id,ticket_lock_id,ticket_answered,user_id,group_id," & _
"ticket_priority_id,ticket_state_id) values " & _
"('111111',4,1,0,2,1,3,2)"
Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Dim myticketid As Integer
Dim myArticleid As Integer
rs.CursorLocation = adUseServer
rs2.CursorLocation = adUseServer
rs.Open "select * from ticket", conn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs!tn = "2005073010000069"
rs!queue_id = 4
rs!ticket_lock_id = 1
rs!ticket_answered = 0
rs!user_id = 2
rs!group_id = 1
rs!ticket_priority_id = 3
rs!ticket_state_id = 2
rs!valid_id = 1
rs!create_time = "2003-03-03 00:00:00"
rs!create_by = 1
rs!change_time = "2003-03-03 00:00:00"
rs!change_by = 1
rs.Update
rs.Close
myticketid = conn.Execute("select last_insert_id() AS U")("U")
rs.Open "select * from article", conn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs!ticket_id = myticketid
rs!article_type_id = 1
rs!article_sender_type_id = 1
rs!a_from = "OTRS Feedback <[EMAIL PROTECTED]>" 'Null allowed
rs!a_reply_to = "" 'Null allowed
rs!a_to = "linseish" 'Null allowed
rs!a_cc = "" 'Null allowed
rs!a_subject = "Test" 'Null allowed
rs!a_message_id = ""
rs!a_content_type = "text/plain; charset=iso-8859-1"
rs!a_body = "11"
rs!incoming_time = 1055926356
'rs!content_path='2003/06/18'
rs!valid_id = 1
rs!create_time = "2003-03-03 00:00:00"
rs!create_by = 1
rs!change_time = "2003-03-03 00:00:00"
rs!change_by = 1
rs.Update
myArticleid = conn.Execute("select last_insert_id() AS U")("U")
rs.Close
Set mystream = New ADODB.Stream
mystream.Type = adTypeBinary
mystream.Open
mystream.LoadFromFile "c:\otrs.sql"
rs.Open "select * from article_attachment", conn, adOpenDynamic,
adLockOptimistic
rs.AddNew
rs!article_id = myArticleid
rs!Filename = "test"
rs!content_type = "text"
rs!content = mystream.Read
rs!create_time = "2003-03-03 00:00:00"
rs!create_by = 1
rs!change_time = "2003-03-03 00:00:00"
rs!change_by = 1
rs.Update
rs.Close
mystream.Close
conn.Close
End Sub
--
GMX ProMail (250 MB Mailbox, 50 FreeSMS, Virenschutz, 2,99 EUR/Monat...)
jetzt 3 Monate GRATIS + 3x DER SPIEGEL +++ http://www.gmx.net/derspiegel +++
_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
