{"id":35,"date":"2005-10-31T17:18:24","date_gmt":"2005-10-31T22:18:24","guid":{"rendered":"http:\/\/www.tigoe.com\/pcomp\/code2\/category\/picbasic-pro\/35"},"modified":"2008-01-21T21:34:10","modified_gmt":"2008-01-22T02:34:10","slug":"xport-to-sql","status":"publish","type":"post","link":"https:\/\/www.tigoe.com\/pcomp\/code\/picbasic-pro\/35\/","title":{"rendered":"Xport to SQL"},"content":{"rendered":"

This program connects a microcontroller to a mySQL database via a Lantronix Xport and a PHP script.
\nThe PHP script it uses is the SQL_datalog.php script in the Network Data Logging Suite<\/a>.<\/p>\n

<\/p>\n

Technorati Tags: networked objects<\/a>, networks<\/a><\/p>\n


\n
\nThe relevant Xport settings for this application are as follows:<\/p>\n

\n*** Channel 1\nBaudrate 9600, I\/F Mode 4C, Flow 00\nPort 10001\nRemote IP Adr: --- none ---, Port 00000\nConnect Mode : D4\nDisconn Mode : 00\nFlush   Mode : 00\n\n*** Expert\nTCP Keepalive    : 45s\nARP cache timeout: 600s\nHigh CPU performance: disabled\nMonitor Mode @ bootup : enabled\nHTTP Port Number : 80\nSMTP Port Number : 25\n<\/pre>\n
The code:<\/p>\n
\n' Xport_to_SQL\n'    Microcontroller is connected to a Lantronix Xport\n'    serial-to-ethernet device. This program connects\n'    to a HTTP server through the Xport, makes a HTTP GET\n'    request for a PHP script, and sends the value\n'    of an analog sensor through as a part of the\n'    GET request.\n    \n'    Xport communicates at 9600-8-n-1 non-inverted (true) serial.\n \n'    By Tom Igoe, 31 oct 2005\n'    updated 25 Oct. 2005\n\n ' Define ADCIN parameters\nDEFINE  ADC_BITS        10     ' Set number of bits in result\nDEFINE  ADC_CLOCK       3         ' Set clock source (3=rc)\nDEFINE  ADC_SAMPLEUS    50        ' Set sampling time in uS\n\n'   pins to define serial RZX and TX\ntx var portc.6\nrx var portc.7\n'   pins with LEDs to indicate whether we're connecting\n'   or sending an HTTP request:\nhttpPin var portb.7\nTCPPin var portb.6\noutput TCPPin\noutput httpPin\n\n' constant to set the baud rate:\ninv9600 con 16468\n'   for non-inverted connections (PIC to Xport direct):\ntrue9600 con 84\n\n' define variables:\nadcVar var word\ninByte var byte\nconnected var bit\n\nTRISA = %11111111       ' Set PORTA to all input\nADCON1 = %10000010      ' Set PORTA analog and right justify result\n\nconnected = 0\n\n' pause to let Xport boot up:\npause 2000\n main:\n  ' if you're connected to the server, then \n  ' make a HTTP call.  If not, then connect\n  ' to the server:\n  if connected = 1 then\n  ' read sensors, convert to bytes:\n      adcin 0, adcVar\n      ' send HTTP GET request for php script:\n    gosub http_request\n  else\n    ' attempt to connect to the server:\n    gosub xport_connect\n  endif\n  tcpPin = connected\n  ' pause so we're not overwhelming the server:\n  pause 3000  \ngoto main\n\n\nxport_connect:\n    ' turn off LED to indicate HTTP GET is in progress:\n    'low tcpPin\n    '   wait for a \"C\" byte to come back:\n    while inByte <> 67\n        serout2 tx, true9600, [\"C128.122.253.189\/80\", 10]\n        serin2 rx, true9600, [inByte]\n    wend\n    ' turn on TCP pin to indicate that we connected:\n    'high TCPPin\n    connected = 1\nreturn\n\nhttp_request:\n    ' light LED to indicate HTTP GET request in progress:\n    high httpPin\n    SEROUT2 TX, true9600, [\"GET \/~tqi6023\/sql_datalog.php?action=insert&\"]\n    serout2 tx, true9600, [\"sensorValue=\", DEC adcVar]\n    serout2 tx, true9600, [\" HTTP\/1.1\", 10]\n    serout2 tx, true9600, [\"HOST: itp.nyu.edu\", 10, 10]\n    ' wait for bytes from server:\n    ' php script sends a 0 to end transmission:\n    while inByte <> 0\n        serin2 rx, true9600, [inByte]\n    wend\n    ' now we're disconnected:\n    connected = 0\n    ' turn off LED, since GET request is complete:\n    low httpPin\nreturn \n<\/pre>\n","protected":false},"excerpt":{"rendered":"
This program connects a microcontroller to a mySQL database via a Lantronix Xport and a PHP script.The PHP script it uses is the SQL_datalog.php script in the Network Data Logging Suite<\/a>.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,10],"tags":[],"class_list":["post-35","post","type-post","status-publish","format-standard","hentry","category-Lantronix","category-picbasic-pro"],"_links":{"self":[{"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/posts\/35","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/comments?post=35"}],"version-history":[{"count":0,"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/posts\/35\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/media?parent=35"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/categories?post=35"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/tags?post=35"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}