asked on

Single quotes and tick quotes within strings.

Tried this in all sorts of combinations. I get varied results from errors to losing the powershell prompt which does not return until powershell restart.

I have a SQL query inside a string that i am successfully passing to an SQL server so all working ok there. Unfortunately it overwrites the nulls with blanks. Problem is that in the generated SQL query there are properties within an object that are not predictable if they are set or not.

The simplest way of getting around this is to remove any parts of the string that are like , '' (that is comma space 2 single quotes)

I have used the replace method lots of times to good effect but because of the single quotes i need to remove i have to use tick quotes so they get processed literally.

here follows the generated SQL query as a variable and then using the replace method - a typical code example as i understand it, but in this case as it crashes the powershell session prompt and nothing after responds.

C:\Users\Stuart\Desktop\Amy-EVA3 [master = +0 ~1 -0 !]> $SQLQuery                                                                                                                   
INSERT INTO (timestamp, event, StationName, StationType, StarSystem, Faction, FactionState, Allegiance, Economy, Government, Security) VALUES('2017-03-06T01:08:21Z', 'Docked', 'Pat
terson Enterprise', 'Coriolis', 'Sirius', '', 'Boom', '', '', '', '');                                                                                                              

C:\Users\Stuart\Desktop\Amy-EVA3 [master = +0 ~1 -0 !]> $SQLQuery = $SQLQuery.replace(', `'`'' , '')  
>>                                                                                                    
>>                                                                                                    
>> ls                                                                                                 
>>

Open in new window

as you can see in the last 4 lines powershell has crashed to a >> with no response to any commands

How can i get rid of the null values in the $SQLQuery above, replacing the [comma space single quote single quote] with nothing ?

the backticks are tricky !! - the parameters i used can be described in their 'value', 'value' format like this:
(whenever i say quote i mean single quote)

( quote comma space tick quote tick quote quote , space quote quote ) (', `'`'' , '')

Thanks :)

ASKER CERTIFIED SOLUTION

oBdA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Stuart Dryden

ASKER

Perfect !

worked first time.

thanks again for your help time and time again.... im getting there

footech

In fact, you don't even need the backticks in that last one.

Stuart Dryden

ASKER

nobody spotted the deliberate mistake here ;)

now of course i am getting the error:

"There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement."

this is getting crazy - i am going to have to completely re-write my query generator functions to 10 times the complexity multiplied by a few hundred event types.

is there a way to pipe the names of the not null noteproperties in to the query string and then take it in 2 halves so after that handle the values.

I got a real sinking feeling on this one if there is no code efficient way of doing it and i have to bespoke every query generator chunk by chunk to all possible variations according to the spec / schema of the json event.

hitting head on table hard right now.

Stuart Dryden

ASKER

Do i open a new question or is that kind of HHEEEEEELLLLLPPP !!! too much ?

oBdA

Why don't you change the query generator so that it returns a query string that only contains the columns that actually have values?

Stuart Dryden

ASKER

They are unpredictable. you have probably realised by now by the JSON that it is Elite: Dangreous. As you travel to different places for example the event type Location - some have a government type where an anarchy system has no government. You can never predicct what combination of JSON properties will occur inside each event type. My SQL table is almost 300 Columns and growing. I know that is inefficient but these are only temporary records. events are further sorted in to their permanent tables by triggers. The events table is kind of the incoming data table which is why it has to be all accommodating.

Stuart Dryden

ASKER

oBdA, sorry re-read your comment, yes that is what i am trying to do but i hope by one piece of mutual code. I think it needs to detect which properties are not null, pipe those names to a variable. pipe the corresponding values to another variable. then i can construct a query generator to insert the field names from the first and the matching positioned values from the second. I am getting better at powershell all the time and try to reach beyond 2 dimensional programming but this for the moment is beyond me unless i can be given some direction. I take that and play around until i achieve new understanding.

Stuart Dryden

ASKER

Going to play with this a clarify an exact new question. thanks for your help on this :) all done here.