SQL'ing into Micros

[kevgais]

Hi there,

Does anyone have any experience of using any other software tools to import/export data in and out of Micros??

I'm trying to UPDATE a range of MI Numbers using SQL but am not having much luck.

I currently write the SQL script in notepad save it as a .sql file then call it from NUTCRACKER using this command:

8700sql -eaf update.sql

How do other people get their data in and out of Micros??

Any Pointers greatly appreciated.

 

[Shift4SMS]

I'm not certain NUTCRACKER is required, but I think you are on the right track. Good luck.

Steve Sommers
Shift4 Corporation --

http://www.shift4.com

Creators of $$$ ON THE NET(tm) payment processing services

 

[Wildbar]

Use dbisql. It is far the easiest.
Jeff

 

[kevgais]

sorry, forgot to mention that it is 9700 I'm using.

 

[regilobo]

You are on the right track...8700sql is the way to get data in/out of the MICROS db. It's helpful not to think of it in the same way you'd normally think of using SQL to manipulate data in a normal db because MICROS doesn't use standard SQL for 8700/9700. 8700sql is more of an import/export tool...usually you end up creating an intermediary file to operate on from an external tool, then use 8700sql to import/update that back into the database.

The basic procedure is usually to Export Data to a flat ASCII file. Manipulate that data using some tool (use a scripting language,Excel, whatever can read a CSV).
Then import that file back into the db using the UPDATE command.

It sounds like you want to do something like the following in a shell script:
[ol]
[li]Export the midef file

This should output numbers 1-100 of the midef table in RVC 1:

# Build up the command file
echo "OUTPUT_TO midef.out" > /micros/les/pos/9700/sql/cmd_file
echo "FIELD_SEPARATOR , " >> /micros/les/pos/9700/sql/cmd_file
echo "SELECT number,name_1,def_seq_num FROM mi_def.1 WHERE number >= 1 AND number <= 100" >> /micros/les/pos/9700/sql/cmd_file
# Execute the command file
/micros/les/pos/9700/bin/8700sql -e -a -f /micros/les/pos/9700/sql/cmd_file

[/li]
[li]Now you have midef.out which contains your records. You'll need to make your changes to this file.[/li]

[li]Use the modified file as the INPUT_FROM argument in your update:

[tt]
# Build up the command file
echo "INPUT_FROM midef.out" > /micros/les/pos/9700/sql/cmd_file
echo "FIELD_SEPARATOR , " >> /micros/les/pos/9700/sql/cmd_file
echo "update * into mi_def.1" >> /micros/les/pos/9700/sql/cmd_file
# Execute the command file
/micros/les/pos/9700/bin/8700sql -e -a -f /micros/les/pos/9700/sql/cmd_file
[/tt]

[/li]
[/ol]
When you say you aren't having much luck, what is happening?

 

[kevgais]

thanks regilobo.

I already use the input_from statement to update a whole table but what I want to do is UPDATE a range of records say the first 50,000 records.

I would imagin the script to look like this.

INPUT_FROM data.txt
UPDATE * mi_master_def WHERE number >= 1 AND number <= 50000

thanks for any advice you can give.

Kevin.

 

[regilobo]

You've probably got it by now, but I think that should work. You should be able to use the WHERE clause with an UPDATE.

INPUT FROM data.txt
UPDATE * INTO mi_master_def.1 WHERE number >= 1 AND number <= 50000

or you could just make sure that when you generate or modify data.txt, that it only contains the records 1-50000 and then:

INPUT FROM data.txt
UPDATE * INTO mi_master_def.1

 

[kevgais]

Hi regilobo,

thanks for the reply. unfortunaly I am still having issues with this.

It would appear that 9700 doesn't support the WHERE command when using it with the INPUT command, maybe 9700 does support it but I cannot get it to work.

your suggestions of splitting the file is not really an option due to our set up and would make things a lot more complicated.

do you have any other suggestions?

Regards
KEvin.

 

[regilobo]

What issues are you having? Do you get an error message? I don't have a 8700/9700 box to test with right now, but the docs indicate the WHERE clause should be acceptable.

Try adding the -t argument to the 8700sql command to generate debugging output:

/micros/les/pos/9700/bin/8700sql -e -a -f /micros/les/pos/9700/sql/cmd_file -t

That should create an 8700sql.log file in /micros/les/pos/9700/etc (I think). See if that gives you any extra info. Otherwise I think you'll have to figure out how to manage your ranges via selective exports and multiple files. But this doesn't have to be too difficult...I would think you could automate it using some shell scripting. i.e build upon the ideas in my first post to see how you can integrate your processes that modify the data files.

Maybe you could give some more detail on what you are trying to do?

 

[kevgais]

thanks regilobo.

The 8700.log contained no additional information. Just the usual:

"Command line: 8700sql -e -a -f 2.sql -t
Command file set to 2.sql
UPDATE mi_master_def SET number = number FROM mi_ma.txt WHERE number ge 1 le 50
Command error: Invalid syntax for UPDATE command
Ending 8700sql Version 2.80.18.1 19:12:34 12/13/05 pid 2684"

Baisclly, all I'm trying to do is import a range of records from a .txt file. We have another database that generates these .txt files for the mi_master, def and price records. But that database has got large (500,000 records) when a normal update is run using:

INPUT_FROM file.txt
UPDATE * into mi_def.1

It fails. It seems to fail because the input file is too big as if I manually split the file into half it works.

It is not possible to alter the export from the database so the only way to import the txt file in stages .

Thanks for any advice

 

[regilobo]

Ok, so one way to approach this is to automate splitting of the import file using a shell script:

# Split file.txt into files of 200000 lines each
# Each file will be named INFILEaa, INFILEab, INFILEac, etc...
split -l 200000 file.txt INFILE

# Loop through each new input file
for in_file in `ls INFILE*`
do
	# Build up the command file (overwrites cmd_file each time)
	echo "INPUT_FROM $in_file" > /micros/les/pos/9700/sql/cmd_file
	echo "update * into mi_def.1" >> /micros/les/pos/9700/sql/cmd_file
	
	# Execute the command file
	/micros/les/pos/9700/bin/8700sql -e -a -f /micros/les/pos/9700/sql/cmd_file
	
	# Remove the temporary input file
	rm $in_file
done

# Remove the cmd_file
rm $in_file

You may want to comment the rm lines to see the input files that are created. I hope that helps.

 

[kevgais]

thanks,

how should excute this script?

I normally create .sql files using notepad and run them from nutcracker using 8700sql -eaf export.sql

thanks in advance.

kevin

 

[regilobo]

Put the code in a file named with an .sh extension and you can then double-click it from the desktop like a Windows batch file.

Or, just put it in a file and from Nutcracker type:

./something.sh

Also, a couple of notes:

You can interactively enter the above commands in Nutcracker
You may want to explicitly define the paths to file.txt in the script.