Sidehistorik
This little piece of code takes my 600+ news articles (and attachment) and injects the inty them into a MySQL database - in this case Joomla; but is could also be WordPress or Drupal
The code has been used with success, but may need some tweaking at the moment
Kodeblok |
---|
Function RunSQL(sql)
Const adOpenStatic = 3
Const adUseClient = 3
Const adLockBatchOptimistic = 4
'On Error Resume Next
Dim RS
Set RS = CreateObject("ADODB.Recordset")
RS.CursorLocation = adUseClient
RS.Open sql,"uid=root;pwd=******;server=moserver.mos-eisley.dk;driver={MySQL ODBC 5.1 Driver};database=joomla;dsn=;",adOpenStatic,adLockBatchOptimistic
If Err.Number <>0 Then
If Err.Number=-2147217900 AND InStr(LCase(sql),"ipageid='")>0 Then Response.Redirect("/Common/Error/iis/404b.asp")
Response.write "<b>SQL Error: <font color=red>" & Err.Number & " - " & Err.Description & "</b><br><br><font color=blue>" & sql
Response.write "<br><br>"
Response.write "<center><input type=button class=Button value=""Close"" onclick=""self.close();""> <input type=button class=Button value=""Go Back"" onclick=""history.go(-1);""></center>"
Set RS.ActiveConnection = Nothing
Response.End
End If
Set RS.ActiveConnection = Nothing
Set RunSQL = RS
On Error Goto 0
End Function
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Set fso = CreateObject("Scripting.FileSystemObject")
Set wshShell = WScript.CreateObject ("WSCript.shell")
'Define the WebService URL from Sharepoint
SiteURL="http://sp.mos-eisley.dk"
ViewID="615491DF%2D61B4%2D41BE%2DB447%2D4BFC1306957C"
ListID="C333569C%2DDEAD%2D495F%2D8959%2DEEE87FE6EDF8"
LookupURL = SiteURL & "/_vti_bin/owssvr.dll?CS=109&XMLDATA=1&RowLimit=0&List=%7B" & ListID & "%7D&View=%7B" & ViewID & "%7D%"
Set Req = CreateObject("MSXML2.XMLHTTP")
Req.open "GET",LookupURL,false,"Administrator","******"
Req.send()
Set Doc = CreateObject("Msxml2.DOMDocument.5.0")
Set Doc2 = CreateObject("Msxml2.DOMDocument.5.0")
Doc.loadXML(Req.ResponseText)
Set Data = doc.documentElement.childNodes(1)
NumOfElements=Data.childNodes.length
isFirst=True
'On Error Resume Next
Id=89
LastMexLeft=
'For Each Node in Data.childNodes
For i=0 to NumOfElements
Title = data.childNodes(i).attributes.getNamedItem("ows_Title").value
Created = data.childNodes(i).attributes.getNamedItem("ows_Created").value
Body = data.childNodes(i).attributes.getNamedItem("ows_Body").value
'Change the Data to suit Joomla rules and Joomla Schema values
Title=Replace(Title,"""","")
Title=Replace(Title,"?","")
Title=Replace(Title,"/","")
Title=Replace(Title,"<","")
Title=Replace(Title,">","")
Title=Replace(Title,":"," ")
Title=Replace(Title,";"," ")
Title=Replace(Title,"""","\""")
Title=Replace(Title,"'","\'")
TitleSlug=Replace(Title,"(","")
TitleSlug=Replace(TitleSlug,")","")
TitleSlug=Replace(TitleSlug,"!","")
TitleSlug=LCase(Replace(TitleSlug," ","-"))
Body=Replace(Body,"""","\""")
Body=Replace(Body,"'","\'")
ar1=split(Created," ")
ar2=split(ar1(0),"-")
sDay=ar2(2)
sMonth=ar2(1)
sYear=ar2(0)
PostDate=sYear & "-" & sMonth & "-" & sDay & " 10:00:00"
PostDateGMT=sYear & "-" & sMonth & "-" & sDay & " 08:00:00"
'WordPress inject
'sql ="INSERT INTO `wordpress`.`wp_posts` (`ID`,`post_author`,`post_date`,`post_date_gmt`,`post_content`,`post_title`,`post_excerpt`,`post_status`,`comment_status`,`ping_status`,`post_password`,`post_name`,`to_ping`,`pinged`,`post_modified`,`post_modified_gmt`,`post_content_filtered`,`post_parent`,`guid`,`menu_order`,`post_type`,`post_mime_type`,`comment_count`) VALUES (" & Id & ",1,'" & PostDate & "','" & PostDateGMT & "','" & Body & "','" & Title & "','','publish','open','open','','" & TitleSlug & "','','','" & PostDate & "','" & PostDateGMT & "','',0,'http://wp.mos-eisley.dk/?p=" & Id & "',0,'post','',0);"
'RunSQL(sql)
'sql="INSERT INTO `wordpress`.`wp_term_relationships` (`object_id`,`term_taxonomy_id`,`term_order`) VALUES (" & Id & ",3,0);"
'RunSQL(sql)
'Joomla inject
sql="INSERT INTO joomla.jos_content(asset_id,title,alias,title_alias,introtext,state,sectionid,mask,catid,created,created_by,created_by_alias,modified,modified_by,checked_out,checked_out_time,publish_up,publish_down,images,urls,attribs,version,parentid,ordering,metakey,metadesc,access,hits,metadata,featured,language,xreference) VALUES (" & Id & ",'" & Title & "','" & TitleSlug & "','','" & Body & "',1,0,0,7,'" & PostDate & "',42,'','" & PostDate & "',42,0,'0000-00-00 00:00:00','" & PostDate & "','0000-00-00 00:00:00','','','{\""show_title\"":\""\"",\""link_titles\"":\""\"",\""show_intro\"":\""\"",\""show_category\"":\""\"",\""link_category\"":\""\"",\""show_parent_category\"":\""\"",\""link_parent_category\"":\""\"",\""show_author\"":\""\"",\""link_author\"":\""\"",\""show_create_date\"":\""\"",\""show_modify_date\"":\""\"",\""show_publish_date\"":\""\"",\""show_item_navigation\"":\""\"",\""show_icons\"":\""\"",\""show_print_icon\"":\""\"",\""show_email_icon\"":\""\"",\""show_vote\"":\""\"",\""show_hits\"":\""\"",\""show_noauth\"":\""\"",\""alternative_readmore\"":\""\"",\""article_layout\"":\""\""}',1,0,1,'','',1,1,'{\""robots\"":\""\"",\""author\"":\""\"",\""rights\"":\""\"",\""xreference\"":\""\""}',0,'*','');"
RunSQL(sql)
sql="INSERT INTO joomla.jos_content (parent_id, lft, rgt, level, name, title, rules) VALUES (0,LastMexLeft,LastMexLeft+1,3,'com_content.article." & Id & "',Title,'{"core.delete":[],"core.edit":[],"core.edit.state":[]}')
LastMexLeft=LastMexLeft+2
Id=Id+1
Next
'Set MyFile2 = fso.OpenTextFile("xxx.txt", ForWriting, True)
'MyFile2.write Req.ResponseText
'MyFile2.close
|