We help IT Professionals succeed at work.

New podcast episode! Our very own Community Manager, Rob Jurd, gives his insight on the value of an online community. Listen Now!


Is this spec enough for a developer or is it just blabble ?

Last Modified: 2017-03-07
I am hoping to post the following to a few freelancer boards and wanted to get some feedback before i do.  I am not a developer, just an averagely struggling powershell learner.  I have a personal project in which i have hit more of a mountain in front of me than a bump in the road.  I think it is beyond me and so I need to get this coded.

Would people mind having a look for me and let me know if it is enough, if i am missing some major points or if i am saying too much?

Other information might also prove useful to me like where the best places are to post this spec and how much it might cost me.


Proposals and quotes are invited, qualified by proving your knowledge applied to the following expression  
[string](0..24|%{[char][int](44+("71727353707202567077565766110920596553616402556765").substring(($_*2),2))}) -replace " "

Open in new window

Using Powershell, a function returning an SQL insert query, starting with an input of the  following $object as an example, converted from a $json string as follows:
$json = '{ "timestamp":"2017-03-06T01:08:21Z", "event":"Docked", "StationName":"Patterson Enterprise", "StationType":"Coriolis", "StarSystem":"Sirius", "StarPos":[-24.875,-9.563,96.000], "StationFaction":"Sirius Free", "FactionState":"", "StationGovernment":"$government_Democracy;", "StationGovernment_Localised":"Democracy", "StationAllegiance":"Federation", "StationEconomy":"$economy_Industrial;", "StationEconomy_Localised":"Industrial" }'

$ErrorActionPreference = "SilentlyContinue"
$json = $json.Replace(':true,', ':"1",')
$json = $json.Replace(':false,', ':"0",')
$json = $json.Replace('$government_', 'government')
$json = $json.Replace('$SYSTEM_SECURITY_', 'SYSTEM_SECURITY')
$json = $json.Replace(';', '')
$json = $json.Replace('$economy_', 'economy')

$object = ConvertFrom-Json($json)

Open in new window

In order to generate an SQL Query from the $object you need to be able to build the query from listing the noteproperty names and separately the values of them but be able to correspond them within the string.  An SQL query lists the column names together and then the values together..

Please especially note the FactionState field in the JSON string above. Sometimes the JSON will have multiple fields with no value set in no predictable combination and so convertfrom-json will add a noteproperty of that name with a null value.  These can NOT appear in the output query string as when sending to SQL Server it will put a blank in the field replacing it’s null.

This function needs to be able to process any valid JSON, that of course via ConvertFrom-Json will have different collections of NoteProperty members specific to that type.  Please note further on, the processing of nested objects is specified. Therefore the function is ‘in the blind’ and needs to step through the NoteProperty members and potentially nested values, checking they are not null and then working on something like  (and only as an example) the following string accommodating any commas or other punctuation characters (except backtick escapes) in the values as literal to be passed within the following output to an SQL query generator.

Nested values within the NoteProperty (as in the JSON example) can follow any nested format.  If there is a limit to the amount of nesting levels then please advise but 1 level is essential and 2 levels deep preferable. Preference will be given to anyone that can produce code that is effective at handling any depth for complete JSON handling with no bounds. The nested naming convention can be anything you like but must include a way of interpreting the nesting path, for example as in JSON, StarPos has 3 nested values so would be StarPos_1 through StarPos_3.  Deeper nesting would be along the lines of StarPos_1_2_1.  Maybe you have a better way to suggest but it has to be human interpretable.
$data = ‘timestamp, 2017-03-06T01:08:21Z, event, Docked, StationName, Patterson Enterprise, StationType, Coriolis, StarSystem, Sirius, StationFaction, Sirius Free, FactionState, StationGovernment, government_Democracy, StationGovernment_Localised, Democracy, StationAllegiance, Federation, StationEconomy, economy_Industrial, StationEconomy_Localised, Industrial’

Open in new window

As an example, during the middle processing of the function the SQL query could be generated somehow to achieve the valid SQL query output result such as in the following very approximate example which would be modified somehow to include all columns and values applicable in $data (a blind process so has to be able to detect how many are present and not null)

Please note that $SQLEventTableName is declared above the function in the global scope already. So set in your mind $SQLEventTableName = ‘event’

You might need to clear up my horrible backticks here. (it’s just an example)
$SQLQuery = 'INSERT INTO ' + $SQLEventTableName + "($data[0], $data[2], $data[4],$data[6],$data[8],$data[10],$data[12],$data[14],$data[16],$data[18],$data[20],$data[22],$data[24],$data[26],) VALUES(`'" +$data[1], + "', '" + $data[3], + "`', `'" + $data[5], + "', '" + $data[7], + "', '" + $data[9], + "', '" + $data[11], + "', '" + $data[13], + "', '" + $data[15], + "', '" + $data[17], + "', '" + $data[19], + "', '" + $data[21], + "', '" + $data[23], + "', '" + $data[27], + "', '" + $data[29], + "`');"

Open in new window

This would produce the end result return from the function of
INSERT INTO event (timestamp, event, StationName, StationType, StarSystem, StarPos_1, StarPos_2, StarPos_3, Faction, Allegiance, Economy, Government, Security) VALUES ('2017-03-06T01:08:21Z', 'Docked', 'Patterson Enterprise', 'Coriolis', 'Sirius', ‘-24.975’, ‘-9.563’, ‘96.000’, ’Sirius Free’, ‘Democracy’, ‘Democracy’, ‘Federation’, ‘Industrial’, ‘Industrial’ );

Open in new window

Please forgive any strict mistakes - this has been written as an example only.  I am hope this will be enough information as to the requirements and look forward to hearing from you.

Kindest regards !
Watch Question

Automation solutions architect & senior technical support engineer
Unlock this solution and get a sample of our free trial.
(No credit card required)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.