Basic SQL Commands

[rgpepper]

We have the Database connect module.
I'm not finding any examples of using INSERT INTO to send data to a SQL database from Switch (including the reference manual). Can anyone give me an example statement of inserting say 5 fields into a table (named Transactions)? The database name is defined in the SQL data source in the System DSN.

What I have:

INSERT INTO Transactions ('FileName', 'Salesperson', 'Email', 'SecondEmail') VALUES (`[Job.Name],[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML",Before="@"]`, `[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML"]`, `[Metadata.Text:Path="/field-list/field[2]/value",Dataset="Submit",Model="XML"]`)

The errors I'm getting seem to be format related - complains about "incorrect syntax near "`"" or "...`Transactions`".

[lombert]

It should be;

INSERT table_name SET key1 = 'value1' , key2 = 'value2' ... ...

See this; viewtopic.php?f=12&t=1405

[sander]

True. In addition to my other topic reply.
I'm heavily using this one, like a couple of 100 times a day when I create new carrier labels. Haven't failed once

Code: Select all

update SwitchTransport
set TrackTraceURL='[Job.PrivateData:Key="TrackingURL"]', TrackTraceCode='[Job.PrivateData:Key="Barcode"]'
where ID=[Metadata.Text:Path="//ID",Dataset="Xml",Model="XML"]

[rgpepper]

And this is strictly using the Database connect module, and a SQL statement - no scripting? I'm not having any success.

[lombert]

Yes. Is the SQL on the local computer? Can you write the data to the database from any sql-program at the switch-computer?

At the switch-computer you need to have an 32bit connector SQL -> ODBC.

Then you need to use a "ODBC Administrator" there you setup the connection between the database and the switch-computer (even if there is on the same computer). (Can't connect to my Switch now, so I can't make any screenshots.)

Then you configure Switch to talk to the ODBC Administrator.

Then you should connect throw Switch to SQL with the command I and Sander wrote.

[rgpepper]

Check on all of the above (well, the MS SQL server is external but I went through all the setup stuff and it configured just fine and I get a SQL error back as I mentioned in my original post), except I can't make the insert command work. Can someone please comment on my original query:


INSERT INTO Transactions ('FileName', 'Salesperson', 'Email', 'SecondEmail') VALUES (`[Job.Name],[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML",Before="@"]`, `[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML"]`, `[Metadata.Text:Path="/field-list/field[2]/value",Dataset="Submit",Model="XML"]`)

Transactions is the name of the table. All the data I'm working with is being pulled with the "Multi-line text with variables defined" dialog in Switch.

[lombert]

Then is should look like this;

INSERT Transactions SET FileName = `[Job.Name]' , Salesperson = '[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML",Before="@"]`, Email = `[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML"]`, SecondEmail = `[Metadata.Text:Path="/field-list/field[2]/value",Dataset="Submit",Model="XML"]'

Everything as one line.

[rgpepper]

I copied and pasted your statement, nada. "Incorrect syntax near the keyword `SET`" and "unclosed quotation mark after the character string "`" "

Switch 13u1, SQL 12.

[lombert]

You need to change all " ' " , it looks like they are not the same..

[rgpepper]

Are they supposed to be single quotes (apostrophe's) or the acute character - same key as tilde?

[lombert]

Copy that in Sander's code. Must be the right one..

[rgpepper]

This worked:

INSERT INTO Transactions
VALUES ( '[Job.Name]' , '[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML",Before="@"]', '[Metadata.Text:Path="/field-list/field[1]/value",Dataset="Submit",Model="XML"]', '[Metadata.Text:Path="/field-list/field[2]/value",Dataset="Submit",Model="XML"]');

[lombert]

Good!

But strange. Mabe not the same if it is MySQL or MS-SQL? I have MySQL..